Master-Master replication of Apache/MySQL tutorial with configuration example

In this article, I will show you how to create a redundant Apache/MySQL cluster. I personally used this on two Raspberry PI to had them in redundancy and have load-balanced traffic to networkgeekstuff.com handled by both. But this tutorial is not limited to Raspberry PI and can be applied to most linux based apache/MySQL systems. It will be a “Master-Master” setup so this means none of the two servers will be considered primary and/or secondary.

To also see how to do load-balancing on Mikrotik router, see previous article I made here.

Now lets start with introducing the topology, or lets say the two servers we will load-balance on. I have called them CAIN and ABEL. And no, there is no particular reason other than they are interesting and both are 4 characters short.

Step 1) Preparing the two servers

Create a local hostname/IP resolution in the /etc/hosts file on both servers like this:

192.168.10.134 cain
192.168.10.132 abel

Step 2) Generate SSH access public keys for automated access

Next, generate ssh.pub keys between the two system so that both systems can access each other over SSH without login. This will be used by synchronization system called “unison” later.

On server CAIN:

ssh-keygen
ssh-copy-id -i .ssh/id_rsa.pub user@abel

On server ABEL:

ssh-keygen
ssh-copy-id -i .ssh/id_rsa.pub user@cain

Step 3) Install INCRON and UNISON

UNISON is a two-way synchronization project that specifically targets the limits of RSYNC. When I started researching how to do synchronization of my two servers, I started playing with RSYNC immediatelly as an obvious choice just to find out that RSYNC is in no way a good choice for “master-master” synchronization because the side that always starts the synchronization to the remote side will always assume the local files have precedence. For example if you delete a file on one of the two systems, then depending on which system you start the RSYNC first the file still existing on the other is either deleted or replicated back. So definitely a problem.  UNISON on the other hand maintains the complete state information that includes deleted files so he knows that some file should be deleted on both systems regardless from where it is started. It also uses the same algorithms like RSYNC to only transfer differences so it is also very effective to synchronize changes quickly.

INCRON is a deamon very similar to the average cron, with the difference that INCRON can be configured to watch a directory for events like file creation, file deletion, file modification and many other. Once an event is detected, it can start a specific script on demand. We will use INCRON to watch our apache web directories for changes and trigger file synchronizations with unison. There is just one single drawback, it cannot watch recursively all sub directories.

On both servers install both INCRON and UNISON, if you use debian or raspbian:

apt-get install unison incron

You can also test the unison first by synchronizing your web directories first before making it automatic with unison. The command is simple, for example this is executed on abel towards cain:

testuser@abel ~$unison -batch /var/www/networkgeekstuff \
ssh://cain//var/www/networkgeekstuff

Step 4) Create script for configuring UNISON

As mentioned, UNISON cannot watch all sub directories with a single configuration, so in order to enable synchronization of all possible directories being created/deleted, we will create a script that will always automatically configure UNISON with all sub directories of your web container. I personally use this script to only synchronize the dynamic wordpress /upload directory, not all

Put this script to a file on both servers:

#!/bin/bash
#########################################################################
# This script will generate importable incrontab table for
# tracking changes in the wordpress upload directory (all subdirectories)
#########################################################################

_syncdir="/var/www/networkgeekstuff/wp-content/uploads";
_myhostname="`hostname`";
_remotehostname="none";

# the two hostnames you are using, I am using cain and abel
hostname1="cain";
hostname2="abel";

if [[ ${_myhostname} == $hostname1* ]]; then
        echo "I am cain";
        remotehostname=$hostname2;
fi

if [[ ${_myhostname} == $hostname2* ]]; then
        echo "I am abel";
        remotehostname=$hostname1;
fi

if [[ $remotehostname != "none" ]]; then
        echo "Creating tmp file and importing it to incrontab"
        find ${_syncdir} -type d | xargs -I{} echo \
        "{} IN_CREATE,IN_DELETE,IN_CLOSE_WRITE,IN_MOVED_TO unison \
        -batch {} ssh://$remotehostname/{}" > /tmp/incrontab_to_im
        incrontab /tmp/incrontab_to_import
fi

In my case I created the script file under /home/testuser/generate_incrontab and made this file execute every night inside the normal cron job (google “crontab” for help on this very easy linux step).

The script uses hostname to automatically recognize if it it is run on abel or cain to configure the incrontab. This is one example of a single line it will generate and place into your incrontab:

/var/www/networkgeek/wp-content/uploads/2012 IN_CREATE,IN_DELETE,IN_CLOSE_WRITE,IN_MOVED_TO unison -batch /var/www/networkgeek/wp-content/uploads/2012 ssh://cain//var/www/networkgeek/wp-content/uploads/2012

