mysql主从同步常见错误以及解决方法总结

不念不忘少年蓝@ 2021-12-19 03:45 306阅读 0赞

**前言**

在发生故障切换后，经常遇到的问题就是同步报错，数据库很小的时候，dump完再导入很简单就处理好了，但线上的数据库都150G-200G，如果用单纯的这种方法，成本太高，故经过一段时间的摸索，总结了几种处理方法。

生产环境架构图

目前现网的架构，保存着两份数据，通过异步复制做的高可用集群，两台机器提供对外服务。在发生故障时，切换到slave上，并将其变成master，坏掉的机器反向同步新的master，在处理故障时，遇到最多的就是主从报错。下面是我收录下来的报错信息。

**常见错误**

**最常见的3种情况**

这3种情况是在HA切换时，由于是异步复制，且sync\_binlog=0，会造成一小部分binlog没接收完导致同步报错。

**第一种：在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

**第二种：主键重复。在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

**第三种：在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 263

**异步半同步区别**

**异步复制**  
简单的说就是master把binlog发送过去，不管slave是否接收完，也不管是否执行完，这一动作就结束了.

**半同步复制**  
简单的说就是master把binlog发送过去，slave确认接收完，但不管它是否执行完，给master一个信号我这边收到了，这一动作就结束了。（[谷歌][Link 1]写的代码，5.5上正式应用。）

**异步的劣势**  
当master上写操作繁忙时，当前POS点例如是10，而slave上IO\_THREAD线程接收过来的是3，此时master宕机，会造成相差7个点未传送到slave上而数据丢失。

**特殊的情况**

slave的中继日志relay-bin损坏。  
Last\_SQL\_Error: Error initializing relay log position: I/O error reading the header from the binary log  
Last\_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

这种情况SLAVE在宕机，或者非法关机，例如电源故障、主板烧了等，造成中继日志损坏，同步停掉。

人为失误需谨慎：多台slave存在重复server-id  
这种情况同步会一直延时，永远也同步不完，error错误日志里一直出现上面两行信息。解决方法就是把server-id改成不一致即可。

Slave: received end packet from server, apparent master shutdown:  
Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.000012' at postion 106

**问题处理**

**删除失败**

在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;

如果这种情况很多，可用我写的一个脚本skip\_error\_replcation.sh，默认跳过10个错误（只针对这种情况才跳，其他情况输出错误结果，等待处理），这个脚本是参考maakit工具包的mk-slave-restart原理用shell写的，功能上定义了一些自己的东西，不是无论什么错误都一律跳过。）

**主键重复**

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 </td> 
 <td> <code>Last_SQL_Error: Could not execute Write_rows event on table hcy.t1; </code> <code>Duplicate entry '2' for key 'PRIMARY', </code> <code>Error_code: 1062; </code> <code>handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 924</code> </td> 
 </tr> 
 </tbody> 
</table>

**解决方法：**

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

**删除重复的主键**

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 </td> 
 <td> <code>mysql&gt; delete from t1 where id=2;</code> <code>Query OK, 1 row affected (0.00 sec)</code> &nbsp; <code>mysql&gt; start slave;</code> <code>Query OK, 0 rows affected (0.00 sec)</code> &nbsp; <code>mysql&gt; show slave status\G;</code> <code>……</code> <code>Slave_IO_Running: Yes</code> <code>Slave_SQL_Running: Yes</code> <code>……</code> <code>mysql&gt; select * from t1 where id=2;</code> </td> 
 </tr> 
 </tbody> 
</table>

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

**更新丢失**

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 </td> 
 <td> <code>Last_SQL_Error: Could not execute Update_rows event on table hcy.t1; </code> <code>Can't find record in 't1', </code> <code>Error_code: 1032; </code> <code>handler error HA_ERR_KEY_NOT_FOUND; </code> <code>the event's master log mysql-bin.000010, end_log_pos 794</code> </td> 
 </tr> 
 </tbody> 
</table>

**解决方法：**

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 </td> 
 <td> <code>/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000010 | grep -A '10' 794</code> &nbsp; <code>#120302 12:08:36 server id 22 end_log_pos 794 Update_rows: table id 33 flags: STMT_END_F</code> <code>### UPDATE hcy.t1</code> <code>### WHERE</code> <code>###&nbsp; @1=2 /* INT meta=0 nullable=0 is_null=0 */</code> <code>###&nbsp; @2='bbc' /* STRING(4) meta=65028 nullable=1 is_null=0 */</code> <code>### SET</code> <code>###&nbsp; @1=2 /* INT meta=0 nullable=0 is_null=0 */</code> <code>###&nbsp; @2='BTV' /* STRING(4) meta=65028 nullable=1 is_null=0 */</code> <code># at 794</code> <code>#120302 12:08:36 server id 22 end_log_pos 821 Xid = 60</code> <code>COMMIT/*!*/;</code> <code>DELIMITER ;</code> <code># End of log file</code> <code>ROLLBACK /* added by mysqlbinlog */;</code> <code>/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;</code> </td> 
 </tr> 
 </tbody> 
