Using BACKUP and RESTORE to Resolve Replication Issues with MariaDB Xpand


Fast Backup and Restore can also be used to resolve the following issues in replicated environments.


  • MariaDB Xpand 5.3

  • MariaDB Xpand 6.0

  • MariaDB Xpand 6.1

Scenario I : Xpand slave does not exist or needs to be reset with master.

  1. Take a fresh backup using BACKUP sql command on master. This will minimize the time it will take for the slave to catch up if older backups were to be used.

  2. Stop the slave if one already exists. If none exists as yet, see "Configure MariaDB Xpand as a Replication Slave" for steps to create a new Xpand slave and leave it in stop mode after successful creation.

  3. Restore backup taken in step 1 on slave using RESTORE command.

  4. Master's binlog position when backup initiates is stored under your_backup_location/metadata/binlogs file. This file contains relevant information in the format logfile.seq#:position, for example, foo1.002843:99744547.

  5. Reset the position recorded in the binlogs file by issuing the command:

sql> CHANGE MASTER TO MASTER_LOG_FILE = 'foo1.002843',
  MASTER_LOG_POS = 99744547, MASTER_HOST = 'foo1',
  MASTER_USER = 'root', MASTER_PORT = 3306;
  1. Start the slave.

Scenario II: Xpand Master needs to be restored, Xpand Slave looks good.

  1. Promote Xpand slave to act as master. Please refer to "Configure Replication Failover with MariaDB Xpand" for the necessary steps to promote a passive Xpand slave to an active Master mode.

  2. Follow steps outlined in Scenario I to restore and reset the old master which is now in passive slave mode.

  3. If necessary, repeat the steps to switch over the current passive slave to active master once both master and slave are fully caught up.

Scenario III: Xpand Master and Slave both need to be restored and reset.

  1. Stop the slave.

  2. Restore the master and slave using RESTORE command.

  3. Reset the slave to the current position of the master (i.e., from SHOW MASTER STATUS on the master), after the restore, before it begins taking writes from clients. Note that using this technique, you are losing incremental changes since the backup. An advanced method to capture the incremental changes since backup would be to replay the binlogs if they are still available, and change the serverid of the master, in order to have it reapply the events in the binlog. Please contact MariaDB Support for assistance with this advanced procedure.

  4. Start the slave.


If you receive the following timeout error during the backup process, "Socket timeout while waiting for server response: read_timer", the global variable ftp_read_timeout_secs can be increased to a higher value, up to 3000, to prevent this error. This global applies to backups and restores of both FTP and SFTP servers.