title: MySQL Replication on RHEL 6 link: https://onemoretech.wordpress.com/2013/08/05/mysql-replication-on-rhel-6/ author: phil2nc description: post_id: 6186 created: 2013/08/05 12:50:27 created_gmt: 2013/08/05 16:50:27 comment_status: closed post_name: mysql-replication-on-rhel-6 status: publish post_type: post

MySQL Replication on RHEL 6

Needed to set up a MySQL master/slave environment for load balacing WordPress. Rackspace has a really nice, clearly laid out article on doing this here: MySQL Master-Slave Replication You should also look this official documentation: How to Set Up Replication The Rackspace article is a lot more accessible than the official doc, which is, after all, a detailed reference manual. The former will give you an overview of what needs to be done, the latter should be helpful in answering any questions you may still have. A couple of notes on this. When it comes to MySQL configuration it is critical to get the syntax of all commands and procedures exactly right. There is no such thing as "close enough". Neither the article nor the reference doc cited above provide any advice on the how/what/where/why of monitoring replication. This is a very important topic that will need a later post of its own. The following procedure is taken mostly from the Rackpace article. 1. Edit /etc/my.cnf file. Add the following under [mysqld] on the master: [code language="bash" gutter="false"] log-bin=mysql-bin server-id=1 [/code] For the slave: [code language="bash" gutter="false"] server-id=2 [/code] Restart each mysqld instance to effect these changes. 2. Create a replication user on the master. [code language="sql" gutter="false"] mysql> GRANT REPLICATION SLAVE on . \ TO 'repl'@'slave.example.com' \ identified by 'slavepass'; mysql> flush privileges; [/code] You'd think that I've explicitly named the replication user's host for security reasons, and you'd be partly right. Another reason is to reduce the potential for mistakes, especially in an environment where a very few overworked admins are managing several dozen MySQL servers that need to talk to the correct partners. Note that I use fully qualified hostnames throughout. In an ever changing enterprise network IP addresses can, and often do, change. Using the hostname helps me avoid having to reconfigure after a network change. 3. Test connectivity from slave to master. [code language="bash" gutter="false"] mysql -h master.example.com -u repl -p [/code] 4. Get master data file and position information. [code language="sql" gutter="false"] mysql> flush tables with read lock; mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 | 106 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> unlock tables; [/code] 5. Final server configuration on the slave. [code language="sql" gutter="false"] mysql> change master to MASTER_HOST='master.example.com', MASTER_USER='repl', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=106; [/code] Again, notice I've explicitly named the master hostname. 6. Start the slave thread. [code language="sql" gutter="false"] mysql> start slave; mysql> show slave status\G; [/code] As the Rackpace article states the value you want to see in the slave status output for Slave_IO_State is 'Waiting for master to send event'. If it says anything else (e.g. "Waiting to connect'), stop the slave thread ('stop slave') and then redo steps 4 through 6. Additional Notes: If you want to set up a slave to an existing database you would follow the basic steps above with the additional task of backing up your master data and importing it into the slave between steps 4 and 5. Use mysqldump on the master host to create a master data file: [code language="sql" gutter="false"] mysqldump -u root -p --all-databases --master-data >alldata.sql [/code] Then import into the slave by copying over to the slave host and using the mysql client: [code language="sql" gutter="false"] mysql -u root -p < alldata.sql [/code]

Copyright 2004-2019 Phil Lembo