mysql从库start slave无法启动问题处理

it2024-08-02  38

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
最新回复(0)