mysql数据不同步

原创

九月朦胧 2019-03-11 20:14:49 博主文章分类：Linux游乐园 ©著作权

©著作权归作者所有：来自51CTO博客作者九月朦胧的原创作品，请联系作者获取转载授权，否则将追究法律责任

mysql数据库突然不同步数据了查看slave的状态

show slave status\G

查看日志 2019-03-11 18:40:03 10283 [ERROR] Slave SQL: Error 'Duplicate entry '39' for key 'PRIMARY'' on query. Default database: 'db_jd'. Query: 'insert into tb_goods values (null,'ty','10.45','27','')', Error_code: 1062 2019-03-11 18:40:03 10283 [Warning] Slave: Duplicate entry '39' for key 'PRIMARY' Error_code: 1062 2019-03-11 18:40:03 10283 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000003' position 10743.

网上说是可能在slave里进行了写操作解决办法是

mysql> stop slave ; mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; mysql> start slave ;

发现还是不行，对了我的数据库是双主模型 slave当然可以写了又看到网上说是binlog的格式不对然后检查了my.cnf 发现确实少了几条，红框里的内容是原来没有的，后来添加上去的

然后重启mysql 查看日志又有报错 2019-03-11 19:34:36 454 [ERROR] Slave SQL: Slave failed to initialize relay log info structure from the repository, Err or_code: 1872 网上说是由之前的残留文件，于是删除了这两个文件 rm -rf slave-relay-bin.index rm -rf relay-log.info 再重启mysql

然后重新配置一下mysql同步进入mysql1 mysql> stop slave; Query OK, 0 rows affected (0.05 sec)

在mysql2上查看mysql-bin和position mysql> show master status; mysql> change master to master_host='192.168.255.57',master_port=3306,master_user='zhangsan',master_password='123456',m aster_log_file='mysql-bin.000006',master_log_pos=120; Query OK, 0 rows affected, 2 warnings (0.41 sec)

mysql> start slave; Query OK, 0 rows affected (0.05 sec)

mysql> show slave status\G;

mysql2也重新配置 mysql> stop slave; Query OK, 0 rows affected (0.05 sec) 在mysql1上查看mysql-bin和position mysql> show master status; mysql> change master to master_host='192.168.255.52',master_port=3306,master_user='zhangsan',master_password='123456',m aster_log_file='mysql-bin.000008',master_log_pos=120; Query OK, 0 rows affected, 2 warnings (0.30 sec)