Explanation, I hope you would like to read “man incrontab”, but in summary the line shown above generated by the script tells unison to wath the directory for any “CREATE”,”DELETE”,”CLOSE”,”MOVE_IN” event in the directory and will execute unison synchronizing the two exact directories. So whenever someone uploads a file to the uploads folder, it will get synchronized on the fly!

Step 5) Prepare MySQL for Master-Master replication

Ok, you have your web directory synchronized now, lets move to MySQL. A few words on theory, normal MySQL you probably have installed only can do replication from Master to Slave. So if you want only one of your server to be master for all MySQL operations, and the other server only kicking in as a backup, then this is enough for you. If you however want complete redundancy, you have to start installing MySQL cluster, that is a complete difference install package.  In this example, I will show you a nice little trick how you can make two MySQL servers doing both Master and Slave in both directions at the same time. In essence you can make the Master->Slave configuration so that for example our CAIN will be Master1/Slave2 and ABEL will be Slave1/Master2. They will replicated all changes to both instantly via network and with a little trick of auto indexing, there will be no collision even if write operations are done on both servers at the same time.

First, lets modify the mysql configuration files, usually stored at /etc/mysql/my.cfg. This is how to configure both systems, note the red parts are important to avoid collisions when writing to both masters at the same time to be as they are shown here, modify the log_bin for anything you want, binlog_do_db for your web specific database (if you use wordpress you should know the name of the database, or see wp-config.php in main directory) :

Cain: joe /etc/mysql/my.cfg

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = wordpress_data
binlog_ignore_db        = mysql
auto_increment_increment= 2    
auto_increment_offset   = 1

Abel: joe /etc/mysql/my.cfg

server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = wordpress_data
binlog_ignore_db        = mysql
auto_increment_increment= 2    
auto_increment_offset   = 2

Now restart your MySQL daemon for the configuration to take effect with /etc/inid.d/mysql restart

Step 6) Synchronize the database manually for before continuing

To be 100% sure that you start your database dynamic replication correctly, I would like you to follow this super quick guide on how to backup a databse to file, move the file and load it to the other server.

First select the server that you believe has the most recent database version and get the database to a file with this commnad:

Abel:

mysqldump -h localhost -u root -prootpassword wordpressdata > wordpressdata.sql

Cain:

testuser@cain ~#mysql -u root -p
mysql>drop database wordpress
mysql>quit
testuser@cain ~]mysql -u root -prootpassword -D wordpressdata < wordpressdata .sql

Step 7) Create Master(CAIN) to Slave(ABEL)

Now with the databases in the exact same initial state, we can create one of the servers as Master that will now be CAIN. On CAIN, create the replication username in the database.

Cain:

mysql>grant replication slave on *.* to 'replicator'@'%' \
      identified by 'replicationpassword';
mysql>reset master;

mysql> show master status;
+------------------+----------+-----------------+------------------+
| File             | Position | Binlog_Do_DB    | Binlog_Ignore_DB |
+------------------+----------+-----------------+------------------+
| mysql-bin.000001 |      107 | wordpressdata   | mysql            |
+------------------+----------+-----------------+------------------+
1 row in set (0.01 sec)

Abel:

mysql> CHANGE MASTER TO MASTER_HOST='cain', MASTER_USER='replicator', \
MASTER_PASSWORD='replicationpassword', MASTER_PORT=3306, \
MASTER_LOG_FILE='mysql-bin.000001', \
MASTER_LOG_POS=107, MASTER_CONNECT_RETRY=10;
mysql> start slave;

To verify your slave status, use the following command on slave:

mysql> show slave status;

In the output, you can look for these parts in the table and both should be “Yes”.
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

I also encourage you to make some small tests manually inside the database, you can maybe create a simple table and see if it has been replicated from CAIN to ABEL and so on. For example create a table on CAIN: mysql>create table testsync (id INT, text VARCHAR(50)); then on ABEL see if it was created with mysql>show tables; and then drop it again on CAIN with: mysql>drop table testsync;, if it is no longer again on ABEL, you have a working replication from CAIN to ABEL.

Step 8) Create reverse Master(ABEL) to Slave(CAIN) to actually create Master-Master replication

This is exactly what we have done in Step 7, only with ABEL being Master and CAIN being Slave. The autoindex parameters configured in my.cfg will make sure there is no conflict. When you now basically do the previous step in the oposite direction. You can also change the username of the replication if you wish, but I wanted to have the same.

