How to Backup/Export your Database

There are multiple ways to export a database. In this article we will use phpMyAdmin and shell commands. If you are not familiar with the terminal and shell commands we recommend you stick to phpMyAdmin.

If you wonder how to import databases, please check out this article.

Exporting Databases with phpMyAdmin

To export your database through phpMyAdmin, you first have to login to phpMyAdmin. Before exporting your database, make sure you are on the correct database, and not information_schema. When you have clicked on the database you want to export you can easily go to the export tab and click finish. This will save the database dump locally on your computer as a .sql file.

Exporting Databases with mysql

For exporting your database through terminal, you need to access the server through SSH. Once you have accessed the server you insert your information into this command:

mysqldump --single-transaction -u USERNAME -p DATABASENAME > ~/private/FILENAME.sql

You can find your database’s user and database name in your CMS’ config file. Otherwise you’ll fin the database name under the database section of your Bolt, where you also can reset the database password. We also want to export the .sql file to somewhere (like ~/private) else than your document root otherwise others can see your database via the web. If you want to go a step further to gzip your sql file you can run this command:

mysqldump --single-transaction -u USERNAME -p DATABASENAME | gzip > ~/private/FILENAME.sql.gz

To extract your gzipped sql file you can run this command: gunzip FILENAME.sql.gz

After filling in your database credentials, you are asked to enter the database password. After filling in the correct password, the server will process the information and create a database dump in your current directory. 

Exporting Databases with WP-CLI

If you have a WordPress site you can use the WP-CLI command line extension. WP-CLI is quite a bit easier than using mysql because it will fetch the database credentials for you. Simply run wp db export in your main WordPress directory and it will do the rest for you. Be sure to add the --path= parameter if you run the command outside that directory.

To download the .sql file from the server you can use either rsync or scp from your local machine, one or both usually comes preinstalled with most operating systems. If we use the same credentials as in the above examples it will look something like this

scp [email protected]:private/db_dump.sql /path/to/local/dir

An easier method to download files is with a FTP program like FileZilla.

And as always, should you have any additional questions please don’t hesitate to contact our support chat at!