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

前置信息

集群信息

复制代码
服务器连接信息：
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、主、从库修改数据库配置
1> 修改mysql配置文件

vi /etc/my.cnf
添加如下内容：

服务编号(与其它节点不冲突即可)

server_id=1
log_bin=binlog
binlog_format=ROW
2> 重启MySQL数据库

systemctl restart mysqld.service
1.2、主库操作说明
1> 主库创建复制账号，命令如下
mysql> CREATE USER repl IDENTIFIED BY '123456';
mysql> GRANT SELECT, SHOW VIEW, REPLICATION SLAVE, REPLICATION CLIENT ON . TO 'repl'@'%';
mysql> FLUSH PRIVILEGES;
2> 获取主库的binlog文件和当前位置

mysql> show master status;
获取主库的binlog文件和当前位置，即查询结果的 File、Position 字段，例如：File字段值为 binlog.XXXXXXXX，Position 字段值为 YYYYYYYY

1.3、从库关联同步主库数据
复制代码
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、解决方案
2.2.2.1、方案1--从库插入不存在数据，然后开启数据同步
1> 在从库执行如下命令：

mysql> show slave status;
找到Exec_Master_Log_Pos的值，例如：XXXX；Last_Error信息中的end_log_pos的值，例如：YYYYYY

2> 在主库使用自带的mysqlbinlog查看删除信息：

cd /usr/bin
mysqlbinlog --no-defaults -v -v --base64-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.2.2.2、方案2--从库直接跳过GTID
1> 在从库中执行如下命令

mysql> show slave status\G;
找到同步失败时主库log位置，即Exec_Master_Log_Pos的值，例如：XXXX；同时从Last_Error信息中找到同步失败的binlog文件名，例如：binlog.000021

2> 在主库中查询执行如下语句

mysql> show binlog events IN 'binlog.000021' FROM XXXX limit 10;
找到XXXX对应的SESSION.GTID_NEXT值，例如：ZZZZZ

3> 在从库中执行如下语句

复制代码
mysql> stop slave;
mysql> set GTID_NEXT='ZZZZZ';
mysql> begin;commit;
mysql> set GTID_NEXT='AUTOMATIC';
mysql> start slave;
mysql> show slave status;
复制代码
上面方法只能跳过一个事务，那么对于一批事务跳过，参考如下指令：

（1）主库执行如下语句

mysql> show master status;
根据查询结果，找到 Executed_Gtid_Set 字段对应的值，例如：2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-19345623

（2）在从库中执行如下语句

mysql> stop slave;
mysql> reset master;
mysql> set global gtid_purged='2a09ee6e-645d-11e7-a96c-000c2953a1cb:1-19345623';
mysql> start slave;
mysql> show slave status;
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的值，例如：YYYYYY

2> 在主库使用mysqlbinlog查看更新操作的数据：

mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS --start-position=XXXX --stop-position=YYYYYY /var/lib/mysql/binlog.000001
3> 在主库中查看更新之后的数据，然后将这些数据导出；并在从库执行插入语句插入数据。

4> 在主库中查询执行事件：

mysql> show binlog events IN 'binlog.000001' FROM XXXX limit 50;
找到XXXX对应的SESSION.GTID_NEXT值 ZZZZZ

5> 在从库中执行如下语句

复制代码
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_errors

mysql> show variables like '%max_connect_errors%';
(2) 修改max_connection_errors的数量为1000

mysql> 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, end_log_pos 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 log
Last_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;

主键重复

主键重复有哪些情况?

全备之后

再从库还没启动之前，主库又有了新的数据，这个时候 Binlog的位置 选择起来比较麻烦。
如果位置选的靠后 比如 18:00 全备 18:01 到18:10产生了新的数据和binlog-pos
这个时候开始启动binlong同步，时间选择为17:00左右的binlog-pos 这个时候就会产生主键重复的问题
[mysqld]

跳过指定 error no 类型的错误

slave-skip-errors = 1062,1053,1146

跳过所有错误

slave-skip-errors = all

常用binlog 命令

# 是否启用binlog日志
show variables like 'log_bin';
 
# 查看详细的日志配置信息
show global variables like '%log%';
 
# 查看所有二进制日志列表
show master logs; 或 show binary logs;
 
# 查看最新一个binlog日志文件名称和Position
show master status;
 
# 修改binlog的格式
set global binlog_format='mixed' 
 
 
# 查看 binlog 内容
show binlog events;
 
# 查看具体一个binlog文件的内容 （in 后面为binlog的文件名）
show binlog events in 'master.000003';
 
# 设置binlog文件保存时间，过期自动删除，单位: 天
set global expire_log_days=3;