Aston J

Ultimate MYSQL backup script – part one!

Posted on: July 27th, 2009 by AstonJ 12 Comments

There are lots of scripts out there that do a great job of backing up your MYSQL database – so why’s this one any different? And why does it deserve the ‘Ultimate’ label? Well, it

  • Is one of the few backup scripts that elegantly turns your site ‘off’ whilst carrying out a backup
  • Stores a copy of your backup on your server
  • Transfers another copy to a different server (offsite backup)
  • Keeps a month’s worth of backups
  • Sends you a nice report each day with detailed stats on how long each element took
  • Works with any site regardless of application, e.g. vBulletin, IPB, WordPress, Drupal, etc

The backup will take place every day, and simply overwrite the previous month’s backups, and as some months have 31 days, you’ll have that extra bit of contingency too. I’ve opted to create a guide on how to create this script rather than just give you the files and let you get on with it – it’ll help you understand what’s going on, and give you a warm fuzzy feeling inside!  (Not a hands-on type of person? Want someone to configure/set it all up for you? Scroll down to the end of this guide for more info!)

Part two will show you how to create a backup of your files too (e.g. image uploads) complementing this script perfectly, so stay tuned!

Be sure to make a backup of your database in your usual way before proceeding – and take heed of the disclaimer at the end of this page!

Host = the server your site is on
Dest = the destination server you are using to store your offsite backups.

1) Creating your SSH Keys

First of all, let’s create some SSH keys, these will allow you to dial in to your destination server. On your Host or Dest server (doesn’t matter which) login in to Cpanel’s WHM, then:

Click on: Manage SSH Keys > Generate a New Key

  • Enter a name
  • Leave the password blank
  • Key type, DSA
  • Key Size 1024
  • Click on: Generate Key

You can then click on SSH Manger to return. You should now see your key.

  • Under public keys: For the key you just made, click on: View/Download Key
  • Then copy the contents into a text file on your PC.
  • It should look something like this:

ssh-dss AAAAB3NzaC1kc3MAAACBAKZEDiV/wBBjVrgo4KxhsN9YUTSmjrLcC11zaoGO578kUjuFTcl+BJcZG4 S0WZ094MPPO4sQ1Fyk8HmaX/wPIPe3QIIf/2w0Q8HIQ5eyKusv4/XaeQkLHnkmajWPWfcPawTxKuBVyocQnA6nBCmoScYAGX0fh6sv h92LY4PLXHuNAAAAFQDOzi268KCzf+a66R71wz8di4tJsQAeAA IAoins1q8345WkKQfc2LB51NZdPDvmpbE9+JVA0j7X6UJwUZnG 2mEopKzygmYkLv86FSbtfQBQhydmD0rR+k2a2lAKFo91RyqZX2 Rtz8ws6Lh4tMVIzDFK/d4CkvNv1Zp0O9aFzuwSk0kQVPp5j+nBzHV5zzvotjXXZhjNrXO hXmAAAAIAN3vTvXC34ACXmv6eITglKZiYz8Rq5TuQin1GxRrnj WgjUV+A7MX3zUiD9Wbg3IyhMwMA0vlrburtRv4V+E0aL+0SPOI wbiwug+E0UFPIqgYDpuHFgH70QrMKWq0+w1vFzb6FfhxK72LEt BTPsOZVUUPL0TaoJXiqvnSL/iHfzoQ== my@hostname.com

Don’t worry, this is a dummy key hence me showing you it!

  • Now do the same for your key that’s listed under the Private Keys bit (copy it to your PC).
  • Then simply Return to SSH Manager, and delete both of those keys, public and private. You don’t have to as they need be activated before they can be used, but I like to delete anyway.

For those of you who’d rather generate your keys manually, you can run this command (or even in a Terminal window on your Mac) to generate your keys. Just remember to leave the password blank, and note the directories in which they are saved. (If you used cpanel to generate your keys just ignore this bit).


ssh-keygen -t rsa

2) Setting up your SSH Keys

