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 can't sync the slave server after master server crashed. Could you please advise if the recovery is possible (without data/transaction lost) or do I have to create a new slave server?

Please note that I stooped slave after this error:

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.1.1
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000010
          Read_Master_Log_Pos: 940132528
               Relay_Log_File: cb1s-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000010
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 940132528
              Relay_Log_Space: 107
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysql-bin.000010' at 940132528, the last event read from './mysql-bin.000010' at 4, the last byte read from './mysql-bin.000010' at 4.'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

Logs:

120711 13:04:24 [Note] Slave I/O thread: connected to master 'user@192.168.1.1:3306',replication started in log 'mysql-bin.000010' at position 940132528
120711 13:04:24 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position; the first event 'mysql-bin.000010' at 940132528, the last event read from './mysql-bin.000010' at 4, the last byte read from './mysql-bin.000010' at 4. ( server_errno=1236)
120711 13:04:24 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysql-bin.000010' at 940132528, the last event read from './mysql-bin.000010' at 4, the last byte read from './mysql-bin.000010' at 4.', Error_code: 1236
120711 13:04:24 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000010', position 940132528
120711 13:04:24 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000010' at position 940132528, relay log './cb1s-relay-bin.000026' position: 940132674
120711 13:05:08 [Note] Error reading relay log event: slave SQL thread was killed
120711 13:06:20 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000010' at position 940132528, relay log './cb1s-relay-bin.000027' position: 4
share|improve this question
3  
The solution is explained here and here. –  David Schwartz Jul 11 '12 at 13:55

1 Answer 1

up vote 1 down vote accepted

You don't have to create a new slave. You just would need to replay the master's data and re-set the replication coordinates on the slave (CHANGE MASTER TO ...) as described in the MySQL documentation.

You might get the data off the master without stopping the database by specifying the --master-data=2 option to your mysqldump call - this would write the replication coordinates of your dump into the mysqldump output.

Note that running mysqldump could still mean a downtime for your database-backed application as all tables would be read-locked for the duration of the dump.

Another option would be to just reset the replication coordinates as suggested by David Schwartz in his comment to your question and replay all table's data to the slave(s) using pt-table-sync from the Percona Toolkit to ensure integrity.

share|improve this answer

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.