1.slave中继日志损坏

         slave意外宕机时,有可能损坏中继日志relay-log,再次开启同步复制时,就会报错:

Last_SQL_Error:Relay log read failure: Could not parse relay log event entry. The possiblereasons are: the master's binary log is corrupted (you can check this byrunning 'mysqlbinlog' on the binary log), the slave's relay log is corrupted(you can check this by running 'mysqlbinlog' on the relay log), a networkproblem, or a bug in the master's or slave's MySQL code. If you want to checkthe master's binary log or slave's relay log, you will be able to know theirnames by issuing 'SHOW SLAVE STATUS' on this slave.

1.1.  模拟中继日志损坏

[root@gflinux103log]# rm relay-bin.00001*

         把中继日志全部删除。

1.2. 查看主从数据一致性

master

mysql>delete from t1 where id=7;

Query OK,1 row affected (0.00 sec)

 

mysql>select * from t1;

+----+

| id |

+----+

|  1 |

|  2 |

|  3 |

+----+

3 rows inset (0.00 sec)

slave

mysql>select * from t1;

+----+

| id |

+----+

|  1 |

|  2 |

|  3 |

|  7 |

+----+

4 rows inset (0.00 sec)

mysql>stop slave;

Query OK,0 rows affected (0.01 sec)

 

mysql>start slave;

Query OK,0 rows affected (0.01 sec)

mysql>show slave status\G;

***************************1. row ***************************

               Slave_IO_State: Waiting formaster to send event

                  Master_Host: 192.168.6.100

                  Master_User: repl_user

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: bin-log.000001

          Read_Master_Log_Pos: 7335

               Relay_Log_File: relay-bin.000012

                Relay_Log_Pos: 492

        Relay_Master_Log_File: bin-log.000001

             Slave_IO_Running: Yes

            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: 1594

                   Last_Error: Relay log readfailure: Could not parse relay log event entry. The possible reasons are: themaster'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 byrunning 'mysqlbinlog' on the relay log), a network problem, or a bug in themaster's or slave's MySQL code. If you want to check the master's binary log orslave's relay log, you will be able to know their names by issuing 'SHOW SLAVESTATUS' on this slave.

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 7335

              Relay_Log_Space: 2211

              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 readfailure: Could not parse relay log event entry. The possible reasons are: themaster'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 byrunning 'mysqlbinlog' on the relay log), a network problem, or a bug in themaster's or slave's MySQL code. If you want to check the master's binary log orslave's relay log, you will be able to know their names by issuing 'SHOW SLAVESTATUS' on this slave.

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 10

                  Master_UUID:014ede19-f822-11e3-94e4-080027be9228

             Master_Info_File:/opt/mysql/mysql/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State:

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp: 140730 13:52:58

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set:

                Auto_Position: 0

1 row inset (0.00 sec)

         查看中继日志及索引信息:

[root@gflinux103log]# ll

total1178316

-rw-rw----1 mysql mysql       143 Jul  8 13:59 bin-log.000049

-rw-rw----1 mysql mysql       390 Jul  8 08:48 bin-log.index

-rw-r-----1 mysql root    2169794 Jul 30 13:52gflinux.err

-rw-rw----1 mysql mysql       120 Jun 24 11:05gflinux.index

-rw-rw----1 mysql mysql 645656511 Jul 29 17:26 gfsunny-slow.logs

-rw-rw----1 mysql mysql       281 Jul 30 13:52relay-bin.000001

-rw-rw----1 mysql mysql        96 Jul 30 13:52relay-bin.index

[root@gflinux103log]#

[root@gflinux103log]# more relay-bin.index

/opt/mysql/log/relay-bin.000012

/opt/mysql/log/relay-bin.000012

/opt/mysql/log/relay-bin.000001

[root@gflinux103log]#

1.3.解决办法

1.3.1.手工处理

         找到同步的binlog日志和pos点,然后重新同步,这样就可以有新的同步日志了。

         参数解读:

Slave_IO_Running:  Yes

接收masterbinlog信息

Master_Log_File:  bin-log.000001

正在读取masterbinlog日志名

Read_Master_Log_Pos:  7335

正在读取masterbinlog日志POS

Slave_SQL_Running:  No

执行写操作

Relay_Master_Log_File:  bin-log.000001

正在同步master上的binlog日志名

Exec_Master_Log_Pos: 7335

正在同步当前binlog日志的Pos

         1)以Relay_Master_Log_FileExec_Master_Log_Pos: 7335参数值为基准,重置主从复制。

mysql>change master to master_log_file='bin-log.000001',master_log_pos=7335;

Query OK,0 rows affected, 2 warnings (0.01 sec)

         2)重启从复制进程,查看其状态

mysql>start slave;

Query OK,0 rows affected (0.01 sec)

 

mysql>show slave status\G;

***************************1. row ***************************

               Slave_IO_State: Waiting formaster to send event

                  Master_Host: 192.168.6.100

                  Master_User: repl_user

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: bin-log.000001

          Read_Master_Log_Pos: 7335

               Relay_Log_File: relay-bin.000002

                Relay_Log_Pos: 281

        Relay_Master_Log_File: bin-log.000001

             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: 7335

              Relay_Log_Space: 448

              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:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 10

                  Master_UUID:014ede19-f822-11e3-94e4-080027be9228

             Master_Info_File:/opt/mysql/mysql/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has readall relay log; waiting for the slave I/O thread to update it

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set:

                Auto_Position: 0

1 row inset (0.00 sec)

         3)查看中继日志及索引信息

[root@gflinux103log]# ll

mysql       143 Jul 8 13:59 bin-log.000049

-rw-rw----1 mysql mysql       390 Jul  8 08:48 bin-log.index

-rw-r-----1 mysql root    2171426 Jul 30 14:21gflinux.err

-rw-rw----1 mysql mysql       120 Jun 24 11:05 gflinux.index

-rw-rw----1 mysql mysql 645656511 Jul 29 17:26 gfsunny-slow.logs

-rw-rw----1 mysql mysql       167 Jul 30 14:21relay-bin.000001

-rw-rw----1 mysql mysql       281 Jul 30 14:21relay-bin.000002

-rw-rw----1 mysql mysql        64 Jul 30 14:21relay-bin.index

[root@gflinux103log]# more relay-bin.index

/opt/mysql/log/relay-bin.000001

/opt/mysql/log/relay-bin.000002

[root@gflinux103log]#

         索引信息自动更新。

1.3.2.自动处理

         mysql5.5考虑到slave宕机中继日志损坏这一问题,只要在slave的的配置文件my.cnf里增加一个参数relay_log_recovery=1即可。

         salve的重要参数:relay_log_recovery=1

mysql>show variables like 'relay%';

+---------------------------+--------------------------------+

|Variable_name             | Value                          |

+---------------------------+--------------------------------+

|relay_log                 |/opt/mysql/log/relay-bin       |

|relay_log_basename        |/opt/mysql/log/relay-bin       |

|relay_log_index            | /opt/mysql/log/relay-bin.index |

|relay_log_info_file          | relay-log.info                 |

|relay_log_info_repository    | FILE                           |

|relay_log_purge            | ON                             |

|relay_log_recovery          |OFF                            |

|relay_log_space_limit        | 0                              |

+---------------------------+--------------------------------+

8 rows inset (0.00 sec)

relay_log_purge:启用和禁用中继日志的自动清理,只要他们不需要的时候,默认是启用。全局变量,可以自动修改。

relay_log_recovery={0|1}:紧随服务器启动立即启用自动中继日志恢复。默认值是0,禁用。

relay_log_space_limit:默认为0,没有限制。