On ABEL:

    mysql>grant replication slave on *.* to 'replicator'@'%' \
          identified by 'replicationpassword';
    mysql>reset master;

Now you should also see that the ABEL went to master status with show master status.

mysql> show master status;
+------------------+----------+-----------------+------------------+
| File             | Position | Binlog_Do_DB    | Binlog_Ignore_DB |
+------------------+----------+-----------------+------------------+
| mysql-bin.000001 |      107 | wordpressdata   | mysql            |
+------------------+----------+-----------------+------------------+
1 row in set (0.00 sec)

Now take the parameters from the previous table connect CAIN with this command and the parameters:

CHANGE MASTER TO MASTER_HOST='abel', MASTER_USER='replicator', \
MASTER_PASSWORD='replicationpassword', MASTER_PORT=3306, \
MASTER_LOG_FILE='mysql-bin.000001', \
MASTER_LOG_POS=107, MASTER_CONNECT_RETRY=10;

Again, you can verify the slave status in the show slave status command:

mysql> show slave status;

In the output, you can look for these parts in the table and both should be again “Yes”.
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

Step 9) OPTIONAL: Paranoid prevention of MySQL de-synchronization with VRRP

Ok, despite the fact that the auto indexing works great, if you have a web/system that often creates or drops MySQL tables, then it might be useful to dynamically use only one server for database communication, and only when this fails, move to the other database. Let me show you how it looks right after finishing step 1 to step 8:

VRRP is a protocol that creates a single virtual IP in the network, that is shared by multiple servers (or routers), but only one server is actively using it. The one who is allowed to also use it is dynamically elected based on priorities on local network and is called Master. Lets imagine that we create new IP in LAN 192.168.10.135 that will be VRRP virtual IP. But only ABEL will be VRRP master and use it. When ABEL fails, CAIN will take-over the VRRP IP and will start handling traffic going to it dynamically. When ABEL recovers back to working state, it will again take over the VRRP IP handling and CAIN will be quiet again.

Then all you have to do is configure MySQL to be able to accept SQL interface on 192.168.10.135, create VRRP IPs on lan interfaces and lately tell your apache/PHP to use the VRRP IP as contact point for database instead of localhost. All I will show below as step by step.

First, this is a diagram how the VRRP system should technically work. Note that the point is that despite the MySQL replication is two-way, at any given time only the information flow (replication) if only going in one direction, so there is no way/risk to have de-synchronization happen in any SQL write operation imaginable.

And if something happens to ABEL, everything is redirected to CAIN as follows.

Steps to create this VRRP setup, you do not have to do much, simply :

Step 9a) Add the new VRRP

On both CAIN and ABEL add a new vrrp IP to /etc/hosts file:

192.168.10.135  databasevrrp
192.168.10.134  cain
192.168.10.132  abel

Step 9b) Install VRRPd

On both CAIN and ABEL:

apt-get install vrrpd

Step 9c) Start VRRPd on the servers

CAIN:

vrrpd -D -i eth0 -v 1 -a none -p 100 192.168.10.135

ABEL:

vrrpd -D -i eth0 -v 1 -a none -p 110 192.168.10.135

Verification can be done by either pinging the virtual IP on any other machine on your LAN, alternatively you can look on who is master by using the ip -4 addr ls command, the router that became master will have the new IP address listed there as active IP.

Step 9d) Create a MySQL user with the privilege to access MySQL from external source

mysql>CREATE USER 'mysqluser'@'%' IDENTIFIED BY 'mysqluser';
mysql>GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON wordpressdata.* \
      TO 'mysqluser'@'%';
mysql>FLUSH PRIVILEGES;

Step 9e) Configure your apache/PHP system to connect to MySQL on the external IP.

Well, to be honest, this point depends on what you are using, as I am using wordpress, I can only tell you how to do this in this particular system. In wordpress it is very simple. Open file wp-config.php and change this line:

/** MySQL hostname */
define('DB_HOST', 'localhost');

to this line:

/** MySQL hostname */
define('DB_HOST', 'databasevrrp');

And that’s it, the settings take effect immediatelly and if you didn’t break something, you should have connection to your MySQL via VRRP IP already going over the external IP.

Summary

I hope you enjoyed the whole example because I absolutely love this setup! Maybe it is because I am a network guy who just built first practical cluster on a two Raspberry PI and enjoys that I have cluster that takes 3W in total. So yes, that is true.

In regards to the article, I think I covered all the things you need for recreating the cluster, I was a little limiting all the outputs, verifications and troubleshooting, but I didn’t believe them to be that hard and it would make this article around three times bigger for only small practical reason.