第一步,首先如下检查:
//查看slave状态
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.112.128
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 416
Relay_Log_File: mysql-relay-bin.000005
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000002
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: 1062
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000002, end_log_pos 385. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
如下表明:slave_SQL线程停止工作了
Slave_IO_Running: Yes
Slave_SQL_Running: No
看如下报错
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000002, end_log_pos 385. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
翻译如下:
Last _ error: coordinator 停止,因为在 worker (s)中有错误。最近的失败是: 工作者1在主日志 mysql-bin. 000002,end _ log _ pos 385执行事务‘ anonymous’失败。请参阅错误日志和/或 performance _ schema。复制 _ applier _ status _ by _ worker 表,以获取有关此故障或其他故障的更多详细信息。
要想看详细日志,用如下命令:
select * from performance_schema.replication_applier_status_by_worker\G
那就简单了,那我们就从mysql-bin. 000002,end _ log _ pos 385,开始复制,重新启动slave服务即可;
第二步,操作如下:
//停止服务
mysql> stop slave;
第三步,重新设置从主库复制,复制binglog文件名称就是上面的mysql-bin. 000002,end _ log _ pos 385即可,如下示例:
mysql> change master to master_host='192.168.112.128',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000002',master_log_pos=385;
参数的注释:
master_host=‘主库的ip’
master_port=主库的端口
master_user='主库的用户root'
master_password='主库的密码'
master_log_file='主库的binglog日志,文件名称'
master_log_pos=从主库复制的起始位置
第四步:
//重新启动从库
mysql>start slave;
第五步,可以再用上面的命令检查一下,复制主从复制是否正常启动,再去看看数据是否一致了:
mysql> show slave status \G;
这样,我们就恢复了主从复制,并且从库和主库的数据保持了一致,如果对你有用点个赞呗