Assumptions: Bash, SSH, SCP, MySQL, root access, crontab
The easiest way to backup your MySQL databases to a remote server is with mysqldump and SCP along with SSH keys. It takes one command to get an SQL dump off all databases on the server (mysqldump –all-databases) but we would like to split that up. The way we do this is by first getting a list of all the databases and iterating over the list to specify which database to backup and to which file.
The following code is written in Bash, but it doesn’t really use anything unique to Bash so it would be easily portable to Tcsh and friends.
#!/bin/bash
cd /backup
databases=`/usr/bin/mysql -Bse 'show databases'`
day=`date +%A`
for i in $databases; do
if [[ "${i}" == "information_schema" ]] ; then
:
else
`/usr/bin/mysqldump $i | /bin/gzip -9 > /backup/mysql/${day}-${i}.sql.gz`
fi
done
output=`/usr/bin/scp -r /backup/mysql/${day}* username@machinename.example.com:/home/ximian/backups/mysql/.`
I keep my backups in the /backup directory so I change the directory in that. Looking further into the code, I use full paths for everything so this is unnecessary, but in case I want to change that behavior later, I know where the script is running from.
if [[ "${i}" == "information_schema" ]] ; then
:
This means that if one of the database names (${i}) is information_schema, then we skip it. The information_schema database stores information about the data and databases, which are not necessary for backups.
`/usr/bin/mysqldump $i | /bin/gzip -9 > /backup/mysql/${day}-${i}.sql.gz` – to clear up what’s going on here, we’re doing a mysqldump on the database name stored in $i then piping it to gzip for compression following by writing the file to /backup/mysql/DayOfWeek-DatabaseName.sql.gz where DayOfWeek or Day Sunday, Monday, Tuesday, etc…
`/usr/bin/scp -r /backup/mysql/${day}* username@machinename.example.com:/home/username/backups/mysql/.` – here, we are sending copy for today’s MySQL backup to machinename.example.com with “username” as our login account. The -r command to scp is means to recursively copy everything everything in the directory tree; it’s useless here but I added it to demonstrate the feature. If I specified a directory to send to the remote machine, -r would tell scp to copy everything contained in the directory, including sub-directories. In this example, we only wish to keep backups for a week. After a week, they get overwritten due to the file names we picked.
If SSH keys are not set up, there are a few simple steps to follow to get them working;
1. ssh-keygen -t dsa
2. Hit enter for everything, don’t specify a password as you want to use it for password-less login
When finished, it will present a message such as -
Your identification has been saved in /home/elvedin/.ssh/id_dsa.
Your public key has been saved in /home/elvedin/.ssh/id_dsa.pub.
3. Copy the id_dsa.pub file to the remote host with the following information –
scp ~/.ssh/id_dsa.pub username@machinename.example.com:~/.ssh/authorized_keys2
4. Done. You should be able to log in (ssh username@machinename.example.com) without being asked for a password.
To get this process to work automatically, we add it to the root user’s crontab.
crontab -e will open up an editor and will allow you to add the following;
0 0 * * * /backup/mysqlbackup.sh
The leftmost character specifies the minute of the day to run and the next field specifies the hour of day. In that example, our backup would run at midnight. If we were to change it to
0,12 0,12 * * * /backup/mysqlbackup.sh
the job would run at 12:00AM, 12:12AM, 12:00PM, 12:12PM
The next three fields specify ‘day of month’, ‘week’, and ‘day of week’. A ‘*’ means that it will run for all enumerations of those values.