MySQL master master replication on Debian 5 (Lenny)

This article is about setting up mysql master master replication between two cloud servers. The operating system which I’m going to use is Debian 5 (Lenny) Rackspace cloud base image.

Setup outline: We will have two cloud servers, named debian501 and debian502 during this exercise. Both servers have two IP addresses(one public, one private). We will configure the replication to be done using the private IP interface so that we don’t incur any bandwidth charges.

Creating the cloud servers: You need to create two Linux cloud servers, using the Debian 5 image. You can create them by logging into your cloud control panel, and spinning up two cloud servers. Choose the RAM configuration depending on your requirement of the database. Name the server accordingly so that you can easily identify. For my exercise I have named them debian501 and debian502. All the below command I’m running as a root user.

Instaling MySQL #

We need to install mysql on both the debian cloud servers. Before installing MySQL we need to run few extra commands needed for any freshly installed Debian.

Update the package database: #

#aptitude update

Install locales: #

#aptitude install locales
#dpkg-reconfigure locales

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 my case I have choose “en_GB.UTF-8”.

Now, you can run the following commands to get the MySQL installed

#aptitude install mysql-server mysql-client libmysqlclient15-dev

Enabling the replication #

After this we need to make configuration changes into each of the server to enable replication.

debian501 server

We need to create our database which we will setup for replication, and also we need to create a replication username and password. Run the following commands to set it up. Do change all the values as per your needs.

#mysql -u root -p
mysql>

Open the file /etc/mysql/my.cnf and create/update following entries:

bind-address = 0.0.0.0
server-id = 1
log-bin = /usr/local/mysql/var/bin.log
log-slave-updates
log-bin-index = /usr/local/mysql/var/log-bin.index
log-error = /usr/local/mysql/var/error.log
relay-log = /usr/local/mysql/var/relay.log
relay-log-info-file = /usr/local/mysql/var/relay-log.info
relay-log-index = /usr/local/mysql/var/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]

debian502 server

Open the file /etc/mysql/my.cnf and create/update following entries:

bind-address = 0.0.0.0
server-id = 2
log-bin = /usr/local/mysql/var/bin.log
log-slave-updates
log-bin-index = /usr/local/mysql/var/log-bin.index
log-error = /usr/local/mysql/var/error.log
relay-log = /usr/local/mysql/var/relay.log
relay-log-info-file = /usr/local/mysql/var/relay-log.info
relay-log-index = /usr/local/mysql/var/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 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, and update the configuration checking 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 any and all the 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 and then starting the service on debian502 once again. 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 in you database and application then it can cause signifient hard disk space usage in logs. So, I would recommend changing those values as per 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.

– Sandeep Sidhu