mysql因为掉电,导致不同步的恢复
发布日期:2021-09-16 04:38:31
浏览次数:70
分类:技术文章
本文共 6233 字,大约阅读时间需要 20 分钟。
有mysql-mm数据库一套,36 和37 ,由于掉电,导致其中一台数据库down机,然后查看数据库日志发现 如果目前以36为主,37为slave 查看37服务器 2015-06-19 10:23:09 3109 [ERROR] Slave I/O: error connecting to master 'repl_user@ip' - retry-time: 60 retries: 1, Error_code: 2003 2015-06-19 10:24:09 3109 [Note] Slave I/O thread: connected to master 'repl_user@ip',replication started in log 'mysql-bin.000377' at position 16063934 2015-06-19 11:19:22 3109 [Warning] Hostname 'localhost' does not resolve to 'ip'. 2015-06-19 11:19:22 3109 [Note] Hostname 'localhost' has the following IP addresses: 2015-06-19 11:19:22 3109 [Note] - ::1 2015-06-19 11:19:22 3109 [Note] - 127.0.0.1 mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host:ip Master_User: repl_user Master_Port: 3366 Connect_Retry: 60 Master_Log_File: mysql-bin.000389 Read_Master_Log_Pos: 23841730 Relay_Log_File: mysql-bin.000464 Relay_Log_Pos: 852 Relay_Master_Log_File: mysql-bin.000377 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: mysql,information_schema,performance_schema,test Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1032 Last_Error: Could not execute Delete_rows event on table zjstms.tak_trackdetails; Can't find record in tak_trackdetails', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000377, end_log_pos 16064750 Skip_Counter: 0 Exec_Master_Log_Pos: 16064503 Relay_Log_Space: 322379477 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: 1032 Last_SQL_Error: Could not execute Delete_rows event on table zjstms.tak_trackdetails; Can't find record in 'tak_trackdetails', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000377, end_log_pos 16064750 Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: aa90a402-a73c-11e4-8ab7-a0369f338279 Master_Info_File: /usr/local/mysql/data/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: 150623 17:25:59 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec) 如果下面参数是NO,对应修改的的参数是: Slave_IO_Running: 指的就是从服务器上负责读取主服务器的线程工作状态。 从服务器用这个专门的线程链接到主服务器上,并把日志拷贝回来。 Slave_SQL_Running: 指的就是专门执行sql的线程。 它负责把复制回来的Relaylog执行到自己的数据库中。 这两个参数必须都为Yes 才表明复制在正常工作。 Master_Log_File & Read_Master_Log_Pos:下一个传输的主日志信息(其实就是主服务器上的日志位置)。 Relay_Master_Log_File & Exec_Master_Log_Pos:下一个执行的主日志信息。 Relay_Log_File & Relay_Log_Pos:下一个执行的中继日志信息。从字面意思看, (Relay_Master_Log_File, Exec_Master_Log_Pos)和(Relay_Log_File, Relay_Log_Pos)是对应的,它们表示的是Slave中的SQL进程中正在执行的语句的位置,表明的是Slave和Master之间的同步状态。当Slave中Relay_Master_Log_File和Master_Log_File相同且Read_Master_Log_Pos和Exec_Master_Log_Pos完全相同时,表明Slave和Master处于完全同步的状态。既然有了(Relay_Master_Log_File, Exec_Master_Log_Pos)能够表明同步状态,还要(Relay_Log_File, Relay_Log_Pos)做什么呢? 我感觉(Relay_Log_File, Relay_Log_Pos) 是给程序看的,记录Relay log中的执行点, 而(Relay_Master_Log_File, Exec_Master_Log_Pos)可以给人看,能够清楚的表明Master和Slave之间的同步状态。
Slave_IO_Running: 对应:Master_Log_File & Read_Master_Log_Pos Slave_SQL_Running 对应:Relay_Master_Log_File & Exec_Master_Log_Pos 如果显示No,则说明前面某些配置步骤出错,或者对应的日志文件有问题。 解决方案: STOP SLAVE ; change master to master_log_file='mysql-bin.000377', master_log_pos=16063934; start SLAVE; Slave_SQL_Running: NO Seconds_Behind_Master: NULL 一般是事务回滚造成的: 解决方法: stop slave; set global sql_slave_skip_counter =1 ; mysql> START SLAVE SQL_THREAD ; start slave; 之后Slave会和Master去同步 主要看: Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master是否为0,0就是已经同步了 查看你跳过去的日志内容,最后检查表中数据是否一致; mysqlbinlog mysql-bin.000097|tail mysqlbinlog mysql-bin.000097 --start-datetime="2010-01-07 11:25:56" --stop-datetime="2010-01-07 13:23:50" 目前以37为主,36为slave 再去看36服务器上: mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Master_Host: ip2 Master_User: repl_user Master_Port: 3366 Connect_Retry: 60 Master_Log_File: mysql-bin.000461 Read_Master_Log_Pos: 9636028 Relay_Log_File: mysql-bin.000379 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000461 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: mysql,information_schema,performance_schema,test 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: 9636028 Relay_Log_Space: 497 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: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size; the first event 'mysql-bin.000461' at 9636028, the last event read from '/usr/local/mysql/mysql_log/mysql-bin.000461' at 4, the last byte read from '/usr/local/mysql/mysql_log/mysql-bin.000461' at 4.' Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_UUID: 48625158-a758-11e4-8b6b-a0369f33803b Master_Info_File: /usr/local/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: 150623 15:14:45 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec) 去37上查看主的日志序列 mysql> show master status\G; *************************** 1. row *************************** File: mysql-bin.000508 Position: 16396251 Binlog_Do_DB: Binlog_Ignore_DB: mysql,information_schema,performance_schema,test Executed_Gtid_Set: 1 row in set (0.00 sec) ERROR:No query specified
解决方法:
STOP SLAVE ; change master to master_log_file='mysql-bin.000508', master_log_pos=16396251; start SLAVE ;转载地址:https://blog.csdn.net/wll_1017/article/details/46695629 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!
发表评论
最新留言
很好
[***.229.124.182]2024年04月01日 06时42分23秒
关于作者
喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!
推荐文章
关于iOS XCode的entitlements文件
2019-04-27
Airtest自动化测试神器,教你实现Unity自动化测试
2019-04-27
模拟器连接端口汇总和常用ADB命令
2019-04-27
ShaderGraph使用教程与各种特效案例:Unity2020(持续更新)
2019-04-27
Unity爆炸、闪电、火焰、雷雨特效Demo
2021-06-30
使用python登录和访问Confluence
2021-06-30
Unity2020中使用MemoryProfile卡死和报错的问题
2021-06-30
Unity中加载Texture2D不断消耗内存的问题
2021-06-30
手把手教你使用Unity制作一个飞机喷射火焰尾气的粒子效果
2021-06-30
gitee使用教程,创建项目仓库并上传代码
2021-06-30
Unity3D 上传日志
2021-06-30
Unity3D 序列帧
2019-04-27
Unity3D 动态创建图集并压入精灵(NGUI)
2019-04-27
C# 将Excel转xml
2019-04-27
Unity3D log写入文件
2019-04-27
Unity3D动态创建摄像机
2019-04-27
unity 根据纪元时间(1970/1/1)转换为DateTime
2019-04-27