修改主机名导致Slave failed to initialize relay log info structure from the repository

最新推荐文章于 2024-07-05 05:34:35 发布

遇星

最新推荐文章于 2024-07-05 05:34:35 发布

阅读量5.8k

点赞数 3

分类专栏： MySQL 文章标签： MySQL 开源数据库

本文链接：https://blog.csdn.net/weixin_39004901/article/details/84847640

版权

MySQL 专栏收录该内容

99 篇文章 3 订阅

订阅专栏

我自己的主从环境出现如下报错，从库无法开启复制，版本是5.7：

mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

检查error.log有如下报错：

2018-12-06T01:40:22.887954Z 0 [ERROR] Failed to open the relay log './mysql2-relay-bin.000003' (relay_log_pos 1429).
2018-12-06T01:40:22.888774Z 0 [ERROR] Could not find target log file mentioned in relay log info in the index file './slave-relay-bin.index' during relay log initialization.
2018-12-06T01:40:22.890565Z 0 [ERROR] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.
2018-12-06T01:40:22.890932Z 0 [ERROR] Failed to create or recover replication info repositories.
2018-12-06T01:40:22.891332Z 0 [ERROR] Slave SQL for channel '': Slave failed to initialize relay log info structure from the repository, Error_code: 1872
2018-12-06T01:40:22.891639Z 0 [ERROR] /usr/local/mysql/bin/mysqld: Slave failed to initialize relay log info structure from the repository
2018-12-06T01:40:22.891952Z 0 [ERROR] Failed to start slave threads for channel ''
2018-12-06T01:40:22.892190Z 0 [Note] Some of the channels are not created/initialized properly. Check for additional messages above. You will not be able to start replication on those channels until the issue is resolved and the server restarted.
2018-12-06T01:40:22.921763Z 0 [Note] Event Scheduler: Loaded 0 events
2018-12-06T01:40:22.923371Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.7.23-log'  socket: '/usr/local/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2018-12-06T01:40:50.341817Z 2 [ERROR] Slave SQL for channel '': Slave failed to initialize relay log info structure from the repository, Error_code: 1872

从日志信息来看，MySQL无法获取relay log的记录信息，所以不知道从哪里开始复制了。
从以下日志才忽然想起我上一次关机前，修改过主机名，应该就是这个原因。

2018-12-06T01:40:22.887954Z 0 [ERROR] Failed to open the relay log './mysql2-relay-bin.000003' (relay_log_pos 1429).
2018-12-06T01:40:22.888774Z 0 [ERROR] Could not find target log file mentioned in relay log info in the index file './slave-relay-bin.index' during relay log initialization.

看一下relay log的情况，

[root@slave data]# ls
auto.cnf    client-cert.pem  ibdata1      ibtmp1       mysql2-relay-bin.000002  performance_schema  sam              slave-relay-bin.000001
ca-key.pem  client-key.pem   ib_logfile0  master.info  mysql2-relay-bin.000003  private_key.pem     server-cert.pem  slave-relay-bin.index
ca.pem      ib_buffer_pool   ib_logfile1  mysql        mysql2-relay-bin.index   public_key.pem      server-key.pem   sys

果然，修改主机名之后，MySQL按照新hostname生成了信息的relay log。那么既然从库的relay log无法初始化，看一下记录relay log信息的文件内容。

[root@slave data]# cat relay-log.info 
7
./mysql2-relay-bin.000003
1429
mysql-bin.000007
1216
0
0
1

可以看到，上一次关机前，从库已经应用到mysql2-relay-bin.000003的1429位置。

[root@slave data]# mysqlbinlog --no-defaults -v mysql2-relay-bin.000003
......
# at 1398
#181205 10:41:35 server id 128  end_log_pos 1216 CRC32 0xdcc5aa20       Xid = 30
COMMIT/*!*/;
# at 1429
#181205 11:27:57 server id 129  end_log_pos 1452 CRC32 0x3f271ae7       Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

可以看到，1429即是mysql2-relay-bin.000003的末尾了，也就是，从库没办法自动地去应用新生成的slave-relay-bin.000001。
再看一下当前的从库状态：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.204.128
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 1216
               Relay_Log_File: mysql2-relay-bin.000003
                Relay_Log_Pos: 1429
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1872
                   Last_Error: Slave failed to initialize relay log info structure from the repository
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1216
              Relay_Log_Space: 0
              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: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1872
               Last_SQL_Error: Slave failed to initialize relay log info structure from the repository
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: f4859d1f-f82f-11e8-afbe-000c2999b048
             Master_Info_File: /mysqlData/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 181206 08:25:02
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 2b7ac8e6-f834-11e8-97d4-000c29d2f92c:1,
f4859d1f-f82f-11e8-afbe-000c2999b048:1-7
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

从库也是应用到1429的位置。那么只要让从库识别slave-relay-bin.000001并开始应用即可。

怎么办呢？可以从error.log中找出点思路。

2018-12-06T01:40:22.888774Z 0 [ERROR] Could not find target log file mentioned in relay log info in the index file './slave-relay-bin.index' during relay log initialization.

从库读取relay log，需要通过relay-bin.index去确认这个relay log是否存在。报错信息显示从库已经自动去读取slave-relay-bin.index这个新的索引文件，但是由于上一次relay log的读取位置是1429，从relay log内容可以确认1429以后没有实际的数据操作，但从binlog的执行角度，要执行完mysql2-relay-bin.000003，后面还有一些内容，也就是说mysql2-relay-bin.000003这个relay log还是需要执行的。但mysql2-relay-bin.000003不在slave-relay-bin.index中，所以MySQL并不认可这个relay log，就导致MySQL从库无法读取slave-relay-bin.index中的slave-relay-bin.000001。

那么解决办法就简单了，只需要让slave-relay-bin.index包含mysql2-relay-bin.000003即可，但需要注意mysql2-relay-bin.000003必须在slave-relay-bin.000001之前。

[root@slave data]# cat slave-relay-bin.index >> mysql2-relay-bin.index 
[root@slave data]# cat mysql2-relay-bin.index 
./mysql2-relay-bin.000002
./mysql2-relay-bin.000003
./slave-relay-bin.000001
[root@slave data]# mv mysql2-relay-bin.index slave-relay-bin.index 
mv: overwrite `slave-relay-bin.index'? y

最后重启MySQL从库，即可正常开启复制。