Database Replication with MySQL

A typical way of backing up data in a database server is retrieving a dump of the database(s). Depending on the update frequency and the criticality of the data, this kind of backup can be done, once a week, once a day or even once every hour! When you conclude that the database is to be backed up once an hour or less than an hour, then retrieving a database dump (using tools like mysqldump in case of MySQL) is not an efficient way. This kind of requirement calls for a database replication setup. With replication, you can have two database servers, running on different machines having the same data at any point in time. The main database server, where applications and users connect, issue queries, add/update or delete data is called the master. The second database where the master database is mirrored is called the slave database. We are calling it mirroring because, under ideal circumstances, the slave database will always be an exact replica of the master database.

Replication makes the slave database an exact replica of the master. This means if some incorrect data makes its way into the master database, it will also get replicated on the slave. So replication is not a solution if you are looking for database snapshots with the goal of recovering the data from an earlier snapshot in case the latest data gets erroneous. 

Let's get started with a replication setup for MySQL. For this article we assume that you are running MySQL on PCQLinux 2005 for both master and the slave databases. That is, we set up two machines with PCQLinux 2005, one will run the master database server and the other will be running the slave. 

After installing PCQLinux 2005 on both the machines, install and set up MySQL on them as per the instructions in the section 'Connect to Mysql' in the article, JDBC Drivers, page 76, March 2005, PCQuest. For our setup, the master database server was running on a machine with the IP address 192.168.1.1 and the slave was running at 192.168.1.2. 

Setting up the master

On the master machine, open the file, my.cnf, found in the directory /etc. Add the following under the
section:

log-bin=/var/log/mysqlbin.log

server-id=1

Here the server-id must be a unique ID for the MySQL server. Later we'll give a server ID of 2 to the slave database server. Issue this

mysql -u root -p

This diagramatic representation is of the database replication process

When prompted, supply the password for the MySQL root user, specified during the setup mentioned in the article, JDBC Drivers. Subsequently, you will be dropped into a mysql> prompt. At this prompt issue:

grant replication slave on *.* to repl@"192.168.1.2" identified by 'secret123'; 

Substitute secret123 with your preferred password. We'll use the username 'repl' and password 'secret123' while setting up the slave in the section 'On the slave' later. 

Take master's snapshot 

Before starting the replication process, we need to import the latest snapshot or backup of the master's databases to the slave. Subsequently, once the replication is started, the slave will automatically sync with the master. We must ensure that the no updates happen on the master while taking the snapshot. For this we'll lock all the tables, allowing only 'read only' access. At the mysql> prompt, issue:

flush tables with read lock;

If your database is live and being used by a Web application(s) or any other application, you may like to put up a 'website under maintenance' page or inform users about it. Note that queries which only read the database like the select queries, will still work. Next, quit from the mysql> prompt by typing 'quit'. 

Issue the following to take a database snapshot. 

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

When prompted, supply the password for MySQL's root user. Copy the file backup.sql to 192.168.1.2 (say using SSH or Secure SHell). Before proceeding with the slave configuration, release the read only lock. For this, get into the mysql> prompt and issue:

unlock tables;

This is also the right time to restart the database for the settings, made in the previous section, to get effective. Restart MySQL as:

/etc/init.d/mysql stop

/etc/init.d/mysql start

Your master database is back online and you may inform users to get started and remove the maintenance message from your site. 

On the slave

On the slave machine open the file, my.cnf, found in /etc directory and add the following under the
section.

server-id = 2

master-host = 192.168.1.1

master-user = repl

master-password = secret123

replicate-ignore-db=mysql

Note that we have set the server-id to 2, which must be different from the master database's server ID (which is 1 in our case). Also note that we have set the master-host to the IP address of the master machine and the master-user and master-password to the username and password that we had set up in the section 'Setting up the master'. With replicate-ignore-db we have specified not to replicate the database named mysql for the reasons mentioned in the following paragraph. 

Next, we will import the data from backup.sql file. The backup.sql file will also contain the table definition and data for the database named mysql. You may like to exclude this database because it contains the database users and access privileges for the master. You may like to have a different set of users and access privileges on the slave. To exclude this database, open the file backup.sql on the slave machine in a text editor. Scroll down to the line that says,
Current Database: `mysql`

Delete all the lines till you find the next Current Database line or upto the file's end. Delete all the SQL statements pertaining 

to the mysql database. Now import backup.sql to the slave database as:

mysql -u root -p < backup.sql

When prompted, supply the password you had set up for root MySQL user on the slave database. Restart the slave database.

Get, set, go

To start the replication process, issue the start slave command from the mysql> prompt. Next, issue:

show slave status

Check whether the output shows any errors. Issue the above intermittently to check for any errors. If you don't notice any 

error, it means the replication has been set up properly. To test, make changes (update or insert) on one of databases on the master machine. The changes will also get reflected on the slave
machine too. 

Not to forget that here you have not only configured a real-time back-up setup, but also a fault-tolerant setup. If the master machine goes down for some reason, point your application to the slave machine to resume your operations.

Shekhar Govindarajan, IT4Enterprise