MySQL replication out of sync

Question

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.

Answer 1

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.

Answer 2

If you need to reset the slaves, and you have a good dump that has the master data information (mysqldump with the --master-data option), you can use the last dump to resync:

Connect to the MySQL slave and issue:

STOP SLAVE;
RESET SLAVE;
CHANGE MASTER to MASTER_USER='master user', MASTER_PASSWORD='master user password';

On the slave, reload the slave database from the last master dump you have:

gzip -dc masterdump.sql.gz |mysql --user=username --password=password

Restart the slave:

START SLAVE

If all goes well, the slave will start replicating from the spot of the last dump that you loaded. This may take some time, so check the SECONDS BEHIND MASTER to wait for it to catch up.

Answer 3

Seconds_Behind_Master: 1376343

That would be why you're not seeing the database you created, because the slave is still churning through the masters binary log. When it is 0 seconds behind the master you will start to see the changes you make replicate across to the slave.

Answer 4

If all else fails, remove the "bad" master from the service, dump and restore a new clean copy of the database to it. Follow the usual steps for "adding another master" to do this.

Answer 5

Try to see if it's not a firewall issue. Try to stop and restart the slave. this : Slave_IO_Running: Yes Slave_SQL_Running: Yes means that everything seems to be ok... could you past the replication settings ?

Answer 6

Note that by skipping 99,999 log events your master and slave will most likely not contain the same data. You can use the mk-table-checksum script to check the master and slave content.

I never skip more than one event at a time and always try to understand the cause of any replication errors. If I get several/many replication errors in a row it is usually a symptom of other issues (corrupt table/disk for example).