• 欢迎访问DBA的辛酸事儿，推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号：DBA的辛酸事儿
  • 博文中若有错误的地方，请大家指正，大家的指正是我前进的动力
> 关系型数据库 > MySQL > MySQL通过物理备份恢复实例，配置主从复制关系报错1236

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

MySQL SEian.G 4年前 (2021-03-14) 1500次浏览 已收录 0个评论
文章目录[隐藏]

场景描述

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

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)

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

开启GTID主从同步出现1236错误问题

解决字符集不同引起的主从同步异常1677报错问题

MySQL复制错误ERROR 1837的相关缺陷案例

MySQL主从复制，启动slave时报错:Slave failed to initialize relay log info structure from the repository

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

如果您觉得本站对你有帮助，那么可以收藏和推荐本站，帮助本站更好地发展，在此谢过各位网友的支持。
转载请注明原文链接：MySQL通过物理备份恢复实例，配置主从复制关系报错1236
喜欢 (3)
SEian.G
关于作者：
用心去记录工作，用心去感受生活，用心去学着成长；座右铭：苦练七十二变，笑对八十一难
作者主页
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi，您需要填写昵称和邮箱！

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址