温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

slave的中继日志relay-log损坏

发布时间:2020-08-05 10:38:06 来源:ITPUB博客 阅读:326 作者:StevenBeijing 栏目:MySQL数据库
  当slave意外宕机时,有可能会损坏中继日志relay-log,再次开启同步复制时,会出现报错。
  解决方法:找到同步的binlog日志和POS点,然后重新进行同步,这样就可以有新的中继日志了。
  下边看个案例,模拟了中继日志损坏的情况,查看到的信息如下:

点击(此处)折叠或打开

  1. mysql> show slave status \G
  2. ERROR 2006 (HY000): MySQL server has gone away
  3. No connection. Trying to reconnect...
  4. Connection id: 4
  5. Current database: test

  6. *************************** 1. row ***************************
  7.                Slave_IO_State: Waiting for master to send event
  8.                   Master_Host: 10.10.10.200
  9.                   Master_User: repl
  10.                   Master_Port: 3306
  11.                 Connect_Retry: 60
  12.               Master_Log_File: mysql-bin.000005
  13.           Read_Master_Log_Pos: 220
  14.                Relay_Log_File: mysql-relay-bin.000010
  15.                 Relay_Log_Pos: 283
  16.         Relay_Master_Log_File: mysql-bin.000004
  17.              Slave_IO_Running: Yes
  18.             Slave_SQL_Running: No
  19.               Replicate_Do_DB:
  20.           Replicate_Ignore_DB:
  21.            Replicate_Do_Table:
  22.        Replicate_Ignore_Table:
  23.       Replicate_Wild_Do_Table:
  24.   Replicate_Wild_Ignore_Table:
  25.                    Last_Errno: 1032
  26.                    Last_Error: Could not execute Update_rows event on table test.test01; Can't find record in 'test01', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000004, end_log_pos 310
  27.                  Skip_Counter: 0
  28.           Exec_Master_Log_Pos: 120
  29.               Relay_Log_Space: 2251
  30.               Until_Condition: None
  31.                Until_Log_File:
  32.                 Until_Log_Pos: 0
  33.            Master_SSL_Allowed: No
  34.            Master_SSL_CA_File:
  35.            Master_SSL_CA_Path:
  36.               Master_SSL_Cert:
  37.             Master_SSL_Cipher:
  38.                Master_SSL_Key:
  39.         Seconds_Behind_Master: NULL
  40. Master_SSL_Verify_Server_Cert: No
  41.                 Last_IO_Errno: 0
  42.                 Last_IO_Error:
  43.                Last_SQL_Errno: 1032
  44.                Last_SQL_Error: Could not execute Update_rows event on table test.test01; Can't find record in 'test01', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000004, end_log_pos 310
  45.   Replicate_Ignore_Server_Ids:
  46.              Master_Server_Id: 1
  47.                   Master_UUID: 4adfcd1d-4059-11e7-9532-080027d597f9
  48.              Master_Info_File: mysql.slave_master_info
  49.                     SQL_Delay: 0
  50.           SQL_Remaining_Delay: NULL
  51.       Slave_SQL_Running_State:
  52.            Master_Retry_Count: 86400
  53.                   Master_Bind:
  54.       Last_IO_Error_Timestamp:
  55.      Last_SQL_Error_Timestamp: 170808 11:45:26
  56.                Master_SSL_Crl:
  57.            Master_SSL_Crlpath:
  58.            Retrieved_Gtid_Set:
  59.             Executed_Gtid_Set:
  60.                 Auto_Position: 0
  其中,涉及几个重要的参数:
slave_IO_Running:接受master的binlog的信息
master_Log_file:正在读取master上binlog日志名
Read_master_Log_Pos:正在读取master上当前binlog日志POS点。
slave_SQL_Running:执行写操作
Relay_master_Log_File:正在同步master上的binlog日志名。
Exec_master_Log_Pos:正在同步当前binlog日志的POS点。
 以Relay_master_Log_File参数值和Exec_master_Log_Pos参数值为基准。
Relay_Log_File: mysql-relay-bin.000010
Relay_Log_Pos: 283
  接下来开始重置主从复制:

点击(此处)折叠或打开

  1. mysql> stop slave;
  2. Query OK, 0 rows affected (0.04 sec)

  3. mysql> change master to master_log_file='mysql-relay-bin.000010',master_log_pos=283;
  4. Query OK, 0 rows affected (0.06 sec)

  5. mysql> start slave;
  6. Query OK, 0 rows affected (0.12 sec)
  通过这种方法可以修复中继日志,但是在MySQL5.5版本之后,在slave的my.cnf配置文件中加入relay_log_recovery=1就可以了。

向AI问一下细节
推荐阅读:
  1. DHCP中继原理
  2. DHCP中继

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI