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.

CAIN and ABEL servers we will synchronize

CAIN and ABEL servers we will synchronize

Step 1) Preparing the two servers

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

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:

On server ABEL:

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:

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:

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:

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:

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

Abel: joe /etc/mysql/my.cfg

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:

Cain:

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:

Abel:

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

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:

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

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

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

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:

MySQL going to with write commands to localhost

Apache/PHP going to MySQL with write commands via localhost

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.

MySQL write access directed to VRRP IP

MySQL write access directed to VRRP IP

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

MySQL still directed to backup VRRP IP on CAIN

MySQL still directed to backup VRRP IP on CAIN

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:

Step 9b) Install VRRPd

On both CAIN and ABEL:

apt-get install vrrpd

Step 9c) Start VRRPd on the servers

CAIN:

ABEL:

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

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:

to this line:

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.

 

If you enjoyed this blog, please share.

About Peter Havrila

Author's Profile