MySQL Master-Master-Slave(s) Database Replication

Details
Published on Thursday, 04 June 2009 17:34
Written by David L Norris

I have watched the technology sessions and slideshows explaining how many large websites, such as Google or Facebook, setup their MySQL Databases. But none have ever gone into the details of how to configure MySQL in such a way. So I had to tackle that myself. I am documenting the process here so its hopefully useful to others (and myself when I forget in 6 months).

Overview of the resources involved

Data Integrity Concerns
The very first thing you need to consider is that MySQL technically does not support multiple masters. This is significant because it means that there is no conflict resolution if you write data to both masters. If you happen to change the same record on both masters they wipe each other's changes out. MySQL replication works simply by replaying queries from the master on the slave server.

The safest solution is to simply never write data to both masters. Although, there are strategies that allow you to safely write to multiple masters. For example, limit writes to specific tables on each master server. In my case I use the secondary master as a replication source and hot-spare in case I need to take down the primary master for maintenance. Using the secondary master as a replication source takes the burden of slave replication off your primary database server.
Master Configuration
We have two masters. They are identified as 1 and 2. (Slaves are 11, 12, etc.)
The very first thing you should do is to run /usr/bin/mysql_secure_installation on each of your masters to set MySQL's root password and other security settings.
Master 1
Add the following to the [mysqld] section of my.cnf making the appropriate changes and restart mysqld:

# primary master server id
server-id=1
auto_increment_offset=1
# total number of master servers
auto_increment_increment=2
# local slave replication options
log-bin=master1-bin
log-slave-updates
# remote master replication options
master-host=master2.yourdomain.com
master-port=3306
master-user=replica
master-password=replic8
master-connect-retry=10

Master 2
Add the following to the [mysqld] section of my.cnf making the appropriate changes and restart mysqld:

# secondary master server id
server-id=2
auto_increment_offset=2
# total number of master servers
auto_increment_increment=2
# local slave replication options
log-bin=master2-bin
log-slave-updates
# remote master replication options
master-host=master1.yourdomain.com
master-port=3306
master-user=replica
master-password=replic8
master-connect-retry=10

Create Replication Accounts
On both master servers run the following query as root:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%' IDENTIFIED BY 'replic8';


Dump/Load Existing Data and start Replication

On Master 1
Prevent writing to the database.

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: master1-bin.000001
Position: 254
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.20 sec)

Make note of the position and file name. You must have these later. From a terminal you now need to dump the database for loading onto the slaves.

bash> mysqldump -A -u root -p > master1.sql


On Master 2
Load the data from Master 1 onto Master 2.

bash> mysql -h master2.yourdomain.com -u root -p < master1.sql

Enable Master 2 as a slave to Master 1 (refer to master 1's show master status above for MASTER_LOG* values)

mysql> CHANGE MASTER TO
MASTER_HOST='master1.yourdomain.com',
MASTER_USER='replica',
MASTER_PASSWORD='replic8',
MASTER_LOG_FILE='master1-bin.000001',
MASTER_LOG_POS=254;
mysql> START SLAVE;


Get the Log Info for Master 2

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: master2-bin.000005
Position: 12314580
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

On Master 1
Enable Master 1 as a slave to Master 2 (refer to master 2's show master status above for MASTER_LOG* values)

mysql> CHANGE MASTER TO
MASTER_HOST='master2.yourdomain.com',
MASTER_USER='replica',
MASTER_PASSWORD='replic8',
MASTER_LOG_FILE='master2-bin.000005',
MASTER_LOG_POS=12314580;
mysql> START SLAVE;


Master-Master Setup is complete
If all you wanted was a master-master setup then you are finished. Any query executed on master 1 will also be executed on master 2.

Adding Slaves to your Master-Master

On Each Slave Follow this Procedure
Add the following to the [mysqld] section of my.cnf changing the server-id and master settings as appropriate and restart mysqld:

# this slave's server-id
server-id=11
# replicate from master 2
master-host=master2.yourdomain.com
master-port=3306
master-user=replica
master-password=replic8
master-connect-retry=10


Load the data dump from Master 1, if needed:

bash> mysql -h slave11.yourdomain.com -u root -p < master1.sql

Start Replication from Master 2 to each slave (refer to master 2's show master status above for MASTER_LOG* values)

mysql> CHANGE MASTER TO
MASTER_HOST='master2.yourdomain.com',
MASTER_USER='replica',
MASTER_PASSWORD='replic8',
MASTER_LOG_FILE='master2-bin.000005',
MASTER_LOG_POS=12314580;
mysql> START SLAVE;


Master-Master-Slave Setup is Complete
Congratulations, you are done with the server configuration! You may verify this by inserting or updating records on Master 1 and then verifying that the change is made on Master 1 and all of your slaves.

Client Access Considerations
To take full advantage of this configuration your client applications will need to be written in such a way that they perform all critical writes (that is, writing data that you want to keep) on Master 1. Also consider that some or all reads immediately following a write may need to come from the master. This is because it can take a few seconds for the slaves to synchronize with Master 1. This is what we call a "dirty" read. Clients performing only reads or creating temporary tables for use with those reads can access your pool of slaves and never touch the master.
Examples

  • Generate query-intense reports on dedicated slaves.
  • A portal page that doesn't need up-to-the-second data.
  • Statistics calculations can be performed on the slaves.

Enforcing Safe Practices
You would be well advised to GRANT only SELECT privileges to clients accessing your tables via a slave. This way a rogue client application cannot manipulate upstream data on the slave. Any changes made on the slave would not be known to any other slave nor the masters. This could be a devastatingly bad situation to find yourself in. Please think through these issues before you implement your clients.
You almost certainly will want to allow clients to create temporary tables, though.


Advanced Stuff
MySQL doesn't require slave tables to have the same schema as masters, either. This allows you to do interesting things like create your master database tables as InnoDB tables with full referential integrity and foreign keys while creating your slave tables as high performance InnoDB tables. You can create different indexing strategies on the slaves to fine-tune specific slaves to specific tasks such as report generation.

---