【MySQL】mysql5.7多源复制报错问题处理

场景:

Master2

192.168.9.144:5000

Master1

multi-slave

host:port

192.168.9.78:4001

testdba-mysql56.rdsm2wctkd8fa14.rds.bj.baidubce.com:3306

uuid

b3ac499d-6662-11e9-be7e-005056877607

1e345bf9-32a4-11ea-b269-fa163ee30083

8ef26d45-82df-11ea-85b4-00505687de1b

复制的db

Slavedb01

dba_test01

问题1:跳过某个channel的报错

1、gtid模式

方式1:
stop slave for channel 'master_mysql9784001'; 
set gtid_next='b3ac499d-6662-11e9-be7e-005056877607:10';
begin;commit;set gtid_next='automatic';
start slave for channel 'master_mysql9784001';G

复制

方式2:(代价太大,一般不采用)
(1) 查看报错的channel的已执行的位置点
--查看报错的channel的已执行的位置点
Retrieved_Gtid_Set: b3ac499d-6662-11e9-be7e-005056877607:2-9
Executed_Gtid_Set: 1e345bf9-32a4-11ea-b269-fa163ee30083:1-2224,
8ef26d45-82df-11ea-85b4-00505687de1b:1,
b3ac499d-6662-11e9-be7e-005056877607:1-7

复制

(2) 停止所有的slave复制, 查看并纪录已经执行的gtid的位置
--停止所有的slave复制
stop slave;
--查看并纪录已经执行的gtid的位置
show master status;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 422144
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 1e345bf9-32a4-11ea-b269-fa163ee30083:1-2320,
8ef26d45-82df-11ea-85b4-00505687de1b:1,
b3ac499d-6662-11e9-be7e-005056877607:1-7

复制

(3) 重新设置gtid的点
--重新设置gtid的点
reset master;
set @@GLOBAL.GTID_PURGED="1e345bf9-32a4-11ea-b269-fa163ee30083:1-2320,b3ac499d-6662-11e9-be7e-005056877607:1-8";

复制

(4) 开启复制
start slave;
show slave status; --报错的channel已经恢复正常

复制

2、filepos模式:

停止要跳过的channel,下面是不使用gtid模式的步奏

--步骤:
1. mysql> stop slave for channel 'channel name';
2. mysql> set global sql_slave_skip_counter=1;
3. mysql> start slave for channel 'channel name';

--如果停止start slave;则会报错:
ERROR 3086 (HY000): When sql_slave_skip_counter> 0, it is not allowed to start more than one SQLthread by using 'START SLAVE [SQL_THREAD]'. Value ofsql_slave_skip_counter can only be used by one SQL thread at atime. Please use 'START SLAVE [SQL_THREAD] FOR CHANNEL' to startthe SQL thread which will use the value ofsql_slave_skip_counter.
通过上面报错可知,每一个sql_slave_skip_counter 只能适用一个sqlthread 线程

复制

问题2:上游master2进行了主从切换

1、gtid模式

无需人为接入,同步不会报错,自动获取新的master的gtid

2、filepos模式

新master的file、pos位置已经改变,需要重新change master配置新的复制点


MySQL主从同步报错故障处理集锦

前言

在发生故障切换后,经常遇到的问题就是同步报错,下面是最近收集的报错信息。

记录删除失败

在master上删除一条记录,而slave上找不到

Last_SQL_Error: Could not execute Delete_rows event on table hcy.t1; 
Can't find record in 't1',
Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;
the event's master log mysql-bin.000006, end_log_pos 254

解决方法:master要删除一条记录,而slave上找不到报错,这种情况主都已经删除了,那么从机可以直接跳过。

stop slave;
set global sql_slave_skip_counter=1;
start slave;

如果这种情况很多,需要针对这种错误专门写相关脚本

主键重复

在slave已经有该记录,又在master上插入了同一条记录。

Last_SQL_Error: Could not execute Write_rows event on table hcy.t1; 
Duplicate entry '2' for key 'PRIMARY',
Error_code: 1062;
handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 924

解决方法

