Backing up your website database can be incredibly challenging and it’s not always clear why certain backups fail.
So, this article is going to help you to backup WordPress database:
- Even if… you’ve got no idea where to begin because you’ve never done WordPress database backup before;
- Even if… you’ve tried taking backups before and databases intimidated you;
- Even if… you’ve taken WordPress database backups before but they failed to restore your WordPress website for some reason;
One thing to keep in mind — the database alone is useless. To restore your WordPress site, you need to backup both your files and database. Most of the techniques in this article will be focused on WP database backups only. If you’d like a tutorial on how to backup your full WordPress site, read this article instead.
TL;DR: Use BlogVault to take a full backup of your website in 60 seconds or less. Backing up only your database isn’t useful as you can’t restore your website from just the database — you need the files as well.
How to backup WordPress database using a WordPress backup plugin
Using a backup plugin to take a backup of your WordPress site is by far the simplest solution. However, not all backup plugins are built the same. We’ll show you how to take a backup using two plugins that deal with backups in different ways.
If you’d like to check out a full list of the best WordPress backup plugins first, go ahead and pick one before you begin.
We recommend that you sign up with BlogVault for the most reliable backups your money can buy. Whether you have a simple portfolio website or a WooCommerce website with hundreds of products, BlogVault will backup WordPress database and files without slowing down your website like the other plugins.
Biggest advantages over the other solutions:
- The plugin works on autopilot: You don’t have to be present while the plugin backs up your WordPress site
- No timeouts and interrupted backups ever: You get 100% reliable backups for WordPress every time
- FREE staging site for test restores: You can test your backups before your restore your WordPress site
- 1-Click Restore: Never struggle with restoring your site ever again
- The History Page helps you label and find the right backup to restore from: You can even see granular changes in each backup version
What you get:
- Incremental backups
- Scheduled backups
- Automated backups
- Zero additional server load
- Inbuilt test and staging site option
- Easy migration to another hosting service
- Centralized dashboard to manage multiple tasks for multiple websites
- Complete backup to Google Drive and Dropbox accounts
- Customized backups
- Real-time backups for WooCommerce websites
- Uptime monitoring
All you have to do is install the BlogVault plugin on your website and login to your BlogVault dashboard. The WordPress backup plugin automatically syncs with your website and takes a full backup to an Amazon S3 server. After that, it will keep taking a backup of your website every 24 hours automatically:
If you are running a WooCommerce store, the plugin also offers real-time backups. WooCommerce websites store lots of critical information on their databases that change very frequently. For instance, WooCommerce sets up tables to manage orders, coupons, carts, product metadata, tax rates, shipping rates, and a lot more.
Every time something changes on your website, it automatically gets added to your latest backup. This way, you don’t have to take hundreds of backups every month.
BlogVault backs up your entire WordPress site with one click. If you’re interested in only to backup WordPress database, we recommend using the WP Database Backup plugin. It’s a free plugin. All you have to do is click on ‘Create a New Backup’:
Then click on ‘Download’.
We don’t recommend using the WP Database Backup plugin to backup large databases as there may be timeout and server overload issues.
Some other popular backup WordPress plugins include:
- Backup Buddy
Check out our full list of best WordPress backup plugins to choose the right one for you.
How to backup WordPress site database using cPanel/phpMyAdmin
Right off the bat, we’ll say that this is never a good option to take. Manual backups require some technical expertise and you should only go ahead with this idea if you’re very sure of what you’re doing.
That said, backing up WordPress database manually is a completely free method. It is however extremely risky and can crash your WordPress site. So, we don’t encourage it. But if you’d like to proceed with this method, follow these steps exactly:
Step #1: Log in to cPanel
Head over to https://yourwebsite.com/cpanel and log in with your username and password.
Step #2: Head to phpMyAdmin
Click on the ‘phpMyAdmin’ option under ‘Databases’:
You should see this on your screen:
Step #3: Export your database as gzip or zip file
Click on your WordPress database to select it. You will be able to see the list of WordPress database tables when it is selected. Click on the ‘Export’ button on top:
You will get a prompt to select the export method:
Keep the default ‘Quick’ method if you want to take a full database backup. If you want to choose which tables to export, you can click on ‘Custom’ and select the tables manually as well.
And that’s it!
A word of caution, though: a database backup from cPanel is incredibly difficult to use. The whole point behind to backup WordPress database is to be able to restore your WordPress site quickly and effectively—should the need arise. A manual backup guarantees neither. On top of that, you have no way of knowing if the backup even works or if it got corrupted during a timeout.
NOTE: Backing up WordPress database from managed web hosting
Now, with certain web hosts, you also get a backup as part of their plan.
For instance, GoDaddy offers a full backup every month. This is not a good solution either. For one thing, you’ll only get a limited number of backups each month. So, you may end up losing an entire month’s worth of work when you restore your WordPress site.
The upside is that you can restore your WordPress website from a managed hosting dashboard very quickly. But if the server crashes, then you’ll lose all your backups and your original website at the same time.
Another major downside is that this backup can actually be used by web hosts as leverage against you in case you forget to renew your hosting plan. For instance, if you forget to pay GoDaddy, you may have to pay £150 to restore your site.
All in all, it’s best not to rely on server backups by your web host. We recommend that you use BlogVault to backup your website instead.
How to do WordPress database backup using SSH
A backup of WordPress site database using phpMyAdmin fails when the database is too large. This is usually the case with WooCommerce websites that have loads of products and plugins.
More products = more content = a bigger database.
More plugins = more custom tables = a bigger database.
Of course, you can have a large database for regular websites as well. One interesting example is when certain backup plugins actually create a database of its backups and add it to your already bloated WordPress database.
In those instances, using SSH is a much better option. Keep in mind, though, this requires serious technical expertise and you’ll need some knowledge of SQL to even attempt this.
To get this done, you’ll need to know about the mysqldump command. The mysqldump command with default settings will generate a .sql file. This is just a list of SQL commands that you can run.
If you run each of the SQL commands listed in the .sql file generated by mysqldump, you’ll end up with an exact copy of your WordPress database.
NOTE: Before you begin, you’ll need the following bits of information:
- The name of the database you’re backing up
- A database username with access to that database
- The password for that user
With that info, you can run the mysqldump command from your command prompt with this line:
mysqldump -u my_db_user -p my_db_name > /path/to/save/backup_file.sql
How to do WordPress database backup using MySQL GUI tools
For advanced users who hate using a command prompt but are also looking for a manual approach, there are MySQL GUI tools that you can use. This is only going to make sense to database administrators who understand how to code in MySQL.
If that’s not you then just skip ahead.
If you’re looking for GUI tools, here’s a list of the most popular ones:
- MySQL Workbench
- EMS SQL Management Studio for MySQL
- Aqua Data Studio
- Navicat for MySQL
- Toad for MySQL
- Sequel Pro
Of course, if all this seems complicated and confusing to you, that’s a clear indication that you should use a plugin instead. We recommend that you backup WordPress database quickly with BlogVault.
How to schedule daily backups on WordPress
If you’ve ever run into server crashes and malicious cyberattacks, then you already know that a single backup once in a while is quite useless. You’re going to end up regretting that decision. You need a backup every time there’s a change on your WordPress site.
But who has the time, right?
It’s a whole cumbersome process with you having to take a backup, label it in a way that’s convenient for a quick restore, find a good place to store it, and then create redundant copies. With most WordPress websites, this is an impossible task if you’re updating your content daily.
So, the simplest thing to do is to use a plugin that does this for you on autopilot.
While most paid plugins will do this for you, there are always issues with storage, data corruption, security breaches, version history, and convenient restore options.
Another major inconvenience is that most plugins require a trigger for the backup to start.
That’s exactly where BlogVault shines. Once you install the plugin, BlogVault keeps taking backups of your entire website every day like clockwork without exception. If you have a massive website or a WooCommerce store, you can enable real-time backups as an added benefit.
What exactly does the database backup contain?
WordPress stores loads of critically important data in the website database. If you’ve ever used phpMyAdmin, then you already know that your website’s content is stored in the database. This includes all your blog posts, pages, custom post types, and comments.
These are all important as is.
But the WordPress database stores much more than content including and not limited to:
- Widgets and sidebar content
- User accounts
- Navigation menus
- Plugin settings
- Theme settings
- Layouts and templates for theme builders
- Cron schedules
That’s a lot of very important data that you never want to lose. And this is all just for any WordPress installation. For WooCommerce websites, you get many additional tables.
Check out our article on how to backup WooCommerce databases for more information on what a WooCommerce database actually contains.
Common problems with taking database backups
WordPress backup plugins often fail to create backups that work. This is especially true for large sites and e-commerce sites. The size of the backup in most cases is so large that it fails halfway through the process.
Don’t get us wrong — it’s still a better option than trying to manually backup WordPress database. But even the most popular WordPress backup plugin comes with its own share of problems:
- Website recovery: It’s incredibly difficult to restore a site from a .sql file. For one thing, the database alone isn’t sufficient. You need both files and databases to restore a website. But even with both backups, manual restore from a .sql file is a painful process.
- Data corruption: Unless you have a staging site handy, there’s no way to tell if the backup you took even works or not. So many backups fail to restore a website because the data was corrupted.
- Server load: Backing up a database is a severely resource-intensive task. Your server has to fetch, process, and compress several GB worth of data simultaneously. This can cause server timeouts in between backups that corrupt the data without you realizing it.
- Unreliable initialization: Like all plugins, WordPress backup plugins only get initiated when a user visits a site. WordPress decides what plugins it should load for a user to get served. As a result, in many instances, your auto-backups may never be triggered at all.
- Storage issues: While most backup plugins only offer a direct download of your backup, there are premium plugins that offer cloud storage on Drive or Dropbox. But this is as expensive as it is risky. Sooner or later, you will run out of free storage space. And what if the website that is now synced to your Dropbox or Google Drive account gets hacked?
- Configuration issues: This is a relatively minor issue in comparison to the rest on this list. Nevertheless, no one likes to use plugins that are incredibly complicated to set up or use. If your plugin has configuration settings that force you to Google everything about backups, then what’s even the point of getting a plugin?
- Poor documentation and support: The worst part for most cheap backup solutions is that you often get little to no product documentation and support. So, not only is your solution likely to fail, you won’t even be able to use what little it can offer.
It may surprise you that these are very common problems and they happen to everyone. The way out of this mess is to use a premium WordPress backup service like BlogVault. Unlike cheap plugins that mess up quite often, BlogVault gives you a centralized dashboard to manage and store your backups.
What this means is that the BlogVault dashboard was created especially to create a solution that doesn’t have any of these problems. You can genuinely install a plugin and the dashboard will do the rest for you on autopilot or with a few intuitive clicks.
And if you ever get stuck with absolutely anything, our customer-acclaimed support team will swoop in and help out in any way they can. Seriously, everyone who’s ever spoken to our support team has always loved the experience. That’s just how we’re built to serve you.
If you took our advice to heart and signed up for BlogVault, we thank you for your trust and attention. Now, it’s our turn to repay it with backups that allow you to sleep soundly at night.
And if you’re still on the fence about whether you should use BlogVault or not, you could always talk to our support team and talk to us about what’s bothering you.
We also recommend that you check out our article on exporting WordPress database. And if you liked this article, or found the manual process to backup WordPress database useful, then share it with more people.