Mysql基于binlog方式进行数据同步常见问题解决方案汇总

0.1、集群信息

服务器连接信息：

192.168.91.131(master)192.168.91.132(slave1)192.168.91.133(slave2)使⽤ssh⽅式访问服务器：ssh root@192.168.91.*输⼊对应密码访问

0.2、从库数据导⼊

在192.168.91.131命令⾏窗⼝下直接执⾏数据库表数据导⼊：

mysqldump --default-character-set=utf8mb4 --host=192.168.91.131 -uroot -p123456 --opt --set-gtid-purged=OFF 从库需要导⼊的表名 | mysql --host=从库IP地址 --port=3306 -uroot -p123456 --default-character-set=utf8mb4 -C 从库需要导⼊的表名

说明： 使⽤此⽅式进⾏数据导⼊时，保证⽬标数据库中数据库表与源数据库中数据表⼀致，同时，⽬标数据库中数据表保证为空表

0.3 、从库设置同步过滤规则

---- 从库设置同步过滤规则（在my.cnf中设置，对应k8s在配置字典中配置） ----replicate_wild_do_table = 要同步的数据库名.%replicate_wild_ignore_table = 要忽略的数据库名.%

1、数据同步(binlog⽅式)

主、从库使⽤binlog⽅式同步数据，操作步骤：

1.1、主库执⾏命令

mysql> show master status;

获取主库的binlog⽂件和当前位置，即查询结果的 File、Position 字段，例如：File字段值为 binlog.XXXXXXXX，Position 字段值为 YYYYYYYY

1.2、从库执⾏命令

mysql> show slave status;mysql> stop slave;mysql> reset slave;

mysql> CHANGE MASTER TO MASTER_HOST = '192.168.91.131', MASTER_USER = 'repl',

MASTER_PASSWORD = '123456', MASTER_PORT = 3306, MASTER_LOG_FILE='binlog.XXXXXXXX',MASTER_LOG_POS=YYYYYYYY; mysql> start slave;

mysql> show slave status;

2、常见问题汇总及解决

2.1、主、从库数据表字符集不⼀致

2.1.1、报错信息

Last_SQL_Errno: 1677

Last_SQL_Error: Column 1 of table 'XXX' cannot be converted from type 'varchar(150(bytes))' to type 'varchar(110(bytes))'

2.1.2、解决⽅案

1> 主、从库查看数据表的字符集信息mysql> show create table 表名;2> 从库执⾏如下命令

mysql> stop slave;

mysql> alter table table_name convert to character set 主库数据表字符集;mysql> start slave;

mysql> show slave status;

2.2、主库删除从库不存在数据

2.2.1、报错信息

Last_Errno: 1032

Last_SQL_Error: Could not execute Delete_rows event on table XXX; Can't find record in 'XXX', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;

2.2.2、解决⽅案

1> 在从库执⾏如下命令：mysql> show slave status;

找到Exec_Master_Log_Pos的值，例如：XXXX；Last_Error信息中的end_log_pos的值，例如：YYYYYY2> 在主库使⽤⾃带的mysqlbinlog查看删除信息：

cd /usr/bin

mysqlbinlog --no-defaults -v -v --base-output=DECODE-ROWS --start-position=XXXX --stop-position=YYYYYY /var/lib/mysql/binlog.000001说明：

主库mysqlbinlog所在⽂件夹位置，可以使⽤如下命令查找：

whereis mysqlbinlog

3> 找到删除语句之后，在从库插⼊删除数据，例如：insert into test values(1, 'jack');4> 在从库执⾏如下命令mysql> start slave;

mysql> show slave status;

插⼊数据时，如果遇到索引冲突的问题，可参考如下操作：

mysql插⼊数据时，出现Duplicate entry 'XXX' for key 'XXX'的问题：可以使⽤replace into,

replace into是insert into的增强版：

(1) 如果插⼊的数据不重复，执⾏的是insert into操作，影响1条记录

(2) 如果插⼊的数据重复，执⾏的是update操作，影响2条记录：先删除旧的数据，再插⼊新的数据。⽰例：

