Automatic Backup: Shell Scripting MySQLdump and FTP File Backup
The purpose of this article is to give you a quick fire introduction to the world of shell scripting. It is by no stretch of the imagination a complete guide to the technique of shell scripting but It will give you the basis of a script for backing up your web files and mysql databases, locally and remotely. You can then take this and work with it to make it fit your needs and requirements.
During this article we will be using the Bourne shell interpreter to perform the tasks and functions we need. We will be writing our script in a plain text editor and then saving it and providing the file with the necessary:
Scripting Tools
For the most part, I either write my own scripts in BBedit or in a little shareware app called ScriptGUI. The latter allows you to quickly and easily test the results of your scripts as you go along, and also allows you to save the script as a droplet so that it can be run from the desktop with a simple double click, or by dragging and dropping files onto it.
Getting Started
As with any shell script you need to let the host computer know which shell interpreter to use for the tasks you are going to perform. You do this by opening the first line of your script with the following syntax:
#! /bin/sh
After this first line the hash/pound key is then used to comment out lines so that the interpreter can ignore them as it runs through your script.
To kick off the script then, we will start by creating certain number of fixed variables. User-assigned variables tend to be written in lowercase whilst system and environment variables are handled in uppercase.
Assuming we are keeping the backups on a second hard drive in OS X called Backups we will create a user defined variable containing a path to this as follows:
pathtobackup="/Volumes/Backups";
In calling this variable at a later date you will precede it by a dollar ($) sign as follows:
$pathtobackup
Another aspect of shell scripting that is very important to understand is how to handle back (`), double (“) and single quotes ("). Double and single quotes are used to contain literals. That means that anything contained within double quotes will be treated as is. Anything in back quotes is used for piping output from a command into a variable stripping out carriage returns as it does so.
Back quotes would be used in our script as follows:
date = `date {y}-{m}-{d}_{H}{m}{s}`
this would assign a value of the current date and time (24hour clock) into a variable called date.
As we quickly run through a series of ways to handle variables, perhaps the last important way to handle variables for the purpose of this article is user input. In order to achieve this you simply need to precede a dollar-ed variable with the command "read", for example:
echo Please Enter Username:
read $username
This will output the words "Please Enter Username:" and prompt the end user for input. Having typed in the value and pressed return, the value will be assigned to the respectively named variable and referenced in the script in the same way any other user-assigned variable is, preceding it with a dollar sign.
Creating The Script
Okay! so now we understand a little more about handling variables we will proceed by initialising a series of variables that our script will need to know in order to operate successfully.
The beginning of the script should therefore look like something as follows:
#! /bin/sh
# path for backups
pathtobackup = "/Volumes/Backups";
# path to files being backed up
pathtofiles = "/Library/WebServer/Documents";
# name for backup folder date and time stamped
foldername = `date {y}-{m}-{d}_{h}{m}{s}`
# mysql user name
dbuname = "<mysql username here>";
# mysql password
dbpwd = "<mysql user passwword here>";
# mysql db name
dbname = "<mysql db name here>";
# mysql host name
dbhost = "127.0.0.1";
Should you wish also to set up an FTP transfer of the files to an online storage space somewhere, you can initialise the following variables also:
# switch ftp on or off // 1=on 0=off
ftpswitch = 0;
# ftp server
ftphost = "ftp.yourdomain.com";
# ftp username
ftpuname = "<ftp username here>";
# ftp pass
ftppwd = "<ftp user password here>";
# ftp path
ftppath = "<path to ftp file store here>";
Because of course we are storing passwords in the script, if your computer is publicly accessible, you may want to make sure that security of the file is paramount. You can do this by ensuring you store the file in the right place perhaps in your ~/bin folder, and also to give it no group/everyone read or execution permissions
Ok so now we should be ready to proceed.
Intelligent Scripting
There are two things I like to do when writing a script. The first is to allow the end user running the script to be able to see output so they know at what point the script is in the process of running. Admittedly, a script may run too fast for your to read the text scrolling up the terminal window, but there are times when you may be dumping a large database or tarring the dump file, when the output will pause and you will be able to read it.
Screen output can be achieved by using echo statements, as follows:
echo Backup Script Processing // - - -
The second thing I like to do is to run the script through a series of if/else statements so that you can always give your script the option to do something, like exit depending on the result of an action. An example of this would be if you checked the amount of disk space left on your backup hard drive and found it to be too low for the file size then you could simple exit.
Results from if/else statements can be achieved as follows:
if
cd $pathtobackup
then
echo ... successfully navigated to Backup Dir!
else
echo ... Backup Dir NOT Mounted! Exiting Script
exit 0
fi
Putting Everything Together
Ok so now that we have the three parts of the jigsaw in place in order to write our script, we can start by piecing the series of actions we need together.
I am assuming from this point forward that you have the correct first line for the shell interpreter in place and you have all the required variables initialised, so that our script can work with those and generate a result without any errors.
we will start by echoing an opening processing statement.
# echo message
echo backup script // processing ....
Navigating to the folder into which we will store our backup files locally can then follow this.
# navigate to backup dir
if
cd $pathtobackup
then
echo ... successfully navigated to Backup Dir!
else
echo ... Backup Dir NOT Mounted! Exiting Script
#exit script with error
exit 0
fi
Now we need to make the date and time stamped directory into which we will copy the files for the backup.
# echo message
echo ..... making export directory
# create directory for the current backup script
if
mkdir $foldername
then
echo .......... exporting directory successfully created
# chmod the directory
chmod 777 $foldername
fi
At this point we will go to the mysqldump application and dump out all the structure and data from our selected database for storage.
# echo message
echo ...... exporting SQL dump
if
# dump the db into a .sql file in the directory
mysqldump --user=$dbuname \
--password=$dbpwd --flush-logs \
--lock-tables $dbname > \
$pathtobackup/$foldername/$dbname.sql;
then
# echo message
echo ...... SQL dump file created successfully
else
# echo message
echo ...... mysqldump error!
exit 0
fi
The reason for using the \ symbol is to ensure that if you are putting this in into a terminal window using PICO or VI then keeping the command lines short enough to avoid the command scrolling automatically onto a new line and then causing errors when your script runs.
Having done this we will now compress the database dump file into a gzipped tar format:
# echo message
echo ...... compressing SQL dump file
# navigate itno folder
cd $foldername
# compress and gzip
tar -cf $dbname.tar $dbname.sql
if
gzip $DB.tar
then
echo .......... $dbname.tar successfully gzipped
fi
We can now remove the uncompressed SQL file which if it is large we wont necessarily bother transferring via ftp!
# echo message
echo ...... removing uncompressed SQL dump file
if
# compress and gzip
rm -f $dbname.sql
then
echo .......... $dbname.sql successfully deleted
fi
To avoid going through essentially very similar code, you should be able to mimic the above processes using CP commands instead of mysqldump etc to generate a copy of the web files into the backup folder, gzip'ing the copied folder and then removing the contents of it once this is done.
If your going to be doing backups on files and databases stored on a shared Linux system, there is in fact a nifty little encryption application called ncrypt (ncrypt.sourceforge.net) which uses AES algorithms to encrypt and decrypt files using a pass phrase, and which also has a secure wipe mechanism for deleting files off the system.
If you have this installed you can run a simple command, pulling the pass phrase from a secondary file to that the system can run automated, although this does of course make the system less secure, it can be useful in a system where files are being transferred and stored in a remote location using ftp.
# echo message
echo ... encrypting backup SQL file
if
# encrypt
ncrypt –e –z –-serpent \
-i $dbname.tar.gz \
–o $dbname.tar.gz.enc < <passphrasefilename&path>
then
echo ...... $dbname.tar.gz encrypted and wiped
fi
Be sure to replace the passphrase and file path with the correct information. The format of the file should have the pass phrase entered twice on a new line each time, as follows:
Passphrase
Passphrase
Once this has been done you can then start thinking about ftp-ing the backup files over to your remote host. This can be achieved in the following code:
# if we turned the feature on in the initialize variables
if
$ftpswitch -eq 1
then
# login to ftp assigning out put to END SCRIPT
ftp -n $ftphost <<END_SCRIPT
# specify a user
user $ftpuname
# provide pass
$ftppwd
# navigate to folder store and put files
cd $ftppath
mkdir $foldername
cd $foldername
put $dbname.tar.gz.enc
put $webfiles.tar.gz.enc
# quit the ftp
quit
fi
if you then wish to echo the output from the ftp session you can run an echo statement on the END_SCRIPT variable
echo END_SCRIPT
Now that his has been done your script is essentially finished and ready to close off and exit. To finish off we will echo one last process statement and tie up things with an exit result.
# echo message
echo ... // processing completed
# return result
exit 1
This concludes the make up of a vary basic shell script for performing an essential function.
Having stored the file in some location you then simply need to give it the correct permissions to run, and then you can Use a utility like cronnix in OSX to manage the cron jobs and assign the shell script to one on a regular basis.
To assign the permissions to your file, simply navigate to the folder where it is stored and assuming you saved the file as backup.sh enter in the terminal the following:
chmod 600 backup.sh
To run it you would then simply type:
sh backup.sh
Conclusion
Now you should be ready to roll for backups. This script could easily be used in OSX as in any other *nix variant. By way of a follow up you can look up more complex case/switch arguments and more complex pipe commands for checking actions that are being performed by the script. You may also want to learn how to create shell functions for repeating tasks in a complex script over and over without repeatedly typing the same code!
By way of a last hint and quick tip. If your host runs php and is web accessible, it would be very easy to call the script from a php web page whenever you wish in a file that looks as follows:
<?php
$ouput = shell_exec(“/path/to/backup.sh”);
echo $output;
?>