MySQL replication out of sync

databaseMySQLreplicationsynchronization

I have a master-master replication system.
However, due to an auto-increment issue, I got an error in replication…and it stopped replicating.

Someone told me to do:

stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;  start slave;

It didn't work. Then they told me to do:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2;

It didn't work. Then to test it out, I did:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 99999;

It starts, but it is not updating. I created a table on DB1…and it is not showing up on DB2…

Below are the SHOW STATUS for both my DB1 and DB2 (I hit them together):

mysql> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000605
        Position: 2019727
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host:
                Master_User: 
                Master_Port: 
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000605
        Read_Master_Log_Pos: 2008810
             Relay_Log_File: mysqld-relay-bin.001731
              Relay_Log_Pos: 10176595
      Relay_Master_Log_File: mysql-bin.000470
           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: 4255373725
        Exec_Master_Log_Pos: 10176458
            Relay_Log_Space: 135062517347
            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: 1376343
1 row in set (0.00 sec)

How do I fix it so that they sync back up again?
Thank you.

Best Answer

You don't really seem to know what you are doing. First you should get a dump of data to safeguard against any kind of corruption.

So you're using a Master-Master replication, right?

This sounds strange, since you only provide one SLAVE status and one MASTER status, but we'd need both MASTER and both SLAVE status outputs. Further, there are no replicate_ignore_* statements, this doesn't look like a Master-Master replication. Also, there are no error statements in the SLAVE STATUS. They would have shown any replication errors.

How to fix this: Get a clean dump from each master and insert it on the other master. It is described in the mysql manual, here is the short version:

mysql> FLUSH TABLES WITH READ LOCK;
$> mysqldump -uroot -p<pass> --opt --all-databases | gzip --fast > dump_master1.sql.gz
mysql> UNLOCK TABLES;

Remember: Don't just execute a command someone told you! Look it up in the manual first and check what it does. Setting your SKIP_SLAVE_COUNTER to someting like 9999 is the reason why your newly create table isn't showing up on your slave becaus it skips 9999 sql statements, which probably haven't happened yet! Usually you don't set SKIP_SLAVE_COUNTER to more the 1, then execute START SLAVE and see with SHOW SLAVE STATUS if there are any new errors.

Related Topic