mysql replication - slave seems up to date but data not synchronized

Question

I have a master-slave setup with 2 servers over a network. Due to an error, the slave IO thread stopped, though I could get it started and running, the slave is way behind the master. Also, SHOW SLAVE STATUS shows Seconds_Behind_Master: 0. Have a look:

               Slave_IO_State: Waiting for master to send event
                  Master_Host: <master ip>
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000722
          Read_Master_Log_Pos: 101043816
               Relay_Log_File: localhost-relay-bin.000008
                Relay_Log_Pos: 101043961
        Relay_Master_Log_File: mysql-bin.000722
             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: 101043816
              Relay_Log_Space: 101044163
              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:

Do the slave was in sync until recently, so I haven't taken a snapshot from master to slave. Should I do this?
Any help is much appreciated.
Thanks in advance.

How did you take snapshot from Master to Slave? I saw a big different between them (binlog & position) — quanta, Oct 19 '11 at 09:25
The master has binary logging. Could please tell me more about the difference you saw and what that could mean? — shyam, Oct 19 '11 at 09:30
000307 & 000722 and 106 & 101043816. The best way is take snapshot again: http://dev.mysql.com/doc/refman/5.0/en/replication-howto-existingdata.html — quanta, Oct 19 '11 at 09:55

score 1 · Answer 1 · edited Apr 13 '17 at 12:43

What is unusual about the IO Thread is that sometimes if there is any heavy network layer intermittency, the IO Thread will simply stop reading being unaware of that level.

With regard to the data itself, you could just take the snapshot, reset the log file and position via CHANGE MASTER TO, and be done with it. However, if the dataset is too big and takes too long to create, you should look into using data synchronization tools from Percona.

mk-table-checksum and mk-table-sync

I have used these tools for about 2 years and they help you hunt down differences in tables between master and slave, even if the table on the master is InnoDB and the same table on the slave is MyISAM (provided the tables have the same table structure).

Replication must be on while running these tools.

BTW Percona has a new set of tools called the Percona Toolkit. They forked away from the own MAATKIT tools to make better one. The tools are probably called pt-table-checksum and pt-table-sync.

I hope the tools you suggested works. The dataset is too large. I tried taking the snapshot, but while trying to import it to the slave I encountered `ERROR 2013 (HY000) at line 1654: Lost connection to MySQL server during query`. — shyam, Oct 19 '11 at 13:12

mysql replication - slave seems up to date but data not synchronized

1 Answers1