Now that you have some SSH Keys you’ll want to set them up on your host and destination server. We’re going to place the private key on the server you are ‘dialling from’ and the public key onto the server we are ‘dialling to’.

On your Host Server, log-in to your cpanel account (for that site) and click on:

  • SSH/Shell Access > Manage SSH Keys > Import Key
  • Then give your key a name and paste the Private key you saved earlier into the box for the Private Key
  • Then click: Import

On your Dest Server, log-in to your cpanel account that you are going to use to store these backups (which doesn’t have to be attached to a ‘live’ domain btw) and click on:

  • SSH/Shell Access > Manage SSH Keys > Import Key
  • Then give your key a name and paste the Public key you saved earlier into the box for the Public Key
  • Then click: Import
  • Then go back to the manage SSH Key page, and click on Manage Authorization, and Authorise the key you just imported.

Just want to quickly mention that you can even put a restriction on your key so it can only be used for a single use (so for example, you could restrict it for use with this backup only). But I’ll leave that for another guide.

Right one final thing for the keys. Ssh telnet to your host server with your usual root account, then su to the user account for your site (ie the account that is going to ‘dialing to’ the destination server). Then run the following:


ssh -i /path/to/file.key root@server.ip

Where the path is where your ssh key is stored on the host (usually in: /home/accountname/.ssh) and where the root@server is your destination account.

A message will pop up about RSA key fingerprint, type in ‘yes’ and then enter, when it asks for a passphrase just hit enter again.

This will add the RSA fingerprint to the list of known hosts. For those not using Cpanel (so ignore this bit if you’ve already done the above) to import keys, they need to be copied in the ‘.ssh’ directory (which is usually just below the public_html folder) And you need to copy the contents of the key to the ‘authorized_keys’ file:


cat key_name_here.pub >>~/.ssh/authorized_keys
chmod 700 ~/.ssh/authorized_keys

3) Creating the files used to close/open your site

Here we’ll create the files that will close down and open the site – it will consist of three files:

  • htaccess-open
  • htaccess-closed
  • backsoon.php

Copy the contents of your usual .htacess file into a plain text file and call it htaccess-open, then copy the below into a new file, and call it htaccess-closed


RewriteEngine On
RewriteBase /

RewriteCond %{REQUEST_URI} !^/backsoon\.php$
RewriteRule ^(.*)$ /backsoon.php [L]

Next we want to create a page that will display to any site visitors when the site is down. It will also tell search engines the site is down for maintenance and to come back after an hour!>

Create a file called backsoon.php and paste the following into it (just replace ‘Site-name-here’ with the name of your site, and ‘we’ll be back shortly…’ with something more appropriate after you’ve done a few runs).

<?php
header('HTTP/1.1 503 Service Temporarily Unavailable',true,503);
header('Status: 503 Service Temporarily Unavailable');
header('Retry-After: 3600');
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en-GB">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<meta name="robots" content="noindex, nofollow, noarchive, noimageindex, noimageclick" />
<title>Temporarily Closed For Maintenance</title>
<style type="text/css">
#downnotice { width: 400px; margin: 50px auto 0 auto; padding: 10px; border: 4px solid #adadad; background: #efefef;}
#downnotice p { font: 12px/22px verdana,arial,sans-serif; color: #333; text-align: center; margin: 0; padding: 0; }
</style>
</head><body>
<div id="downnotice">
<p><strong>Site-name-here</strong> is down for maintenance.</p>
<p>We'll be back shortly...</p>
<p>Thank you for your patience!</p>
</div>
</body>
</html>

Now, upload backup.php to your public_html folder (make sure the permissions are 644) and the two htaccess files into a directory under the public_html one so they’re not accessible by the general public, and chmod them 744. For the purpose of this guide, call the directory you place them in ‘myscripts’.

4) Create a backup directory on the destination and host servers

This step is easy. Simply create a directory where you want your backups to be saved to, one on your host server and one on the destination.

