How to Back Up Your WordPress Database

WordPress stores a ton of important stuff in the database. You probably know your actual content is in the database: blog posts, pages, custom post types, comments, etc.

But the database stores more than content. Much more. Just to name a few:

• Plugin settings
• Theme settings
• Widgets & sidebar content
• Layouts & templates (if you use a fancy drag-and-drop theme)
• Cron schedules
• User accounts
• Menus

Holy cow – you can’t lose that stuff! It doesn’t matter if your site is big, small, live or in development – backing up is for every WordPress site.

If you’re a WordPress designer or developer, think about how much care you take to save the PHP, HTML, or CSS code you write. You probably use version control. At the very least, you don’t rely on a single copy of your code – that’d be risky! So let’s take that same care with the much more important database.

WordPress database backups can be taken numerous ways, ranging from “highly-technical” to “supremely easy”. How (and how often) you take backups will vary depending on the importance of the site you’re working on.

Let’s walk through some of the ways you can back up your WordPress database.

How to Back Up With mysqldump

Using the command line for backups is a manual process, and can be a bit daunting if you’re not comfortable on the command line. But even if you’re afraid of the shell, backing up using mysqldump isn’t too awful hard. If you aren’t comfortable with it, skip to the next section on using phpMyAdmin for backups.

• Recommended for: Development & non-critical sites
• Difficulty: Sorta hard
• Cost: Free

mysqldump is an appropriately-named program for dumping a MySQL database. No surprises here.

With default settings, mysqldump will generate a .sql file, which is just a list of SQL commands – CREATE TABLEs, INSERTs, etc.

If you were to run each of the SQL commands in the .sql file generated by mysqldump, you’d end up with an exact copy of your WordPress database – which is what we’re trying to get!

To run mysqldump, you’ll need to access the command line for your server.

ssh [email protected]

Some software can help you connect to your server through SSH. Here’s the setup screen for that in Coda:

To run mysqldump, you’ll need a few things handy:

1. The name of the database you’re backing up
2. A database user with access to the that database
3. The password for that user

If you don’t know these by heart, you can reference the wp-config.php file for your WordPress site. The configuration values will look something like this:

/** The name of the database for WordPress */
define('DB_NAME', 'my_db_name');

/** MySQL database username */
define('DB_USER', 'my_db_user');

/** MySQL database password */
define('DB_PASSWORD', 'my_db_password');

With our database info in hand we can run the mysqldump command, which looks like this:

mysqldump -u my_db_user -p my_db_name > /path/to/save/backup_file.sql

If you’re not familiar with command line lingo, the “dash-followed-by-letters” are called “flags”. Flags give the command-line utility (in this case, mysqldump) answers it needs to run correctly.

The -u flag tells mysqldump to use my_db_user to back up our database. For our purposes, we just need to make sure that my_db_user is allowed to read the database we’re trying to back up.

The -p flag tells mysqldump that my_db_user has a password, and we’re going to provide that password. Note that it’s possible to enter the password directly on the command line like so:

mysqldump -u my_db_user -pmy_db_password my_db_name > /path/to/save/backup_file.sql

Beware: Entering the password this way is considered bad security practice because it makes the password visible to programs that don’t need to know about it. We’re showing you this use of the -p flag just for completeness, so you know how each of these bits and bobs works.

The > after my_db_name is called an “output redirection symbol”, which is just a fancy way of telling mysqldump to send the backup data to a specific file.

To understand output redirection better, let’s see what would happen if we didn’t add the > to the end of our command. This is what is printed to the screen after running the command without output redirection:

-- MySQL dump 10.13  Distrib 5.5.40, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: my_db_name
-- ------------------------------------------------------
-- Server version   5.5.40-0ubuntu0.14.04.1

/* ...snip a zillion SQL commands... */

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2014-12-31 20:41:58

Whoa! Our whole database backup was printed to the screen!

By using the > operator, we can tell mysqldump where to put our backup, so we can download and use it. In this case, we’re telling mysqldump to put our backup here in the file named /path/to/save/backup_file.sql.

If your database happens to live somewhere besides localhost (e.g. if you have a separate MySQL server hosting your database), you can use two additional flags to tell mysqldump where to look:

• -h your.dbserver.com tells mysqldump to connect using the URL your.dbserver.com. You can also use IP addresses.
• -P 1234 tells mysqldump to use port 1234 – obviously, switch this if your database server is using a different port.

After you run mysqldump, you can find your backup file in the location you specified (in our example, /path/to/save/backup_file.sql). The simplest way to grab it is using your favorite FTP client or by using secure copy from the (local) command line.

scp [email protected]:backup_file.sql .

How to Restore From a .sql File

Once you have a .sql file handy, you can restore your database using the mysql command-line utility.

Here’s the command you’ll use:

mysql -u my_db_user -p my_db_name < /path/to/save/backup_file.sql

You’ll notice this looks a lot like the mysqldump command we used to create our backup – with a couple of changes:

1. The first command is mysql instead of mysqldump.
2. The output redirection symbol (>) has been changed to an input redirection symbol (<). This tells our command to read the .sql file as input for the mysql utility.

All this does is replay the SQL commands written in our .sql file in order to recreate/restore the database to the state it was in when the backup was taken.

Note that mysql can be used with any .sql file – not just those you generate with mysqldump.

Locally, you can also use free software like Sequel Pro to work with your database, including running .sql.

How to Back Up With phpMyAdmin

phpMyAdmin is software that lets you view, edit, and (important for our purposes) export your MySQL database.

