MySQL Tutorial: Replication

MySQL’s Community Server is one of the most popular choices for anything from small-time web hosting all the way up to huge organisations. PHP developers use it because it ties in so nicely with the basic language, with extensions such as mysqli, PDO and third-party frameworks such as Doctrine that maximise it’s potential. I’ve been using MySQL for a number of years, and am fairly comfortable trusting it for pretty much any situation that arises.

MySQL works wonderfully as a stand-alone database server, but has the flexibility so that it can be deployed in a multi-database server environment. Commonly, this is with one database server as the “master”, which holds the original copy of the data, and one or more “slaves”, which the master is responsible for keeping upto date. The following diagram illustrates how a MySQL multi-server setup plays out:

As shown above, the master server is normally used for all of the client writes to the databases, and the client will normally read from the various slave servers. In this quick tutorial, I’ll describe how to set-up a MySQL environment with one master server that is already in use, and a brand new single slave server using one database called exampledb. This presumes you’ve already got MySQL installed and working on both machines, as normally that’s a simple task of installing a package in Linux, an installation package in Windows, or a disk image in Mac OS X.

MySQL’s reference manual also some pretty cool info on replication.

Master Configuration

So first off, you need to make some configuration changes to the MySQL server that will become your master. These changes will be made on the main MySQL configuration file, commonly, on Linux/Mac at /etc/my.cnf or /etc/mysql/my.cnf. Firstly, find the following two lines and comment them out by adding a leading # character:

#skip-networking
#bind-address            = 127.0.0.1

Then add the following lines at the bottom of the file to tell MySQL where to store the log files that will be used to keep the slaves up-to-date:

server-id=1
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=exampledb

Now you need to restart MySQL to make sure it recognises itself as a master, and to trigger the new configuration changes. Normally this is done with the following command, but this may vary depending on your system:

/etc/init.d/mysql restart

Next, the slave will need to be able to authenticate with the master as a user to receive the updates it requires. This means you’ll need to login to MySQL using the client as follows:

mysql -u root -p

Make sure to type your root password when prompted. Then create a user with the privileges the slave server will need, and make sure you replace example_password with an actual password, as follows:

GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'%' IDENTIFIED BY 'example_password';
FLUSH PRIVILEGES;

We’re finished with the master server for now, but don’t shut your client because we’ll need to come back to it later on to extract the data from the master to transport to the slave. We aren’t dumping the data now because it means locking the database into read-only mode until the slave is up-to-date. It’s best to finishing setting up the slave first.

Slave Configuration

OK, now you need to log into the server that holds the MySQL installation you want to set-up as a slave server. You need to tell the server it is a slave in mostly the same way the master knows it’s a master, so add the following lines to your MySQL configuration file, changing the IP address, username and password where required:

server-id=2
master-host=192.168.2.1
master-user=replicate
master-password=example_password
master-connect-retry=60
replicate-do-db=exampledb

Then restart the server in the same way you did for the master. That’s it! The slave is now set-up and ready to receive data from the master.

Data Synchronisation

There are a few ways to transfer the initial data from the master to the slave, but I always find the easiest way is to read lock the database and make a dump of the current tables, import it into the slave and start replicating from there. Before you start this, make sure you have a client for both MySQL servers open, ready to input commands, and a separate terminal window on each server too for entering commands not in the MySQL client.

On the master, we need to read lock the tables and get the info ready for replication. To do this, lock the tables using the following commands:

USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

That will give you some information similar to the following:

+---------------+----------+--------------+------------------+

| File | Position | Binlog_do_db | Binlog_ignore_db |
+—————+———-+————–+——————+
| mysql-bin.002 | 754 | exampledb | |
+—————+———-+————–+——————+
1 row in set (0.00 sec)

Make sure you take a note of the log file AND position, as you’re going to need this info to synchronise the slave! Next, use the terminal on the master to run the following command to create a dump of the current database:

mysqldump -u root -pexample_password --opt exampledb > exampledb.sql

And then run the following command in the MySQL client:
 

UNLOCK TABLES;

This will unlock the read lock from the tables meaning your master database will operate normally again. Now, you need to copy the SQL dump file over to the slave. I’ll leave it up to you how to accomplish this, there are far too many ways to list. Once copied, create the database on the slave’s MySQL client:

CREATE DATABASE exampledb;

And use the terminal to import the dumped master data like this, replacing the password and path as needed:

mysql -u root -pexample_password; exampledb < /path/to/exampledb.sql

Now, finally, go into the slave’s MySQL client again, and stop the slave from running to update the config:

SLAVE STOP;

 Update the master configuration on the slave to the log file and position you noted above by running the following command in the client, replacing the password, IP, etc. as required:

CHANGE MASTER TO MASTER_HOST='192.168.2.1', MASTER_USER='replicate', MASTER_PASSWORD='example_password', MASTER_LOG_FILE='mysql-bin.002', MASTER_LOG_POS=754;

Now start the slave again:

SLAVE START;

And that’s it! Your slave should now be receiving data from the master, and will update whenever a change on the master is pushed to it automatically.

More Tips

A very quick tip, use the following command to check how the slave is doing in relation to the master, and to make sure there are no I/O or SQL errors that have propagated:

SHOW SLAVE STATUS\G

The \G forces the client to output the parameters line-by-line instead of in a tabular make-up, making it a lot easier to read.

If you want to add more slaves to the master, simply increment the server-id configuration you set-up, and dump the data from the master in the same format as shown above.

There are more configuration parameters you can give to the slave, such as replicate-do-table which will specify only the tables that are allowed to be replicated, instead of an entire database.

I hope you’ve found this tutorial helpful, and if, as ever, you have any comments or suggestions, please feel free to leave them!