本帖最后由 郑全 于 2018-7-22 17:34 编辑
现在不少公司都在用MySQL（master）-->MySQL（slave）的框架，当然也有一主多从的架构，这也是MySQL主从的一个延伸架构;当然也有的公司MySQL主主的架构，MySQL主主架构要是处理得不适当，会面临各种各样的问题，当然啦，每种数据库构架都有自己的优缺点，合适自己公司业务需求的且方便自己维护的架构都可以认为是理想的构架，当出现同步断开了，我们是不是一味的使用--slave-skip-errors=[error_code]来跳过错误代码呢？其实不是的，这样做可能会造成数据不一致的可能，下面我只针对MySQL Replication常见的错误进行说明及处理。 一、在master上更新一条记录时出现的故障（master与slave处理同步的情况下，binlog为row格式） 在slave库上，模拟slave少了一条数据，所以把id=6的记录在slave上先delete掉：
root@mysql-slave> select * from test; +----+------+----------+ | id | name | code | +----+------+----------+ | 6 | aa | 10002011 | | 7 | bb | 10002012 | | 8 | cc | 10002013 | | 9 | dd | 10002014 | +----+------+----------+ 4 rows in set(0.00 sec) root@mysql-slave> delete from test where id=6; Query OK, 1 row affected (0.00 sec)
然后在master上更新id为6的记录：
root@mysql-master> show variables like 'binlog_format'; +---------------+-------+ |Variable_name | Value | +---------------+-------+ |binlog_format | ROW | +---------------+-------+ 1 row in set(0.00 sec) root@mysql-master> select * from test; +----+------+----------+ | id | name | code | +----+------+----------+ | 6 | aa | 10002011 | | 7 | bb | 10002012 | | 8 | cc | 10002013 | | 9 | dd | 10002014 | +----+------+----------+ 4 rows in set(0.00 sec) root@mysql-master> update test set name='AA' where id=6; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
root@mysql-master>
回slave库看下同步状态是否正常：
Replicate_Wild_Ignore_Table: Last_Errno: 1032 Last_Error: Could not execute Update_rows event ontable xuanzhi.test; Can'tfind record in 'test', Error_code: 1032; handlererror HA_ERR_KEY_NOT_FOUND; the event's master logmysql-bin.000004,end_log_pos 3704 Skip_Counter: 0 Exec_Master_Log_Pos: 3529 Relay_Log_Space: 4183 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 Update_rowsevent on tablexuanzhi.test; Can't find record in 'test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; theevent's master log mysql-bin.000004,end_log_pos 3704 Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set(0.00 sec) root@mysql-slave>
可以看到，同步已经断开，根据slave的报错信息去查看master的binlog到底做了什么，从上面看现在master做的操作写的binlog是mysql-bin.000004，end_log_pos=3704
[root@localhost ~]#/usr/local/services/mysql/bin/mysqlbinlog -v --base64-output=DECODE-ROWS /data/mysql/data/mysql-bin.000004 | grep -A '10' 3704 #150610 22:33:08 server id 1 end_log_pos 3704 Update_rows: table id34 flags:STMT_END_F ### UPDATE xuanzhi.test ### WHERE ### @1=6 ### @2='aa' ### @3=10002011 ### SET ### @1=6 ### @2='AA' ### @3=10002011 # at 3704 #150610 22:33:08 server id 1 end_log_pos 3731 Xid = 89 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; [root@localhost ~]#
可以看到做了更新的操作UPDATE xuanzhi.test where id=6的操作，我们在slave库上查看id为6的记录: root@mysql-slave> select * from xuanzhi.test whereid=6; Empty set (0.00 sec) root@mysql-slave> 可以看到slave库上并没存在这样的记录。我们回到master查看下id=6的记录：
root@mysql-master> select * from xuanzhi.test whereid=6; +----+------+----------+ | id | name | code | +----+------+----------+ | 6 | AA | 10002011 | +----+------+----------+ 1 row in set(0.00 sec) root@mysql-master>
下面我们要解决同步问题呢？操作如下：把丢失的数据补到slave上：
root@mysql-slave> stop slave sql_thread;
Query OK, 0 rows affected (0.00 sec) root@mysql-slave> insert into test(id,name,code) values (6,'AA',10002011); Query OK, 1 row affected (0.00 sec) root@mysql-slave> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec) root@mysql-slave> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.132 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 3731 Relay_Log_File: localhost-relay-bin.000004 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes
正常同步了。如果有N多数据缺失，得用pt-table-checksum校验数据一致性，很多同学会好奇为什么slave库上会少数据呢？我总结了以下几种情况，当然还有别的： 1、当人为设置set session sql_log_bin=0时，当前session操作是不记录到Binlog的。 2、就是slave没设置为read only，在slave库上有删除操作 3、slave读取master的binlog日志后，需要落地3个文件：relay log、relaylog info、master info，这三个文件如果不及时落地，则主机crash后会导致数据的不一致 二、估计比较常见的一种错误，就是错误代码为1062的错误，主键冲突 在slave上添加一条记录，模拟slave上还存在旧的数据记录，此时master是没有的这条记录的（这里的id自增主键）
root@mysql-slave> insert into testvalue (5,'zz',10002010); Query OK, 1 row affected (0.00 sec) root@mysql-slave> select * from test; +----+------+----------+ | id | name | code | +----+------+----------+ | 5 | zz | 10002010 | | 6 | AA | 10002011 | | 7 | bb | 10002012 | | 8 | cc | 10002013 | | 9 | dd | 10002014 | +----+------+----------+ 5 rows in set(0.00 sec) root@mysql-slave>
在master上操作，添加一条id为5的记录： root@mysql-master> select* from test; +----+------+----------+ | id | name | code | +----+------+----------+ | 6 | AA | 10002011 | | 7 | bb | 10002012 | | 8 | cc | 10002013 | | 9 | dd | 10002014 | +----+------+----------+ 5 rows in set(0.00 sec) root@mysql-master> insert into test value (5,'ZZ',10002010); Query OK, 1 row affected (0.00 sec)
回到slave库查看同步状态：
Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Could not execute Write_rows event ontable xuanzhi.test; Duplicate entry '5' for key 'PRIMARY',Error_code: 1062; handler errorHA_ERR_FOUND_DUPP_KEY; the event's master logmysql-bin.000004, end_log_pos 3893 Skip_Counter: 0 Exec_Master_Log_Pos: 3731 Relay_Log_Space: 2322 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: 1062 Last_SQL_Error: Could not execute Write_rows event on tablexuanzhi.test; Duplicate entry '5' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;the event's master logmysql-bin.000004,end_log_pos 3893 Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set(0.00 sec) root@mysql-slave>
可以看到提示1062主键冲突错误，在表xuanzhi.test上，那么，此时我们应该考虑以谁的数据为准？我们当然要以master库的数据为准啦，所以我们需要把slave上主键为5的记录给删除掉，删除前要先desc查看表结构确定自增主键在什么列：
root@mysql-slave> stop slave sql_thread;
Query OK, 0 rows affected (0.00 sec)
root@mysql-slave> desc xuanzhi.test; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL |auto_increment | | name | char(10) | YES | | NULL | | | code | int(20) | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ 3 rows in set(0.00 sec) root@mysql-slave> delete fromxuanzhi.test where id=5; Query OK, 1 row affected (0.00 sec) root@mysql-slave> start slave sql_thread; Query OK, 0 rows affected (0.00 sec) root@mysql-slave> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.132 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 3920 Relay_Log_File: localhost-relay-bin.000005 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes
嘻嘻，有人会想，如果有N多主键冲突，这样手动清除冲突的记录有点不科学，是的，所以我写了写脚本去清除，可以参考我写的主从复制1062错误的解决方法。有人会好其，这样删除后，启动同步线程，记录还会不会同步过来呢，答案是会的
root@mysql-slave> select * from test; +----+------+----------+ | id | name | code | +----+------+----------+ | 5 | ZZ | 10002010 | | 6 | AA | 10002011 | | 7 | bb | 10002012 | | 8 | cc | 10002013 | | 9 | dd | 10002014 | +----+------+----------+ 5 rows in set(0.00 sec) root@mysql-slave>
可以看到主键值为5的记录被同步过来了。当备库在一次非计划的关闭后重启时，会去读master.info文件以找到上次停止复制的位置。不幸的是，该文件可能并没有同步写到磁盘，因为该信息是在缓存中，可能并没有刷新到磁盘文件master.info。文件中存储的信息可能是错误的，备库可能会尝试重新执行一些二进制日志事件，这可能导致主键冲突，就是我们常常看见的1062错误。除非能确定备库在哪里停止（很难），否则唯一的办法就是忽略那些错误。 三、master上删除一条记录时出现的故障。 在master上删除一条记录，但这条记录在slave库上并不存在的时候，同步会不会断开，下面我们瞧瞧看： 在slave上delete一条数据，模拟slave比master少了数据：
root@mysql-slave> select * from test; +----+------+----------+ | id | name | code | +----+------+----------+ | 5 | ZZ | 10002010 | | 6 | AA | 10002011 | | 7 | bb | 10002012 | | 8 | cc | 10002013 | | 9 | dd | 10002014 | +----+------+----------+ 5 rows in set(0.00 sec) root@mysql-slave> delete from test where id=5; Query OK, 1 row affected (0.00 sec) root@mysql-slave>
在master上进行dalete行记录的操作，此时的slave是不存在这条记录了的：
root@mysql-master> select * from test; +----+------+----------+ | id | name | code | +----+------+----------+ | 5 | ZZ | 10002010 | | 6 | AA | 10002011 | | 7 | bb | 10002012 | | 8 | cc | 10002013 | | 9 | dd | 10002014 | +----+------+----------+ 5 rows in set(0.00 sec) root@mysql-master> delete from test where id = 5; Query OK, 1 row affected (0.00 sec)
回到slave库查看下状态，同步已经断开：
Replicate_Wild_Ignore_Table: Last_Errno: 1032 Last_Error: Could not execute Delete_rows event ontable xuanzhi.test; Can'tfind record in 'test', Error_code: 1032; handlererror HA_ERR_KEY_NOT_FOUND; the event's master logmysql-bin.000004,end_log_pos 4082 Skip_Counter: 0 Exec_Master_Log_Pos: 3920 Relay_Log_Space: 937 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_rowsevent on tablexuanzhi.test; Can't find record in 'test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; theevent's master log mysql-bin.000004,end_log_pos 4082 Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set(0.00 sec) root@mysql-slave>
我们分析错误信息可以看到关键字是Delete_rows，这样处理就很简单了，因为master库是删除数据操作，所以slave库上没这条数据也没关系，所以在slave库上跳过此错误即可（当出现这种情况，就应该引起注意了，应该去检查是否还有更多的数据丢失了）
root@mysql-slave> stop slave sql_thread; Query OK, 0 rows affected (0.00 sec) root@mysql-slave> set global sql_slave_skip_counter=1; Query OK, 0 rows affected (0.00 sec) root@mysql-slave> start slave sql_thread; Query OK, 0 rows affected (0.00 sec) root@mysql-slave> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.132 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 4109 Relay_Log_File: localhost-relay-bin.000006 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes
可以看到同步正常了。你在master上delete一条master都没有记录，同步是不会断开的。 四、slave的中继日志relay-log损坏 现在模拟slave库down机，relay-log损坏了，同步无法正常： 断电后启动slave库后，执行slave start后查看状态会报日志读不了或者损坏（有时直接断电slave并不一定损坏或者掉数据，如果配置参数合理的话）：
root@mysql- show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.132 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 401269011 Relay_Log_File: localhost-relay-bin.000010 Relay_Log_Pos: 439914363 Relay_Master_Log_File: mysql-bin.000004 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: 1594 Last_Error: Relay log read failure: Could notparse relay log event entry. The possiblereasons are: the master's binary log is corrupted (youcan check this by running 'mysqlbinlog' on thebinary log), the slave's relay log is corrupted (you can checkthis by running 'mysqlbinlog'on the relay log),a network problem, or a bug in the master's or slave'sMySQL code. If you want to check the master's binary log or slave's relay log, you will be able to knowtheir names by issuing 'SHOWSLAVE STATUS' on this slave. Skip_Counter: 0 Exec_Master_Log_Pos: 590788350 Relay_Log_Space: 2398604371
show slave status几个重要参数说明： Slave_IO_Running: 接收master的binlog信息 Master_Log_File： 正在云读取master上binlog日志名 Read_master_Log_Pos: 正在读取master上当前的binlog日志POS点 slave_SQL_Running: 执行写操作。 Relay_master_Log_File： 正在同步master上binlog日志名 Exec_master_log_Pos: 正在同步当前binlog日志的POS点 出现relay log损坏的话，以 Relay_master_Log_File 和 Exec_master_Log_Pos参数值为基准，从上面看到Relay_master_Log_File：mysql-bin.000004 、Exec_master_Log_Pos=590788350，这时我们需要做的就是change master操作：
root@mysql-slave> stop slavesql_thread; Query OK, 0 rows affected (0.01 sec)
root@mysql-slave> changemaster to master_host='192.168.10.132',master_port=3306,master_user='root',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=590788350;
Query OK, 0 rows affected (0.04 sec) root@mysql-slave> start slavesql_thread;
Query OK, 0 rows affected (0.00 sec) root@mysql-slave> show slave status\G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.10.132 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 590788573
Relay_Log_File: localhost-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000004 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: 590788573 Relay_Log_Space: 107
这样会导致丢弃所有在磁盘上的中继日志。 如果出现以下的报错，也是按以上的方法解决：
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: Errorinitializing relay log position: I/ O errorreading the header from the binary log Skip_Counter: 0 Exec_Master_Log_Pos: 59078
通过这种方法去修改中继日志，是不是发现有些麻烦呢？其实MySQL5.5已经考虑到slave宕机中继日志损坏这一问题了，即在slave的配置文件my.cnf里要增加一个参数relay_log_recovery=1就可以了。 总结： 一、遇到同步断开时，不能一味的使用--slave-skip-errors=[error_code]来跳过错误代码，这样很容易导致数据不一致的发生 二、binlog为STATEMENT格式时，在mater进行更新或者删除一条slave库没有的数据，同步是不会断开的。 三、定期检查数据的完整性，可以用pt-table-checksum校验主从数据的一致性，数据的完整性，对一个公司来说，无疑是最重要的。 四、slave库上建议把一些重要的选项开启，例如设置为read only、relay_log_recovery、sync_master_info、sync_relay_log_info、sync_relay_log这些重要选项开启。
| 
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2018-7-22 17:21:49
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对