</table>

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

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

然后再到master查看

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 </td> 
 <td> <code>mysql&gt; select * from t1 where id=2;</code> <code>+----+------+</code> <code>| id | name |</code> <code>+----+------+</code> <code>| 2 | BTV | </code> <code>+----+------+</code> <code>1 row in set (0.00 sec)</code> </td> 
 </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 </td> 
 <td> <code>mysql&gt; insert into t1 values (2,'BTV');</code> <code>Query OK, 1 row affected (0.00 sec)</code> &nbsp; <code>mysql&gt; select * from t1 where id=2;&nbsp; </code> <code>+----+------+</code> <code>| id | name |</code> <code>+----+------+</code> <code>| 2 | BTV | </code> <code>+----+------+</code> <code>1 row in set (0.00 sec)</code> &nbsp; <code>mysql&gt; stop slave ;set global sql_slave_skip_counter=1;start slave;</code> <code>Query OK, 0 rows affected (0.01 sec)</code> <code>Query OK, 0 rows affected (0.00 sec)</code> <code>Query OK, 0 rows affected (0.00 sec)</code> &nbsp; <code>mysql&gt; show slave status\G;</code> <code>……</code> <code>&nbsp;</code><code>Slave_IO_Running: Yes</code> <code>&nbsp;</code><code>Slave_SQL_Running: Yes</code> <code>……</code> </td> 
 </tr> 
 </tbody> 
</table>

中继日志损坏

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 </td> 
 <td> <code>Last_SQL_Error: Error initializing relay log position: I/O error reading the header from the binary log</code> <code>Last_SQL_Error: Error initializing relay log position: Binlog has bad magic number; </code> <code>It's not a binary log file that can be used by this version of MySQL</code> </td> 
 </tr> 
 </tbody> 
</table>

**手工修复**

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

例子：

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 </td> 
 <td> <code>mysql&gt; show slave status\G;</code> <code>*************************** 1. row ***************************</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Master_Log_File: mysql-bin.000010</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Read_Master_Log_Pos: 1191</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Relay_Log_File: vm02-relay-bin.000005</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Relay_Log_Pos: 253</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Relay_Master_Log_File: mysql-bin.000010</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Slave_IO_Running: Yes</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Slave_SQL_Running: No</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Replicate_Do_DB: </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Replicate_Ignore_DB: </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Replicate_Do_Table: </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Replicate_Ignore_Table: </code> <code>&nbsp;&nbsp;&nbsp;</code><code>Replicate_Wild_Do_Table: </code> <code>&nbsp;</code><code>Replicate_Wild_Ignore_Table: </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Last_Errno: 1593</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Last_Error: Error initializing relay log position: I/O error reading the header from the binary log</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Skip_Counter: 1</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Exec_Master_Log_Pos: 821</code> </td> 
 </tr> 
 </tbody> 
</table>

Slave\_IO\_Running ：接收master的binlog信息

Master\_Log\_File  
                   Read\_Master\_Log\_Pos

Slave\_SQL\_Running：执行写操作

Relay\_Master\_Log\_File  
                   Exec\_Master\_Log\_Pos

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

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

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

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

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

**下面一个演示例子：**

备份：ibbackup /bak/etc/my\_local.cnf /bak/etc/my\_bak.cnf

恢复：ibbackup --apply-log /bak/etc/my\_bak.cnf

把ibdata1 ib\_logfile0 ib\_logfile1拷贝到从，把.frm也拷贝过去，启动MySQL后，做同步，那个点就是上面输出的：

ibbackup: Last MySQL binlog file position 0 1191, file name ./mysql-bin.000010  
CHANGE MASTER TO MASTER\_LOG\_FILE='mysql-bin.000010',MASTER\_LOG\_POS=1191;

Maatkit工具包  
http://www.maatkit.org/

**简介**

maatkit是一个开源的工具包，为mysql日常管理提供了帮助。目前，已被Percona公司收购并维护。其中：

mk-table-checksum是用来检测master和slave上的表结构和数据是否一致。

mk-table-sync是发生主从数据不一致时，来修复的。

