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

落日映苍穹つ 2022-04-11 11:50 159阅读 0赞

我自己的主从环境出现如下报错，从库无法开启复制，版本是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的记录信息，所以不知道从哪里开始复制了。  
从以下日志才忽然想起我上一次关机前，修改过主机名，应该就是这个原因。

看一下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从库，即可正常开启复制。