Dual Master Replication(Master-Master)

MySQL replication is the most flexible way to deal with scalability and availability.

The most common problem with replication is primary key collision. Primary key collision involves two MySQL servers creating table rows containing different data, but the same primary key. When this happens replication stops. With replication stopped, the difference between the data on the servers grows. At some point the weirdness gets noticed. Then begins the painful process of recovery, of trying to weave masses of conflicting data into a whole.

 we'll outline, step-by-step, how to avoid disaster by creating a dual master MySQL replication setup configured to avoid primary key collision. We'll use  two MySQL servers Server A and Server B. In a dual master setup each server functions as both a master and a slave to the other server.

Top of Form

Bottom of Form

Preparing For Replication

The first thing to do when getting ready for replication is to make sure that the database on each server is in the same state. If in doubt, create a dump of one server's version of the database then import it into the other server.

Server A > mysqldump -u <mysql user> -p<mysql password> -c <database name> > <filename of dump>
(copy dump file to Server B)
Server B > mysql -u <mysql user> -p<mysql password> -D <database name> < <filename of dump>

The next task  is to  create a "slave user" on each of the two servers. These users are used by MySQL for the slave to master connection and need to be given specific privileges.

Creating A Slave User:

USE mysql;

 INSERT INTO user (Host, User, Password, Select_priv, Reload_priv, Super_priv, Repl_slave_priv) VALUES ('<Hostname/IP>', '<slave user>', password('<slave password>'), 'Y', 'Y', 'Y', 'Y');

 FLUSH PRIVILEGES;

Configuring The MySQL Servers

The next thing to do is configure each MySQL server. You'll need to know the IP address of each server.

On each server you'll need to edit your MySQL Server configuration file (usually called my.cnf or my.ini).

Below is what needs to be added to the configuration for Server A:

server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1

master-host = <IP address of Server B>
master-user = <slave user>
master-password = <slave password>
master-connect-retry = 60
replicate-do-db = <database name>

log-bin = C:\mysql\log\log-bin.log

binlog-do-db = <database name>

Below is what needs to be added to the configuration for Server B:

server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2

master-host = <IP address of Server A>
master-user = <slave user>
master-password = <slave password>
master-connect-retry = 60
replicate-do-db = <database name>

log-bin= C:\mysql\log\log-bin.log

binlog-do-db = <database name>

After making the changes to your configuration, restart the two servers. Check your MySQL error logs for any problems. A warning message will likely recommend that you specify the name of your relay log in your server configuration.

Note: The two MySQL configuration variables that prevent key collisions are auto-increment-increment andauto-increment-offset. The value of auto-increment-increment should be set to N, where N is equal to the number of servers in the replication setup (in this case two). The auto-increment-offset and server-id configuration variables should be set as consecutive integers (in this case 1 and 2).

Synchronizing the Servers

Now, synchronization the two servers.

In the MySQL command line of each server, issue the "slave stop" command then the "show master status" command. This will give you infomation that you'll need to manually provide to the other server. This information is needed to "synchronize" the two servers.

Next, on each server enter the command below into the MySQL command line of each server

CHANGE MASTER TO MASTER_HOST='<master's IP>', MASTER_USER='<slave user>', MASTER_PASSWORD='<slave password>', MASTER_LOG_FILE='<master's log file name>', MASTER_LOG_POS=<master's log file position>;

Once you've entered the above command, issue the "start slave" command on both servers. Replication should now be working!

To check that replication is working fine, issue the "show slave status" command on both servers. Both "Slave_IO_Running" and "Slave_SQL_Running" should be "YES". If both aren't "YES", you'll need to reset replication.

Resetting Replication

It doesn't take much for replication to go out of sync. A simple network interruption to one server can effectively break two-way replication if data gets written during the interruption.

A network outage can be simulated by unplugging one of the MySQL servers from the network. While one server is unplugged, try inserting rows to both. This will generally disrupt replication even though, after restoring network connectivity, the slave status of each server may look normal.

To reset replication, shut down both servers, delete their relay logs, and synchronize the servers (as outlined in the previous section). Deleting the relay logs will cause each server to re-read from their master.

Testing

Before putting a replication setup into production, be sure to thoroughly test it. Primary keys generated on Server A should always be odd numbers, while those generated on Server B should always be even.

Have fun!