Establish Master-Master Replication

Background

Every MySQL server can replicate changes from exactly one master server.

In this lesson, we set two servers to replicate from each other. This is called a Master-Master configuration.

It is possible to run this configuration with both servers accepting writes at the same time. Unfortunately, because MySQL replication is asynchronous, enormous care has to be taken to make sure the application cannot cause conflicting updates to be written near-simultaneously to both servers. This can silently cause the servers to contain different data, while neither server is itself corrupted.

In production, we advocate that you set one of the pair to be read_only until it is needed (typically in a disaster.) You could still use that read_only server to accept read queries, or even run backups, but it won't be possible for it to cause collisions or corruption.

A Master-master pair can be used as the root of a much larger replication topology, with writes going to a master, and each master replicating to slaves. In the event of a master failing, half the slaves will still be pointed to a healthy master that only needs read_only disabled to take the full load.

Steps

Connect to A

The a server is an existing database. We'll use the data on this database to build the second master.

Configure MySQL on A for Replication

a will be the master and a slave to the new server, so this config is a hybrid of the two configurations used to Establish Replication.

In order to be a replication master, it needs log_bin enabled, and a server_id that is not the default (0).

In order to be a replication slave, it also needs the relay_log file name to not depend on its host name.

And to prepare this server to be a master of more slaves, we'll enable log_slave_updates. This ensures changes from b will be propagated out to a's slaves.

Set those values by editing /etc/my.cnf:

a ~ $ sudoedit /etc/my.cnf

and adding these four lines directly under [mysqld]

/etc/my.cnf
[mysqld]
log_bin = mysql-bin
server_id = 10
log_slave_updates = 1
relay_log = mysql-relay-bin
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
......some content not shown

Now restart MySQL:

a ~ $ sudo service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

And log in to MySQL to check that those changes took effect.

a ~ $ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.29-log MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables where Variable_Name IN ("server_id", "log_bin", "relay_log", "log_slave_updates");
+-------------------+-----------------+
| Variable_name     | Value           |
+-------------------+-----------------+
| log_bin           | ON              |
| log_slave_updates | ON              |
| relay_log         | mysql-relay-bin |
| server_id         | 10              |
+-------------------+-----------------+
5 rows in set (0.00 sec)

mysql>

After this step is completed:

On A, /etc/my.cnf sets server_id to 10
On A, /etc/my.cnf sets log_bin to mysql-bin
On A, /etc/my.cnf sets log_slave_updates to 1
On A, /etc/my.cnf sets relay_log to mysql-relay-bin
On A, the MySQL variable server_id has value 10
On A, the MySQL variable log_bin has value ON
On A, the MySQL variable log_slave_updates has value ON
On A, the MySQL variable relay_log has value ON

Configure a Replication Account

Add a service account with replication privileges.

Each server will use this account to authenticate to the other.

The account itself will be copied to the new server in the backup.

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'%' IDENTIFIED BY 'DragBuckNextWeak';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>

After this step is completed:

replicator can log into MySQL on a from b with password DragBuckNextWeak

Back Up Server A

This backup will also contain all the data from a, as well as the binary log information that b needs to start replicating after restoring this backup.

mysql> exit
a ~ $ mysqldump -u root --single-transaction --all-databases --master-data=1 > /tmp/master_backup.sql 
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

You can see where the backup file includes replication information. When b applies this backup then starts replicating, it will know to look on a in a specific binary log, after a specific position for changes newer than this backup.

a ~ $ egrep "^CHANGE MASTER" /tmp/master_backup.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
a ~ $

After this step is completed:

The backup file exists on a at /tmp/master_backup.sql

Copy the Backup to B

Now copy the backup file to b using SCP.

a ~ $ scp /tmp/master_backup.sql b.example.com:/tmp/
The authenticity of host 'b.example.com (10.242.58.189)' can't be established.
RSA key fingerprint is 0f:47:42:f4:71:51:4c:a3:70:94:db:83:03:4c:d2:48.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'b.example.com,10.242.58.189' (RSA) to the list of known hosts.
you@b.example.com's password: (input your password)
master_backup.sql                           100%  501KB 501.3KB/s   00:00
a ~ $

After this step is completed:

The backup file exists on b at /tmp/master_backup.sql

Connect to B

b is a new server with a fresh MySQL installation. We'll clone the data from a to make this a new master.

Configure MySQL on B for Replication

b's configuration will be identical to a, except that it needs a unique server_id.

We'll set those values by editing /etc/my.cnf:

b ~ $ sudoedit /etc/my.cnf

and add these four lines directly under [mysqld]

/etc/my.cnf
[mysqld]
log_bin = mysql-bin
server_id = 20
log_slave_updates = 1
relay_log = mysql-relay-bin
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
......some content not shown

