MySQL Master-Master Replication – Setting Up MySQL Master-Master with Additional Slaves

MySQLreplication

We have two datacenters with a copy of our web app (online training platform) at each. The primary datacenter receives all of the traffic at any given time and the second datacenter serves as a failover(using DNS failover). The databases are set up in master-master with different auto-increment-increment values. The server at datacenter 1 also has its own local slave server on which we do backups, that way we can pause replication, do the backup with mysqldump, then start replication again.

 SERVER 1 <=========> SERVER 2
    |
  Slave 1

This weekend we did a failover to server 2 because datacenter 1 was doing planned network maintenance. Everything went smoothly and after a few hours we brought everything back to DC1. Last night I start getting replication errors on the slave saying it failed to DELETE an entry that didn't exist. I found it odd that the Slave 1 was getting the error while Server 2 was not (since they're both acting as slaves to Server 1). After digging, I found out that we still have a user that's going to Server 2.. for whatever reason their DNS hasn't updated for over 2 days. So the new data that they are producing is being replicated to Server 1, but Slave 1 is not getting these updates because they're not in Server 1's binlogs.

I now see that this was poor planning on my part for not foreseeing this issue. My question is, how can I make this work? We want to keep the master-master and we want to allow Slave 1 to exist, not only for backups but for additional read only reporting servers and such. Those of you that run Master-Master, what have you done to remedy similar issues? Or is this just a bad setup and I'm SOL? The app was not built with redundancy in mind, so everything is having to be done system side.

Best Answer

What you want is server1 to binlog not only its own updates, but also those updates he received from server2, so that slave1 will get all updates.

You just need to tell server1 to do this, which can be accomplished by setting log-slave-updates to yes.

You might want to set this option on server2 too, if the server topology should change someday (as Rolando kindly remarked in the comments below).

This does not loop endlessly as the default setting for replicate-same-server-id is 0 (and cannot be changed as long as log-slave-updates is on). This setting set to 0 means the slave will skip statements he receives with his own server-id.

Your infrastructure decision in general seems fine to me.

Related Solutions

Mysql – Is MySQL Replication Affected by a High-Latency Interconnect

The direct answer to your question is Yes, but it depends on the version of MySQL you are running. Before MySQL 5.5, replication would operate as follows:

Master Executes SQL
Master Records SQL Event in its Binary Logs
Slave Reads SQL Event from Master Binary Logs
Slave Stores SQL Event in its Relay Logs via I/O Thread
Slave Reads Next SQL Event From Relay Log via SQL Thread
Slave Executes SQL
Slave Acknowledges Master of the Complete Execution of the SQL Event

As of MySQL 5.5, using Semisynchronous Replication, now replication would operate as follows:

Master Executes SQL
Master Records SQL Event in its Binary Logs
Slave Reads SQL Event from Master Binary Logs
Slave Acknowledges Master of the Receipt of the SQL Event
Slave Stores SQL Event in its Relay Logs via I/O Thread
Slave Reads Next SQL Event From Relay Log via SQL Thread
Slave Executes SQL
Slave Acknowledges Master of the Complete Execution of the SQL Event

This new paradigm will permit a Slave to be closer sync'd to its Master.

Notwithstanding, latency within the network could hamper MySQL Semisync Replication to the point where it reverts back to the old-style asynchronous replication. Why ? If a timeout occurs without any slave having acknowledged the transaction, the master reverts to asynchronous replication. When at least one semisynchronous slave catches up, the master returns to semisynchronous replication.

UPDATE 2011-08-08 14:22 EDT

The configuration of MySQL 5.5 Semisynchronous Replication is straightforward

Step 1) Add these four(4) lines to /etc/my.cnf

[mysqld]
plugin-dir=/usr/lib64/mysql/plugin
#rpl_semi_sync_master_enabled
#rpl_semi_sync_master_timeout=5000
#rpl_semi_sync_slave_enabled

Step 2) Restart MySQL

service mysql restart

Step 3) Run these commands in the MySQL client

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave  SONAME 'semisync_slave.so';

Step 4) Uncomment the three rpm_semi_sync options after the plugin-dir option

[mysqld]
plugin-dir=/usr/lib64/mysql/plugin
rpl_semi_sync_master_enabled
rpl_semi_sync_master_timeout=5000
rpl_semi_sync_slave_enabled

Step 5) Restart MySQL

service mysql restart

All Done !!! Now just setup MySQL Replication as usual.

Mysql – Rows already DELETEd on MySQL slave; How to DELETE on master

Here is something quick and dirty you can do

Run the DELETE like this on the Master:

SET SQL_LOG_BIN=0;
DELETE FROM ... ;

The first line tells the DB Session not to record the SQL that follows. Thus, when you run the DELETE on the Master, the SQL will not be written in the Master's binary logs. Consequently, the Slave will never receive the DELETE in its relay logs.

This will only affect the session you use to run these two lines. All other DB Connections will replicate as usual. Once you disconnect, any new session will replicate properly.

Give it a Try !!!

Best Answer

Related Solutions

Mysql – Is MySQL Replication Affected by a High-Latency Interconnect

Mysql – Rows already DELETEd on MySQL slave; How to DELETE on master

Related Question