儿童 网站 设计,学校网站建设及使用,安卓手机做网站,高端外贸建站由于平台虚拟机宿主机迁移#xff0c;导致一套MySQL主从库从节点故障#xff0c;从节点服务终止#xff0c;在服务启动后#xff0c;恢复从节点同步服务#xff0c;发现了如下报错#xff1a;
mysql show slave status\G; *************************** 1. row *****…
由于平台虚拟机宿主机迁移导致一套MySQL主从库从节点故障从节点服务终止在服务启动后恢复从节点同步服务发现了如下报错
mysql show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: XXXX Master_User: cdbsync Master_Port: 3366 Connect_Retry: 60 Master_Log_File: mysql-bin.007843 Read_Master_Log_Pos: 78338072 Relay_Log_File: nxscjdtsjkmysqlzc-mysql-master-2-367f7-0-relay-bin.023264 Relay_Log_Pos: 78930577 Relay_Master_Log_File: mysql-bin.007841 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: 13121 Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the masters binary log is corrupted (you can check this by running mysqlbinlog on the binary log), the slaves relay log is corrupted (you can check this by running mysqlbinlog on the relay log), a network problem, the server was unable to fetch a keyring key required to open an encrypted relay log file, or a bug in the masters or slaves MySQL code. If you want to check the masters binary log or slaves 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: 78930363 Relay_Log_Space: 300215564 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: 13121 Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the masters binary log is corrupted (you can check this by running mysqlbinlog on the binary log), the slaves relay log is corrupted (you can check this by running mysqlbinlog on the relay log), a network problem, the server was unable to fetch a keyring key required to open an encrypted relay log file, or a bug in the masters or slaves MySQL code. If you want to check the masters binary log or slaves relay log, you will be able to know their names by issuing SHOW SLAVE STATUS on this slave. Replicate_Ignore_Server_Ids: Master_Server_Id: 2887743427 Master_UUID: e4a6bc6b-9de0-11eb-b5e2-fa163e5aaa5e Master_Info_File: mysql.slave_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: 241016 02:50:23 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: e4a6bc6b-9de0-11eb-b5e2-fa163e5aaa5e:1-9339028 Executed_Gtid_Set: e4a6bc6b-9de0-11eb-b5e2-fa163e5aaa5e:1-9337179 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 1 Network_Namespace: 1 row in set (0.00 sec)
ERROR: No query specified
mysql
查看报错信息提示由于日志损坏、网络问题或bug导致无法获取中继日志中的事件信息。 在尝试了跳过从节点错误方法后依旧不能正常同步报错还是一样。 于是就计划reset slave重新从中断的日志及position开始复制整个操作流程如下
确认Relay_Master_Log_File和Exec_Master_Log_Pos信息 Relay_Master_Log_File: mysql-bin.007841 Exec_Master_Log_Pos: 78930363
STOP SLAVE; RESET SLAVE; CHANGE MASTER TO master_log_filemysql-bin.007841, master_log_pos78930363; START SLAVE;
操作过程 mysql stop slave; Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql RESET SLAVE; Query OK, 0 rows affected (0.10 sec)
mysql CHANGE MASTER TO master_log_filemysql-bin.007841, master_log_pos78930363; ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.
根据提示信息在从节点上设置master_auto_position为0 mysql change master to master_auto_position0; Query OK, 0 rows affected (0.08 sec)
mysql CHANGE MASTER TO master_log_filemysql-bin.007841, master_log_pos78930363; Query OK, 0 rows affected (0.06 sec)
重新启动slave mysql mysql start slave; Query OK, 0 rows affected (0.01 sec) mysql stop slave; Query OK, 0 rows affected (0.03 sec)
同步正常后将master_auto_position修改为1并重新启动同步
mysql change master to master_auto_position - 1; Query OK, 0 rows affected (0.05 sec)
mysql start slave; Query OK, 0 rows affected (0.02 sec)
mysql show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: XXXX Master_User: cdbsync Master_Port: 3366 Connect_Retry: 60 Master_Log_File: mysql-bin.007843 Read_Master_Log_Pos: 79142894 Relay_Log_File: nxscjdtsjkmysqlzc-mysql-master-2-367f7-0-relay-bin.000002 Relay_Log_Pos: 10529 Relay_Master_Log_File: mysql-bin.007843 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: 79142894 Relay_Log_Space: 10772 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: 2887743427 Master_UUID: e4a6bc6b-9de0-11eb-b5e2-fa163e5aaa5e Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: e4a6bc6b-9de0-11eb-b5e2-fa163e5aaa5e:9339373-9339378 Executed_Gtid_Set: e4a6bc6b-9de0-11eb-b5e2-fa163e5aaa5e:1-9339378 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 1 Network_Namespace: 1 row in set (0.00 sec)
ERROR: No query specified
mysql
至此整个同步报错处理完成。
