Mysql Master/Master Replication & File sync for quick easy scaling or load balance

A lot of our customers often need a quick easy way to scale up without moving to a new host or changing IP, or even just want an overseas copy of their site, so this is a brief tutorial on how to duplicate your server, and have the database and files replicate between the 2 instances.

First, order your second VPS. This will work between data centers, around the world, or even within the same data center no problems at all. Make sure the disk size is the same size on both VPS. Install the new VPS with a copy of the old one, you can do this in your rimuhosting panel. This step is not overly necessary, but may save you setup/config and initial data transfer.

MySQL Replication

First you need to edit your my.cnf , this will be located in /etc/ or /etc/mysql/ . Add in the following items onto both servers
master-host = 192.168.16.4
master-user = replication
master-password = slave321
master-port = 3306
master-connect-retry = 60

Make sure you change the IP to the IP of the OTHER server, so server 1 has server 2 IP and vice versa, it just needs to know what to connect too and how. Check that you replace the 'replication' with user of choice, and passwords of choice

Next you need to put the following into one server to give it an id
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1

And this into the other
server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2

This identifies the servers with an id, and also sets auto_increment to be offset so you do not get clashes with trying to overwrite any data.
If you only want to replicate the one database you can add a line like this on both
replicate-do-db = databasename

If you want to sync the entire database then you leave that out. If you want to ignore syncing for just one database then do something like this
binlog-ignore-db=mysql # input the database that should be ignored for replication

Okay, next you need to check the binlog line is uncommented if it is commented out, or add one
log_bin = /var/log/mysql/mysql-bin.log

Remove any instances of skip-networking and if you have bind-address make sure it looks like
bind-address = 0.0.0.0

Save and exit the mysql config now, and login to mysql on the command line. You will need to add the user you just setup on those configs that will be used for replication. Run this on both mysql servers, changing the IP/user/pass for the ones in your configs.
grant replication slave on *.* to 'replication'@192.168.16.5 identified by 'slave';
Make sure the user for each server has the hostname of the other server they are getting connections from on both of those.
Now the hard work is done, restart mysql on both servers. Now all you need to do is login via command line and start each as a slave

mysql> start slave;
mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.16.4
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: MASTERMYSQL01-bin.000009
Read_Master_Log_Pos: 4
Relay_Log_File: MASTERMYSQL02-relay-bin.000015
Relay_Log_Pos: 3630
Relay_Master_Log_File: MASTERMYSQL01-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 4
Relay_Log_Space: 3630
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 1519187
1 row in set (0.00 sec)

If there are any errors they should show up here, and if you get stuck at all let us know, we are happy to help out!

File Replication

For file replication we are going to use Unison, its easy to install and use . Make sure you have passwordless ssh setup on both servers so they can connect to each other without passwords or prompts (preferably as root).

Now create a directory in /root/.unison/
This is where you will store your configs. You can have multiple configs and include them from each other if needed, but in this tutorial we will be only using the one for now called default.prf
Create a file called /root/.unison/default.prf and insert something similar to the following

log = true
logfile = /var/log/unison.log

# Roots of the synchronization
root = /var/www
root = ssh://192.168.1.5:1022//var/www # this is the IP of the remote server, SSH is on port 1022 as an example

# things you want to ignore - check manual if you want to do this
ignore = Name *.tmp ## ignores all files with the extension .tmp
#ignore = Name *cache*
ignore = Path .unison

auto=true
batch=true
confirmbigdel=true
fastcheck=true
group=true
owner=true
prefer=newer
silent=true

Now you should be able to run 'unison' from the command line and see any output, make sure things are ok. Pays to test a few times, I had errors regarding tmp files which was why i ignored those. No need to sync a cache for a website either if that is what you are syncing.
Despite this running on one server, it will sync both ways, so this only needs to be run on the one server not both (unless you have multiple servers).
When you have the unison as you want it to run, add that into a crontab and have it run every minute or two.
I have mine run only every 10 minutes or so, but you can have it running every minute if you need, or even have it triggered by another method if needed.
Here is my crontab entry

*/10 * * * * /usr/bin/unison > /dev/null 2>&1

Pays to create and delete files to test this on both sides to make sure it is working also.

Now you have 2 servers replicating each other. This means you can now add DNS so that doing a DNS lookup on a website shows 2 IPs, this means people will be spread over the 2 servers instead of the one, lowering the load on a single server. You can scale this up to multiple servers if required, and then take servers out of the array if they need work, without having downtime.

Things to note:
You may also want to sync configs (eg apache configs, websites, passwd files for users, etc).
Master/Master replication can be buggy - especially if you have very busy servers. There may be clashes on updates on both servers at the same time or things getting out of sync. This is why monitoring is critical to make sure it is going ok. For some sites it may be more suitable to have a single mysql on its own for some sites, and give it huge memory and CPU instead.
Other alternative ways for replication are described here http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication-multi-master.html
As a general rule, there is no one fix or solution for scaling upwards, this post was just to get you started on some of the basics.

If you need any help with this, just pop in a ticket and let us know what we can do for you.

This entry was posted in Featured, Rimuhosting and tagged , , . Bookmark the permalink.

9 Responses to Mysql Master/Master Replication & File sync for quick easy scaling or load balance

  1. Simon Welsh says:

    Would it be possible to set this up using a SSH tunnel between servers for MySQL rather than running it world-accessible? Or does replication not support that?

    • Liz Quilty says:

      A VPN would be what you need then, a VPN means you can do it over private IP addresses. I would suggest openvpn due to its easy setup and config, but feel free to do anything.

  2. cass says:

    Acctually Simon Welsh knows what he is talking about, to have an idea you could take a look at this post: http://robpickering.com/2011/02/mysql-replication-over-an-ssh-tunnel-373.

    • Liz Quilty says:

      Great link, thanks! (though nobody suggested Simon did not know what he was talking about)

  3. Jaime says:

    Good read! :)

    about the tunneling im not sure, but i think that tunelling has the same 'concept' of vpn, in a blind guess.

    http://en.wikipedia.org/wiki/Virtual_private_network#Plaintext_tunnels

    best regards,

  4. jamon says:

    mysql usually has support for ssl built in. see

    http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-ssl.html for more. much less overhead and relies on mysql instead of another set of network connections and dependencies.

  5. Debo Jurgen says:

    Try to setup your secure tunnel with stunnel. A vpn is too much overhead

  6. John says:

    Are there any automated ways to check replication? I wanted to add the "show slave status\G;" to a bash script and add it to Cron...

    Thank You!

    • Liz Quilty says:

      Heya, indeed there are a few, just google 'bash script check replication' . I think even monit has something that does the monitoring of it, however unsure if that relies on a script or restarts things when it breaks.