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.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;

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

posted @ 2022-03-15 09:54  星空流年  阅读(235)  评论(0编辑  收藏  举报