我测试环境有一套主从环境.都关机了小一个月.期间我修改了我的主机名.最近启动起来之后发现主从不能正常同步.且在start slave时抛出Slave failed to initialize relay log info structure from the repository
报错: Slave failed to initialize relay log info structure from the repository
1 首先查看自己的master info 和relay log info是表形式还是文件形式
mysql> show variables like '%reposi%';+---------------------------+-------+| Variable_name | Value |+---------------------------+-------+| master_info_repository | TABLE || relay_log_info_repository | FILE |+---------------------------+-------+从5.7之后推荐使用table 模式.我这里一个表形式一个文件形式.错误设置.
2 查看两个表中记录的数据是否缺失
如果是文件形式.就去自己的数据目录下查看relay_log.index文件和master_info文件如果是table形式.就去mysql库下查看slave_relay_log_info和slave_master_info我首先查看的slave_relay_log_info 但是因为我当时设置的文件模式.所以没看到数据然后查看mysql> select * from mysql.slave_master_info\G*************************** 1. row *************************** Number_of_lines: 31 Master_log_name: Master_log_pos: 4 Host: 10.2.4.178 User_name: repl User_password: 123 Port: 3306 Connect_retry: 60 Enabled_ssl: 0 Ssl_ca: Ssl_capath: Ssl_cert: Ssl_cipher: Ssl_key: Ssl_verify_server_cert: 0 Heartbeat: 30 Bind: Ignored_server_ids: 0 Uuid: ffe65c3f-e953-11eb-b4da-005056ad2592 Retry_count: 86400 Ssl_crl: Ssl_crlpath: Enabled_auto_position: 1 Channel_name: Tls_version: Public_key_path: Get_public_key: 0 Network_namespace: Master_compression_algorithm: uncompressedMaster_zstd_compression_level: 3 Tls_ciphersuites: NULL1 row in set (0.00 sec)从这里看到Master_log_name 已经丢失了处理到这里的时候我也没有去看自己的relay_log.index了.我此时已经怀疑到是因为我修改主机名的原因了
3 处理
mysql> reset slave;Query OK, 0 rows affected (0.02 sec)mysql> CHANGE MASTER TO -> MASTER_HOST='10.2.4.178', -> MASTER_USER='repl', -> MASTER_PASSWORD='123', -> MASTER_PORT=3306, -> master_auto_position=1;Query OK, 0 rows affected, 2 warnings (0.02 sec)mysql> start slave;Query OK, 0 rows affected (0.05 sec)mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.2.4.178 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000098 Read_Master_Log_Pos: 196 Relay_Log_File: 178-cs2-relay-bin.000002 Relay_Log_Pos: 371 Relay_Master_Log_File: mysql-bin.000098 电脑 Slave_IO_Running: Yes Slave_SQL_Running: Yes复现故障
修改主机名[root@178-cs2 ~]# hostnamectl set-hostname cs178[root@178-cs2 ~]# bash[root@cs178 ~]# /data/app/mysql/bin/mysqld_safe --defaults-file=/data/backups/my.cnf &[1] 30570[root@cs178 ~]# Logging to '/data/backups/cs178.err'.2021-08-26T00:23:17.084881Z mysqld_safe Starting mysqld daemon with databases from /data/backups查看主从状态:mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Master_Host: 10.55.2.152 Master_User: lzm Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000006 Read_Master_Log_Pos: 6140 Relay_Log_File: 178-cs2-relay-bin-152.000003电脑 Relay_Log_Pos: 5242 Relay_Master_Log_File: binlog.000006 Slave_IO_Running: No Slave_SQL_Running: Nomysql> start slave;ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository解决:mysql> reset slave all;Query OK, 0 rows affected (0.03 sec)mysql> CHANGE MASTER TO MASTER_HOST="10.55.2.152", MASTER_USER="lzm", \ -> MASTER_PASSWORD="123", MASTER_AUTO_POSITION=1 FOR CHANNEL "152";Query OK, 0 rows affected, 2 warnings (0.03 sec)mysql> CHANGE MASTER TO MASTER_HOST="10.2.4.178", MASTER_USER="repl", MASTER_PORT=3306,\ -> MASTER_PASSWORD="123", MASTER_AUTO_POSITION=1 FOR CHANNEL "178";Query OK, 0 rows affected, 2 warnings (0.02 sec)mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('lzm.%') FOR CHANNEL &#电脑34;152";Query OK, 0 rows affected (0.00 sec)mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('inc1.%') FOR CHANNEL "178";Query OK, 0 rows affected (0.00 sec)mysql> start slave for channel '152';Query OK, 0 rows affected (0.09 sec)mysql> start slave for channel '178';Query OK, 0 rows affected (0.03 sec)mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.55.2.152 Master_User: lzm Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000006 Read_Master_Log_Pos: 6140 Relay_Log_File: cs178-relay-bin-152.000002 Relay_Log_Pos: 409 Relay_Master_Log_File: binlog.000006 Slave_IO_Running: Yes Slave_SQL_Running: Yesreset slave到底做了什么
官网中有解释: https://dev.mysql.com/doc/refman/5.7/en/reset-slave.html
在执行reset slave之后
删除slave_master_info ，slave_relay_log_info两个表中数据；删除所有relay log文件，并重新创建新的relay log文件；如果设置了延迟复制.也会被清除.需要重新设置delay不会清除gtid相关的参数.如 gtid_executed or gtid_purged其实也就是重新搭建一次主从了.只不过不需要进行数据补偿.