MySQL Master-Master Replication

This article is about setting up MySQL Master-Master database replication between two Cloud Servers. Master-Master data replication allows for replicated data, stored on multiple computers, to be updated by any authorized contributing member of the group. This allows for more open collaboration than Master-Slave replication where any needed changes identified by a group member must to be submitted to the designated “master” of the node.

The operating system we will use is Debian 5 (Lenny).

Setup Outline

• We will have two Cloud Servers, named debian501 and debian502 for the purpose of this exercise. Both servers have two IP addresses (one public, one private). We will configure the replication to be done over the private IP interface so that we don’t incur any bandwidth charges.

Installing MySQL

• First we need to install MySQL on both the Debian Cloud Servers. As always, prior to installing any packages, we need to make sure that our package list is up to date and our locale/language settings are configured properly.

• Update the package database:

• Install locales (optional):

• The dpkg-reconfigure locales command will bring up a locales setting window where you can choose the locales for your system depending on your country and region. In this case we have chosen en_GB.UTF-8.

• Now, you can run the following commands to install MySQL:

Configuring Replication

• Once the mysql-server package has been installed successfully, we can start configuring each of the MySQL nodes in order to enable replication between them.

• We need to create the database that will be replicated as well as the replication username and password to be used with it. You can use the commands outlined below to set them up, remembering to change all the strings/values in brackets to apply to your specific configuration.

• First on debian501, login to the mysql console (using mysql root password setup during MySQL installation).

• Now let’s create the replication user, which will be used to synchronize the changes.

 
mysql> grant replication slave on *.* to slaveuser@'[private IP of debian502]' identified by '[some password]';
mysql> flush privileges;
mysql> exit

• Do the same for debian502

 
mysql> grant replication slave on *.* to slaveuser@'[private IP of debian501]' identified by '[some password]';
mysql> flush privileges;
mysql> exit

• Back on debian501, edit /etc/mysql/my.cnf and insert/update or uncomment following entries:

 
bind-address = 0.0.0.0
server-id = 1
log-bin = /var/log/mysql/var/bin.log
log-slave-updates
log-bin-index = /var/log/mysql/log-bin.index
log-error = /var/log/mysql/error.log
 
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
 
auto_increment_increment = 10
auto_increment_offset = 1
master-host = [private IP address of debian502]
master-user = [replication username]
master-password = [replication password]
 
replicate-do-db = <database name to be replicated>

• Repeat the steps on the debian502 server

 
bind-address = 0.0.0.0
server-id = 2
log-bin = /var/log/mysql/bin.log
log-slave-updates
log-bin-index = /var/log/mysql/log-bin.index
log-error = /var/log/mysql/error.log
 
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
 
auto_increment_increment = 10
auto_increment_offset = 2
master-host =  [private IP address of debian501]
master-user = [replication username]
master-password = [replication user password]
 
replicate-do-db = [database name to be replicated]

• Now, restart both databases. If the service restart on either server fails, then please check the /var/log/mysql/error.log file for any errors. Update the configuration and check for any typos, etc.,

Testing the scenarios

• For the purpose of testing our replication setup, we can create the database specified in the configuration section above (replicate-do-db), as well as a test table on one of the nodes and watch the log files in /var/log/mysql directory. Note that all database changes should be replicated to our other server immediately.

 
mysql> create database [your-db-name];
mysql> use [your-db-name]
mysql> create table foo (id int not null, username varchar(30) not null);
mysql> insert into foo values (1, 'bar');

• An additional test is to stop the MySQL service on debian502, making database changes on the debian501 server and then restarting the MySQL service on debian502. The debian502 MySQL service should sync up all the new changes automatically.

• You should also consider changing the default binary log rotation values (expire_logs_days and max_binlog_size) in the /etc/mysql/my.cnf file, as by default all the binary logs will be kept for 10 days. If you have high transaction count on your database application then it can cause significant hard disk space usage in logs. So, we recommend changing those values to match your server backup policies. For example, if you have daily backups setup of your MySQL node then it makes no sense to keep 10 days worth of binary logs.

A full configured sample my.ini file under [mysqld] section-

# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
#log-bin = /var/log/mysql/var/bin.log
log-slave-updates
log-bin-index = /var/log/mysql/log-bin.index
log-error = /var/log/mysql/error.log

relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index

auto_increment_increment = 10
auto_increment_offset = 1
master-host = 192.169.11.183
master-user = slaveuser
master-password = q1w2e3r4

replicate-do-db = lemon
replicate-do-db = orange
replicate-do-db = banana
replicate-do-db = apple
replicate-do-db = mango
replicate-ignore-db=test
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
relay-log=mysqld-relay-bin