mysql master-master setup with one active master and one passive master

assumptions

  • only the active master is writable

  • using the innodb engine

  • Active master and standby master have unique server-id’s

  • Standby master does not have any unnecessary databases or tables and it does not get any traffic

  • standby master is read-only

  • no application user has super privileges

  • disable replication on unneeded tables with replicate-wild-ignore-table=<db>.<table>

    • can be used as many times as needed

    • wild cards are ok. e.g., thedb.% to skip all tables on the db schema

  • for the examples below, the following values will be used:

    • dump file: /tmp/fulldump.sql

    • replication user: repluser

    • replication password: replpass

    • active master:

      • ip: 10.0.0.1

      • server id: 1

      • bin log: binlog.001

      • bin log position: 12345

    • standby master:

      • ip: 10.0.0.2

      • server id: 2

      • bin log: binlog.001

      • bin log position: 23456

/etc/my.cnf replication settings

  • auto-increment-increment and auto-increment-offset are optional if only writing to one server

    • must be used if writing to both servers, but not recommended*

  • active master

    log-bin=binlog
    relay-log=relaylog
    log-slave_updates=1
    server-id=1
  • standby master

log-bin=binlog
relay-log=relaylog
log-slave_updates=1
server-id=2

setup

copy data from active master to standby master

updates will continue during the dump (not blocking)

root@active# mysqldump -A --single-transaction --master-data=2 > /tmp/fulldump.sql
root@active# scp /tmp/fulldump.sql 10.0.0.2:/tmp

load the dump on the standby master (drops existing databases and tables)

root@standby# mysql < /tmp/fulldump.sql

start replication on the standby master (get bin log name and position):

root@standby# sed -ne "s/.*MASTER_LOG_FILE='_;s/'.*MASTER_LOG_POS=_;s/;_p" /tmp/fuuldump.sql
root@standby# mysql <<EOM
  CHANGE MASTER TO
  MASTER_LOG_FILE='binlog.001',
  MASTER_LOG_POS=12345,
  MASTER_HOST='10.0.0.1',
  MASTER_USER='repluser',
  MASTER_PASSWORD='replpass';
  START SLAVE;
  SHOW SLAVE STATUS\G
  RESET MASTER;
  SHOW MASTER STATUS;
EOM
  • start replication on the active master using the binlog and position just obtained

root@active# mysql <<EOM
CHANGE MASTER TO
  MASTER_LOG_FILE='binlog.001',
  MASTER_LOG_POS=23456,
  MASTER_HOST='10.0.0.1',
  MASTER_USER='repluser',
  MASTER_PASSWORD='replpass';
  START SLAVE;
  SHOW SLAVE STATUS\G
  • two-way replication should now be working.

  • it may take a while to synchronize, but eventually, Seconds_Behind_Master: 0.

step by step:

active master

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'10.0.0.%' IDENTIFIED BY 'replpass';

(note the bin-file name and position)
mysql> SHOW MASTER STATUS;
************** 1. row **************
            File: binlog.000003
        Position: 98
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.1',
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='binlog.000003',
MASTER_LOG_POS=0;

standby master

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'10.0.0.%' IDENTIFIED BY 'replpass';

# note the bin-file name and position

mysql> SHOW MASTER STATUS;
************** 1. row **************
            File: binlog.000004
        Position: 98
    Binlog_Do_DB:
Binlog_Ignore_DB:

mysql> CHANGE MASTER TO
MASTER_HOST='10.0.0.1',
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='binlog.000004',
MASTER_LOG_POS=0;

Start and Stop

To Start the replication (on the slave): mysql> START SLAVE

Replication Checks

  1. Check on each slave that "Slave_IO_State" is waiting for master.

  2. Check on each slave that "Slave_IO_Running" and "Slave_SQL_Running" are set to Yes.

mysql> show slave status\G
************** 1. row **************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.0.0.1
                Master_User: repluser
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: binlog.00004
        Read_Master_Log_Pos: 98
             Relay_Log_File: relaylog.01
              Relay_Log_Pos: 235
      Relay_Master_Log_File: binlog.00004
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
  1. Check on each master for a connection created by the slave’s I/O thread:

************** 3. row **************
     Id: 86
   User: repl
   Host: 10.0.0.2:37740
     db: NULL
Command: Binlog Dump
   Time: 3166
  State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL

(There might be more threads if the server is in Master-Master mode, but this is the one we care about)

  1. Check on the slave for two threads. One is the I/O thread and the other is the SQL thread:

************** 1. row **************
     Id: 1
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 55874
  State: Waiting for master to send event
   Info: NULL
************** 2. row **************
     Id: 2
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 3315
  State: Has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
  1. Optional: create/modify a record on one Master and verify that it appears on the other Master.

To Re-Start/Re-Sync Replication

In short do this:

master1:

stop slave;
reset master;

master2:

stop slave;
reset master;

master1:

show master status\G      (take note of this file position and replace it in the "MASTER_LOG_POS below)

mysql>   CHANGE MASTER TO MASTER_HOST='10.0.0.2', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='binlog.01', MASTER_LOG_POS=107;

master2:

show master status\G      (take note of this file position and replace it in the "MASTER_LOG_POS below)

mysql>   CHANGE MASTER TO MASTER_HOST='10.0.0.1', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='binlog.01', MASTER_LOG_POS=107;

master1;

start slave;
show slave status;    (both Slave_IO_Running: and Slave_SQL_Running: should be set to YES)

master2;

start slave;
show slave status;    (both Slave_IO_Running: and Slave_SQL_Running: should be set to YES)

Test:

Test by creating a table in the "test" database on master1 and making sure it shows up on the other server.

Test by createing a table in the "test" databaate on master2 and making sure it shows up in the other server.

Verify once again that "show slave status\G" on both servers shows the slave processes are in "Yes" state