Simple MySQL Master-Slave Replication

I’ve been doing a lot of research (not cutting edge type stuff) into MySQL scalability, and the first exercise I went through was configuring a simple master-slave replication setup. It was much simpler than I thought it would be. Here are the steps.

Because of the way replication works in MySQL, you will need to turn on binary logging. Essentially, the slave is going to connect to the master and request the log. After it gets the log (or the parts that it needs) it will replay it, executing the queries to bring it up to date. You also need to give the server an ID. So add these lines to the my.cnf (usually /etc/my.cnf) on the master database:

<mysqld>
log_bin = mysql-bin
server_id = 1001

You can give it any ID you want, you just want it to have a different ID than the slave. In the slave we will add a few additional lines, as well:

<mysqld>
log_bin = mysql-bin
server_id = 1002
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1

Note: Replace the angle brackets above with square brackets. I haven’t figured out how to make my code plugin not treat [mysqld] as another code block definition.

According to Schwartz, et al. in High Performance MySQL (review forthcoming), the only parameter required on a slave is the server_id. The other parameters make it easy to switch the server between being a master or a slave. They also mention the read_only parameter is a good safety precaution but might not be applicable in all cases.

Replication privileges need to be granted to the replication user on the master. If you want to be able to easily switch your server between master or slave, you can grant these privileges on both servers.

GRANT REPLICATION SLAVE
ON *.*
TO 'rep_user'@'10.0.0.%'
IDENTIFIED BY 'reppassword';

There are certain features that are accessible by granting the REPLICATION CLIENT privilege. You can also limit the replication privileges to only certain databases or tables if so desired. Though it is not recommended, you could also grant access to all hosts, just as you might in any other GRANT statement.

Now that both the master and slave are configured, and correct permissions are granted to the replication user, the slave needs to be “started”. This is done by declaring the master host, and indicating necessary credentials and log file information, and then issuing the ’start slave’ command.

The host and credential information can be declared in the my.cnf file. However there are some advantages to making the declarations as MySQL commands. Specifically you will be able to make changes to these without having to restart the daemon. Here is the command you should issue to configure the slave:

CHANGE MASTER TO MASTER_HOST='master.mysql.host',
MASTER_USER='rep_user',
MASTER_PASSWORD='reppassword',
MASTER_LOG_FILE='mysql-bin-000001',
MASTER_LOG_POS=0;

And then you issue the command to start the slave:

start slave

If you were previously using binary logs on the master, and then cloned the server to create the slave, you might have trouble getting replication started without first deleting the binary log from the slave.

Sources
The MySQL documentation
High Performance MySQL, by Baron Schwartz et al.