在slave上用desc hcy.t1; 先看下表结构:

mysql> desc hcy.t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | char(4) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+

删除重复的主键

mysql> delete from t1 where id=2;
Query OK, 1 row affected (0.00 sec)

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

mysql> show slave statusG;
……
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
……
mysql> select * from t1 where id=2;

在master上和slave上再分别确认一下。

更新丢失

在master上更新一条记录,而slave上找不到,丢失了数据。

Last_SQL_Error: Could not execute Update_rows event on table hcy.t1; 
Can't find record in 't1',
Error_code: 1032;
handler error HA_ERR_KEY_NOT_FOUND;
the event's master log mysql-bin.000010, end_log_pos 794

解决方法:

在master上,用mysqlbinlog
分析下出错的binlog日志在干什么。

/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000010 | grep -A '10' 794

#120302 12:08:36 server id 22 end_log_pos 794 Update_rows: table id 33 flags: STMT_END_F
### UPDATE hcy.t1
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='bbc' /* STRING(4) meta=65028 nullable=1 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='BTV' /* STRING(4) meta=65028 nullable=1 is_null=0 */
# at 794
#120302 12:08:36 server id 22 end_log_pos 821 Xid = 60
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

在slave上,查找下更新后的那条记录,应该是不存在的。

mysql> select * from t1 where id=2;
Empty set (0.00 sec)
然后再到master查看

mysql> select * from t1 where id=2;
+----+------+
| id | name |
+----+------+
| 2 | BTV |
+----+------+
1 row in set (0.00 sec)

把丢失的数据在slave上填补,然后跳过报错即可。

mysql> insert into t1 values (2,'BTV');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1 where id=2;
+----+------+
| id | name |
+----+------+
| 2 | BTV |
+----+------+
1 row in set (0.00 sec)

mysql> stop slave ;set global sql_slave_skip_counter=1;start slave;
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

mysql> show slave statusG;
……
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
……

1236错误,
二进制文件缺失

误删二进制文件等各种原因,导致主库mysql-bin.000012文件丢失,从库同步失败。

Master_Log_File: mysql-bin.000012
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
  • 首先停止从库同步
