How to Create Daily Backups of WordPress MySQL Database

Create daily backups of your WordPress blog MySQL database as it is the single most important data resource that is required to restore your blog should you need to urgently migrate to another host or restore your database from a crashed server.

WordPress codex suggests some excellent ways to back up database using phpMyAdmin, MySQL commands, and MySQL administrator. But are you willing to backup your site daily using phpMyAdmin? We are all lazy to do that everyday over the year. You need a database backup tool which will 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.

Daily WordPress MySQL Database BackUp

I recommend you use WP Database backup Plugin which is an amazing plugin which can perform a daily (even hourly!) backup of your entire database and store it on your server, download database to your computer or email it across to you. It backs up the core wordpress tables, though you can choose to include more custom tables as well.

How frequently to backup your database? – I prefer the daily back up as it keeps you current database copy ready, though very active wordpress blogs with hundreds of hourly comments may find hourly backups or twice daily backups  more useful (and they would be obviously having powerful hosting packages to care of the hourly backup server loads).

database backup schedule

Where to store your database? – I prefer to get our MySQL database emailed everyday over to our email. This is useful because then you have access to you most current database copy anywhere in the world, and should you need to restore database far from home, you can do it. The database is sent as a zipped copy and does not occupy much of your email space. Remember large attachments get blocked by email clients and you need to reduce your database size by deleting the Akismet blocked spam comments.

If you store it on a server, and are locked out then you have no access to database copies. Also note that daily server backups will consume huge server storage space over time, unless you manually keep deleting older copies).

If you download backups to you computer – its difficult to do daily, hard disks do crash, and again you might not have access to it far across the world, or your assistant might not be able to access it on password protected computers. But it is still preferred to keep copies of your database in at least 2 places, lest the other one is not accessible.

How we backup our database

We have used this plugin for daily backup of our WordPress MySQL database for a several years now, and get it sent to our email. When I needed to urgently move hosting, I was out of the city, and was happy to see the latest database copy in our email, which was eventually used to accurately restore our wordpress database on our new host. At that time we had no access to our server via FTP or the base desktop computer on which we stored weekly backups.

In addition to automatic email back ups, we now also back up our database using phpMyAdmin every week on our computer to keep a solid accurate copy of our database, should anything get corrupted with the plugin or email delivery. Also remember to test .sql file in WordPad, and see if the text loads up correctly, and is not garbled. Remember to unzip the file first if you got it in the zipped .gz format.

So keep daily database backups and you will never have  a sleepless night worrying if your site is wiped out the next day! Of course you need other backups like uploaded images etc. via FTP to restore your complete site, which will be discussed in a later article…

Share with friends

About the Author: P Chandra is editor of QOT, one of India's earliest tech bloggers since 2004. A tech enthusiast with expertise in coding, WordPress, web tools, SEO and DIY hacks.