记一次MySQL Slave库恢复实战记录

mysql> show slave status\G;
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
         Master_Host: *.*.*.*
         Master_User: dbsync
         Master_Port: 3306
        Connect_Retry: 60
       Master_Log_File: mysql-bin.000095
     Read_Master_Log_Pos: 869242147
        Relay_Log_File: mysqld-relay-bin.000146
        Relay_Log_Pos: 871280529
    Relay_Master_Log_File: mysql-bin.000075
       Slave_IO_Running: Yes
      Slave_SQL_Running: No
       Replicate_Do_DB: cdb,cdb_admin
     Replicate_Ignore_DB: mysql
      Replicate_Do_Table: 
    Replicate_Ignore_Table: 
   Replicate_Wild_Do_Table: 
 Replicate_Wild_Ignore_Table: 
          Last_Errno: 1594
          Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
         Skip_Counter: 0
     Exec_Master_Log_Pos: 871280384
       Relay_Log_Space: 19994786573
       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: 0
        Last_IO_Error: 
        Last_SQL_Errno: 1594
        Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
1 row in set (0.00 sec)

ERROR: 
No query specified

slave stop;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000095',MASTER_LOG_POS=869242147; //mysql master节点上mysql-bin.000095的已有位置
slave start;

mysql> show master status;
+------------------+-----------+-------------------------------+------------------+
| File       | Position | Binlog_Do_DB         | Binlog_Ignore_DB |
+------------------+-----------+-------------------------------+------------------+
| mysql-bin.000095 | 871760173 | cdb,cdb_admin | mysql      |
+------------------+-----------+-------------------------------+------------------+
1 row in set (0.01 sec)
 mysqldump -u root -p --databases cdb,cdb_admin > bak.master.sql

mysqldump -u root -p --databases cdb,cdb_admin > bak.slave.sql

mysql -u root -p < bak.master.sql

slave stop;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000095',MASTER_LOG_POS=871760173; //POS为刚才记录的master节点日志记录位置
slave start;