这两个工具包，没有在现网实际操作的经验，这里仅仅是新技术探讨和学术交流，下面展示下如何使用。

http://www.actionsky.com/products/mysql-others/maatkit.jsp

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 </td> 
 <td> <code>[root@vm02]# mk-table-checksum h=vm01,u=admin,p=123456 h=vm02,u=admin,p=123456 -d hcy -t t1</code> <code>Cannot connect to MySQL because the Perl DBI module is not installed or not found. </code> <code>Run 'perl -MDBI' to see the directories that Perl searches for DBI.</code> <code>If DBI is not installed, try:</code> <code>&nbsp;</code><code>Debian/Ubuntu apt-get install libdbi-perl</code> <code>&nbsp;</code><code>RHEL/CentOS&nbsp; yum install perl-DBI</code> <code>&nbsp;</code><code>OpenSolaris&nbsp; pgk install pkg:/SUNWpmdbi</code> </td> 
 </tr> 
 </tbody> 
</table>

提示缺少perl-DBI模块，那么直接 yum install perl-DBI。

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 </td> 
 <td> <code>[root@vm02 bin]# mk-table-checksum h=vm01,u=admin,p=123456 h=vm02,u=admin,p=123456 -d hcy -t t1</code> <code>DATABASE TABLE CHUNK HOST ENGINE&nbsp;&nbsp; COUNT&nbsp;&nbsp;&nbsp;&nbsp; CHECKSUM TIME WAIT STAT LAG</code> <code>hcy&nbsp;&nbsp; t1&nbsp;&nbsp;&nbsp; 0 vm02 InnoDB&nbsp;&nbsp;&nbsp; NULL&nbsp;&nbsp;&nbsp; 1957752020&nbsp; 0&nbsp; 0 NULL NULL</code> <code>hcy&nbsp;&nbsp; t1&nbsp;&nbsp;&nbsp; 0 vm01 InnoDB&nbsp;&nbsp;&nbsp; NULL&nbsp;&nbsp;&nbsp; 1957752020&nbsp; 0&nbsp; 0 NULL NULL</code> </td> 
 </tr> 
 </tbody> 
</table>

如果表数据不一致，CHECKSUM的值是不相等的。

**解释下输出的意思：**

DATABASE：数据库名  
TABLE：表名  
CHUNK：checksum时的近似数值  
HOST：MYSQL的地址  
ENGINE：表引擎  
COUNT：表的行数  
CHECKSUM：校验值  
TIME：所用时间  
WAIT：等待时间  
STAT：MASTER\_POS\_WAIT()返回值  
LAG：slave的延时时间

如果你想过滤出不相等的都有哪些表，可以用mk-checksum-filter这个工具，只要在后面加个管道符就行了。

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 </td> 
 <td> <code>[root@vm02 ~]# mk-table-checksum h=vm01,u=admin,p=123456 h=vm02,u=admin,p=123456 -d hcy | mk-checksum-filter&nbsp;&nbsp;&nbsp; </code> <code>hcy&nbsp;&nbsp; t2&nbsp;&nbsp;&nbsp; 0 vm01 InnoDB&nbsp;&nbsp;&nbsp; NULL&nbsp;&nbsp;&nbsp; 1957752020&nbsp; 0&nbsp; 0 NULL NULL</code> <code>hcy&nbsp;&nbsp; t2&nbsp;&nbsp;&nbsp; 0 vm02 InnoDB&nbsp;&nbsp;&nbsp; NULL&nbsp;&nbsp;&nbsp; 1068689114&nbsp; 0&nbsp; 0 NULL NULL</code> </td> 
 </tr> 
 </tbody> 
</table>

知道有哪些表不一致，可以用mk-table-sync这个工具来处理。

注：在执行mk-table-checksum时会锁表，表的大小取决于执行的快慢。

MASTER上的t2表数据：

SLAVE上的t2表数据：

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 </td> 
 <td> <code>mysql&gt; select * from t2;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; mysql&gt; select * from t2;&nbsp; </code> <code>+----+------+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; +----+------+</code> <code>| id | name |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | id | name |</code> <code>+----+------+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; +----+------+</code> <code>| 1 | a&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 1 | a&nbsp; | </code> <code>| 2 | b&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 2 | b&nbsp; | </code> <code>| 3 | ss&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 3 | ss&nbsp; | </code> <code>| 4 | asd |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 4 | asd | </code> <code>| 5 | ss&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; +----+------+</code> <code>+----+------+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4 rows in set (0.00 sec)</code> <code>5 rows in set (0.00 sec) </code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>mysql&gt; \! hostname; </code> <code>mysql&gt; \! hostname;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; vm02&nbsp;&nbsp;&nbsp; </code> <code>vm01 </code> <code>[root@vm02 ~]# mk-table-sync --execute --print --no-check-slave --transaction --databases hcy h=vm01,u=admin,p=123456 h=vm02,u=admin,p=123456 </code> <code>INSERT INTO `hcy`.`t2`(`id`, `name`) VALUES ('5', 'ss') /*maatkit src_db:hcy src_tbl:t2 src_dsn:h=vm01,p=...,u=admin dst_db:hcy dst_tbl:t2 </code> <code>dst_dsn:h=vm02,p=...,u=admin lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:3246 user:root host:vm02*/;</code> </td> 
 </tr> 
 </tbody> 
