How to backup MySQL & MariaDB database using mysqldump

Backing up your website database before major changes or updates is important step, and there are many different ways to backup a database using GUI or CLI. In this article you will learn how to use a utility called mysqldump to backup a MySQL or MariaDB database. The same instructions work for both database engines.

First, we need to identify the database we are backing up. Login to mysql to show all current databases using the following commands:

mysql -u root -p

show databases;

You should get an output similar to this:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bitnami_wordpress |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)

The database of interest is called bitnami_wordpress in my case, so I just need to enter the following mysqldump command to take a snapshot of it:

mysqldump -u root -p bitnami_wordpress > backup.sql

Finally you can download the backup.sql output file using scp or an SFTP client like FileZilla.

To restore the database backup file:

1. Upload the backup.sql to the new server.
2. Navigate to the directory where your uploaded backup.sql.
3. Use the following mysql command to import the backup file into a database named bitnami_wordpress:

mysql -u root -p -D bitnami_wordpress < mysqldump7-7-23.sql

If the new database already has data, you can wipe it first, then re-create it, and finish by using mysql command for importing the backup to a clean empty database:

mysql -u root -p
mysql> drop database bitnami_wordpress;
mysql> create database bitnami_wordpress;
mysql -u root -p -D bitnami_wordpress < mysqldump7-7-23.sql