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
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!
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:
mysqldump, you’ll need a few things handy:
- The name of the database you’re backing up
- A database user with access to the that database
- 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.
-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.
-p flag tells
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.
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 [email protected]_COLLATION_CONNECTION */; /*!40111 SET [email protected]_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
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:
mysqldumpto connect using the URL your.dbserver.com. You can also use IP addresses.
mysqldumpto 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:
- The first command is
- 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
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.
mysql can be used with any .sql file – not just those you generate with
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 [email protected]_CHARACTER_SET_CLIENT */; /*!40101 SET [email protected]_CHARACTER_SET_RESULTS */; /*!40101 SET [email protected]_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:
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:
- Install the WP DB Migrate plugin using the built-in WordPress plugin search & installation interface. Make sure to activate after installation!
- Go to Tools → WP DB Migrate on your dashboard.
- 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:
- 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:
- Pay a monthly (or yearly) fee.
- Provide the service with access to your site. For some, you install a plugin. For others, you provide login credentials to your host.
- 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:
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.
Great post, another noteworthy (FREE) plugin that I have used for backup of not just the DB, but of site files as well, is Duplicator. It makes it VERY easy to move a site from your local to staging, or for general backup purposes. You get an installer.php file and a zip, which you can upload, run the installer, enter the new URL info (or not, since the plugin auto-detects with pretty much 100% accuracy) and BAM your site is auto-magically installed on the new host in seconds.
I know it sounds like an infomercial, but I am a legitimate developer and not just pimping a product. I just couldn’t help mention it, because I have evaluated lots of these plugins and this one is by far the best.
Duplicator is awesome. Easiest way to backup/migrate a site.
If you can write something similar that hooks into grunt/gulp I’ve got $$$ for you.
…or just host your WordPress on Google App Engine and use Google Cloud SQL that automatically takes backups of all your databases once a day :)
And be spied on by Google even more. No thanks.
There’s a certain charm to having control of your own websites, to say the least.
@Martijn If you think Google is the only one with an eye on your information, you are confused.
Nice write-up. Only thing I would add is instructions for setting up a cron job or equivalent to schedule regular backups.
Most website hosting companies do not allow this if on a sharing host? Though if you have your own website stored on your own server then I guess your good to go .. is there another solution for the shared hosting people?
After a year of torture with Drupal, I’ve been getting into WordPress more. One note about the phpMyAdmin Export… I’m not sure what the “Quick” method actually chooses, but per various training (books, lynda.com etc) a safer way is to choose “Custom” and check “Add drop tables…” and UNCHECK “Add create procedure…” Again, maybe that is what is set for the “Quick” export but it’s good to know.
Brian Krogsgard mentioned in a tweet that this is worth checking out, in regards to command line database stuff: http://wp-cli.org/commands/db/
Another thing to consider here is that these options don’t need to be mutually exclusive. I actually use all of them. Here on CSS-Tricks, I use VaultPress for the realtime backup and security monitoring. I get server snapshots through Media Temple. I use the command line to save backups and store them locally once in a while (usually so my dev environment is up to date). And on other sites, I use WP DB Migrate Pro.
Sorry to be redundant, but here’s one more vote for Duplicator. It’s great for both the client AND the developer.
I tought phpmyadmin is simple, but can we create crontjob for every day backup schudule in our web ??
in local server we can create on sqlyog..
maybe somebody can help??
I am using the Online Backup plugin. Choose the backup tab, click the “local” radio button, and start the backup. Backs up the files and the database, puts a copy on your host, you can download a copy with another click.
Also recommend “Anti-malware”, by ELI. This plugin fixed extensive back-door PHP on a site I worked on this fall (PHP that came in through the Mailpoet plugin over the summer), allowing the site to pass Google’s malware tests again.
WP_CLI works really great for me. It’s the easiest and fastest way.
I don’t see why mysqldump is “kinda hard”. It’s dead-simple. It’s a single command that you can store in a script file and have your database backed up to your local computer, literally at the tip of a doubleclick.
If I recall, you can even make mysqldump work through a SSH pipe, which I hope everyone needs in some form or the other.
And a script that does the reverse is equally simple.
Difficulty depends on the audience.
Many people reading this post are probably not familiar or comfortable with the command line, let alone setting up SSH.
It’s easy once you are used to it and have everything set up. If you’ve never done it before, it can be tough. Like anything, really!
My “simple” backup solution uses a Laravel backup manager package to make a daily backup locally and to Dropbox. Because it’s an artisan command, it’s simple to schedule using Dispatcher. Simple.
Simple for me, that is. It’s only simple because I already had most of the knowledge and setup required. If I’d tried it a year ago, it would have been impossible for me.
It’s easy if you’re comfortable on the command line, but not all front-end developers are. Which is why it’s “kinda” – it’s a good baby step into Shell land :)
Use HTTRACKER to take website backup
This post is about backing up the websites database, while HTTracker is more suited towards spidering website content as it is presented to the viewer (a rather unrelated task).
It’s important not to get these two things confused :)
I recommend wp-cli http://wp-cli.org/, because is very powerful and once you get used to it, every operation is very fast. Great for people who like working in terminals.
As a newbie, this tutorial was really usefull to me. Thank and keep up the good work :)
I use BackWPUp (https://wordpress.org/plugins/backwpup/). I can automate db and file backups to a backup folder, an external FTP and other options.
I’ve started using BackWPUp on all my sites to backup databases and files to multiple destinations (dropbox, server, etc.) on a regular schedule. Really easy to set up. Though I did read somewhere recently that some web host bans this plugin for some reason… Anyone know why?
Do you ever have problems with UTF-8 characters when either doing a mysqldump or using Migrate DB Pro? I always seem to have troubles re-importing a backup taken via these methods as it doesn’t correctly translate characters like a back tick or a ‘fancy’ apostrophe. If I export using SequelPro (btw you may want to add that as a backup method too), I can always re-import it no problem without issue.
FYI, databases are all set to UTF8, tables are UTF8, and the wp_config file is set to access the db as UTF8.
On another note, with large tables in phpMyAdmin export, select ‘Custom export method’ on the export and set the ‘Maximal length of created query’ to something much much higher. This will avoid any issues of tables being too big.
If you want an alternative solution for backup your database, servers, virtual machine and workstation then try CloudBacko Pro. It is the best solution for backup. It majorly supports windows, linux, Mac OS etc. You can check this software here http://www.cloudbacko.com
It is worth to mention that Sequel Pro also allows for connection to remote database through SSH.
Just choose the SSH tab when creating a new connection and enter all the information to connect to the server, plus the information for the database.
You can use various WordPress plugins that take auto backup themes ,plugins & database.
Updraftplus is one the greatest of them.
Just a friendly heads up of a problem I encounter all the time on client sites…a lot of these solutions do not work well at all for a LARGE wordpress site! You can increase memory and timeout for phpMyAdmin to run larger backups and for longer but if you have 1,000mb of upload files to backup and a 400mb database…then all the remote services are crap too. I personally had to use on the server something like Rsync which can be configured to upload only changed files since the last backup!
Nice article. This article is going to help those people who want to add some kind of motion in their blog.
Thanks for share !
Great article – you guys have convinced me to use Duplicator.
Being free, it’s also a no-brainer.
Backups in wordpress sib very important and article helps a lot to know how to manage ourselves.
You don’t necessarily need command line support for using mysqldump.
You can also run mysqldump from your local machine and have it connect directly to your remote mysql database (use the –ssl switch). You do have to allow access for your local IP address (per database) in the control panel at your webhost though. Having a fixed IP address from your provider is a must for this.
Since my local machine runs 24h a day I have automated this and it makes backups of all my remote/live databases daily. Works great with simple shared webhosting where you don’t have access to the command line.
Backing up WordPress with command line is slightly difficult for those who don’t know much about command line. But using phpmyadmin adn plugins is easiest and best way. Yes, But the export timed out issues creates issues 9 times out of 10 because most of web host has the limit for time out, So be careful about that before taking backups.
Great article! Been looking for a local program like Sequel Pro to manage my databases.
Also CodeGuard is awesome! It has saved my ass many times.
Reader “x mix” wrote in: