mysql主备复制报错,MySQL通过物理备份恢复实例，配置主从复制关系报错1236

场景描述

通过物理备份文件(从库的全量备份)恢复实例后，与主库建立主从复制关系，出现如下的报错信息：

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

恢复物理备份后，首先进行applylog操作

innobackupex --apply-log /data/mysql_4306/data/

查看物理备份文件中记录备份时候的GTID相关信息

cat data/xtrabackup_binlog_info

mysqlbin.010299 1101521 17136423-a6e9-11e9-9b46-005056b7f430:1-14744282:14744284-52079172,

7bd27dfa-a6e9-11e9-92df-005056b7552b:1-2

物理备份恢复后，创建主从复制的方式如下所示：

stop slave;reset slave all;reset master;

set global gtid_purged="17136423-a6e9-11e9-9b46-005056b7f430:1-14744282:14744284-52079172,,7bd27dfa-a6e9-11e9-92df-005056b7552b:1-2";

CHANGE MASTER TO

MASTER_HOST='10.21.124.118',

MASTER_USER='dba_repl',

MASTER_PASSWORD='replsafe',

MASTER_PORT=4306,

MASTER_AUTO_POSITION = 1;

start slave;show slave status\G

配置好复制后，启动复制，复制出现如下的报错信息：

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: 10.21.124.118

Master_User: dba_repl

Master_Port: 4306

Connect_Retry: 60

Master_Log_File: mysqlbin.000714

Read_Master_Log_Pos: 454311663

Relay_Log_File: slave-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysqlbin.000714

Slave_IO_Running: No

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: 454311663

Relay_Log_Space: 154

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: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 1236

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 242

Master_UUID: 17136423-a6e9-11e9-9b46-005056b7f430

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp: 200917 16:13:15

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set: 17136423-a6e9-11e9-9b46-005056b7f430:1-14744282:14744284-52078201,

7bd27dfa-a6e9-11e9-92df-005056b7552b:1-2

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

从报错信息看，是由于从库在执行主库传输过来的binlog event的时候，发现主库的binlog已经被purge掉了

首先，查看主库的gtid相关信息

mysql--dba_admin@127.0.0.1:(none) 16:30:48>>show master status;

+-----------------+-----------+--------------+------------------+-------------------------------------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-----------------+-----------+--------------+------------------+-------------------------------------------------+

| mysqlbin.000714 | 552383972 | | | 17136423-a6e9-11e9-9b46-005056b7f430:1-52127386 |

+-----------------+-----------+--------------+------------------+-------------------------------------------------+

1 row in set (0.00 sec)

原因分析

发现主库的GTID是连续的，而备份文件中的GTID是断开的，缺少14744283的事务，那么从库在执行到14744283事务的时候，发现主库的该事务已经被purge，所以出现了上述的报错情况

之所以从库的备份中出现GTID不连续的情况，有可能是由于在从库执行过空事务或跳过事务导致的

解决方案

重新设置gtid_purge，

stop slave;reset slave all;reset master;

set global gtid_purged="17136423-a6e9-11e9-9b46-005056b7f430:1-52079172,7bd27dfa-a6e9-11e9-92df-005056b7552b:1-2";

CHANGE MASTER TO

MASTER_HOST='10.21.124.118',

MASTER_USER='dba_repl',

MASTER_PASSWORD='replsafe',

MASTER_PORT=4306,

MASTER_AUTO_POSITION = 1;

start slave;show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Checking master version

Master_Host: 10.21.124.118

Master_User: dba_repl

Master_Port: 4306

Connect_Retry: 60

Master_Log_File:

Read_Master_Log_Pos: 4

Relay_Log_File: slave-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File:

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: 0

Relay_Log_Space: 154

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:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 0

Master_UUID:

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set: 17136423-a6e9-11e9-9b46-005056b7f430:1-52079172,

7bd27dfa-a6e9-11e9-92df-005056b7552b:1-2

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

主从复制失败的场景比较多，针对不同的问题，采用不同的解决方案；

如果您觉得本站对你有帮助，那么可以收藏和推荐本站，帮助本站更好地发展，在此谢过各位网友的支持。

转载请注明原文链接：MySQL通过物理备份恢复实例，配置主从复制关系报错1236