How to Schedule Daily Backup of WordPress Database
Do a schedule backups of your WordPress MySQL database is the most important thing to do when running your own WordPress website, it is required to restore your blog, it needs to urgently migrate to another host or restore your database from a crashed server.
From official WordPress guidline – The WordPress Codex, you will find some excellent ways to back up database using phpMyAdmin, MySQL commands, and MySQL administrator. But are you willing to backup your site daily, weekly, monthly and everyday of the year by yourself? There are too many methods, plugins, tools which help you done this job by automatically back up your MySQL database for you and send it across to a safe storage space such that the latest backup is there whenever you need it, wherever you need to restore database from across the world.
There are several ways to backup MySQL data. In this article I will show you how to backup your databases using different methods, we will also learn how to achieve an automatic backup solution to make the process easier, including the backup of your database to a file, another server, and even a compressed gzip file and send it to your email.
Why WordPress regular backup is must
Your WordPress is self hosted and you have to care it yourself. There are many reasons to loss your WordPress database or any other data;
- A plugin may corrupt your database and sometime it may not be recoverable
- If your blog is hacked than you must need a backup to restore your website
- Sometime your hosting provider encounter a fatal server error and all your data may be loosed
- Or any other unknown error may occur anytime
Automatic backup MySQL database with CPanel
CPanel is most popular hosting control panel on Linux server (most popular too). If your hosting service support CPanel, you can create a cron job in your server and schedule it to backup your database everyday.
1. Login to your CPanel then looking for a Cron Job icon.
2. Setting the time and frequency to run backup command
3. Under the command field, copy and paste the following command:
mysqldump --opt -Q -u dbusername --password=dbpassword dbname | gzip > /path-to-store-the-backup-file/db_backup.sql.gz
- Replace dbusername with the database user
- Replace dbpassword with the database user password. In some server, you might need to put a pair of single quote
'dbpassword'around the dbpassword for it to work.
- Replace dbname with the database that you are backing up
- Replace the path-to-store-the-backup-file to the file path in your server where you want to save the backup
Tips: I recommend you choose the storage location of backup above the
/public_html/ to prevent unwanted eyes.
Now, you can test this cronjob first to make sure it works.
Use WordPress plugins to automatic backup your website
WordPress has a ton of plugins to help you backup your website include MySQL database and files. Obviously there are many more available and a whole host of cloud backup services, but I have kept this post as backup plugins that are tried, tested and come highly recommended by colleagues that are actually using the plugins.
The backup files can be used to save your whole installation including /wp-content/ and push them to an external Backup Service, if you don’t want to save the backups on the same server. With the single backup .zip file you are able to restore an installation.
- Database Backup (needs mysqli)
- WordPress XML Export
- Generate a file with installed plugins
- Optimize Database
- Check and repair Database
- File backup
- Backups in zip, tar, tar.gz, tar.bz2 format (needs gz, bz2, ZipArchive)
- Store backup to directory
- Store backup to FTP server (needs ftp)
- Store backup to S3 services (needs curl)
- Store backup to Microsoft Azure (Blob) (needs PHP 5.3.2, curl)
- Store backup to RackSpaceCloud (needs PHP 5.3.2, curl)
- Store backup to Dropbox (needs curl)
- Store backup to SugarSync (needs curl)
- Send logs and backups by email
- Multi-site support only as network admin
If you don’t have a backup schedule in place or you only have a partial backup I recommend you download and install BackWPup. I’ve ran this plugin for a long time ago until I found another way to sync all my website to local computer and online storage services. I guess it’s best backup plugin for WordPress, the free version is enough for personal using.
2. Hot Backup Manager
Hot Backup Manager is WordPress plugin, which allows to create database and/or files system backups and restore them. This plugin is created for owners of WordPress sites and will be handy in site maintainance.
Backups creation is performed according to schedule configuration and not required manual support. The plugin is developed with usage of WordPress core CRON implementation, it means that you don’t need access to your hosting panel (or whatever you use) to setup CRON jobs.
The plugin could notify owners about successful or unsuccessful backup creation by email. Additional option is ability to attach backup file to email.
After successful backup creation it is possible to send backup file to remote FTP host or to online storage provider like Dropbox or Amazon S3.
Each backup could be restored from the plugin. The restoring process is quite simple and could be started by clicking a restore button for certain backup.
Backup is a plugin that provides backup capabilities for WordPress. Backups are zip archives of created locally and uploaded to a folder of your choosing on Google Drive.
You are in total control of what gets backed up.
- Schedule automatic backups.
- Back up the database.
- Back up files and directories.
- Fine grained control over what gets backed up.
- Store backups locally and/or on Google Drive.
- Interrupted uploads to Google Drive automatically resume.
- Get email notifications when something goes wrong.
- The settings page interface uses standard WordPress elements to fit right in.
- Extensive contextual help included.
- Advanced options are provided to control the inner workings of the plugin.
Smart Backup is a complete WordPress solution for database backup and restore operations. You can create backups of your complete WordPress installation, files only, or database only. Backups can be restored with one click.
Options for the scheduler include timeouts (for tight PHP servers), backup types (files, .sql, complete) and notification emails for backup success or failure.
The backup manager allows for easy restoring by type, date or ZIP archive.
Allows you to optimize database, repair database, backup database, restore database, delete backup database , drop/empty tables and run selected queries. Supports automatic scheduling of backing up, optimizing and repairing of database.
This plugin is not as simple as some. Most people will head for the email daily back up option and that is not as straightforward as you would like it to be. It takes persistence to stick with this plugin but it is worth it.
I found this article from David Walsh’s blog, with a PHP script you can export MySQL database automatic to XML format. XML isn’t the easiest format to restore but it’s easy to read. Combinned with CronJob, it’s can schedule daily or as you want.
Backup MySQL with SSH
Some web hosting services supported SSH login like Digital Ocean. This is easy way to backup large MySQL database that doesn’t require PhpMyAdmin or PHP script.
Here are the steps.
- Enable SSH login inside your hosting control panel or customer panel.
- Using utility such as PuTTY or similar, log into your server via SSH. I’m using Terminal.
- Change to the directory where you want to save your backup files — e.g.,
mysqldumpcommand to export the database into a file on the server. For example:
mysqldump --add-drop-table -u db_username -p db_name > mybackup.sql
mybackup.sqlto your local computer or tranfer to online storage services
About the command:
- Omit the –add-drop-table argument if you’ll want to merge this backup with an existing database upon restore
db_usernamewith the name of your database user
db_name withthe name of your database
mybackup.sqlwith the name of your backup file
Automatic WordPress Database Backups to Email
After choosed best solution to schedule backup WordPress database, do you want it sends database files to specified email? All the WordPress plugins which I mentioned above has this feature, if you want to do it by hand, here for you:
Using this command:
mysqldump -e --user=username --password=password dbname | gzip | uuencode dbbackup_e.gz | mail firstname.lastname@example.org
Some Questions for Your
- Do you back-up your WordPress site(s)? If so, how often?
- Have you ever lost your WordPress site?
- Does your web host provide consistent, automatic and easily accessible back-ups? If so, what web host are you using?
So keep daily database backups and you will never have a sleepless night worrying if your site is wiped out the next day! If I have not mentioned a plugin or procedure that works well for you, would you be kind enough to share in the comments? Are you using any of the WordPress Backup plugins that are mentioned above on your blog?