You can either create these in public_html and then password protect the directory (so they are not accessible by the public unless they have your password) or, put the directory under public_html so it is not accessible via the web at all. The choice is yours! For the purpose of this guide we’ll assume you put it under public_html in: /home/backupuser/mybackups

5) Creating the Bash Script

This is the script we are going to use to tell your server what to do. Copy the contents of it into a plain text file and name it BackupScript.sh

#!/bin/bash
#BackupScript.sh
#
# Backup script for Mysql datbases.
# Supplied with no warranty implied or otherwise - use at your own risk.
#
# For latest build, bug fixes and where to get the info required for this script,
# please refer to the original guide: http://astonj.com/tech/ultimate-mysql-backup-script-part-one/
#
#----------------------------------------------------------#
# - Set the variables below to match your site's details - #
#----------------------------------------------------------#
# - See guide for help on where to get the required info - #
#----------------------------------------------------------#
###
##
#

#--- (1) DATABASE INFO ---#

# Database name (eg, apple_apple1)
DBNAME=replace-this

# Username (eg, apple_user1)
DBUSER=replace-this

# Password (eg, mypassword)
DBPASS=replace-this

#--- (2) HTACCESS FILE PATHS ---#

# Path to htaccess-closed (eg, "/home/apple/myscripts/htaccess-closed")
HTACCESSCLOSED="/replace-this"

# Path to htaccess-open (eg, "/home/apple/myscripts/htaccess-open")
HTACCESSOPEN="/replace-this"

#---- (3) OTHER INFO ----#

# Site name (eg, Apple.com)
SITENAME=replace-this

# Path to public_html (eg, /home/apple/public_html)
PATHTOPUBLIC="/replace-this"

# Backup Label (Brief label for backups, eg, appleDBbackup)
LABEL=replace-this

# Backup directory on host machine (eg, "/home/apple/public_html/mybackups")
BACKUPDIRHOST="/replace-this"

# Backup directory on destination machine (eg, "/home/random5/public_html/sitebackups")
BACKUPDIRDEST="/replace-this"

# Path to SSH Key (eg, "/home/apple/.ssh/mykey")
SSHKEYLOCATION="/replace-this"

# Destination server login url/IP (eg, random5@88.54.157.40) (or by domain, eg, random5@random5.com)
HOSTLOGIN=replace-this

#----------------------------------------------------------#
#----------------------------------------------------------#
#----------------------------------------------------------#
# ------- You shouldn't need to modify the below!! ------- #
#----------------------------------------------------------#
#----------------------------------------------------------#
#----------------------------------------------------------#
echo "Starting at `date '+%T'`"
STARTSCRIPTTIME=$(date '+%s')
echo "......"
echo "..."
echo "."
echo "Closing down $SITENAME"
echo "......"
echo "..."
echo "."
cat $HTACCESSCLOSED > $PATHTOPUBLIC/.htaccess
echo "Sleeping 10 seconds to let connections die"
echo "......"
echo "..."
echo "."
sleep 10s
echo "Starting MYSQL dump"
STARTDUMPTIME=$(date '+%s')
echo "......"
echo "..."
echo "."
mysqldump --opt $DBNAME -u $DBUSER -p$DBPASS > $BACKUPDIRHOST/$LABEL-$(date +%d).sql
sleep 3s
echo "MYSQL dump end. Starting file compression"
ENDDUMPTIME=$(date '+%s')
STARTCOMPTIME=$(date '+%s')
echo "......"
echo "..."
echo "."
bzip2 -f $BACKUPDIRHOST/$LABEL-$(date +%d).sql
echo "File compress end. Opening up $SITENAME "
ENDCOMPTIME=$(date '+%s')
cat $HTACCESSOPEN > $PATHTOPUBLIC/.htaccess
SITEDOWNTIME=$(date '+%s')
echo "......"
echo "..."
echo "."
echo "Starting SCP backup transfer to destination"
STARTSCPTIME=$(date '+%s')
echo "......"
echo "..."
echo "."
scp -i $SSHKEYLOCATION $BACKUPDIRHOST/$LABEL-$(date +%d).sql.bz2 $HOSTLOGIN:$BACKUPDIRDEST
echo "SCP transfer end"
ENDSCPTIME=$(date '+%s')
echo "......"
echo "..."
echo "."
echo "Ending at: `date '+%T'`"
echo "(If there are any errors reported please investigate)"
ENDSCRIPTTIME=$(date '+%s')

