MySQL主从复制报错、从节点无法同步数据

2021-04-23

字数统计: 637字 | 阅读时长≈ 3分

1.重启slave之后，启动slave时报错Slave failed to initialize relay log info structure from the repository。

1
2
3

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

2.先查看日志，具体报错的信息

 m /var/lib/mysql/ib_buffer_pool
edat18041514:43:25
 .pem in data directory. Trying to enable SSL support using them.
ort:3306
 r --relay-log-index were used; so replication may break when this MysQL sorver acts as a slave and has his hostname changed! P
 ocathost relay-bin.000002' (relay log pos 537),
 mentioned in relay log info in the index file ' /server246-relay-bin.index' during relay log initialization.
 the ldster imio structure for chdnnet its record stitt be present in mysql slave_mas ter _inio taote, consider dele
 ver replication info repositories.
 Slave failed to initialize relay log info structure from the repository, Error_code: 1872
 ed to initialize relay log info structure from the repository
 Is for channel ''d
 ole to
 d/initialized properly. Check for additional messages above. You will not be able to start replication on those chann
 MySQL Community Server (GPL)
 g pas
 initialize relay tog info structure from the repository Error_code: 1872
2018-04-
 Previous state master_host='192.168.1.237', master_port= 3306, master_log_file='mysql-bin000002', master_ Lo
2018-04-15T07:06
 ture from thmaster_bind="'.
 ster port= 3306, master_log_file=', master_log pos= repository, Er
2018-04-15T07:19
 initialize relay Log info structure from the repository, Error code: 1872fi1.. master
 revious state master_host='192.168.1.237', master_port= 3306, master_log_file='', master_log_pos= 4, master
 bind=''. Nev
2018-04-15T07:19:18.27
 master_log_pos= 4, master_bind''
 in the master info repository is not secure and is therefore not recommended Please consider using t
 he USER and PASSWORD co
2018-04-15T07:19:18.27
2018-04-15T07:19:18.274211Z5Note
 configuration does not guarantee that the relay log info will be consistent, Error codesition: A
 ng replication in log 'FIRST' at position 0, relay
0000
2018-04-15T07:19:18.378178Z4ote
root@192.168.1.237:3306', replication started
 in 1og 'FIRST atepsitionog. csdn. net/weixin_37998647

3.报错信息分析
找不到./server246-relay-bin.index文件，由于在使用的是冷备份文件恢复的实例，在mysql库中的slave_relay_log_info表中依然保留之前relay_log的信息，所以导致启动slave报错。

4.问题处理：通过slave reset删除记录

1、删除slave_master_info ，slave_relay_log_info两个表中数据；
2、删除所有relay log文件，并重新创建新的relay log文件；
3、不会改变gtid_executed 或者 gtid_purged的值；
mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)


mysql> change master to ......


mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

5.或者使用以下方法，也可以尝试。

mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
mysql>  reset slave;
Query OK, 0 rows affected (0.04 sec)


mysql> start slave IO_THREAD;
Query OK, 0 rows affected (0.07 sec)


mysql> stop slave IO_THREAD;
Query OK, 0 rows affected (0.01 sec)


mysql> reset slave;
Query OK, 0 rows affected (0.10 sec)


mysql> start slave;
Query OK, 0 rows affected (0.20 sec)


mysql> show slave status\G

本文作者： Leoheng
本文链接： http://www.leoheng.com/2021/04/23/MySQL主从复制报错、从节点无法同步数据/
版权声明： Design by leoheng