Setting up Master-Master Replication with MySQL on Debian

This tutorial describes a replicated MySQL setup (Mater/Master replication) with 2 nodes where data can be read and written to both nodes at the same time. MySQL takes care to replicate the data to the other node and ensures that primary auto increment keys don't collide.

Since version 5, MySQL comes with built-in support for master-master replication, solving the problem that can happen with self-generated keys. In former MySQL versions, the problem with master-master replication was that conflicts arose immediately if node A and node B both inserted an auto-incrementing key on the same table. The advantages of master-master replication over the traditional master-slave replication are that you don't have to modify your applications to make write accesses only to the master, and that it is easier to provide high-availability because if the master fails, you still have the other master.

1 Preliminary Note

In this tutorial I will show how to replicate the database exampledb from the server server1.example.com with the IP address 192.168.1.101 to the server server2.example.com with the IP address 192.168.1.102 and vice versa. Each system is the slave of the other master and the master of the other slave at the same time. Both systems are running Debian 8; however, the configuration should apply to almost all distributions with little or no modifications.

2 Installing MySQL 5.5

If MySQL isn't already installed on server1 and server2, install it now:

server1/server2:

apt-get -y install mysql-server-5.5 mysql-client-5.5

To make sure that the replication can work, we must make MySQL listen on all interfaces, therefore we comment out the line bind-address = 127.0.0.1 in /etc/mysql/my.cnf:

server1/server2:

nano /etc/mysql/my.cnf

[...]
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
[...]

Restart MySQL afterwards:

server1/server2:

service mysql restart

Then check with

server1/server2:

netstat -tap | grep mysql

that MySQL is really listening on all interfaces:

netstat -tap | grep mysql
tcp 0 0 *:mysql *:* LISTEN 15437/mysqld
server1:~#

Now we set up a replication user slave2_user that can be used by server2 to access the MySQL database on server1.

server1:

Login to the MySQL shell:

mysql --defaults-file=/etc/mysql/debian.cnf

On the MySQL shell, run the following commands:

server1:

GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'secretpassword';
FLUSH PRIVILEGES;
quit;

Replace the word "secretpassword" with a secure password of your choice. Now we do the last two steps again on server2:

server2:

mysql --defaults-file=/etc/mysql/debian.cnf

GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'secretpassword';
FLUSH PRIVILEGES;
quit;

Replace the word "secretpassword" with a secure password here as well. Note down the passwords as we need them later. 

3 Some Notes

