Master to Master MySQL Replication

Posted on: Fri, 14/12/2012

Replication can be a very useful tool, which when done properly, can provide benefits such as Availability (e.g. reducing server load by distributing users to multiple servers), Performance (e.g. enabling users to access the database with the least latency, usually enabled by providing an instance which is geographically close to them), Network Load Reduction (e.g. providing a local version of the database for the user to connect to as opposed to making external connections over the web) and Redundancy/Backups (e.g. keeping a complete running version of a database in the event something should go wrong).

We at Realnet have implemented replication to solve specific problems for a number of our clients.

Case Study 1: After an initial investigation of poor website response time, we discovered our client had severe bandwidth limitations at their offices. This was making daily operations on-site (creating and handling enquiries) a grind of waiting for pages to load. We solved this problem by sourcing (and configuring) a web-server and loading it up with a copy of their website and database. We configured the database to run in sync with their public website by implementing master-master replication. Once we’d situated the server onsite and inside of the customers network, we’d eliminated the issue of the clients bandwidth limitations, and as a result, the site load time is vastly improved.

Case Study 2: A local event required a gate control system for verifying ticket validity monitoring usage of tickets sold via their website. In addition to the barcode generation and barcode scanners which were required to enable this to happen efficiently, we had to work around the issue of having an internet connection in the middle of a field, in the middle of nowhere. We tested out 3G connections and although the signal wasn’t actually that bad, we knew we couldn’t rely solely on it, and we needed a solution. What we did is again, set up replication to replicate live ticket sales from the website to an on-site version of the ticket sales database. MySQL replication was great for this scenario, as if the connection were to drop, the replication process would handle this gracefully, and pick back up when the connection became available again, all without the gate control system skipping a beat.

So, I’m going to get a bit technical now and give you a rough guide of how to achieve Master – Master replication in MySQL.

Master – Master MySQL Replication Guide

Prerequisites: MySQL (version 5+), this tutorial is based on Cent OS 6, but most of this is MySQL specific. It is assumed that your are adding a second server from scratch (e.g. no data present on second server).

Preparation

When starting off with replication we need to get  both Server A (the current live database) and Server B into the same state. The easiest way to do this is to import a dump of Server A onto Server B.

On Server-A:

1 SERVER-A# mysqldump -u <your mysql user> -p<your mysql password> -c <your database name you are setting up replication on> > <the filename of this sql dump>

Copy the above SQL dump to Server B and use the following command to import the file:

1 SERVER-B# mysql -u <your mysql user> -p<you mysql password> <your database name you are setting up replication on> < <the filename of the sql dump>

Now we need to create a MySQL slave account on each server. We require a slave account as Master-Master replication is actually 2 instance of Master-Slave replication.

On each server run the following command:

1 mysql> USE mysql;
2 mysql> INSERT INTO user (Host, User, Password, Select_priv, Reload_priv, Super_priv, Repl_slave_priv) VALUES ('<Hostname/IP of the connecting server>', '<slave user>', password('<slave password>'), 'Y', 'Y', 'Y', 'Y');
3 mysql> FLUSH PRIVILEGES;

Be sure to make a note of the accounts to set up for future reference.

Server Configuration via my.cnf/my.ini

You’ll need to make some changes to the mysql configuration file. The following configuration is pretty semantic, but in summary, what we are doing here is:

  • Providing the server with a server id. This must be unique as 2 servers with the same server id can’t communicate.
  • Setting the auto increment to 2, and providing an offset to start from (different offset for each server). This will ensure that when new entries are inserted each server will generate different primary key id’s. If we didn’t do this, we’d run into all sorts of duplicate key issues, breaking MySQLs ability to replicate. This is global to all databases configured under this instance of MySQL meaning that if you have multiple databases, setting these rules as provided will result in all of your database generating odd or even primary keys only. This wasn’t a big problem for us, and it doesn’t effect any of your existing data.
  • We configure the Master connection details, and specify which database we want to replicate.
  • Enable binary logging. Binary logs are at the heart of the replication process.

