mysql insert 主键冲突就更新

转载

网络安全侠 2024-09-23 13:27:45

文章目录

概要
步骤
出错
参考

概要

由于一时大意，在从库里执行了一条delect，本来马上执行跳过一个错误，是可以马上恢复同步的。

stop slave;
set global sql_slave_skip_counter=1; （1是指跳过一个错误）
slave start;

可当时没有检查同步功能，结果十几天后再回查看时已为时已晚，主从数据库差异比较大，已无法使用跳过来执行恢复，于是只能重做主从同步。

在网上查了大量资料，于是动手开干，这也是第一次做。

步骤

同步操作步骤：
1、登录从库的mysql停止slave

mysql> stop slave;

2、登录主库的mysql锁定数据库（期间不能写入数据）
建议半夜执行，这时访问人数不多；

mysql> flush table with read lock;

3、主库清除binlog

mysql>RESET MASTER;

4、主库查看锁定状态下的binlog和位置并截图保存
这步很重要，到时还原后从binlog开始执行；

mysql>show master status\G;

5、主库备份数据库
（我的数据库60GB，估计备份时间18分钟，做参考）

mysqldump --max_allowed_packet=500M -uroot -p  cms > /mysql/backup.sql

输入mysql密码执行备份

6、主库解锁表
备份完成后解锁主库，主库恢复正常使用

mysql>unlock tables;

7、主库传输备份文件到192.168.1.2（从库）

scp /mysql/backup.sql root@192.168.1.2:/mysql/backup.sql

8、进入从库删除已不同步的数据库表（重要：检查是否是从库服务器，不要在主库上操作，以防误删除主库）

mysql>drop database _table;

9、从库创建表_table

mysql>create database _table;

10、从库还原数据库
（我的数据库60GB还原预估时间18个小时，做参考）

mysql>use _table;
mysql>SOURCE /mysql/backup.sql;

11、还原完成，检查数据表是否完整（对比主库的表）

mysql>use _table;
mysql>show tables;

12、重置slave，会重置从库相关设置。
（ reset slave不会改变复制连接使用的参数，例如master_host, master_port, master_user, master_password）

mysql>reset slave;

13、修改同步日志和位点(第4步中保存的数据)

mysql>change master to master_log_file='mysql-bin.000001',master_log_pos=123;

14、从库开启slave

mysql>start slave;

15、查看从库同步状态

mysql>SHOW SLAVE STATUS\G;

如果出现
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
则表示同步成功

16、再次在主库中添加测试数据，看从库数据是否同步。

出错

在第15步时，可能出现错误，如下

[ERROR] Slave SQL: Error 'Duplicate entry '1007-443786-0' for key 'PRIMARY'' on query. Default database: 'ufo'. Query: 'insert into misdata (uid,mid,pid,sta
te,mtime) values (443786,1007,0,-1,1262598003)', Error_code: 1062
100104 17:39:05 [Warning] Slave: Duplicate entry '1007-443786-0' for key 'PRIMARY' Error_code: 1062
100104 17:39:05 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'ufolog.000058
8' position 55793296

Error_code: 1062是主键重复，说明是从库已存在该键值，这时可以直接sql_slave_skip_counter=1跳过，或批量跳过1062。

强制批量跳过 1062错误，可能修改从库的/etc/my.cnf文件
在里面的 [mysqld]下面加入了一行
slave-skip-errors = 1062 (忽略所有的1062错误)
重启下从库的 mysql /etc/init.d/mysqld restart

重新执行查看是否已完成同步