Home Email Us Top Top Top
Dual-Master MySQL 5 Replication Done Right

MySQL replication is the most flexible way to deal with scalability. If not done right, however, replication can result in disaster.

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.

In this tutorial, we'll outline, step-by-step, how to avert disaster by creating a dual master MySQL replication setup configured to avoid primary key collision. We'll call the 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.

If you run into any problems, feel free to visit our forum and share details!

Neo Code Software logo Problems? Neo Code Software Can Help!

What's 10 + 4?
First Name Contact Us!
Last Name
Email

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.

Example of A MySQL Dump:

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

The next thing to do is 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. You can name them whatever you'd like.

Creating A Slave User:

Server MySQL command line> USE mysql;
Server MySQL command line> 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');
Server MySQL command line> 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 # change this to a path/name appropriate to your system
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 # change this to a path/name appropriate to your system
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. To avoid future problems, do this! A relay log, for those that are wondering, is simply a local echo of data read from the master's log.

Note: The two MySQL configuration variables that prevent key collisions are auto-increment-increment and auto-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


The last thing needed to set up replication is the synchronization of the 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:

Server MySQL command line> 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 confirm that replication is working, first 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 halt two-way replication if data gets written during the interruption. It makes sense to learn how to reset replication before something goes wrong.

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!

Neo Code Software logo Problems? Neo Code Software Can Help!

What's 10 + 4?
First Name Contact Us!
Last Name
Email