#---------------------------#
# - OK Lets do some math! - #
#---------------------------#
echo "."
echo "....."
echo ".............."
echo "Detailed Information"
echo ".........."
echo "......"
echo "."

#Time it took to do the dump
DUMPTIME=$(( $ENDDUMPTIME - $STARTDUMPTIME ))
hours=$((DUMPTIME / 3600))
seconds=$((DUMPTIME % 3600))
minutes=$((DUMPTIME / 60))
seconds=$((DUMPTIME % 60))
echo "Dump took: $hours hours $minutes minutes $seconds seconds"

#Time it took to compress the dump
COMPTIME=$(( $ENDCOMPTIME - $STARTCOMPTIME ))
hours=$((COMPTIME / 3600))
seconds=$((COMPTIME % 3600))
minutes=$((COMPTIME / 60))
seconds=$((COMPTIME % 60))
echo "Compression took: $hours hours $minutes minutes $seconds seconds"

#Time it took to transfer the file
SCPTIME=$(( $ENDSCPTIME - $STARTSCPTIME ))
hours=$((SCPTIME / 3600))
seconds=$((SCPTIME % 3600))
minutes=$((SCPTIME / 60))
seconds=$((SCPTIME % 60))
echo "Transfer to destination took: $hours hours $minutes minutes $seconds seconds"

#Site downtime
DOWNTIME=$(( $SITEDOWNTIME - $STARTSCRIPTTIME ))
hours=$((DOWNTIME / 3600))
seconds=$((DOWNTIME % 3600))
minutes=$((DOWNTIME / 60))
seconds=$((DOWNTIME % 60))
echo "Site was down for: $hours hours $minutes minutes $seconds seconds"

#Total script time
TOTAL=$(( $ENDSCRIPTTIME - $STARTSCRIPTTIME ))
hours=$((TOTAL / 3600))
seconds=$((TOTAL % 3600))
minutes=$((TOTAL / 60))
seconds=$((TOTAL % 60))
echo "Time it took to execute this whole script: $hours hours $minutes minutes $seconds seconds"

Be sure to fill in the required info in the:

  • Database info – You should be able to get this from the config file for your site, if not login to cpanel and get the info from there.
  • Htaccess file paths – Here you will put the file-paths of the two htaccess files (which we in step 3)
  • Other info – Getting this info should be fairly straight forward.

Once you’ve filled in your information, save the file and upload it to the same ‘myscripts’ directory where you put the htaccess-open and htaccess-closed files.

6) Settin up the cron job

This is another easy one. Login to the cpanel account for your site, click on ‘Cron jobs’ and then click on ‘Standard’.

Enter your email address where it says, Please enter an email address where the cron output will be sent

Where it says ‘command to run’ put the path to the bash script we created in step 5

/home/accountname/myscripts/BackupScript.sh

Replace yoursitename with the account name for your site.

Then select the following:

  • Minutes: 0
  • Hours: 3
  • Days: Every Day
  • Months: Every Month
  • Weekday: Every Week Day
  • Click on Save Crontab

Ok, that's about it! Your backup will run every day at 3am!

To recap

