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

To read the complete article visit PCQuest.