Ultimate MYSQL backup script – part one!

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).

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

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!