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:

a. --copy-back

b. --move-back

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.

How to Backup a MySQL Database ?

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.

How to Create a sample database and table ?

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:

mysql> quit

How to Export database with mysqldump ?

The syntax of the mysqldump command is:

mysqldump -u username -p database_to_backup > backup_file_name.sql

Replace:

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.

How to Verify database backup ?

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

How to Restore a MySQL Database ?

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.

How to Create a new database ?

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:

mysql> quit

How to Import database from backup ?

The basic mysql syntax for restoring a mysql database is:

mysql -u username -p new_database_name < backup_file_name.sql

Replace:

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.

How to Verify database restore ?

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;

[Need assistance in performing Backups, Restoring and Migrating your Website Database from one Server to another on Linux? We are available to help you. ]