Rechercher

2.6. Backing up MariaDB data

download PDF

There are two main ways to back up data from a MariaDB database in Red Hat Enterprise Linux 9:

  • Logical backup
  • Physical backup

Logical backup consists of the SQL statements necessary to restore the data. This type of backup exports information and records in plain text files.

The main advantage of logical backup over physical backup is portability and flexibility. The data can be restored on other hardware configurations, MariaDB versions or Database Management System (DBMS), which is not possible with physical backups.

Note that logical backup can be performed if the mariadb.service is running. Logical backup does not include log and configuration files.

Physical backup consists of copies of files and directories that store the content.

Physical backup has the following advantages compared to logical backup:

  • Output is more compact.
  • Backup is smaller in size.
  • Backup and restore are faster.
  • Backup includes log and configuration files.

Note that physical backup must be performed when the mariadb.service is not running or all tables in the database are locked to prevent changes during the backup.

You can use one of the following MariaDB backup approaches to back up data from a MariaDB database:

  • Logical backup with mariadb-dump
  • Physical online backup using the Mariabackup utility
  • File system backup
  • Replication as a backup solution

2.6.1. Performing logical backup with mariadb-dump

The mariadb-dump client is a backup utility, which can be used to dump a database or a collection of databases for the purpose of a backup or transfer to another database server. The output of mariadb-dump typically consists of SQL statements to re-create the server table structure, populate it with data, or both. mariadb-dump can also generate files in other formats, including XML and delimited text formats, such as CSV.

To perform the mariadb-dump backup, you can use one of the following options:

  • Back up one or more selected databases
  • Back up all databases
  • Back up a subset of tables from one database

Procédure

  • To dump a single database, run:

    # mariadb-dump [options] --databases db_name > backup-file.sql
  • To dump multiple databases at once, run:

    # mariadb-dump [options] --databases db_name1 [db_name2 ...] > backup-file.sql
  • To dump all databases, run:

    # mariadb-dump [options] --all-databases > backup-file.sql
  • To load one or more dumped full databases back into a server, run:

    # mariadb < backup-file.sql
  • To load a database to a remote MariaDB server, run:

    # mariadb --host=remote_host < backup-file.sql
  • To dump a subset of tables from one database, add a list of the chosen tables at the end of the mariadb-dump command:

    # mariadb-dump [options] db_name [tbl_name ...​] > backup-file.sql
  • To load a subset of tables dumped from one database, run:

    # mariadb db_name < backup-file.sql
    Note

    The db_name database must exist at this point.

  • To see a list of the options that mariadb-dump supports, run:

    $ mariadb-dump --help

Ressources supplémentaires

2.6.2. Performing physical online backup using the Mariabackup utility

Mariabackup is a utility based on the Percona XtraBackup technology, which enables performing physical online backups of InnoDB, Aria, and MyISAM tables. This utility is provided by the mariadb-backup package from the AppStream repository.

Mariabackup supports full backup capability for MariaDB server, which includes encrypted and compressed data.

Conditions préalables

  • The mariadb-backup package is installed on the system:

    # dnf install mariadb-backup
  • You must provide Mariabackup with credentials for the user under which the backup will be run. You can provide the credentials either on the command line or by a configuration file.
  • Users of Mariabackup must have the RELOAD, LOCK TABLES, and REPLICATION CLIENT privileges.

To create a backup of a database using Mariabackup, use the following procedure.

Procédure

  • To create a backup while providing credentials on the command line, run:

    $ mariabackup --backup --target-dir <backup_directory> --user <backup_user> --password <backup_passwd>

    The target-dir option defines the directory where the backup files are stored. If you want to perform a full backup, the target directory must be empty or not exist.

    The user and password options allow you to configure the user name and the password.

  • To create a backup with credentials set in a configuration file:

    1. Create a configuration file in the /etc/my.cnf.d/ directory, for example, /etc/my.cnf.d/mariabackup.cnf.
    2. Add the following lines into the [xtrabackup] or [mysqld] section of the new file:

      [xtrabackup]
      user=myuser
      password=mypassword
    3. Perform the backup:

      $ mariabackup --backup --target-dir <backup_directory>
