Hello. I’m using xtrabackup 8.0.22-15 with MySQL Server 8.0.22 to take full backups from a replica with options --slave-info
and --safe-slave-backup
.
I’m using binary log file position based replication with MIXED binlog-format
Using default relay-log
and relay-log-index
settings. (uses hostname).
Replication fails to start with this error after restoring the backup for a new replica:
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
The corresponding errors from the mysql error log:
[ERROR] [MY-010544] [Repl] Failed to open the relay log ‘./hostname-relay-bin.000002’ (relay_log_pos 224567).
[ERROR] [MY-011059] [Repl] Could not find target log file mentioned in relay log info in the index file ‘./hostname-relay-bin.index’ during relay log initialization.
[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.
Table mysql.slave_relay_log_info
refers to hostname-relay-bin.000002
but this file isn’t present because xtrabackup isn’t backing it up (and not restoring it).
I can resolve this by running RESET SLAVE ALL
and then setting up replication again using the MASTER_LOG_FILE
and MASTER_LOG_POS
settings from the xtrabackup_slave_info
file, but:
Could this result in some lost transactions? I’m considering a situation that there are some statements in the relay log when xtrabackup runs, and so the MASTER_LOG_POS is ahead of what the slave has committed. Since xtrabackup isn’t backing up the relay log and needs a RESET SLAVE ALL
to start replication after a restore, it seems that any such statements in the relay log might be lost.
Does xtrabackup ensure that the replica has fully performed all events in the relay log during backup? Option --safe-slave-backup
says that it temporary stops the slave SQL thread so perhaps this helps, and so is this option definitely needed to ensure that the replica has an empty relay log?
I restore the backup by:
- Stop MySQL
- Run xtrabackup with
--prepare
- Run xtrabackup with
--copy-back
- Start MySQL
- Run the
CHANGE MASTER
command perxtrabackup_slave_info
file:CHANGE MASTER TO MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=1234;
- Run
START SLAVE;
Example mysql.slave_relay_log_info
after the steps:
mysql> select * from mysql.slave_relay_log_info\G
Number_of_lines: 12
Relay_log_name: ./hostname-relay-bin.000002
Relay_log_pos: 224567
Master_log_name: binlog.000002
Master_log_pos: 1234
Sql_delay: 0
Number_of_workers: 0
Id: 1
Channel_name:
Privilege_checks_username: NULL
Privilege_checks_hostname: NULL
Require_row_format: 0
Require_table_primary_key_check: STREAM