MySQL主从数据复制问题解决办法
来源:互联网 发布:手机wifi扫描不到网络 编辑:程序博客网 时间:2024/06/04 19:51
mysql主从复制,经常会遇到错误而导致slave端复制中断,这个时候一般就需要人工干预,跳过错误才能继续
跳过错误有两种方式:
1.跳过指定数量的事务:
mysql>slave stop;
mysql>set global sql_slave_skip_counter=1; #跳过一个事务
mysql>slave start
2.修改mysql的配置文件,通过slave_skip_errors参数来跳所有错误或指定类型的错误
vi /etc/my.cnf
[mysqld]
slave-skip-errors=1062,1053,1146 #跳过指定error no类型的错误
#slave-skip-errors=all #跳过所有错误
注意:当配置文件里写两行:
slave-skip-errors=1062
slave-skip-errors=1032时,第二个参数会覆盖第一个参数。所以一定要写到同一行,并用逗号分隔。
虽然slave会跳过这些错误,继续复制,但是仍会以Warning的形式记录到错误日志中,如:
跳过错误有两种方式:
1.跳过指定数量的事务:
mysql>slave stop;
mysql>set global sql_slave_skip_counter=1; #跳过一个事务
mysql>slave start
2.修改mysql的配置文件,通过slave_skip_errors参数来跳所有错误或指定类型的错误
vi /etc/my.cnf
[mysqld]
slave-skip-errors=1062,1053,1146 #跳过指定error no类型的错误
#slave-skip-errors=all #跳过所有错误
注意:当配置文件里写两行:
slave-skip-errors=1062
slave-skip-errors=1032时,第二个参数会覆盖第一个参数。所以一定要写到同一行,并用逗号分隔。
虽然slave会跳过这些错误,继续复制,但是仍会以Warning的形式记录到错误日志中,如:
160620 10:40:17 [Warning] Slave SQL: Could not execute Write_rows event on table dba.t; Duplicate entry '10' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000033, end_log_pos 1224, Error_code: 1062
重启Mysql服务
那些没有被复制的错误数据会输出到日志中
如:
Error_code: 1062Aug 16 17:44:53 mysql75 mysqld: 2017-08-16T09:44:53.142158Z 2 [Note] Slave SQL for channel '': Could not execute Query event. Detailed error: Duplicate entry '2017-08-14-3002-15616-62' for key 'PRIMARY';, Error_code: 1062Aug 16 17:44:57 mysql75 mysqld: 2017-08-16T09:44:57.910737Z 2 [Note] Slave SQL for channel '': Could not execute Query event. Detailed error: Duplicate entry '2017-08-14-3002-15626-84' for key 'PRIMARY';, Error_code: 1062Aug 16 17:44:57 mysql75 mysqld: 2017-08-16T09:44:57.911141Z 2 [Note] Slave SQL for channel '': Could not execute Query event. Detailed error: Duplicate entry '2017-08-14-3002-15626-85' for key 'PRIMARY';, Error_code: 1062Aug 16 17:44:57 mysql75 mysqld: 2017-08-16T09:44:57.911918Z 2 [Note] Slave SQL for channel '': Could not execute Query event. Detailed error: Duplicate entry '2017-08-14-3002-15626-87' for key 'PRIMARY';, Error_code: 1062Aug 16 17:44:57 mysql75 mysqld: 2017-08-16T09:44:57.912676Z 2 [Note] Slave SQL for channel '': Could not execute Query event. Detailed error: Duplicate entry '2017-08-14-3002-15626-88' for key 'PRIMARY';, Error_code: 1062Aug 16 17:44:57 mysql75 mysqld: 2017-08-16T09:44:57.913248Z 2 [Note] Slave SQL for channel '': Could not execute Query event. Detailed error: Duplicate entry '2017-08-14-3002-15626-89' for key 'PRIMARY';, Error_code: 1062Aug 16 17:44:57 mysql75 mysqld: 2017-08-16T09:44:57.913808Z 2 [Note] Slave SQL for channel '': Could not execute Query event. Detailed error: Duplicate entry '2017-08-14-3002-15626-90' for key 'PRIMARY';, Error_code: 1062Aug 16 17:44:57 mysql75 mysqld: 2017-08-16T09:44:57.914573Z 2 [Note] Slave SQL for channel '': Could not execute Query event. Detailed error: Duplicate entry '2017-08-14-3002-15626-91' for key 'PRIMARY';, Error_code: 1062Aug 16 17:46:18 mysql75 mysqld: 2017-08-16T09:46:18.933328Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 15293ms. The settings might not be optimal. (flushed=4427 and evicted=0, during the time.)Aug 16 17:48:48 mysql75 mysqld: 2017-08-16T09:48:48.759770Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4020ms. The settings might not be optimal. (flushed=4850 and evicted=0, during the time.)Aug 16 17:49:30 mysql75 mysqld: 2017-08-16T09:49:30.383725Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4139ms. The settings might not be optimal. (flushed=4984 and evicted=0, during the time.)
发生这种情况,一般是主机断电引起的!!
阅读全文
0 0
- MySQL主从数据复制问题解决办法
- mysql主从复制及问题解决
- mysql主从数据复制
- mysql 主从复制数据
- Mysql 主从复制+数据恢复
- mysql主从复制中出现的error和解救办法
- 搭建MySQL数据的主从复制环境
- MySQL--数据备份还原以及主从复制
- 分布式数据存储-MySQL主从复制
- mysql的主从复制数据延迟问题
- Mysql导入数据时乱码问题解决办法
- 【mysql 主从复制】掌握MySQL主从复制
- mysql 主从复制 主从配置
- Linux MySQL主从复制(Replication)(MySQL数据同步)配置
- mysql主从同步问题解决
- Mysql主从不同步问题解决
- MySQL主从复制配置
- Mysql 主从复制
- fmt标签格式化输出字符串为日期格式字符串
- Eclipse开发环境配置(Windows)
- 【模板】【数论】快速幂和快速乘法
- MySQL优化原理
- 多态练习题
- MySQL主从数据复制问题解决办法
- nginx支持ssl和panthinfo和去掉index.php
- centos 6.8 x86_64下autoconf版本升级到2.69
- linux awk 命令笔记
- justfy-content用法
- Android TextView图文混排,图片和文字居中对齐
- 小学生自学奥数必备的这些书籍
- JS 和OC的数据传递
- GANs实现(Generative Adversarial Networks)