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
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:
- Upload the
backup.sqlto the new server.
- Navigate to the directory where your uploaded
- Use the following mysql command to import the backup file into a database named
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