Red Hat Summit Red Hat SummitSupportConsoleDéveloppeursCommencer un essaiContact

3.4. Backing up MySQL data

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
Copier lien

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

Procédure

  • To dump a single database, run: 

    # mysqldump [options] --databases db_name > backup-file.sql
    Copy to Clipboard Toggle word wrap

  • To dump multiple databases at once, run: 

    # mysqldump [options] --databases db_name1 [db_name2 ...] > backup-file.sql
    Copy to Clipboard Toggle word wrap

  • To dump all databases, run: 

    # mysqldump [options] --all-databases > backup-file.sql
    Copy to Clipboard Toggle word wrap

  • To load one or more dumped full databases back into a server, run: 

    # mysql < backup-file.sql
    Copy to Clipboard Toggle word wrap

  • To load a database to a remote MySQL server, run: 

    # mysql --host=remote_host < backup-file.sql
    Copy to Clipboard Toggle word wrap

  • 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
    Copy to Clipboard Toggle word wrap

  • To load a subset of tables dumped from one database, run: 

    # mysql db_name < backup-file.sql
    Copy to Clipboard Toggle word wrap
    Note

    The db_name database must exist at this point.

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

    $ mysqldump --help
    Copy to Clipboard Toggle word wrap

3.4.2. Performing file system backup
Copier lien

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.

Procédure

  1. Stop the mysqld service: 

    # systemctl stop mysqld.service
    Copy to Clipboard Toggle word wrap

  2. Copy the data files to the required location: 

    # cp -r /var/lib/mysql /backup-location
    Copy to Clipboard Toggle word wrap

  3. Optionally, copy the configuration files to the required location: 

    # cp -r /etc/my.cnf /etc/my.cnf.d /backup-location/configuration
    Copy to Clipboard Toggle word wrap

  4. Optionally, copy the log files to the required location: 

    # cp /var/log/mysql/* /backup-location/logs
    Copy to Clipboard Toggle word wrap

  5. Start the mysqld service: 

    # systemctl start mysqld.service
    Copy to Clipboard Toggle word wrap

  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
    Copy to Clipboard Toggle word wrap

3.4.3. Replication as a backup solution
Copier lien

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.

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.

Retour au début
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. Découvrez nos récentes mises à jour.

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 le Blog 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.

Theme

© 2025 Red Hat