• Recommended for: Development & non-critical sites
• Difficulty: Not too bad
• Cost: Free

Many web hosts provide phpMyAdmin access as part of regular hosting plans. If you poke around your hosting account’s dashboard, you may find a link to a phpMyAdmin login screen that looks like this:

The username and password is typically the same combo you can find in your wp-config.php file (see above).

Once you’re logged in, you can select the database you want to back up on the left hand side.

Next, you select the Export tab.

By default, phpMyAdmin uses a “Quick” option which is perfect for most use cases. If you’re feeling adventurous, there are an awful lot of options you can tweak under the “Custom” section.

Click “Go” to start exporting and downloading your database.

When the download is finished, you’ll have a complete backup of your WordPress database.

Beware: If you have a large database or your phpMyAdmin is configured to limit the time allowed for a download, your backup file might be incomplete.

The easiest way to tell if your backup exported successfully is to open it up and scroll waaay to the bottom. The last few lines should have some SQL statements similar to this:

--
-- AUTO_INCREMENT for table `wp_users`
--
ALTER TABLE `wp_users`
  MODIFY `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

If you see something different, your backup may be incomplete. If your export timed out, you may see some error messages at the end of your backup file. Or your backup may just cut off at an arbitrary point.

If your backups are incomplete through phpMyAdmin, you may need to tweak your export settings using the “Custom” options or ask your host if they can increase the time limit for PHP requests.

If your host won’t help or you just don’t want to deal with PHP’s quirks, read on to learn about some different (and easier) ways to back up your WordPress database.

How to Restore With phpMyAdmin

It only makes sense that, if you can export a database with phpMyAdmin, you can import one as well. To restore from a database backup, you just need to navigate to the Import tab:

Upload your backup file (.sql or .sql.zip, doesn’t matter) using the uploader and click “Go” to restore to backup.

Beware: If you have a large database or your phpMyAdmin is configured to limit the time allowed for script execution, your restore may not complete. If you hit timeout problems, you might want to try tweaking the phpMyAdmin settings or using another method of restoring your database (such as the mysql command-line utility mentioned in the previous section).

How to Back Up by Web Host

In addition to providing access to phpMyAdmin, many hosts provide regular backup services for MySQL databases. The host will take “snapshots” of your database at specific intervals – sometimes weekly, daily, or hourly. Your host may provide web-based access to download the snapshots of your database.

• Recommended for: Any site
• Difficulty: Easy
• Cost: Varies. Some hosts are free, others have a monthly fee.

Availability varies from host to host, and from hosting plan to hosting plan. If you’re on a shared hosting plan that costs less than $10 per month, backups are probably not included.

The cost and process for setting up automated backups is different for every host. Here are more details for some of the more common WordPress hosts:

• Media Temple
• WP Engine
• DreamHost
• GoDaddy
• Bluehost

How to Back Up by Plugin

One of the simplest ways to back up your WordPress site is using a plugin.

• Recommended for: Any site
• Difficulty: Easy
• Cost: Varies, depends on the features you need. Free plugins can do basic backups. Paid plugins have convenient features.

There’s a cornucopia of backup plugins available, but one of particular interest to developers is WP DB Migrate, and it’s paid version WP DB Migrate Pro.

For simple backups, the free version is more than sufficient. Here’s how to grab a copy of your database using WP DB Migrate:

1. Install the WP DB Migrate plugin using the built-in WordPress plugin search & installation interface. Make sure to activate after installation!
2. Go to Tools → WP DB Migrate on your dashboard.
3. Select “Download as .zip”. If you’re simply looking to download a copy of the database for safe keeping, you can remove the find/replace fields:
   

   

4. Click “Migrate” and you’ll have a copy of your database saved to your computer.

If you’re a developer, it’s worth taking a look at the Pro version of WP DB Migrate. With Pro, you’re able to synchronize the databases of two WordPress sites.

For example: Say you’re working on a live site and you need a copy of the database to do development work. With WP Migrate DB Pro, you can simply synchronize the live version to your development site using the plugin’s interface. If you do a lot of WordPress work, it will change the way you develop.

Another notable backup plugin is BackupBuddy by iThemes. While WP DB Migrate has a lot of features that developers can use, BackupBuddy shines for “regular” WordPress users. BackupBuddy can do scheduled backups (much like your web host) and send the backups to a variety of places, such as email, FTP, DropBox, AWS, and others.

There are lots of backup plugins on the WordPress.org repo, and plenty of premium options in addition to BackupBuddy and WP DB Migrate Pro.

Using a Backup Service

Finally, let’s look at backing up your WordPress database using a backup service.

Using a backup service is the easiest way to keep backups of your site. Naturally, it’s also the most expensive.

• Recommended for: Live/production sites
• Difficulty: Easiest
• Cost: Monthly fee, from $5 to $29 per month.

Here’s how it works:

1. Pay a monthly (or yearly) fee.
2. Provide the service with access to your site. For some, you install a plugin. For others, you provide login credentials to your host.
3. Backups are automatically taken on a regular basis and saved to the cloud. Or the backup happens in real time in the case of VaultPress.

Here are some WordPress/MySQL backup services:

• VaultPress
• CodeGuard
• myRepono

Backup services require minimal setup, are constantly backing up your site, include error checking, and (in the case of VaultPress) do security monitoring for your site.

Go Forth and Back Up

Now you’re ready to back up your WordPress database. Have a backup method, plugin or service you think we overlooked? Add it in the comments.