MySQL主从同步报错故障处理记录

发布时间：2020-07-31 19:14:09 阅读：8152 作者：2012hjtwyf 栏目：MySQL数据库

亿速云mysql数据库，读写分离，安全稳定，弹性扩容，低至0.3元/天！！点击查看>>

前言

在发生故障切换后，经常遇到的问题就是同步报错，下面是最近收集的报错信息。

记录删除失败

在master上删除一条记录，而slave上找不到

Last_SQL_Error: Could not execute Delete_rows event on table hcy.t1;
Can't find record in 't1',
Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;
the event's master log mysql-bin.000006, end_log_pos 254

解决方法：master要删除一条记录，而slave上找不到报错，这种情况主都已经删除了，那么从机可以直接跳过。

stop slave;set global sql_slave_skip_counter=1;start slave;

如果这种情况很多，需要针对这种错误专门写相关脚本。

主键重复

在slave已经有该记录，又在master上插入了同一条记录。

Last_SQL_Error: Could not execute Write_rows event on table hcy.t1; Duplicate entry '2' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 924

解决方法：

在slave上用desc hcy.t1; 先看下表结构：

mysql> desc hcy.t1;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id    | int(11) | NO   | PRI | 0       |       | | name  | char(4) | YES  |     | NULL    |       | +-------+---------+------+-----+---------+-------+

删除重复的主键

