如何精准定位并解决MySQL主从复制过程中出现的各种复杂故障问题？
- 内容介绍
- 文章标签
- 相关推荐
本文共计410个文字，预计阅读时间需要2分钟。
1. 主机器发生一个SQL导导致所有从机同步出错的问题解决流程： a. 进入从机的MySQL控制台 b. 查看复制作业的进程信息：`mysql + -e SHOW SLAVE STATUS\G`
2. 解决流程： a. 进入从机的MySQL控制台 b. 查看复制作业的进程信息，确认错误原因 c. 根据错误原因进行相应的修复操作
一.主机器发出一个sql导致所有的从机同步出错的问题解决流程a.)进入从机的mysql控制台b.)查看复制进程的信息mysql一. 主机器发出一个sql导致所有的从机同步出错的问题解决流程 a.)进入从机的mysql控制台
b.) 查看复制进程的信息
mysql> show slave status\G
c.) 关注以下3个key及其内容
Master_Host192.168.1.212
Relay_Master_Log_Filemysql-master-001-bin.000348
Exec_Master_Log_Pos330637
*************************** 1. row ***************************Slave_IO_State:Master_Host: 192.168.1.212Master_User: replicationMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-master-001-bin.000348Read_Master_Log_Pos: 330637Relay_Log_File: mysql-slave-001-bin.000345Relay_Log_Pos: 4Relay_Master_Log_File: mysql-master-001-bin.000348Slave_IO_Running: NoSlave_SQL_Running: YesReplicate_Do_DB: meerkatReplicate_Ignore_DB: mysql,information_schemaReplicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 330637Relay_Log_Space: 151Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 1236Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: Could not find first log file name in binary log index fileLast_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 2Master_UUID: 3d3b9f4f-f74f-11e5-9a30-005056b324c4Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update itMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp: 160719 14:16:46Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set: 3d3b9f4f-f74f-11e5-9a30-005056b324c4:1-22:26-3944,40844d86-f74f-11e5-9a30-005056b370b3:1-4,73ae7f45-f74c-11e5-9a1e-005056b3294e:1-27485277:27485279-27487144Auto_Position: 01 row in set (0.00 sec)
d.) 根据上面的3个可以的内容修复同步
mysql> stop slave ;mysql> CHANGE MASTER TO MASTER_HOST192.168.1.212, MASTER_LOG_FILEmysql-master-001-bin.000348, MASTER_LOG_POS330637 , MASTER_AUTO_POSITION 0 ;mysql> start slave ;mysql> show slave status\G
本文共计410个文字，预计阅读时间需要2分钟。
1. 主机器发生一个SQL导导致所有从机同步出错的问题解决流程： a. 进入从机的MySQL控制台 b. 查看复制作业的进程信息：`mysql + -e SHOW SLAVE STATUS\G`
2. 解决流程： a. 进入从机的MySQL控制台 b. 查看复制作业的进程信息，确认错误原因 c. 根据错误原因进行相应的修复操作
一.主机器发出一个sql导致所有的从机同步出错的问题解决流程a.)进入从机的mysql控制台b.)查看复制进程的信息mysql一. 主机器发出一个sql导致所有的从机同步出错的问题解决流程 a.)进入从机的mysql控制台
b.) 查看复制进程的信息
mysql> show slave status\G
c.) 关注以下3个key及其内容
Master_Host192.168.1.212
Relay_Master_Log_Filemysql-master-001-bin.000348
Exec_Master_Log_Pos330637
*************************** 1. row ***************************Slave_IO_State:Master_Host: 192.168.1.212Master_User: replicationMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-master-001-bin.000348Read_Master_Log_Pos: 330637Relay_Log_File: mysql-slave-001-bin.000345Relay_Log_Pos: 4Relay_Master_Log_File: mysql-master-001-bin.000348Slave_IO_Running: NoSlave_SQL_Running: YesReplicate_Do_DB: meerkatReplicate_Ignore_DB: mysql,information_schemaReplicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 330637Relay_Log_Space: 151Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 1236Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: Could not find first log file name in binary log index fileLast_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 2Master_UUID: 3d3b9f4f-f74f-11e5-9a30-005056b324c4Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update itMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp: 160719 14:16:46Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set: 3d3b9f4f-f74f-11e5-9a30-005056b324c4:1-22:26-3944,40844d86-f74f-11e5-9a30-005056b370b3:1-4,73ae7f45-f74c-11e5-9a1e-005056b3294e:1-27485277:27485279-27487144Auto_Position: 01 row in set (0.00 sec)
d.) 根据上面的3个可以的内容修复同步
mysql> stop slave ;mysql> CHANGE MASTER TO MASTER_HOST192.168.1.212, MASTER_LOG_FILEmysql-master-001-bin.000348, MASTER_LOG_POS330637 , MASTER_AUTO_POSITION 0 ;mysql> start slave ;mysql> show slave status\G