In the following I will assume that both MySQL servers are empty (don't contain any database yet except of the 'mysql' database).

If that's not the case on your server, then you have to lock and dump the databases on the first server and import them on the second one before you continue. Don't unlock the databases before the replication is setup. Below a few commands that show how to copy over all databases to a new server in case you don't start with a "clean" MySQL setup.

Example on how to lock all database tables in a MySQL database.

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;

 Example on how to dump all databases into a file all_databases.sql.

mysqldump --defaults-file=/etc/mysql/debian.cnf -cCeQ --hex-blob --quote-names --routines --events --triggers --all-databases -r all_databases.sql

Example on how to import all tables on the second server from file all_databses.sql.

mysql --defaults-file=/etc/mysql/debian.cnf < all_databases.sql

4 Setting Up Replication

Now we set up master-master replication in /etc/mysql/my.cnf. The crucial configuration options for master-master replication areauto_increment_increment and auto_increment_offset:

• auto_increment_increment controls the increment between successive AUTO_INCREMENT values.
• auto_increment_offset determines the starting point for AUTO_INCREMENT column values.

Let's assume we have N MySQL nodes (N=2 in this example), then auto_increment_increment has the value N on all nodes, and each node must have a different value for auto_increment_offset (1, 2, ..., N).

Now let's configure our two MySQL nodes:

server1:

nano /etc/mysql/my.cnf

Search for the section that starts with [mysqld], and put the following options into it (commenting out all existing conflicting options):

[...]
[mysqld]

# Unique Server ID
server-id = 1

# Do not replicate the following databases
binlog-ignore-db = mysql
replicate-ignore-db = mysql

# Auto increment offset
auto-increment-increment = 2

# Do not replicate sql queries for the local server ID
replicate-same-server-id = 0

# Beginne automatisch inkrementelle Werte mit 1
auto-increment-offset = 1

# Delete binlog data after 10 days
expire_logs_days = 10

# Max binlog size
max_binlog_size = 500M

# Binlog file path
log_bin = /var/log/mysql/mysql-bin.log

[...]

Then restart MySQL:

server1:

service mysql restart

Now do the same on server2:

server2:

nano /etc/mysql/my.cnf

[...]

# Unique Server ID
server-id = 2

# Do not replicate the following databases
binlog-ignore-db = mysql
replicate-ignore-db = mysql

# Auto increment offset
auto-increment-increment = 2

# Do not replicate sql queries for the local server ID
replicate-same-server-id = 0

# Beginne automatisch inkrementelle Werte mit 1
auto-increment-offset = 2

# Delete binlog data after 10 days
expire_logs_days = 10

# Max binlog size
max_binlog_size = 500M

# Binlog file path
log_bin = /var/log/mysql/mysql-bin.log

[...]

server2:

service mysql restart

Next we lock the exampledb database on server1, find out about the master status of server1, create an SQL dump of exampledb (that we will import into exampledb on server2 so that both databases contain the same data), and unlock the database so that it can be used again:

server2:

Now we start the replication on Server 2. Open the MySQL shell:

mysql --defaults-file=/etc/mysql/debian.cnf

And execute the following SQL command to activate the replication from server1 to server2:

CHANGE MASTER TO MASTER_HOST='192.168.1.101', MASTER_USER='repl', MASTER_PASSWORD='secretpassword';

Replace secretpassword with the password for the repl MySQL user that you have set in chapter 2.

Now check the slave status by executing the command "show slave status\G" in the MySQL shell.

show slave status\G

The output will be similar to this:

mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State:
 Master_Host: 192.168.1.101
 Master_User: repl
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000001
 Read_Master_Log_Pos: 107
 Relay_Log_File: mysqld-relay-bin.000003
 Relay_Log_Pos: 253
 Relay_Master_Log_File: mysql-bin.000001
 Slave_IO_Running: No
 Slave_SQL_Running: No
 Replicate_Do_DB:
 Replicate_Ignore_DB: mysql
 Replicate_Do_Table:
 Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
 Last_Errno: 0
 Last_Error:
 Skip_Counter: 0
 Exec_Master_Log_Pos: 107
 Relay_Log_Space: 410
 Until_Condition: None
 Until_Log_File:
 Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
 Master_SSL_Cert:
 Master_SSL_Cipher:
 Master_SSL_Key:
 Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error:
 Last_SQL_Errno: 0
 Last_SQL_Error:
 Replicate_Ignore_Server_Ids:
 Master_Server_Id: 1
1 row in set (0.00 sec)

The Lines that you should check are these:

Master_Host: 192.168.1.101
Master_User: repl
Master_Port: 3306
Master_Log_File: mysql-bin.000001
Relay_Log_File: mysqld-relay-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No

Now start the replication with this command on the MySQL shell:

start slave;

and then check the slave status again:

show slave status\G

The following two lines should show "yes" now:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

If "Seconds_Behind_Master" is not 0, then wait a few seconds and check the status again. This field shows if master and slave are in sync.

For the next step, we need to know the values of "Master_Log_File" and "Read_Master_Log_Pos" the "show slave status\G" command. In my case these are:

Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107

Write down the values that you get on your server, we need it for the next step on server 1.

Afterward you can leave the MySQL shell:

quit

server1:

We continue on the first server, open the MySQL shell on server1:

mysql --defaults-file=/etc/mysql/debian.cnf

And execute the following MySQL command:

CHANGE MASTER TO MASTER_HOST='192.168.1.102', MASTER_USER='repl', MASTER_PASSWORD='secretpassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

You have to replace a few things in the above command:

1. The IP address has to be the IP of your second MySQL server.
2. The password "secretpassword" has to be the one that you have choosen in chapter 2 for the user repl.
3. The MASTER_LOG_FILE and MASTER_LOG_POS have to be the values that we have written down in the last step.

Now check with:

show slave status\G

on the MySQL shell if there are no errors.

mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State:
 Master_Host: 192.168.1.102
 Master_User: repl
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000001
 Read_Master_Log_Pos: 107
 Relay_Log_File: mysqld-relay-bin.000001
 Relay_Log_Pos: 4
 Relay_Master_Log_File: mysql-bin.000001
 Slave_IO_Running: No
 Slave_SQL_Running: No
 Replicate_Do_DB:
 Replicate_Ignore_DB: mysql
 Replicate_Do_Table:
 Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
 Last_Errno: 0
 Last_Error:
 Skip_Counter: 0
 Exec_Master_Log_Pos: 107
 Relay_Log_Space: 107
 Until_Condition: None
 Until_Log_File:
 Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
 Master_SSL_Cert:
 Master_SSL_Cipher:
 Master_SSL_Key:
 Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error:
 Last_SQL_Errno: 0
 Last_SQL_Error:
 Replicate_Ignore_Server_Ids:
 Master_Server_Id: 0
1 row in set (0.00 sec)

And start the slave.

start slave;

Check the slave status again:

show slave status\G

The following two lines should show "yes" now:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Afterward you can leave the MySQL shell:

quit

If nothing went wrong, MySQL master-master replication should now be working. If it isn't, please check /var/log/syslog for MySQL errors onserver1 and server2.

5 Test the Replication

 Now it's time to test our replication setup. I will create a database exampledb1 on server1 and then check on server2 if the database has been replicated to the second server:

server1:

Login to the MySQL console on server1 and create the database:

mysql --defaults-file=/etc/mysql/debian.cnf

CREATE DATABASE exampledb1;

server2

Now login to the MySQL console on server2 and check if exampledb1 exists there now:

mysql --defaults-file=/etc/mysql/debian.cnf

show databases;

As we can see, the new database shows up on server2 as well.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| exampledb1 |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

Next I'll test if the replication works in the other direction as well. We are still logged in on server2 and create there a database exampledb2:

CREATE DATABASE exampledb2;

Now go back to server1 and run "show databases" in the MySQL console:

server1

show databases;

The result shows our new database exampledb2, so the replication is working in both directions.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| exampledb1 |
| exampledb2 |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.01 sec)