1.6. Backing up and restoring MariaDB data with logical dumps

• Backing up a single database
• Backing up multiple databases
• Backing up all databases

Prerequisites

• The mariadb service is running.
• You have credentials with permissions to back up a database, for example, the root account.

Procedure

• Create a consistent and comprehensive logical backup of MariaDB databases:

  # mariadb-dump -u <username> -p --routines --events --triggers --single-transaction --result-file=backup.sql --databases <database_1> <database_2>

  where:

  -u <username>

  Sets the username the utility uses to connect to the database server.

  -p

  Prompts for the password.

  --routines

  Includes stored procedures and functions in the backup.

  --events

  Includes scheduled events in the backup.

  --triggers

  Includes triggers in the backup.

  --single-transaction

  Initiates a consistent snapshot of databases with a transactional storage engine, such as InnoDB. By using a single transaction, all read operations reflect the database state at the moment the dump begins.

  If you still use a non-transactional storage engine, such as MyISAM, use the --lock-tables option instead of --single-transaction to ensure a consistent backup.

  --result-file=<output_file>

  Defines in which file mariadb-dump stores the output.

  --databases <list_of_databases>

  Defines the databases to back up. Alternatively, to back up all databases at once, use the --all-databases option.

  重要

  A backup of a database contains only the data from that database. It does not include MariaDB user accounts or other server settings. MariaDB stores this essential security and system information in a separate mysql system database. Therefore, you must back up mysql as well if you need to preserve these settings.

Verification

• Restore the backup in a sandbox environment and ensure that the data is correct.

Prerequisites

• The mariadb service is running.
• You have credentials with permissions to restore data, for example, the root account.

Verification

• Connect to a MariaDB database and query data, for example:

  # mariadb -u root -p <database> -e "*SELECT * FROM <table>;"