Linux * Screw

This quick manual tells how to set up database replication in MySQL. Basically it was written for 5.* MySQL versions but is also applicable for 3.23/4.0 ones (btw they are still in use, believe me).

As you might already know, replication allows you to create a copy of certain MySQL database from a master server on another server (slave). What is the most important, all updates made to that database on master server will be replicated to the database on the slave server immediately, so that both databases are synchronized almost in real time mode (if you need completely real-time synchronization/mirroring, the only solution is to deploy MySQL cluster).

One of the main issues is that replication features coming out-of-the-box with Open Source MySQL software don’t provide full back/forward compatibility. This means that you can easily replicate data from master and slave of the same MySQL versions only e.g. 5.0. But if you like to replicate database from 5.0 master to 4.0 slave (or from 3.23 master to 5.0 slave), it is not possible in most cases.

From the beginning we have two Linux boxes with MySQL installed (5.0.27 version in my example), server has database reptest we need to replicate to slave.

A. Configure Master:

Configure MySQL to accept incoming connections from another hosts in the network. In order to do it, comment the following lines in /etc/my.cnf (exact location depends on Linux distribution you use) as follows:

#skip-networking
#bind-address=127.0.0.1

and restart MySQL by “/etc/init.d/mysql restart” or “mysqladmin reload” command. Make sure that slave can access master’s MySQL via network (e.g. execute on slave “telnet <server_ip> 3306“).

The next step is to configure master to log all database changes into binary log that will be used by slave for replicating, add the following lines to /etc/my.cnf in [mysqld] section:

log_bin = mysql-bin
binlog-do-db=reptest
server-id=1

Then restart MySQL and log on to its shell with root rights:

/etc/init.d/mysql restart
mysql -u root -p
Enter password:

Type in MySQL shell the following commands:

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password';
FLUSH PRIVILEGES;

Note: If you use 4.0 MySQL or older, you need to replace REPLICATION SLAVE in above line to FILE, so the lines will look like:

GRANT FILE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password';
FLUSH PRIVILEGES;

The next commands are:

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

The last command should provide the following output we will use later on slave server:

mysql> SHOW MASTER STATUS;
+---------------+----------+-----------------+------------------+
| File          | Position | Binlog_do_db    | Binlog_ignore_db |
+---------------+----------+-----------------+------------------+
| mysql-bin.001 |   73     | reptest         |                  |
+---------------+----------+-----------------+------------------+
1 row in set (0.00 sec)

Now quit from MySQL shell as we need to prepare current dump of reptest database: quit.

Now, run from shell “mysqldump -u root -p --opt reptest > reptest.sql” and transfer reptest.sql file to slave server.

2. Configure Slave:

Create reptest database:

mysqladmin create reptest

and apply previously created/transfered dump to it via command:

mysql -u root -p reptest < /path/to/reptest.sql

Now edit /etc/my.cnf on slave and add the following lines to [mysqld] section:

server-id=2
master-host=192.168.0.1
master-user=slave_user
master-password=slave_password
master-connect-retry=60
replicate-do-db=reptest

where 192.168.0.1 is IP address of the server and server-id is unique ID assigned to slave Linux box.

Now restart MySQL with /etc/init.d/mysql restart and log on MySQL shell:

mysql -u root -p reptest
Enter password:

The next step is to apply changes saved in binary log on server:

SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='192.168.0.1', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.001', MASTER_LOG_POS=73;
SLAVE START;

Now whenever reptest is updated on the master, all changes will be replicated to reptest on the slave.

Here are useful links you can use to get more information about MySQL replication and how to configure it:

http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html
http://www.howtoforge.com/mysql_database_replication
http://www.onlamp.com/pub/a/onlamp/2005/06/16/MySQLian.html

If you’re planning to replicate on a remotely hosted machine, here are some of the best MySQL hosting plans.