[MySQL] 一次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库上重新指定同步位置。(不可行)
  

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

• 重做slave库。
  

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

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