Backup and Restore MariaDB Xpand using MariaDB Dump

Overview

MariaDB Dump and mysqldump can be used to backup and restore Xpand tables.

MariaDB Dump and mysqldump are useful for migrating data from MariaDB Enterprise Server or MySQL to MariaDB Xpand. However, these backup methods do not scale well for large production environments. For the best performance, large production environments should use Xpand's Fast Backup and Restore functionality. For additional information, see "Fast Backup and Restore with MariaDB Xpand".

Compatibility

  • MariaDB Xpand 5.3

  • MariaDB Xpand 6.0

  • MariaDB Xpand 6.1

Backup

To back up Xpand using mariadb-dump, run the following command:

$ mariadb-dump --user user --password \
      --socket /data/clustrix/mysql.sock \
      --single-transaction \
      --master-data=2 \
      --all-databases \
      > backup.sql

  • The --single-transaction flag ensures a consistent snapshot of the database by querying for all of the data in a single transaction and permits continued access to the Xpand while the dump is being created.

  • The --master-data=2 flag inserts a CHANGE MASTER TO statement in a comment near the top of the dump file, indicating the location in the binary log where a Replica Server must start to be consistent with the dump. If no binlog is being created, omit the --master-data flag.

Restore

To restore Xpand, use the MariaDB Client:

$ mariadb --user user --password \
      --socket /data/clustrix/mysql.sock \
      < backup.sql

The client connects to the Xpand service and executes each SQL statement in the backup file to restore the database and the Xpand tables.