This article covers how to take Backups and restore a MySQL database on Red Hat Enterprise Linux 8.
Advantage of logical backup:
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 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.
The mysqldump client is a backup utility, which can 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.
Alternatively, mysqldump can also generate files in other formats, including CSV or other delimited text formats, and XML.
Advantages of Physical backup:
1. Output is more compact.
2. Backup is smaller in size.
3. Backup and restore are faster.
4. Backup includes log and configuration files.
You can use one of the following MariaDB backup approaches to back up data from a MariaDB database:
1. Logical backup with mysqldump
2. Physical online backup using the Mariabackup tool
3. File system backup
4. Replication as a backup solution
To Backing up an entire database with mysqldump:
Execute the command:
# mysqldump [options] db_name > backup-file.sql
Using mysqldump to back up a set of tables from one database:
To back up 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 …]
To restore the data while keeping the original backup files, use the following procedure:
1. Run the mariabackup command with the --copy-back option:
$ mariabackup --copy-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.
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