</table>

它的工作原理是：先一行一行检查主从库的表是否一样，如果哪里不一样，就执行删除，更新，插入等操作，使其达到一致。表的大小决定着执行的快慢。

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 </td> 
 <td> <code>If C&lt;--transaction&gt; is specified, C&lt;</code><code>LOCK</code>&nbsp;<code>TABLES&gt; is not used. Instead, lock</code> <code>and unlock are implemented by beginning and committing transactions.</code> <code>The exception is if L&lt;"--lock"&gt; is 3.</code> <code>If C&lt;--no-transaction&gt; is specified, then C&lt;</code><code>LOCK</code>&nbsp;<code>TABLES&gt; is used for any</code> <code>value of L&lt;"--lock"&gt;. See L&lt;"--[no]transaction"&gt;.</code> <code>When enabled, either explicitly or implicitly, the transaction isolation level</code> <code>is set C&lt;</code><code>REPEATABLE</code>&nbsp;<code>READ&gt; and transactions are started C&lt;</code><code>WITH</code>&nbsp;<code>CONSISTENT</code> <code>SNAPSHOT&gt;</code> </td> 
 </tr> 
 </tbody> 
</table>

**MySQL复制监控**

**MySQL常见错误类型**

1005：创建表失败  
1006：创建数据库失败  
1007：数据库已存在，创建数据库失败  
1008：数据库不存在，删除数据库失败  
1009：不能删除数据库文件导致删除数据库失败  
1010：不能删除数据目录导致删除数据库失败  
1011：删除数据库文件失败  
1012：不能读取系统表中的记录  
1020：记录已被其他用户修改  
1021：硬盘剩余空间不足，请加大硬盘可用空间  
1022：关键字重复，更改记录失败  
1023：关闭时发生错误  
1024：读文件错误  
1025：更改名字时发生错误  
1026：写文件错误  
1032：记录不存在  
1036：数据表是只读的，不能对它进行修改  
1037：系统内存不足，请重启数据库或重启[服务器][Link 2]  
1038：用于排序的内存不足，请增大排序缓冲区  
1040：已到达数据库的最大连接数，请加大数据库可用连接数  
1041：系统内存不足  
1042：无效的主机名  
1043：无效连接  
1044：当前用户没有访问数据库的权限  
1045：不能连接数据库，用户名或密码错误  
1048：字段不能为空  
1049：数据库不存在  
1050：数据表已存在  
1051：数据表不存在  
1054：字段不存在  
1065：无效的SQL语句，SQL语句为空  
1081：不能建立Socket连接  
1114：数据表已满，不能容纳任何记录  
1116：打开的数据表太多  
1129：数据库出现异常，请重启数据库  
1130：连接数据库失败，没有连接数据库的权限  
1133：数据库用户不存在  
1141：当前用户无权访问数据库  
1142：当前用户无权访问数据表  
1143：当前用户无权访问数据表中的字段  
1146：数据表不存在  
1147：未定义用户对数据表的访问权限  
1149：SQL语句语法错误  
1158：网络错误，出现读错误，请检查网络连接状况  
1159：网络错误，读超时，请检查网络连接状况  
1160：网络错误，出现写错误，请检查网络连接状况  
1161：网络错误，写超时，请检查网络连接状况  
1062：字段值重复，入库失败  
1169：字段值重复，更新记录失败  
1177：打开数据表失败  
1180：提交事务失败  
1181：回滚事务失败  
1203：当前用户和数据库建立的连接已到达数据库的最大连接数，请增大可用的数据库连接数或重启数据库  
1205：加锁超时  
1211：当前用户没有创建用户的权限  
1216：外键约束检查失败，更新子表记录失败  
1217：外键约束检查失败，删除或修改主表记录失败  
1226：当前用户使用的资源已超过所允许的资源，请重启数据库或重启服务器  
1227：权限不足，您无权进行此操作  
1235：MySQL版本过低，不具有本功能

