随笔- 729 文章- 0 评论- 51 阅读- 123万

MySQL slave upgrade: Slave failed to initialize relay log info structure from the repository

MySQL slave after upgrade from 5.6.x to 5.7.x may throw the following error:

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

The best solution for this error is to:

Master server – mysqldump the database with –master-data=1 –single-transaction
On the slave server issue command “RESET SLAVE;”
On the slave server import the dump sql file and issue “CHANGE MASTER” command with the meta data written in the sql dump
On the slave server issue START SLAVE to start the replication.

Here is an a real world example:
First, mysqldump in the master with

root@master ~ # mysqldump --master-data=1 --single-transaction mydb > /root/mydb.sql
root@master ~ # grep "CHANGE MASTER" media.sql 
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.023283', MASTER_LOG_POS=537774724;

And then copy the dump file to the slave server and import it and issue several specific slave commands:

root@slave ~ # mysql < /root/mydb.sql
root@slave ~ # mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.7.31-log Gentoo Linux mysql-5.7.31
 
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.023283', MASTER_LOG_POS=537774724;
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: 10.10.10.10
                  Master_User: ruser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.023283
          Read_Master_Log_Pos: 641769286
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 90874706
        Relay_Master_Log_File: mysql-bin.023283
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: mydb.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 628649113
              Relay_Log_Space: 103995088
              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: 2395
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: 101
                  Master_UUID: cd1bcebb-cc27-11e8-90c9-801844f2c4d8
             Master_Info_File: /mnt/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           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: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)