宝塔服务器面板，一键全能部署及管理，送你10850元礼包，点我领取
一、错误描述
1、查看slave节点的状态如下：
mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.232.42 Master_User: repl_user1 Master_Port: 3308 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 892 Relay_Log_File: mysql-relay-bin.000006 Relay_Log_Pos: 296 Relay_Master_Log_File: mysql-bin.000006 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: customer 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: 772 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: 5eb9dc85-2d68-11eb-acf4-000c29cddf72 Master_Info_File: /mysql/mysql5.7/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: 201202 14:32:57 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)
2、启动slave报错如下：
mysql> start slave -> ; ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
二、错误分析
1、查看错误日志
[root@node3 ~]# cd /mysql/mysql5.7/log/ [root@node3 log]# tail -10 mysqld.log WOC : NO Next activation : never 2020-12-02T06:54:37.694621Z 0 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode. 2020-12-02T06:54:37.694638Z 0 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode. 2020-12-02T06:54:37.694657Z 0 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode. 2020-12-02T06:54:37.694663Z 0 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode. 2020-12-02T06:54:37.694672Z 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode. 2020-12-02T06:54:37.694981Z 0 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode. 2020-12-02T06:54:37.694994Z 0 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode. 2020-12-02T06:55:36.446588Z 2 [ERROR] Slave SQL for channel '': Slave failed to initialize relay log info structure from the repository, Error_code: 1872
2、对比slave节点状态信息和my.cnf的配置信息
--1、slave节点的状态
mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.232.42 Master_User: repl_user1 Master_Port: 3308 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 892 Relay_Log_File: mysql-relay-bin.000006 Relay_Log_Pos: 296 Relay_Master_Log_File: mysql-bin.000006 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: customer Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys
--2、/etc/my.cnf配置文件如下
[root@node3 log]# cat /etc/my.cnf
[mysqld]
datadir=/mysql/mysql5.7/data
basedir=/mysql/mysql5.7
socket=/tmp/mysql.sock
#socket=/var/lib/mysql/mysql.sock
user=mysql
port=3308
character-set-server=utf8
# skip-grant-tables
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
log-bin = mysql-bin
relay-log = relay-log
binlog_format = row
server-id = 3
skip_name_resolve
relay_log_purge = 0
注：从上面的信息发现slave状态中要找的relay日志是mysql-relay-bin开头的，而配置文件中配置的是relay-log开头的，所以导致slave启动的时候找不到相应的中继日志文件，原来是因为这个虚拟机是从别的虚拟机的快照克隆过来的，原来虚拟机就是主从的架构，导致安装的mysql数据库里还遗留有别的信息。所以导致启动slave失败。
三、解决办法
1、删除slave记录
2、重新change master开启同步
3、开启slave
mysql> reset slave; Query OK, 0 rows affected (0.04 sec) mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.232.42', -> MASTER_USER='repl_user1', -> MASTER_PORT=3308, -> MASTER_PASSWORD='$a123456', -> MASTER_LOG_FILE='mysql-bin.000002', -> MASTER_LOG_POS=892; Query OK, 0 rows affected, 2 warnings (0.10 sec) mysql> start slave; Query OK, 0 rows affected (0.02 sec) mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.232.42 Master_User: repl_user1 Master_Port: 3308 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 1175 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 603 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: customer 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: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1175 Relay_Log_Space: 804 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: 1 Master_UUID: 5eb9dc85-2d68-11eb-acf4-000c29cddf72 Master_Info_File: /mysql/mysql5.7/data/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: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) mysql>
支付宝扫一扫
微信扫一扫
