MySql Backup and Email Attachment

The importance of backing up your MySQL database shouldn't need much discussion. There are many resources on the web to accomplish the backup using a cron job pretty quickly. But, I couldn't find a nice simple shell script that would also compress and email the attachment. This script below assumes you're using a Linux host. For Windows, you can just setup a Scheduled Task in combination with MySql Administrator. Ok, here we go...

A couple of notes before we get to the script:

1. Use a Terminal Window or download PuTTY if you're on a PC. Run it and enter your domain to gain access. 
2. Make sure your host gave you a "normal" shell to work with. If you SSH to your domain and see "jailshell" at the command prompt, you will need to email your host to make the quick change to a normal shell.
3. Create a folder called backup in your home directory and chmod 644 it.
4. You will need to create a file with the code below. The best way would be to manually create a new file using "nano" or vi right in the SSH session. If you're not familiar with vi syntax, you can copy/paste or download, then just upload the file. Don't use notepad to edit the file locally, as it is not linux friendly with the carriage returns. Use wordpad to create the file on a PC.

Some quick vi commands:

1. vi filename at the command prompt. The gets into the file, if the file doesn't exist, it will be created.
2. pressing i in the file puts you into "insert" mode. Just hit ESC to go back.
3. pressing x will delete the character you're on. I think it's easier to just go into insert mode to delete, type, use the arrows to move around.
4. when you're done press :wq then enter. This stands for "write quit". If you want to just quit, press :q enter to quit without saving

These basic vi commands should be enough. Now let's take a look at the shell script you'll be creating.

At the SSH prompt, type: vi my_backup.sh or whatever you want to call the file. By default, you should be in your home directory already. Alternatively, you can just download the file from here and upload it using file manager in cpanel. Then simply vi into the file and change the variables to match your mysql and folder structure setup.

#!/bin/sh

#this is the prefix before the filename and can be anything you want
fileprefix='mysql_backup_';

#this is your mysql user - best to create a new mysql user called backup that has access to all databases
myuser='backup';

#your mysql password
mypass='s0mething$ecure123';

#the backup directory that you should put at the root, not public_html
#chmod 644 the backup folder and leave the trailing slash
backupdir='/home/{typically your main cpanel account here without brackets}/backup/';

#more emails can be added by using a , to separate
emailto='test@test.com';

#subject of the email
emailsubject='mysql backup';

#body of the email
emailbody='mysql db backup attached';

#shouldn't need to change below this
date=`date '+%Y-%m-%d'`;
file=$fileprefix$date.gz;

mysqldump -u$myuser -p$mypass --all-databases | gzip > $backupdir$file;
find $backupdir -name "$fileprefix*" -mtime +7 -type f -exec rm -rf {} ;
echo $emailbody | mutt -s "$emailsubject" -a $backupdir$file "$emailto";

Go ahead and test this script at the command prompt by typing:

/bin/sh /home/{your account}/my_backup.sh

This script will also delete all backups older than 7 days to keep things nice and clean. All you have to do now is create a cron job in your cpanel. Find the cron jobs icon and create a new job by just choosing the "common settings" for "once a day".

In the command space put: /bin/sh /home/{your account}/my_backup.sh

I could probably clean this up more by adding an "account" variable to specify the paths better, but you get the idea. If you have any questions, let me know!