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:

log-bin=mysql-bin
server-id=1

For the slave:

server-id=2

Restart each mysqld instance to effect these changes.

2. Create a replication user on the master.

mysql> GRANT REPLICATION SLAVE on *.* \
TO 'repl'@'slave.example.com' \
identified by 'slavepass';

mysql> flush privileges;

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.

mysql -h master.example.com -u repl -p

4. Get master data file and position information.

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;

5. Final server configuration on the slave.

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;

Again, notice I’ve explicitly named the master hostname.

6. Start the slave thread.

mysql> start slave;

mysql> show slave status\G;

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:

mysqldump -u root -p --all-databases --master-data >alldata.sql

Then import into the slave by copying over to the slave host and using the mysql client:

mysql -u root -p < alldata.sql
This entry was posted in System Administration on by .

About phil

My name is Phil Lembo. In my day job I’m an enterprise IT architect for a leading distribution and services company. The rest of my time I try to maintain a semi-normal family life in the suburbs of Raleigh, NC. E-mail me at philipATlembobrothersDOTcom. The opinions expressed here are entirely my own and not those of my employers, past, present or future (except where I quote others, who will need to accept responsibility for their own rants).