How to Backup and Restore MySQL or MariaDB Database on CentOS7?

MySQL and MariaDB are the most popular database systems that use the SQL language. MariaDB is just a drop-in substitution for MySQL with enhanced features and improved performance. Many applications on CentOS use MySQL or MariaDB to manage their information.

Automatic backups are important, but sometimes, you just want to take a quick backup before making some changes in your existing data. And no system or application is fully fail-proof, so any unexpected thing can happen at a time when you least expect it. So, for protecting your MySQL database, backup is a must.

How to backup MySQL database?

First , let’s begin by checking the database name by typing this query:

[root@<domain name> ~]# mysql -u root -p

Then type:

Show databases;

Now quit from MySQL.

Mysqldump command is used for taking the MySQL backup. The syntax of taking the backup is as follows:

mysqldump -u root -p db_name > /backup/db_name.sql

Now we need to take the backup of testdb.

For taking the backup of test DB, use the command given below:

[root@<domain name> ~]# mysqldump -u root -p testdb > /backup/testdb.sql

After the command is run, enter your MySQL root password.

Sometimes we need to take the entire database backup. At that time, you can use the command: mysqldump -u root -p –all-databases > /backup/all_db.sql

Type it in as given below:

[root@<domain name> ~]# mysqldump -u root -p –all-databases > /backup/all_db.sql

Some time we need to take a single table backup, at that time we can use the command given below:

Mysql -u root -p database name table name > databasename_tablename.sql

For example, if we need to take the backup of the author table in testdb database, then we need to type the following command:

[root@<domain name> backup] # mysqldump -u root -p testdb accounts >testdb_accounts.sql

That’s all about backups! 🙂

How to restore MySQL database?

In order to restore the database backup, we have to use the command: mysql -u root -p  db_name  < /backup/db_name.sql

Type it in as given below:

[root@<domain name> ~]# mysql -u root -p testdb < /backup/testdb.sql

Hope you find this tutorial useful while taking backup and restoring your database.

Let me know if you have any query regarding databases. I’ll be more than happy to help.