主从同步常见错误以及解决方案

时间：2024-05-19 20:37:06

1.主从复制原理
mysql支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。mysql复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。因此，要进行复制，必须在主服务器上启用二进制日志。每个从服务器从主服务器接收主服务器已经记录到的二进制日志，获取日志信息更新。通过设置在Master上的binlog，使其处于打开状态；Slave通过一个I/O线程从Master上读取binlog，然后传输到Slave的中继日志中，然后使用SQL线程读取中继日志，并应用到自身数据库中，从而实现主从数据同步功能。
主从同步常见错误以及解决方案
前提：mysql数据库主从数据库的版本最好一样，小版本编码不一样也可以，比如：5.7.20备份到5.7.11。
异步复制
简单的说就是master把binlog发送过去，不管slave是否接收完，也不管是否执行完，这一动作就结束了.
半同步复制
简单的说就是master把binlog发送过去，slave确认接收完，但不管它是否执行完，给master一个信号我这边收到了，这一动作就结束了。（谷歌写的代码，5.5上正式应用。）
异步的劣势
当master上写操作繁忙时，当前POS点例如是10，而slave上IO_THREAD线程接收过来的是3，此时master宕机，会造成相差7个点未传送到slave上而数据丢失。

2.常见复制错误
2.1在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写的，功能上定义了一些自己的东西，不是无论什么错误都一律跳过。）
2.2主键重复。在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>stop slave;
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: Yes
Slave_SQL_Running: Yes
……
mysql> select * from t1 where id=2;

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

解决方法：
/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 = 60
COMMIT/!/;
DELIMITER ;

  • End of log file

ROLLBACK /* added by mysqlbinlog /;
/!50003 SET [email protected]_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
……

3.MySQL常见错误类型代码
1005：创建表失败
1006：创建数据库失败
1007：数据库已存在，创建数据库失败
1008：数据库不存在，删除数据库失败
1009：不能删除数据库文件导致删除数据库失败
1010：不能删除数据目录导致删除数据库失败
1011：删除数据库文件失败
1012：不能读取系统表中的记录
1020：记录已被其他用户修改
1021：硬盘剩余空间不足，请加大硬盘可用空间
1022：关键字重复，更改记录失败
1023：关闭时发生错误
1024：读文件错误
1025：更改名字时发生错误
1026：写文件错误
1032：记录不存在
1036：数据表是只读的，不能对它进行修改
1037：系统内存不足，请重启数据库或重启服务器
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版本过低，不具有本功能

相关文章