replace into test values(1,'jack'）

2.3、主库更新从库不存在数据

2.3.1、报错信息

Last_Errno: 1032

Last_Error: Could not execute Update_rows event on table XXX; Can't find record in 'XXXX', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log binlog.0000XX, end_log_pos XXXXX

2.3.2、解决⽅案

1> 在从库执⾏如下命令：mysql> show slave status; 也可使⽤：

mysql> show slave status\\G;

找到Exec_Master_Log_Pos的值，例如：XXXX；Last_Error信息中的end_log_pos的值，例如：YYYYYY2> 在主库使⽤mysqlbinlog查看更新操作的数据：

mysqlbinlog --no-defaults -v -v --base-output=DECODE-ROWS --start-position=XXXX --stop-position=YYYYYY /var/lib/mysql/binlog.0000013> 在主库中查看更新之后的数据，然后将这些数据导出；并在从库执⾏插⼊语句插⼊数据。4> 在主库中查询执⾏事件：

mysql> show binlog events IN 'binlog.000001' FROM XXXX limit 50;找到XXXX对应的SESSION.GTID_NEXT值 ZZZZZ5> 在从库中执⾏如下语句

mysql> stop slave;

mysql> set GTID_NEXT='ZZZZZ';mysql> begin;commit;

mysql> set GTID_NEXT='AUTOMATIC';mysql> start slave;

mysql> show slave status;

2.4、从库连接数不⾜导致同步失败

2.4.1、报错信息

Slave_IO_State: connecting to master

Last_IO_Error: MySql Host is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

2.4.2、解决⽅案

1> 主、从数据库都要查看max_connection_errors(1) 进⼊主、从库Mysql数据库查看max_connection_errorsmysql> show variables like '%max_connect_errors%';(2) 修改max_connection_errors的数量为1000mysql> set global max_connect_errors = 1000;(3) 查看是否修改成功

mysql> show variables like '%max_connect_errors%'; 2> 在主、从库下清理⼀下hosts⽂件mysql> flush hosts;

2.5、主库下caching_sha2_password插件导致连接异常

2.5.1、报错信息

Last_IO_Error: error connecting to master 'repl@192.168.91.131:3306' - retry-time: 60 retries: 7 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

2.5.2、解决⽅案

1> 在主库下数据库名为 mysql下的 user表，查看 user为repl 信息mysql> SELECT plugin FROM `mysql`.`user` where user = 'repl';

2> 修改主库mysql下的 user表中repl对应的mysql_native_password (新旧密码可以保持⼀致)mysql> ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

2.6、主从数据同步报错1782

2.6.1、报错信息

Last_Errno: 1782

Last_Error: Error executing row event: '@@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.'

2.6.2、解决⽅案

在从库中执⾏如下命令：

mysql> stop slave sql_thread;

mysql> set GLOBAL GTID_MODE = ON_PERMISSIVE;mysql> set GLOBAL GTID_MODE = OFF_PERMISSIVE;mysql> set GLOBAL GTID_MODE = OFF;mysql> start slave sql_thread;mysql> start slave;

mysql> show slave status;

2.7、主从数据同步报错1781

2.7.1、报错信息

Last_Errno: 1781

Last_Error: @@SESSION.GTID_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF.

2.7.2、解决⽅案

在从库中执⾏如下命令：

mysql> set GLOBAL GTID_MODE = OFF_PERMISSIVE;mysql> set GLOBAL GTID_MODE = ON_PERMISSIVE;mysql> set GLOBAL GTID_MODE = ON;mysql> start slave;

mysql> show slave status;

2.8、从库存在数据，主库插⼊相同数据同步失败

说明：

此种情况是从库存在数据，导致主库在插⼊相同数据时，从库数据同步报错。

2.8.1、报错信息

Last_Errno: 1062

Last_Error: Could not execute Write_rows event on table XXXX.xxx; Duplicate entry 'XXX' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log binlog.XXXXXXX,

2.8.2、解决⽅案

针对此种情况，保证以主库数据为主，同时删除从库数据时需要考虑从库表数据主键是否为⾃增。mysql> stop slave;

mysql> delete from XXXX.xxx where 主键字段='XXX';mysql> start slave;

mysql> show slave status;

2.9、中继⽇志损坏

2.9.1、报错信息

Last_SQL_Error: Error initializing relay log position: I/O error reading the header from the binary logLast_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

2.9.2、解决⽅案

说明：找到同步的binlog和POS点，然后重新做同步，这样就可以有新的中继⽇志1> 在从库查找执⾏的中继⽇志Relay_Master_Log_File和Exec_Master_Log_Pos节点mysql> show slave status\\G;

获取 Relay_Master_Log_File 值(例如：binlog.xxxxx) 和 Exec_Master_Log_Pos 值(例如：YYYYYY)2> 在从库执⾏如下命令

mysql> stop slave;

mysql> CHANGE MASTER TO MASTER_LOG_FILE='binlog.xxxxx', MASTER_LOG_POS=YYYYYY;mysql> start slave;

mysql> show slave status\\G;

~~~~~~~~~~~~~~~~~~~~~未完待续~~~~~~~~~~~~~~~~~~~