mysql> delete from t1 where id=2;Query OK, 1 row affected (0.00 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G;……Slave_IO_Running: YesSlave_SQL_Running: Yes……mysql> select * from t1 where id=2;

在master上和slave上再分别确认一下。

更新丢失

在master上更新一条记录，而slave上找不到，丢失了数据。

Last_SQL_Error: Could not execute Update_rows event on table hcy.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000010, end_log_pos 794

解决方法：

在master上，用mysqlbinlog 分析下出错的binlog日志在干什么。

/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000010 | grep -A '10' 794#120302 12:08:36 server id 22  end_log_pos 794  Update_rows: table id 33 flags: STMT_END_F### UPDATE hcy.t1### WHERE###   @1=2 /* INT meta=0 nullable=0 is_null=0 */###   @2='bbc' /* STRING(4) meta=65028 nullable=1 is_null=0 */### SET###   @1=2 /* INT meta=0 nullable=0 is_null=0 */###   @2='BTV' /* STRING(4) meta=65028 nullable=1 is_null=0 */# at 794#120302 12:08:36 server id 22  end_log_pos 821  Xid = 60COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

在slave上，查找下更新后的那条记录，应该是不存在的。

mysql> select * from t1 where id=2;Empty set (0.00 sec)

然后再到master查看

mysql> select * from t1 where id=2;+----+------+| id | name |+----+------+|  2 | BTV  | +----+------+1 row in set (0.00 sec)

把丢失的数据在slave上填补，然后跳过报错即可。

mysql> insert into t1 values (2,'BTV');Query OK, 1 row affected (0.00 sec)mysql> select * from t1 where id=2;    +----+------+| id | name |+----+------+|  2 | BTV  | +----+------+1 row in set (0.00 sec)mysql> stop slave ;set global sql_slave_skip_counter=1;start slave;Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G;…… Slave_IO_Running: Yes Slave_SQL_Running: Yes……

1236错误, 二进制文件缺失

误删二进制文件等各种原因，导致主库mysql-bin.000012文件丢失，从库同步失败。

Master_Log_File: mysql-bin.000012Slave_IO_Running: NoSlave_SQL_Running: YesLast_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 file'

首先停止从库同步
```
slave stop;
```

查看主库日志文件和位置

mysql> show master logs;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000013 |       154 |+------------------+-----------+

回从库，使日志文件和位置对应主库
```
CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000013',MASTER_LOG_POS=154;
```
最后，启动从库：

    ```bash
    slave start;

    show slave status\G;

    Master_Log_File: mysql-bin.000013
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Last_IO_Error:
    ```

中继日志损坏

slave的中继日志relay-bin损坏。

Last_SQL_Error: Error initializing relay log position: I/O error reading the header from the binary logLast_SQL_Error: Error initializing relay log position: Binlog has bad magic number;  It's not a binary log file that can be used by  this version of MySQL

1、手工修复
解决方法：找到同步的binlog和POS点，然后重新做同步，这样就可以有新的中继日值了。

例子：

mysql> show slave status\G;*************************** 1. row ***************************              Master_Log_File: mysql-bin.000010          Read_Master_Log_Pos: 1191               Relay_Log_File: vm02-relay-bin.000005                Relay_Log_Pos: 253        Relay_Master_Log_File: mysql-bin.000010             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: 1593                   Last_Error: Error initializing relay log position: I/O error reading the header from the binary log                 Skip_Counter: 1          Exec_Master_Log_Pos: 821Slave_IO_Running ：接收master的binlog信息                   Master_Log_File                   Read_Master_Log_PosSlave_SQL_Running：执行写操作                   Relay_Master_Log_File                   Exec_Master_Log_Pos

以执行写的binlog和POS点为准。

Relay_Master_Log_File: mysql-bin.000010Exec_Master_Log_Pos: 821

mysql> stop slave;Query OK, 0 rows affected (0.01 sec)mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010',MASTER_LOG_POS=821;Query OK, 0 rows affected (0.01 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.8.22                  Master_User: repl                  Master_Port: 3306                Connect_Retry: 10              Master_Log_File: mysql-bin.000010          Read_Master_Log_Pos: 1191               Relay_Log_File: vm02-relay-bin.000002                Relay_Log_Pos: 623        Relay_Master_Log_File: mysql-bin.000010             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: 1191              Relay_Log_Space: 778              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: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:

2、Ibbackup
各种大招都用上了，无奈slave数据丢失过多，ibbackup（需要银子）该你登场了。

Ibbackup热备份工具，是付费的。xtrabackup是免费的，功能上一样。

Ibbackup备份期间不锁表，备份时开启一个事务（相当于做一个快照），然后会记录一个点，之后数据的更改保存在ibbackup_logfile文件里，恢复时把ibbackup_logfile 变化的数据再写入到ibdata里。

Ibbackup 只备份数据（ ibdata、.ibd ），表结构.frm不备份。

MySQL主从复制常见故障及解决方法？

1.1.1故障1：从库数据与主库冲突

show slave status; 报错：且show slave status\G

Slave_I/O_Running:Yes

Slave_SQL_Running:No

Seconds_Behind_Master:NULL

Last_error:Error 'Can't create database 'xiaoliu'; database exists' on query. Default

database:'xiaoliu'.query:'create database xiaoliu'

解决方法1：

stop slave;

set global sql_slave_skip_counter = 1; #将同步指针向下移动一个，如果多次不同步可以重复操作

解决方法2：在从库配置文件中配置，直接跳过不影响业务的错误号

grep slave-skip /etc/my.cnf

slave-skip-errors = 1032,1062,1007

1.1.2故障2：MySQL主从复制延迟问题原因和解决方案

问题1：主库的从库太多，导致复制延迟

从库数量一般 3—5个为宜，要复制的节点过多，导致复制延迟

问题2：从库硬件配置比主库差，导致延迟

查看Master和Slave的配置，可能因为配置不当导致复制的延迟

问题3：慢SQL语句过多

假如一条语句执行时间超过2秒，就需要进行优化进行调整

问题4：主从复制设计问题

主从复制单线程，如果主库的写入并发太大，来不及传送到从库就会导致延迟

更高版本的MySQL可以支持多线程复制，门户网站则会自己开发多线程同步功能

问题5：主从库之间的网络延迟

主从库网卡、网线、连接的交换机等网络设备都可能成为复制的瓶颈

导致复制延迟，另外跨公网主从复制很容易导致主从复制延迟

问题6：主库读写压力大，导致复制延迟

主库硬件要好一些，架构前端要加buffer缓存层

亿速云「云数据库 MySQL」免部署即开即用，比自行安装部署数据库高出1倍以上的性能，双节点冗余防止单节点故障，数据自动定期备份随时恢复。点击查看>>

向AI问一下细节

MySQL主从同步报错故障处理记录

前言

记录删除失败

主键重复

更新丢失

1236错误, 二进制文件缺失

中继日志损坏

猜你喜欢

最新资讯

相关推荐

开发者交流群：

相关标签