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
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.sqlTo dump multiple databases at once, run:
# mysqldump [options] --databases db_name1 [db_name2 ...] > backup-file.sqlTo dump all databases, run:
# mysqldump [options] --all-databases > backup-file.sqlTo load one or more dumped full databases back into a server, run:
# mysql < backup-file.sqlTo load a database to a remote MySQL server, run:
# mysql --host=remote_host < backup-file.sqlTo dump a subset of tables from one database, add a list of the chosen tables at the end of the
mysqldumpcommand:# mysqldump [options] db_name [tbl_name ...] > backup-file.sqlTo load a subset of tables dumped from one database, run:
# mysql db_name < backup-file.sqlNoteThe 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.
Procédure
Stop the
mysqldservice:# systemctl stop mysqld.serviceCopy the data files to the required location:
# cp -r /var/lib/mysql /backup-locationOptionally, copy the configuration files to the required location:
# cp -r /etc/my.cnf /etc/my.cnf.d /backup-location/configurationOptionally, copy the log files to the required location:
# cp /var/log/mysql/* /backup-location/logsStart the
mysqldservice:# systemctl start mysqld.serviceWhen loading the backed up data from the backup location to the
/var/lib/mysqldirectory, ensure thatmysql:mysqlis 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
