As I am now hosting about 8 different sites I was wanting to make restoring from backups a bit easier and less manual. The ultimate goal is to login to my second server and have it up and running with as few key-strokes as possible. Scripting was the answer. It took me several hours of fanangling with syntax in a scratchpad and digging through google to finally put this together. I am sharing my completed work here for anyone else that could possibly make use of it as a spring-board for automating their own restoration process. I just got a new/upgraded backup server so it seemed like a good time to straighten it all out again and streamline things even more.

This is somewhat of a continuation from my earlier posts on automating “off-system” database backups. You can read those posts here:
mysql-database-backup-shell-scripts-that-can-be-run-as-cron-jobs

Setting up automated database backups for your WordPress, Drupal, or Joomla website

Background:

I have two servers. One server is my primary server that hosts all of my live sites.

The second server is a backup server that I pull a copy of my web directory (with sub-directories for each site) and a compressed database dump of each database. (more…)

This tutorial assumes you have some experience with Linux administration although I try to hand-hold as much as possible. If you are on a shared hosting solution and don’t have root access, you can easily modify the shell script we will create later to target your user’s home directory on the server.

You also need to know the address of your SQL server. In my case, I am working on a VPS so the SQL server is local (so I just use localhost) but you might need to use a URL or IP where your SQL server is located.

Okay… digging in. Here is the gist’ of it…

1. Create a new user for your MySQL database that will be just for backups
2. Create some folders on your server to store your backups locally
3. Create a script to actually create the database backups, compress them, clean up backups older than 30 days
4. Test the script
5. Setup the script to run as a cron job.

Here we go!
(more…)