Now restart MySQL:

b ~ $ sudo service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

You can check these settings manually with the same commands you used on the first server, above.

After this step is completed:

On B, /etc/my.cnf sets server_id to 20
On B, /etc/my.cnf sets log_bin to mysql-bin
On B, /etc/my.cnf sets log_slave_updates to 1
On B, /etc/my.cnf sets relay_log to mysql-relay-bin
On B, the MySQL variable server_id has value 20
On B, the MySQL variable log_bin has value ON
On B, the MySQL variable log_slave_updates has value ON
On B, the MySQL variable relay_log has value ON

Import the Backup on B

Log into MySQL on B, then import the backup. This sets up B with the table structure and data from A, the position where B should start replicating new changes, and the replicator user configured in Step 2.

b ~ $ mysql -u root
mysql> source /tmp/master_backup.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 508 rows affected (0.02 sec)
Records: 508  Duplicates: 0  Warnings: 0
......some content not shown

After this step is completed:

On b, MySQL has the database important
On b, MySQL has the table important.stuff
From a, replicator can log into MySQL on b with password DragBuckNextWeak

Note B's Binary Log Info

b picked up a's binary log information from the backup file. Now we need to write down the binary log information from b to provide to a.

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |   511309 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

Configure B to Replicate from A

Configure b with a's hostname and the username and password for the replicator account.

This information, combined with MASTER_LOG_FILE and MASTER_LOG_POS from the backup file, are all b needs to start replicating.

mysql> CHANGE MASTER TO MASTER_HOST='a.example.com',
MASTER_USER='replicator',
MASTER_PASSWORD='DragBuckNextWeak';

After this step is completed:

On b, MySQL shows the replication setting MASTER_HOST has value a.example.com
On b, MySQL shows the replication setting MASTER_USER has value replicator

Start Replication from A to B

Start replication on b, then ask MySQL for replication status.

mysql> SLAVE START;
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: a.example.com
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 960
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 1106
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           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: 960
              Relay_Log_Space: 1262
              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: 0
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: 10
1 row in set (0.00 sec)

mysql>

After this step is completed:

On b, SHOW SLAVE STATUS shows Slave_IO_Running is Yes
On b, SHOW SLAVE STATUS shows Slave_SQL_Running is Yes

Configure Server A to Replicate from Server B

Now we'll return to a and configure it to replicate from b.

The MASTER_LOG_FILE and MASTER_LOG_POS will be the values you collected in step 9 based on SHOW MASTER STATUS; from server b.

a ~ $ mysql -u root
mysql> CHANGE MASTER TO MASTER_HOST='b.example.com',
MASTER_USER='replicator',
MASTER_PASSWORD='DragBuckNextWeak',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=511309;
Query OK, 0 rows affected (0.09 sec)

mysql>

After this step is completed:

On a, MySQL shows the replication setting MASTER_HOST has value b.example.com
On a, MySQL shows the replication setting MASTER_USER has value replicator

Start Replication from B to A

mysql> slave start;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: b.example.com
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 511561
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           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: 511561
              Relay_Log_Space: 409
              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: 0
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: 20
1 row in set (0.00 sec)

mysql>

After this step is completed:

On a, SHOW SLAVE STATUS shows Slave_IO_Running is Yes
On a, SHOW SLAVE STATUS shows Slave_SQL_Running is Yes

Test Replication from A to B

Test that new records created on a are replicated to the new server.

From a, insert a new record in the important.stuff table.

mysql> INSERT INTO important.stuff SET details='Gift from A to B';
Query OK, 1 row affected (0.03 sec)

Now on b, check that the record has replicated.

mysql> select * from important.stuff\G
*************************** 1. row ***************************
      id: 1
 details: Gift from A to B
happened: 2013-03-27 04:06:02
1 row in set (0.00 sec)

mysql>

After this step is completed:

INSERT a new record on a in the table important.stuff
Within 1 second, that record is replicated to b

Test Replication from B to A

Now test that changes to the new server also replicate to the original.

Still on b, insert a new record in the important.stuff table.

mysql> INSERT INTO important.stuff SET details='Gift from B to A';
Query OK, 1 row affected (0.03 sec)

mysql>

Now on a, check that the record has replicated.

mysql> select * from important.stuff\G
*************************** 1. row ***************************
      id: 1
 details: Gift from A to B
happened: 2013-03-27 04:06:02
*************************** 2. row ***************************
      id: 2
 details: inserted by grading server
happened: 2013-03-27 04:09:26
*************************** 3. row ***************************
      id: 3
 details: Gift from B to A
happened: 2013-03-27 04:09:40
3 rows in set (0.00 sec)

After this step is completed:

INSERT a new record on b in the table important.stuff
Within 1 second, that record is replicated to a