Important

Mariabackup does not read options in the [mariadb] section of configuration files. If a non-default data directory is specified on a MariaDB server, you must specify this directory in the [xtrabackup] or [mysqld] sections of configuration files so that Mariabackup is able to find the data directory.

To specify a non-default data directory, include the following line in the [xtrabackup] or [mysqld] sections of MariaDB configuration files:

datadir=/var/mycustomdatadir

Ressources supplémentaires

2.6.3. Restoring data using the Mariabackup utility

When the backup is complete, you can restore the data from the backup by using the mariabackup command with one of the following options:

  • --copy-back allows you to keep the original backup files.
  • --move-back moves the backup files to the data directory and removes the original backup files.

To restore data using the Mariabackup utility, use the following procedure.

Conditions préalables

  • Verify that the mariadb service is not running:

    # systemctl stop mariadb.service
  • Verify that the data directory is empty.
  • Users of Mariabackup must have the RELOAD, LOCK TABLES, and REPLICATION CLIENT privileges.

Procédure

  1. Run the mariabackup command:

    • To restore data and keep the original backup files, use the --copy-back option:

      $ mariabackup --copy-back --target-dir=/var/mariadb/backup/
    • To restore data and remove the original backup files, use the --move-back option:

      $ mariabackup --move-back --target-dir=/var/mariadb/backup/
  2. Fix the file permissions.

    When restoring a database, Mariabackup preserves the file and directory privileges of the backup. However, Mariabackup writes the files to disk as the user and group restoring the database. After restoring a backup, you may need to adjust the owner of the data directory to match the user and group for the MariaDB server, typically mysql for both.

    For example, to recursively change ownership of the files to the mysql user and group:

    # chown -R mysql:mysql /var/lib/mysql/
  3. Start the mariadb service:

    # systemctl start mariadb.service

Ressources supplémentaires

2.6.4. Performing file system backup

To create a file system backup of MariaDB data files, copy the content of the MariaDB data directory to your backup location.

To back up also your current configuration or the log files, use the optional steps of the following procedure.

Procédure

  1. Stop the mariadb service:

    # systemctl stop mariadb.service
  2. Copy the data files to the required location:

    # cp -r /var/lib/mysql /backup-location
  3. Optionally, copy the configuration files to the required location:

    # cp -r /etc/my.cnf /etc/my.cnf.d /backup-location/configuration
  4. Optionally, copy the log files to the required location:

    # cp /var/log/mariadb/ /backup-location/logs*
  5. Start the mariadb service:

    # systemctl start mariadb.service
  6. When loading the backed up data from the backup location to the /var/lib/mysql directory, ensure that mysql:mysql is an owner of all data in /var/lib/mysql:

    # chown -R mysql:mysql /var/lib/mysql

2.6.5. Replication as a backup solution

Replication is an alternative backup solution for source servers. If a source server replicates to a replica server, backups can be run on the replica without any impact on the source. The source can still run while you shut down the replica and back the data up from the replica.

Avertissement

Replication itself is not a sufficient backup solution. Replication protects source servers against hardware failures, but it does not ensure protection against data loss. It is recommended that you use any other backup solution on the replica together with this method.

Red Hat logoGithubRedditYoutubeTwitter

Apprendre

Essayez, achetez et vendez

Communautés

À propos de la documentation Red Hat

Nous aidons les utilisateurs de Red Hat à innover et à atteindre leurs objectifs grâce à nos produits et services avec un contenu auquel ils peuvent faire confiance.

Rendre l’open source plus inclusif

Red Hat s'engage à remplacer le langage problématique dans notre code, notre documentation et nos propriétés Web. Pour plus de détails, consultez leBlog Red Hat.

À propos de Red Hat

Nous proposons des solutions renforcées qui facilitent le travail des entreprises sur plusieurs plates-formes et environnements, du centre de données central à la périphérie du réseau.

© 2024 Red Hat, Inc.