Network Solution for Windows – Windows system, Linux system, Ubuntu server configuration tip

When you have a MySQL database and want to spread the data reads and writes across multiple servers, you can setup master-master replication. What this does is in almost-realtime copy all commands from each server to the other, each acting as both a Master and Slave server.

This is NOT a good system for backups, as any accident on either server will be replicated to the other server, such as dropping all your tables.

To setup Master-Master Replication the first thing you need to do is setup Master-Slave Replication.

Once you’ve set up Master-Slave Replication, start on the current Slave server. Edit your /etc/my.cnf file to turn on binary logging on this server. Under the [mysqld] heading add:

log-bin=mysql-bin
binlog-ignore-db="mysql"

Create a replication slave account on the slave for the original master server:

# mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@"%" IDENTIFIED BY '[repl-password]';
mysql> quit;

Again, be sure to replace [repl password] with the actual password you want to use. Also, you must ensure that your firewall has port 3306:tcp open, the default port for the mysql server service.

Restart MySQL then get the binary position of the data.

# mysql -u root -p
mysql> SHOW MASTER STATUS;

The output should look something like this:

+------------------+--------------------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+--------------------------+------------------+
| mysql-bin.000001 |      3400 |              | mysql            |
+------------------+--------------------------+------------------+
1 row in set (0.00 sec)

Write down the filename and log position for use on the original Master server.

Now back on the original Master server, set it up as a slave also. Edit /etc/my.cnf and under the [mysqld] heading add:

master-host = [IP of Slave Server]
master-user = repl
master-password = [repl password]
master-port = 3306

Restart your MySQL service, then login to MySQL and setup the Master File settings.

mysql -u root -p
mysql> CHANGE MASTER TO MASTER_LOG_FILE='[File written down]', MASTER_LOG_POS=[position];
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

Slave_IO_State status information should identify “Waiting for master to send event”. If it stops at “Connecting to Master” check your log file. By default it is located in /var/log/mysqld.log but may be different on your system. Check your my.cnf file for the exact location of your log file.

Note: I’ve noticed an issue with connecting when using “%” wildcard and have had to specify the connecting server in many instances. MySQL doesn’t seem to like the wildcard in quite a few cases.

At this point all reads and writes can be evenly distributed across servers. This is great for load sharing, but I must reiterate, don’t use it for backup, a single accidental table drop will affect both databases.