3.4. Backing up MySQL data

download PDF

There are two main ways to back up data from a MySQL 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, MySQL versions or Database Management System (DBMS), which is not possible with physical backups.

Note that logical backup can be performed if the mysqld.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 mysqld.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 MySQL backup approaches to back up data from a MySQL database:

  • Logical backup with mysqldump
  • File system backup
  • Replication as a backup solution

3.4.1. Performing logical backup with mysqldump

The mysqldump 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 mysqldump typically consists of SQL statements to re-create the server table structure, populate it with data, or both. mysqldump can also generate files in other formats, including XML and delimited text formats, such as CSV.

To perform the mysqldump 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


  • To dump a single database, run:

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

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

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

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

    # mysql --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 mysqldump command:

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

    # mysql db_name < backup-file.sql

    The db_name database must exist at this point.

  • To see a list of the options that mysqldump supports, run:

    $ mysqldump --help

Ressources supplémentaires

3.4.2. Performing file system backup

To create a file system backup of MySQL data files, copy the content of the MySQL 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.


  1. Stop the mysqld service:

    # systemctl stop mysqld.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/mysql/* /backup-location/logs
  5. Start the mysqld service:

    # systemctl start mysqld.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

3.4.3. 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.

For instructions on how to replicate a MySQL database, see Replicating MySQL.


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.

Ressources supplémentaires

Red Hat logoGithubRedditYoutubeTwitter


Essayez, achetez et vendez


À 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.