Easy Steps to Backup and Restore MariaDB Database on RHEL/CentOS 7/8

Table of Contents

Steps to Backup and Restore MariaDB Database on RHEL/CentOS 7/8

Also Read: 6 Simple Steps to Change/Reset MariaDB root Password on RHEL/CentOS 7/8

Step 1: Backup MariaDB Database using mysqldump tool

MariaDB comes with an inbuilt tool called mysqldump which can be used to take the dump of single database or multiple databases as per the requirements. Here we are going to use this tool to take the backup of our Sample database cyberithub which we will later restore in our destination MariaDB Server using mysql tool. To take the backup of cyberithub database you need to use mysqldump -u root -p cyberithub > cyberithub.sql command as shown below. This command will dump all the tables and its data of cyberithub database into cyberithub.sql file.

[root@localhost ~]# mysqldump -u root -p cyberithub > cyberithub.sql
Enter password:

-u : User Name

-p : Password

After taking the dump if you check the size of the backup using du -sh cyberithub.sql then it will show size around 20K as shown below.

[root@localhost ~]# du -sh cyberithub.sql
20K cyberithub.sql

NOTE:

Please note that here I am using root user to run all the below commands. You can use any user with sudo access to run all these commands. For more information Please check Step by Step: How to Add User to Sudoers to provide sudo access to the User.

Step 2: Create a Database to Restore the Data

Before restoring the data you need to first create a database in MariaDB as described below. First you need to login to MariaDB through root user using mysql -u root -p command and then run create database cyberithub query to create a database cyberithub as shown below. Then to verify the database creation you can run another query show databases and then exit the prompt by using exit query.

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database cyberithub;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cyberithub |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]>

Step 3: Restore MariaDB Database Using mysql tool

Once database is created, you can restore all the backup into that database by using mysql -u root -p cyberithub < cyberithub.sql command as shown below. Here we are restoring all the data of cyberithub.sql dump file into cyberithub database through root user.

[root@localhost ~]# mysql -u root -p cyberithub < cyberithub.sql
Enter password:

Step 4: Check Your Restored Data

If all goes well then data will be restored and available for use. You can again login to MariaDB using mysql -u root -p command and check all the tables in cyberithub database as shown below.

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cyberithub         |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use cyberithub;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [cyberithub]> show tables;
+-----------------------+
| Tables_in_cyberithub  |
+-----------------------+
| customerlist          |
| Phone                 |
| installlist           |
| Product               |
| Account               |
| Address               |
| users                 |
+-----------------------+
7 rows in set (0.00 sec)

MariaDB [cyberithub]>