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.

AI-powered Backup Solution

Unmatched Backup Features from Future

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

MySQL or MariaDB database

Then type:

Show databases;

MySQL or MariaDB database

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

MySQL or MariaDB database

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

MySQL or MariaDB database

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

MySQL or MariaDB database

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

MySQL or MariaDB database

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.

Services ZNetLive offer:

Domain Name Registration

Best Shared Hosting

WordPress Hosting Plan

VPS Hosting Plans

Buy Dedicated Server

Nishant Jain

Nishant Jain, a Linux System Administrator, is a tech enthusiast. A trained engineer, he enjoys chess, cricket and loves to interact, socialize with people and explore new places.
Nishant Jain

Latest posts by Nishant Jain (see all)