Host = the server your site is on
Dest = the server you are using for the offsite backups.

  • Create your SSH Keys in Cpanel
  • Import your SSH Keys so that the private key is on the server you are 'dialling from' (host) and the public key is on the server you are 'dialling to' (dest).
  • Authorise the public key that you placed on the destination server
  • Manually ssh to the destination server to accept RSA key fingerprint on the host
  • On the host server create a directory called 'myscripts'
  • On the host server create a directory called 'mybackups'
  • Then paste the contents of your usual .htaccess file into 'htaccess-open' and copy that along with htaccess-closed to the the 'myscripts directory' (host)
  • Copy the 'backsoon.php' to the public_html directory for your site (host)
  • Copy the BackupScript.sh to your 'myscripts' directory (host)
  • On the dest server, create a directory where you backups will be stored.
  • Create a cron job on your host server to run the BackupScript.sh at your desired time.
  • Good luck!

Although I've tried to hold your hand through the entire process, I appreciate some people may still be daunted or will not have the time to do it themselves. In that case I am available to configure the script for you - for further info just contact me with details about your site and the size of your database (if you know it).

Disclaimer & Credits

This guide is provided with no warranty or guarantees whatsoever - use at your own risk.
It is aimed at those who are running a dedicated server powered by Cpanel - if in doubt contact your host.
I would like to thank Eric (administrator @ cpanel.com) for his help and advice on creating this script. Cheers Eric!

Screenshots

backsoon

email

Tags: , , , , , , ,
  • http://tech6.com Vinayak

    Thanks for the write up.

    Two things:-

    1. The values in BackupScript.sh should be enclosed in single quotes. Like DBPASS=’mypass';

    Otherwise complex passwords that have special chars like ‘/ would throw an error.

    2. What changes need to be done if I’d like to store just last two days backups, instead of 30|31.

    Thanks

  • http://www.etoxified.com eToxified

    Hi Vinayak – you could remove the date from the mysqldump name in the script then simply rename the back-ups _before_ the dump, with something like:

    rm $LABEL.bzip2.4
    mv $LABEL.bzip2.3 $LABEL.bzip2.4
    mv $LABEL.bzip2.2 $LABEL.bzip2.3
    mv $LABEL.bzip2.1 $LABEL.bzip2.2
    mv $LABEL.bzip2 $LABEL.bzip2.1

    However I don’t recommend it because if your database has been creeping in corruption of some sort you may need to go back further than that.

  • http://www.websitepublisher.net Chris

    Some of my mysql databases are ridiculously large, dumps can take a little awhile, is it really so wise to turn off the site during the dump? That could be some significant downtime.

  • http://www.etoxified.com eToxified

    I guess only you can make that decision Chris. But here are the reasons I personally prefer the site to be locked down while a back up is taking place:

    – Users don’t get an unresponsive site. Mysql will lock tables when the dump is taking place.

    – Less pressure on your server/less chance of corruption. If you have a busy site and people are constantly trying to access the DB while you are making a dump, you increase the chances of something going wrong. (Some may argue I am cautious!)

    How big is your DB? How long does it normally take?

  • http://www.websitepublisher.net Chris

    I’ve never actually timed it, but I’ve noticed the process still running long after the cron fired it.

    The biggest is 1.8gb

  • http://www.etoxified.com eToxified

    My biggest (about 1GB in size – zero attachments in db) used to take ages too, but that was on an ageing server (AMD Barton with just 2GB ram!). On my new server the dump takes less than a minute! So it might be worth upgrading – if you need a dedicated server I can make a few recommendations.

    I would give this script a go and see what times it gives you – you’ll know exactly how long it’s taking then :)

  • Pingback: Solutions Pro()

  • Peter

    Unfortunately it wont let me generate a ssh key without entering a password in control panel. Any ideas? Thanks

  • http://www.etoxified.com eToxified

    If you’re using cpanel it has to be done from a root or reseller account. Normal accounts require a password. Failing that, you should be able to generate one on command line (do a google for ‘how to generate a ssh key’) although I did include the basics steps in the guide. Hope that helps!

  • Peter

    Thats for the quick response. I will try it on my root account through my vps.

  • Peter

    Not working out for me at all. I tried doing through my root account but still it asks for a password lol. It was worth the shot.

  • http://www.etoxified.com eToxified

    Peter, you missed out a step somewhere – the bit where you have to manually ssh once.