**复制监控脚本**

参考原文修改。

原脚本

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 </td> 
 <td> <code>#!/bin/bash</code> <code>#</code> <code>#check_mysql_slave_replication_status</code> <code>#</code> <code>#</code> <code>#</code> <code>parasum=2</code> <code>help_msg(){ </code> <code>&nbsp;</code>&nbsp; <code>cat &lt;&lt;</code> <code>&nbsp;</code><code>help</code> <code>+---------------------+</code> <code>+Error</code> <code>&nbsp;</code><code>Cause:</code> <code>+you</code> <code>&nbsp;</code><code>must input $parasum parameters!</code> <code>+1st</code> <code>&nbsp;</code><code>: Host_IP</code> <code>+2st</code> <code>&nbsp;</code><code>: Host_Port</code> <code>help</code> <code>exit</code> <code>}</code> <code>&nbsp;</code>&nbsp; <code>[</code> <code>&nbsp;</code><code>$#</code> <code>&nbsp;</code><code>-ne ${parasum} ] &amp;&amp; help_msg #若参数不够打印帮助信息并退出</code> <code>&nbsp;</code>&nbsp; <code>export HOST_IP=$1</code> <code>export HOST_PORt=$2</code> <code>MYUSER</code><code>=</code><code>"root"</code>&nbsp;&nbsp;&nbsp;&nbsp; <code>MYPASS</code><code>=</code><code>"123456"</code> <code>&nbsp;</code>&nbsp; <code>MYSQL_CMD="mysql</code> <code>&nbsp;</code><code>-u$MYUSER -p$MYPASS"</code> <code>MailTitle</code><code>=</code><code>""</code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<code>#邮件主题</code> <code>Mail_Address_MysqlStatus</code><code>=</code><code>"root@localhost.localdomain"</code>&nbsp;&nbsp;<code>#收件人邮箱&nbsp; </code> <code>&nbsp;</code>&nbsp; <code>time1=$(date +"%Y%m%d%H%M%S")</code> <code>time2=$(date +"%Y-%m-%d</code> <code>&nbsp;</code><code>%H:%M:%S")</code> <code>&nbsp;</code>&nbsp; <code>SlaveStatusFile=/tmp/salve_status_${HOST_PORT}.${time1} </code> <code>#邮件内容所在文件</code> <code>echo "--------------------Begin</code> <code>&nbsp;</code><code>at: "$time2</code> <code>&nbsp;</code><code>&gt; $SlaveStatusFile</code> <code>echo "" &gt;&gt;</code> <code>&nbsp;</code><code>$SlaveStatusFile</code> <code>&nbsp;</code>&nbsp; <code>#get</code> <code>&nbsp;</code><code>slave status</code> <code>${MYSQL_CMD}</code> <code>&nbsp;</code><code>-e "show</code> <code>&nbsp;</code><code>slave status\G" &gt;&gt;</code> <code>&nbsp;</code><code>$SlaveStatusFile #取得salve进程的状态</code> <code>&nbsp;</code>&nbsp; <code>#get</code> <code>&nbsp;</code><code>io_thread_status,sql_thread_status,last_errno&nbsp; 取得以下状态值</code> <code>&nbsp;</code>&nbsp; <code>IOStatus=$(cat $SlaveStatusFile|grep Slave_IO_Running|awk '{print</code> <code>&nbsp;</code><code>$2}')</code> <code>SQLStatus=$(cat $SlaveStatusFile|grep Slave_SQL_Running</code> <code>&nbsp;</code><code>|awk '{print</code> <code>&nbsp;</code><code>$2}')</code> <code>&nbsp;&nbsp;</code><code>Errno=$(cat $SlaveStatusFile|grep Last_Errno</code> <code>&nbsp;</code><code>| awk '{print</code> <code>&nbsp;</code><code>$2}')</code> <code>&nbsp;&nbsp;</code><code>Behind=$(cat $SlaveStatusFile|grep Seconds_Behind_Master</code> <code>&nbsp;</code><code>| awk '{print</code> <code>&nbsp;</code><code>$2}')</code> <code>&nbsp;</code>&nbsp; <code>echo "" &gt;&gt;</code> <code>&nbsp;</code><code>$SlaveStatusFile</code> <code>&nbsp;</code>&nbsp; <code>if [</code> <code>"$IOStatus" ==</code> <code>"No" ]</code> <code>&nbsp;</code><code>|| [ "$SQLStatus" ==</code> <code>"No" ];then&nbsp; #判断错误类型</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>if [</code> <code>"$Errno" -eq 0</code> <code>&nbsp;</code><code>];then&nbsp; #可能是salve线程未启动</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>$MYSQL_CMD</code> <code>&nbsp;</code><code>-e "start</code> <code>&nbsp;</code><code>slave io_thread;start slave sql_thread;"</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>echo "Cause</code> <code>&nbsp;</code><code>slave threads doesnot's running,trying start slsave io_thread;start slave sql_thread;" &gt;&gt;</code> <code>&nbsp;</code><code>$SlaveStatusFile</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>MailTitle="[Warning]</code> <code>&nbsp;</code><code>Slave threads stoped on $HOST_IP $HOST_PORT"</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>elif [</code> <code>"$Errno" -eq 1007</code> <code>&nbsp;</code><code>] || [ "$Errno" -eq 1053</code> <code>&nbsp;</code><code>] || [ "$Errno" -eq 1062</code> <code>&nbsp;</code><code>] || [ "$Errno" -eq 1213</code> <code>&nbsp;</code><code>] || [ "$Errno" -eq 1032</code> <code>&nbsp;</code><code>]\</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>||</code> <code>&nbsp;</code><code>[ "Errno" -eq 1158</code> <code>&nbsp;</code><code>] || [ "$Errno" -eq 1159</code> <code>&nbsp;</code><code>] || [ "$Errno" -eq 1008</code> <code>&nbsp;</code><code>];then #忽略此些错误</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>$MYSQL_CMD</code> <code>&nbsp;</code><code>-e "stop</code> <code>&nbsp;</code><code>slave;set global sql_slave_skip_counter=1;start slave;"</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>echo "Cause</code> <code>&nbsp;</code><code>slave replication catch errors,trying skip counter and restart slave;stop slave ;set global sql_slave_skip_counter=1;slave start;" &gt;&gt;</code> <code>&nbsp;</code><code>$SlaveStatusFile</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>MailTitle="[Warning]</code> <code>&nbsp;</code><code>Slave error on $HOST_IP $HOST_PORT! ErrNum: $Errno"</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>else</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>echo "Slave</code> <code>&nbsp;</code><code>$HOST_IP $HOST_PORT is down!" &gt;&gt;</code> <code>&nbsp;</code><code>$SlaveStatusFile</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code><code>MailTitle="[ERROR]Slave</code> <code>&nbsp;</code><code>replication is down on $HOST_IP $HOST_PORT ! ErrNum:$Errno"</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>fi</code> <code>fi</code> <code>if [</code> <code>&nbsp;</code><code>-n "$Behind" ];then</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>Behind=0</code> <code>fi</code> <code>echo "$Behind" &gt;&gt;</code> <code>&nbsp;</code><code>$SlaveStatusFile</code> <code>&nbsp;</code>&nbsp; <code>#delay</code> <code>&nbsp;</code><code>behind master 判断延时时间</code> <code>if [</code> <code>&nbsp;</code><code>$Behind -gt 300 ];then</code> <code>&nbsp;&nbsp;</code><code>echo `date +"%Y-%m%d</code> <code>&nbsp;</code><code>%H:%M:%S"`</code> <code>"slave</code> <code>&nbsp;</code><code>is behind master $Bebind seconds!" &gt;&gt;</code> <code>&nbsp;</code><code>$SlaveStatusFile</code> <code>&nbsp;&nbsp;</code><code>MailTitle="[Warning]Slave</code> <code>&nbsp;</code><code>delay $Behind seconds,from $HOST_IP $HOST_PORT"</code> <code>fi</code> <code>&nbsp;</code>&nbsp; <code>if [</code> <code>&nbsp;</code><code>-n "$MailTitle" ];then #若出错或者延时时间大于300s则发送邮件</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>cat ${SlaveStatusFile}</code> <code>&nbsp;</code><code>| /bin/mail -s</code> <code>"$MailTitle" $Mail_Address_MysqlStatus</code> <code>fi</code> <code>&nbsp;</code>&nbsp; <code>#del</code> <code>&nbsp;</code><code>tmpfile:SlaveStatusFile</code> <code>&gt;</code> <code>&nbsp;</code><code>$SlaveStatusFile</code> </td> 
 </tr> 
 </tbody> 