Take a backup of the my.cnf first as if you make a mistake you may struggle to get MySQL running again – no body needs that. Also – this text needs to be appended to the mysqld section – the rules in my.cnf, don’t overwrite anything if you don’t know what your doing… It could have a serious affect on your database performance and reliability.

So without further ado – on Server A your my.cnf will need the following added:

01 server-id = 1
02 replicate-same-server-id = 0
03 auto-increment-increment = 2
04 auto-increment-offset = 1
05  
06 master-host = <IP address of Server B>
07 master-user = <slave user>
08 master-password = <slave password>
09 master-connect-retry = 60
10 replicate-do-db = <database name>
11  
12 log-bin = mysql-bin # this will vary from system to system.
13 binlog-do-db = <database name>

 

On Server B:

01 server-id = 2
02 replicate-same-server-id = 0
03 auto-increment-increment = 2
04 auto-increment-offset = 2
05  
06 master-host = <IP address of Server A>
07 master-user = <The Server A slave user>
08 master-password = <The Server A slave password>
09 master-connect-retry = 60
10 replicate-do-db = <The database name to be replicated>
11  
12 log-bin=mysql-bin
13 binlog-do-db = <database name>

Once the configuration has been made on both servers, restart them. For me this is as easy as:

1 service mysqld restart

If an error occurs starting the process, there is a bug in your configuration file. Aren’t you glad you took a backup! If you can’t see a problem (its probably a misspelling of a variable name), you can go to the mysql error logs and see what mysql is complaining about. If you have a backup and can’t fix this instantly – roll back to the backup and start the service.

Final Stage – Synchronisation of the MySQL Servers

First thing you’ll need to do is stop the slave process and find the status of the master on both servers:

1 mysql> slave stop;
2 mysql> show master status;

You’ll get an output like this from each server (remember which output relates to which server):

1 +------------------+-----------+-------------------+------------------+
2 | File             | Position  | Binlog_Do_DB      | Binlog_Ignore_DB |
3 +------------------+-----------+-------------------+------------------+
4 | mysql-bin.000008 | 187421103 | database_for_repl |                  |
5 +------------------+-----------+-------------------+------------------+
6 1 row in set (0.00 sec)

Now we can use this data (lets say the above is the master status for Server A) to sync the servers as follows – on Server B:

1 CHANGE MASTER TO MASTER_HOST='<Server A IP>', MASTER_USER='<Server A slave user>', MASTER_PASSWORD='<Server A slave password>', MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=187421103;

You’ll need to do this on Server B, but using Server A’s master status details.

Once the above is completed, we are ready to start the replication!

1 mysql> start slave;

To test the replication is running:

1 mysql> show slave status;

You should have Slave_IO_Running and Slave_SQL_Running both outputting a big YES. If they aren’t, its time to start debugging. For the most part, a reset of replication should do it.

How to reset replication

These servers can get out of sync during setup. To resync the servers, shut down mysql on each servers:

1 SERVER-A# service mysqld stop

Delete the relay logs, in my case these were found in  /var/lib/mysql.

Now follow the process laid out in the previous section “Synchronisation of the MySQL Servers”.

Test your setup

This is as simple as inserting a row into Server A and checking it creates on Server B and then in reverse to check the replication is two way.

Master to Master MySQL Replication
enlarge image

Subscribe to our Newsletter

* indicates required

Where to start?

If you're not sure where to start, Realnet's completely free website and digital marketing review will point you in the right direction. Our experts conduct a comprehensive analysis of a number of aspects of your website and marketing strategies and will provide you with a free report highlighting areas which are causing you to lose business and make suggestions for improvements that will have a measurable positive impact.

Address

Realnet Ltd

  • The Studio
  • High Green
  • Great Shelford
  • Cambridge
  • Cambridgeshire
  • CB22 5EG
  • United Kingdom

Working Hours

Monday 09:00 - 17:00
Tuesday 09:00 - 17:00
Wednesday 09:00 - 17:00
Thursday 09:00 - 17:00
Friday 09:00 - 17:00
Saturday Closed - except emergency support
Sunday Closed - except emergency support