mysql从库同步数据失败,slave功能无法启用
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
查看错误日志提示如下:
[ERROR] [MY-010544] [Repl] Failed to open the relay log './e4917fb8ce7c-relay-bin.000014' (relay_log_pos 371).
2020-10-20T05:19:46.694608Z 0 [ERROR] [MY-011059] [Repl] Could not find target log file mentioned in relay log info in the index file '/usr/local/mysql/data/e4917fb8ce7c-relay-bin.index' during relay log initialization.
2020-10-20T05:19:46.695814Z 0 [ERROR] [MY-010426] [Repl] 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.
2020-10-20T05:19:46.696492Z 0 [ERROR] [MY-010529] [Repl] Failed to create or recover replication info repositories.
意思是找不到对应的relay-log文件,出现这个原因是因为主机名称发生了变化导致的,mysql默认的relaylog存储是以 hostname-relay-bin来存储的,如果使用了docker。那在重新启动新的容器会导致hostname发生变化而出现以上错误提示。这个错误在mysql文档中有提到(地址):
If a replica uses the default host-based relay log file names, changing a replica’s host name after replication has been set up can cause replication to fail with the errors Failed to open the relay log and Could not find target log during relay log initialization. This is a known issue (see Bug #2122). If you anticipate that a replica’s host name might change in the future (for example, if networking is set up on the replica such that its host name can be modified using DHCP), you can avoid this issue entirely by using the relay_log and relay_log_index system variables to specify relay log file names explicitly when you initially set up the replica. This causes the names to be independent of server host name changes.
解决办法:
1、获取上一次master-bin-log日志同步位置
mysql> SELECT * FROM `mysql`.`slave_master_info`\G;
*************************** 1. row ***************************
Number_of_lines: 31
Master_log_name: mysql-bin.000240
Master_log_pos: 156
Host: 172.18.0.4
User_name: slave1
User_password: slave1123456
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: 7f06a326-b845-11ea-85b9-0242ac110002
Retry_count: 86400
Ssl_crl:
Ssl_crlpath:
Enabled_auto_position: 0
Channel_name:
Tls_version:
Public_key_path:
Get_public_key: 0
Network_namespace:
Master_compression_algorithm: uncompressed
Master_zstd_compression_level: 3
Tls_ciphersuites: NULL
1 row in set (0.00 sec)
ERROR:
No query specified
2、重置slave并设置master信息
stop slave;
reset slave;
change master to master_host="172.18.0.4",master_user="slave1",master_password="slave1123456",master_log_file="mysql-bin.000240",master_log_pos=156;
start slave;
3、通过relay_log设置防止更换主机名后报错
my.cnf 中添加配置如下:
relay_log=/usr/local/mysql/log/relay-log/relay-bin