MySQL Replication Guide

Monday, 21st November 2011 - Michael Halls-Moore - 0 Comments

I recently needed to setup MySQL replication between a master and slave. Having heard plenty of horror stories about it, as well as delegating it to a colleague at my former startup, I was apprehensive. However, it was far more straightforward than I thought it would be. No doubt I'll forget how to do it in the future, so I've decided to write up a tutorial for others to use.

I'm using the following versions of software:

  • Ubuntu Server 9.10 (Karmic Koala)
  • MySQL Server 5.1.37 (pre-built binary with Ubuntu)

Everybody says this, but I'll say it too - MySQL replication is NOT backup. Why is this so? A "DELETE" command issued on the master will also be replicated on the slave. Now imagine issuing (accidentally!) a "DROP DATABASE ****". Not the world's greatest backup plan. However, replication IS good for high availability and hardware failure.

Master Configuration

The first set of configurations will be carried out on the master machine. This is the DB that is mirrored by the slave. We need to edit the MySQL configuration file. On Ubuntu this is found in /etc/mysql/my.cnf. My personal text editor of choice is emacs, but vi, nano or similar will do:

$ sudo emacs /etc/mysql/my.cnf

It is necessary to allow external connections to the MySQL DB as by default only connections from localhost (127.0.0.1) are allowed. Find bind-address, changing the host to 0.0.0.0:

bind-address = 0.0.0.0

MySQL also needs to know which databases we want to replicate. In order for the slave to keep track of the master, it is necessary to use binary log files. We need to tell MySQL which server is the master and which is the slave, so we set the server_id. Let's assume our database is called testdb. We don't want the log file to become too big, but you can set the the max_binlog_size field depending upon your data needs. The following lines need to be modified/added to your my.cnf:

server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = testdb
max_binlog_size = 100M

After making these changes you will need to restart MySQL with the following at the terminal:

$ sudo /etc/init.d/mysql restart

Note that this may fail. It did for me! The solution was to change the ownership of the log directory to the mysql process and then restart mysql:

$ sudo chown mysql:mysql /var/log/mysql

The next step of the process is to give the slave DB the user privileges to access the master DB, so that the data can be replicated. We need to log into the mysql client as the root user and call the grant command:

$ mysql -u root -p

Once in the shell we can call the grant command where **** is replaced with a strong password of your choice. I use this password generator for mine:

mysql> GRANT REPLICATION SLAVE ON testdb.* TO 'testdb_slave_user'@'%'
IDENTIFIED BY '****';
mysql> FLUSH PRIVILEGES;

We now need to add a read lock so that the database can be backed up without any additional changes occuring while we synchronise the slave. Write the following into the MySQL shell:

mysql> USE testdb;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

SHOW MASTER STATUS provides us with the necessary information required by the slave DB in order to obtain the log files - take a note of the information. It will look somewhat like the following:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_do_db | Binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 106      | testdb       |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

The next step is to backup the database and transfer it to the slave. Leave the MySQL shell and use mysqldump to generate a SQL file of the entire DB (replace **** with your root password):

mysql> quit;

On the command line:

$ mysqldump -u root -p**** --opt testdb > testdb.sql

Copy the SQL file to your slave server, which we will make use of shortly.

The last remaining task on the master DB is to unlock the tables in the testdb database:

$ mysql -u root -p

Now at the MySQL command line prompt:

mysql> UNLOCK TABLES;
mysql> quit;

The next section outlines configuration for the slave DB.

Slave Configuration

If it hasn't already been created, then the database to replicate (in this case testdb) must be created on the slave DB:

$ mysql -u root -p

In the MySQL command line shell:

mysql> CREATE DATABASE testdb;
mysql> quit;

Now import the backed up and transferred database into MySQL (replacing **** with your root password):

$ mysql -u root -p**** testdb < /your/file/path/testdb.sql

We now need to tell the slave DB where to find the master. We need to edit the my.cnf on this machine (replace emacs with your favourite text editor):

$ sudo emacs /etc/mysql/my.cnf

Assuming the host IP address of the master is 192.168.1.5, add or modify the following lines (using the password defined above for ****):

server_id = 2
master_host = 192.168.1.5
master_user = testdb_slave_user
master_password = ****
master_connect_retry = 60
replicate_do_db = testdb

Restart MySQL for the changes to make effect. You may have to set permissions as described above:

$ sudo /etc/init.d/mysql restart

The final step is to stop the slave, add details of the master log files and restart it:

$ mysql -u root -p

On the MySQL command line:

mysql> SLAVE STOP;

Run this command on the MySQL command line (remembering to replace the values with those saved earlier):

mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.5',
MASTER_USER='testdb_slave_user',
MASTER_PASSWORD='****',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=106;

Now restart the slave:

mysql> START SLAVE;
mysql> quit;

The replication process should now be functioning. Add/modify some data in your master DB and hopefully it should be replicating in your slave DB!

0 comments ... read them below or add one
Leave a Comment