The ability to restore databases when data failures occur is very important.
Data failures can be as a result of an accidental or malicious modification, deletion or corruption of data.
There are two main ways to back up data from a MariaDB database:
i. Logical backup
ii. Physical backup
Logical backup consists of the SQL statements necessary to restore the data. While Physical backup consists of copies of files and directories that store the content.
Similarly, When the backup is complete, you can restore the data from the backup by using the mariabackup command with one of the following options:
The --copy-back option allows you to keep the original backup files. While the --move-back option moves the backup files to the data directory, and removes the original backup files.
Here at LinuxAPT, as part of our Server Management Services, we regularly perform Database Backup and restore tasks for our Customers.
In this context, we shall look into how to backup an existing MySQL database on Red Hat Enterprise Linux (RHEL) 8 by using the mysqldump utility. Also, you will learn how to restore a MySQL database in order to recover from data failures.
You can use the mysqldump utility to backup your MySQL database.
1. To begin, run the command below to confirm that the mysql service is active (running.):
$ sudo systemctl status mysqld
Press q to return to the terminal prompt.
If you see a message which indicates that the mysql service is disabled or inactive (dead,) then run the next command to start the mysql service:
$ sudo systemctl start mysqld
2. Once the mysql service is active (running,) you may login:
$ sudo mysql -u root -p
Note: You may replace root with another mysql user as appropriate.
Here in this guide, we have created a sample database and table by running the following queries.
You may do the same:
mysql> CREATE DATABASE cars;
mysql> USE cars;
mysql> CREATE TABLE suvs (make varchar(25), model varchar(25), VIN varchar(17));
mysql> INSERT INTO suvs VALUES ('Toyota', 'Rav4', '12345678901234567'), ('Honda', 'Crv', '09876543211234567'), ('Nissan', 'Pathfinder', '25162435453647567');
mysql> SELECT * FROM suvs;
Run the next command to quit mysql:
The syntax of the mysqldump command is:
mysqldump -u username -p database_to_backup > backup_file_name.sql
i. username with an actual username that has appropriate permissions on the database.
ii. database_to_backup with the actual name of the database you intend to backup.
iii. backup_file_name with your desired name for the backup file.
Using the sample cars database as an example, the following command will backup the database to the database_backups folder in my home directory:
$ mysqldump -u root -p cars > /home/linuxapt/database_backups/cars_bk.sql
If no errors are displayed, then the operation completed successfully.
Next, you can quickly examine the content of the database backup file by running the command below. Remember to replace backup_file_name accordingly:
$ head backup_file_name.sql
The only thing that is more important than taking backups is ensuring that you can actually restore those backups in the event of any data failure.
Imagine that the sample cars database which we created earlier has just been maliciously corrupted. Luckily, we have a backup of the cars database and we can restore it.
We would need to login to mysql and create a new database for the restore operation.
Login to mysql as follows:
$ mysql -u root -p
Next, run the query below to create a new database:
mysql> CREATE DATABASE cars2;
Once the query executes successfully (OK), we may quit mysql:
The basic mysql syntax for restoring a mysql database is:
mysql -u username -p new_database_name < backup_file_name.sql
i. username with an actual username that has appropriate permissions on the database
ii. new_database_name with the name of the new database created for the restore operation
iii. backup_file_name with the name of the database backup file
For example, the following command restores the cars database from backup.
$ mysql -u root -p cars2 < /home/linuxapt/database_backups/cars_bk.sql
If no errors are displayed, then the restore operation completed successfully.
We could login to mysql and verify the database restore operation as follows:
$ mysql -u root -p
Once you are logged in, run the query below to show available databases:
mysql> SHOW DATABASES;
The next query selects the cars2 database:
mysql> USE cars2;
Display tables in the cars2 database with the query below:
mysql> SHOW TABLES;
Finally, select all records from the suvs table as follows:
mysql> SELECT * FROM suvs;
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