slave stop;
  • 查看主库日志文件和位置
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000013 | 154 |
+------------------+-----------+
  • 回从库,使日志文件和位置对应主库
  • CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000013',MASTER_LOG_POS=154;
    • 最后,启动从库:
    slave start;

    show slave statusG;

    Master_Log_File: mysql-bin.000013
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Last_IO_Error:

    中继日志损坏

    slave的中继日志relay-bin损坏。

    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

    1、手工修复
    解决方法:找到同步的binlog和POS点,然后重新做同步,这样就可以有新的中继日值了。

    例子:

    mysql> show slave statusG;
    *************************** 1. row ***************************
    Master_Log_File: mysql-bin.000010
    Read_Master_Log_Pos: 1191
    Relay_Log_File: vm02-relay-bin.000005
    Relay_Log_Pos: 253
    Relay_Master_Log_File: mysql-bin.000010
    Slave_IO_Running: Yes
    Slave_SQL_Running: No
    Replicate_Do_DB:
    Replicate_Ignore_DB:
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
    Last_Errno: 1593
    Last_Error: Error initializing relay log position: I/O error reading the header from the binary log
    Skip_Counter: 1
    Exec_Master_Log_Pos: 821

    Slave_IO_Running :接收master的binlog信息
    Master_Log_File
    Read_Master_Log_Pos

    Slave_SQL_Running:执行写操作
    Relay_Master_Log_File
    Exec_Master_Log_Pos

    以执行写的binlog和POS点为准。

    Relay_Master_Log_File: mysql-bin.000010
    Exec_Master_Log_Pos: 821
    mysql> stop slave;
    Query OK, 0 rows affected (0.01 sec)

    mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010',MASTER_LOG_POS=821;
    Query OK, 0 rows affected (0.01 sec)

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


    mysql> show slave statusG;
    *************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 192.168.8.22
    Master_User: repl
    Master_Port: 3306
    Connect_Retry: 10
    Master_Log_File: mysql-bin.000010
    Read_Master_Log_Pos: 1191
    Relay_Log_File: vm02-relay-bin.000002
    Relay_Log_Pos: 623
    Relay_Master_Log_File: mysql-bin.000010
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    Replicate_Do_DB:
    Replicate_Ignore_DB:
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
    Last_Errno: 0
    Last_Error:
    Skip_Counter: 0
    Exec_Master_Log_Pos: 1191
    Relay_Log_Space: 778
    Until_Condition: None
    Until_Log_File:
    Until_Log_Pos: 0
    Master_SSL_Allowed: No
    Master_SSL_CA_File:
    Master_SSL_CA_Path:
    Master_SSL_Cert:
    Master_SSL_Cipher:
    Master_SSL_Key:
    Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
    Last_IO_Errno: 0
    Last_IO_Error:
    Last_SQL_Errno: 0
    Last_SQL_Error:

    2、Ibbackup
    各种大招都用上了,无奈slave数据丢失过多,ibbackup(需要银子)该你登场了。

    Ibbackup热备份工具,是付费的。xtrabackup是免费的,功能上一样。

    Ibbackup备份期间不锁表,备份时开启一个事务(相当于做一个快照),然后会记录一个点,之后数据的更改保存在ibbackup_logfile文件里,恢复时把ibbackup_logfile
    变化的数据再写入到ibdata里。

    Ibbackup 只备份数据( ibdata、.ibd
    ),表结构.frm不备份。


    mysql多源复制 错误_数据库多源复制同步错误修复

    发现有一个数据库报错了。报错如下。

    Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction ‘ANONYMOUS‘ at master log mysql-bin.000029, end_log_pos 6389312. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

    解决方法:

    进入从服务器,mysql

    1.查看从库状态

    mysql> show slave status;

    2.按照从库的提示查找原因。

    select * from performance_schema.replication_applier_status_by_worker\G

    是说主库某个表删除时,从库却找不到删除的记录。

    因一下没看明白。(后续实践,再补充)

    3.先停止主从同步。

    mysql> stop slave;

    4.用navicate工具把数据库同步。

    navicate的菜单栏,工具->数据同步。

    先 结构同步,再 数据同步。

    然后,同步完后,到主库。用命令。

    mysql>SHOW MASTER STATUS;

    因为我这里做的是多源复制。所以把两个数据库同步后,先后记录下来。

    得到:

    ‘mysql-bin.000029‘, ‘20698487‘,

    ‘mysql-bin.000042‘, ‘84950954‘,

    5.清空原来的同步设置

    mysql>reset slave all;

    6.再设置同步。

    下面的ip地址,username,password,是举例的。大家按自己的来。

    change master to master_host=‘192.168.0.1‘,master_user=‘username1‘,master_port=3306,master_password=‘password1‘,master_log_file=‘mysql-bin.000029‘,master_log_pos=20698487 for channel ‘databasename1‘;

    change master to

    master_host=‘192.168.0.2‘,master_user=‘username2‘,master_port=3306,master_password=‘password2‘,master_log_file=‘mysql-bin.000042‘,master_log_pos=84950954 for channel ‘databasename2‘;

    7.启动同步

    mysql> start slave;

    8.查看同步状态

    mysql> show slave status;

    同步恢复正常了。

    为人:谦逊、激情、博学、审问、慎思、明辨、 笃行
    学问:纸上得来终觉浅,绝知此事要躬行
    为事:工欲善其事,必先利其器。
    态度:道阻且长,行则将至;行而不辍,未来可期
    .....................................................................
    ------- 换了个头像,静静的想,默默的思恋,一丝淡淡的忧伤 ----------
    ------- 桃之夭夭,灼灼其华。之子于归,宜其室家。 ---------------
    ------- 桃之夭夭,有蕡其实。之子于归,宜其家室。 ---------------
    ------- 桃之夭夭,其叶蓁蓁。之子于归,宜其家人。 ---------------
    =====================================================================
    转载请标注出处!