</table>

**修改后脚本**

只做了简单的整理，修正了Behind为NULL的判断，但均未测试；

**应可考虑增加：**

对修复执行结果的判断；多条错误的循环修复、检测、再修复？

取消SlaveStatusFile临时文件。

Errno、Behind两种告警分别发邮件，告警正文增加show slave结果原文。

增加PATH，以便加到crontab中。

考虑crontab中周期执行(加锁避免执行冲突、执行周期选择)

增加执行日志？

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 </td> 
 <td> <code>#!/bin/sh</code> <code>#</code> <code>&nbsp;</code><code>check_mysql_slave_replication_status</code> <code>#</code> <code>&nbsp;</code><code>参考:<a href="http://www.tianfeiyu.com/?p=2062" rel="nofollow">http://www.tianfeiyu.com/?p=2062</a></code> <code>&nbsp;</code>&nbsp; <code>Usage(){ </code> <code>&nbsp;&nbsp;</code><code>echo Usage:</code> <code>&nbsp;&nbsp;</code><code>echo "$0</code> <code>&nbsp;</code><code>HOST PORT USER PASS"</code> <code>}</code> <code>&nbsp;</code>&nbsp; <code>[</code> <code>&nbsp;</code><code>-z "$1" -o</code> <code>&nbsp;</code><code>-z "$2" -o</code> <code>&nbsp;</code><code>-z "$3" -o</code> <code>&nbsp;</code><code>-z "$4" ]</code> <code>&nbsp;</code><code>&amp;&amp; Usage &amp;&amp; exit 1</code> <code>HOST=$1</code> <code>PORT=$2</code> <code>USER=$3</code> <code>PASS=$4</code> <code>&nbsp;</code>&nbsp; <code>MYSQL_CMD="mysql</code> <code>&nbsp;</code><code>-h$HOST -P$PORT -u$USER -p$PASS"</code> <code>&nbsp;</code>&nbsp; <code>MailTitle=""&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; #邮件主题</code> <code>Mail_Address_MysqlStatus="root@localhost.localdomain"&nbsp; #收件人邮箱&nbsp; </code> <code>&nbsp;</code>&nbsp; <code>time1=$(date +"%Y%m%d%H%M%S")</code> <code>time2=$(date +"%Y-%m-%d</code> <code>&nbsp;</code><code>%H:%M:%S")</code> <code>&nbsp;</code>&nbsp; <code>SlaveStatusFile=/tmp/salve_status_${HOST_PORT}.${time1} </code> <code>#邮件内容所在文件</code> <code>echo "--------------------Begin</code> <code>&nbsp;</code><code>at: "$time2</code> <code>&nbsp;</code><code>&gt; $SlaveStatusFile</code> <code>echo "" &gt;&gt;</code> <code>&nbsp;</code><code>$SlaveStatusFile</code> <code>&nbsp;</code>&nbsp; <code>#get</code> <code>&nbsp;</code><code>slave status</code> <code>${MYSQL_CMD}</code> <code>&nbsp;</code><code>-e "show</code> <code>&nbsp;</code><code>slave status\G" &gt;&gt;</code> <code>&nbsp;</code><code>$SlaveStatusFile #取得salve进程的状态</code> <code>&nbsp;</code>&nbsp; <code>#get</code> <code>&nbsp;</code><code>io_thread_status,sql_thread_status,last_errno&nbsp; 取得以下状态值</code> <code>&nbsp;</code>&nbsp; <code>&nbsp;</code><code>IOStatus=$(cat $SlaveStatusFile|grep Slave_IO_Running|awk '{print</code> <code>&nbsp;</code><code>$2}')</code> <code>SQLStatus=$(cat $SlaveStatusFile|grep Slave_SQL_Running</code> <code>&nbsp;</code><code>|awk '{print</code> <code>&nbsp;</code><code>$2}')</code> <code>&nbsp;&nbsp;</code><code>Errno=$(cat $SlaveStatusFile|grep Last_Errno</code> <code>&nbsp;</code><code>| awk '{print</code> <code>&nbsp;</code><code>$2}')</code> <code>&nbsp;&nbsp;</code><code>Behind=$(cat $SlaveStatusFile|grep Seconds_Behind_Master</code> <code>&nbsp;</code><code>| awk '{print</code> <code>&nbsp;</code><code>$2}')</code> <code>&nbsp;</code>&nbsp; <code>echo "" &gt;&gt;</code> <code>&nbsp;</code><code>$SlaveStatusFile</code> <code>&nbsp;</code>&nbsp; <code>if [</code> <code>"$IOStatus" =</code> <code>"No" -o</code> <code>"$SQLStatus" =</code> <code>"No" ];then</code> <code>&nbsp;&nbsp;</code><code>case "$Errno" in</code> <code>&nbsp;&nbsp;</code><code>0)</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>#</code> <code>&nbsp;</code><code>可能是slave未启动</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>$MYSQL_CMD</code> <code>&nbsp;</code><code>-e "start</code> <code>&nbsp;</code><code>slave io_thread;start slave sql_thread;"</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>echo "Cause</code> <code>&nbsp;</code><code>slave threads doesnot's running,trying start slsave io_thread;start slave sql_thread;" &gt;&gt;</code> <code>&nbsp;</code><code>$SlaveStatusFile</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>;;</code> <code>&nbsp;&nbsp;</code><code>1007|1053|1062|1213|1032|1158|1159|1008)</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>#</code> <code>&nbsp;</code><code>忽略这些错误</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>$MYSQL_CMD</code> <code>&nbsp;</code><code>-e "stop</code> <code>&nbsp;</code><code>slave;set global sql_slave_skip_counter=1;start slave;"</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>echo "Cause</code> <code>&nbsp;</code><code>slave replication catch errors,trying skip counter and restart slave;stop slave ;set global sql_slave_skip_counter=1;slave start;" &gt;&gt;</code> <code>&nbsp;</code><code>$SlaveStatusFile</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>MailTitle="[Warning]</code> <code>&nbsp;</code><code>Slave error on $HOST:$PORT! ErrNum: $Errno"</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>;;</code> <code>&nbsp;&nbsp;</code><code>*)</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>echo "Slave</code> <code>&nbsp;</code><code>$HOST:$PORT is down!" &gt;&gt;</code> <code>&nbsp;</code><code>$SlaveStatusFile</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>MailTitle="[ERROR]Slave</code> <code>&nbsp;</code><code>replication is down on $HOST:$PORT! Errno:$Errno"</code> <code>&nbsp;&nbsp;&nbsp;&nbsp;</code><code>;;</code> <code>&nbsp;&nbsp;</code><code>esac</code> <code>fi</code> <code>&nbsp;</code>&nbsp; <code>if [</code> <code>"$Behind" =</code> <code>"NULL" -o</code> <code>&nbsp;</code><code>-z "$Behind" ];then</code> <code>&nbsp;&nbsp;</code><code>Behind=0</code> <code>fi</code> <code>echo "Behind:$Behind" &gt;&gt;</code> <code>&nbsp;</code><code>$SlaveStatusFile</code> <code>&nbsp;</code>&nbsp; <code>#delay</code> <code>&nbsp;</code><code>behind master 判断延时时间</code> <code>if [</code> <code>&nbsp;</code><code>$Behind -gt 300 ];then</code> <code>&nbsp;&nbsp;</code><code>echo `date +"%Y-%m%d</code> <code>&nbsp;</code><code>%H:%M:%S"`</code> <code>"slave</code> <code>&nbsp;</code><code>is behind master $Bebind seconds!" &gt;&gt;</code> <code>&nbsp;</code><code>$SlaveStatusFile</code> <code>&nbsp;&nbsp;</code><code>MailTitle="[Warning]Slave</code> <code>&nbsp;</code><code>delay $Behind seconds,from $HOST $PORT"</code> <code>fi</code> <code>&nbsp;</code>&nbsp; <code>if [</code> <code>&nbsp;</code><code>-n "$MailTitle" ];then #若出错或者延时时间大于300s则发送邮件</code> <code>&nbsp;&nbsp;</code><code>cat ${SlaveStatusFile}</code> <code>&nbsp;</code><code>| /bin/mail -s</code> <code>"$MailTitle" $Mail_Address_MysqlStatus</code> <code>fi</code> <code>&nbsp;</code>&nbsp; <code>#del</code> <code>&nbsp;</code><code>tmpfile:SlaveStatusFile</code> <code>&gt;</code> <code>&nbsp;</code><code>$SlaveStatusFile</code> </td> 
 </tr> 
 </tbody> 
</table>

以上这篇线上MYSQL同步报错故障处理方法总结(必看篇)就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持脚本之家。

原文网址：https://www.jb51.net/article/109107.htm

\-------------------------------------------------------

来源：博客园

链接：[https://www.cnblogs.com/wuchangsoft/p/10401545.html][https_www.cnblogs.com_wuchangsoft_p_10401545.html]

[Link 1]: https://www.baidu.com/s?wd=%E8%B0%B7%E6%AD%8C&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd
[xtrabackup]: https://www.baidu.com/s?wd=xtrabackup&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd
[Link 2]: https://www.baidu.com/s?wd=%E6%9C%8D%E5%8A%A1%E5%99%A8&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd
[https_www.cnblogs.com_wuchangsoft_p_10401545.html]: https://www.cnblogs.com/wuchangsoft/p/10401545.html