MySQL Master / Master Replication Sync

IMPORTANT: ALWAYS make a backup of your databases before attempting the following procedures. The following is based under the assumption that your MySQL servers are already setup to be Master / Master.

If you have have a MySQL Master / Master Replication, you know it is when you see a dreaded error that stops replication, such as:

Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
Error 'Duplicate entry 'value' for key 1' on query. Default database: 'break_me'. Query: 'insert into data_table (id, code, data) values ('', 'GYIQB', 'SHBK27637BJD')'

Though you can usually just run “SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;”, there is always worried about data consistency across the two master MySQL servers.

Here is a quick run through of how to sync and re-replicate to ensure data is consistent between the two master nodes:

For the purposes of this article, I've named two machines, SERVER01  (192.168.0.198) and SERVER02 (192.168.0.199), please edit accordingly.

-- SERVER01 (192.168.0.198)
---- STOP SLAVE;

-- SERVER02 (192.168.0.199)
---- STOP SLAVE;

-- SERVER01 (192.168.0.198)
---- GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.0.199' IDENTIFIED BY 'PASSWORD_HERE';
---- FLUSH PRIVILEGES;
---- RESET MASTER;
---- FLUSH TABLES WITH READ LOCK;
---- SHOW MASTER STATUS;
------ MASTER_PASSWORD: PASSWORD_HERE
------ MASTER_LOG_FILE: 000001 (File)
------ MASTER_LOG_POS: 98 (Position)

-- SERVER01 (192.168.0.198) - Separate SSH Session (Important!)
---- mysqldump --all-databases --verbose > transfer.sql
---- scp transfer.sql root@192.168.0.199:~

-- SERVER01 (192.168.0.198)
---- UNLOCK TABLES;

-- SERVER02 (192.168.0.199)
-- mysql < transfer.sql
---- GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.0.198' IDENTIFIED BY 'PASSWORD_HERE';
---- FLUSH PRIVILEGES;
---- RESET MASTER;
---- FLUSH TABLES WITH READ LOCK;
---- SHOW MASTER STATUS;
------ MASTER_PASSWORD: PASSWORD_HERE
------ MASTER_LOG_FILE: 000001(File)
------ MASTER_LOG_POS: 98 (Position)
---- UNLOCK TABLES;
---- CHANGE MASTER TO MASTER_HOST='192.168.0.198', MASTER_USER='replication', MASTER_PASSWORD='PASSWORD_HERE', MASTER_LOG_FILE='mysql-bin.SERVER01_FILE_NUMBER', MASTER_LOG_POS=SERVER01_LOG_POS;
---- START SLAVE;

-- SERVER01 (192.168.0.198)
---- CHANGE MASTER TO MASTER_HOST='192.168.0.199', MASTER_USER='replication', MASTER_PASSWORD='PASSWORD_HERE', MASTER_LOG_FILE='mysql-bin.SERVER02_FILE_NUMBER', MASTER_LOG_POS=SERVER02_LOG_POS;
---- START SLAVE;

After this, you can view the status of your replication using:

mysql -e "SHOW SLAVE STATUS\G";

If everything went right, you should see something like this:

*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.0.198
                Master_User: replication
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 21835973
             Relay_Log_File: relay-log.000006
              Relay_Log_Pos: 5277163
      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: 21835973
            Relay_Log_Space: 5277163
            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
1 row in set (0.00 sec)

Post a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Blog is powered by WordPress and designed by Chico Web Design