Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
How did you take snapshot from Master to Slave? I saw a big different between them (binlog & position) –  quanta Oct 19 '11 at 9: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 9:30
    
000307 & 000722 and 106 & 101043816. The best way is take snapshot again: dev.mysql.com/doc/refman/5.0/en/… –  quanta Oct 19 '11 at 9:55

1 Answer 1

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.

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.