How to Back Up Your Drupal Database

You’re probably wondering what’s so important about the database. Well, this is where Drupal stores your site’s content, login information, and configuration. By having a backup of your database, you’re ensuring that you can set up development or testing copies of your website, recover from server problems, and move your entire site to different machine. 

The database is actually just one out of two aspects of your website. You also need to have a backup of your website files. Whatever method below you choose for backing up your database, make sure that you test the entire process of backing up and restoring your website in a development environment before relying on it and using it for your live site. 

Method #1: Tools from Your Hosting Provider 

If your Drupal site is hosted by a web hosting provider, then they may provide you with a “wizard”, backup services, or control panel option to back up your databases manually or automatically. Even if your database backups are done automatically, it’s a good idea to do your own in the even that your hosting provider is unable to recover it.

Method #2: MySQL GUI Tools

If you have root access to your MySQL database, then you can use MySQL Workbench, a freeware open-source utility for database backup, recovery, and administration. 

Method #3: Backup from the Command Line

Use the mysqldump program to back up your Drupal database from the command line. Enter the following command to create a backup of the tables in a particular database then save them to a file called “mybackup.sql”:

mysqldump –u [mysql username] –p [nameofdatabase] > mybackup.sql

Since you’ll have to store your backup file in a safe location outside the server, it would be helpful to compress it:

gzip mybackup.sql

Now, you can store mybackup.sql.tgz in another location using SCP or FTP. 

You can actually combine the three steps above and enter the following in the command line:

mysqldump –u [database_username] –p [nameofdatabase] |

gzip > mybackup.sql.gz 

On top of the three methods above, you may use contributed Drupal modules and commercial projects to assist you in backing up your database.