Mysql基于binlog方式进行数据同步常见问题解决方案汇总
Mysql基于binlog方式进行数据同步常见问题解决方案汇总
0、前置信息
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.13
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
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、解决方案
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'）
mysql> show slave status\G;
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;
mysql> show master status;
（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;
~~~~~~~~~~~~~~~~~~~~~未完待续~~~~~~~~~~~~~~~~~~~
**************************************************** 林深时见鹿，海蓝时见鲸 ****************************************************
