Skip to content

Automated Daily Backup of the mySQL Database

Now that I have my tikiwiki running on a Linux AS4 server and having felt the sting of having to recover it, I have incentive to create an automated daily backup of the mySQL database. I have decided to do the backup using a simple shell script and schedule it using the Linux crontab.

Here is the final version of the shell script (saved to “/root/sql_backup.sh”). This script will do a mySQL dump of one database to a gzip archive file in a backup directory, keep only the 10 most recent archive files (pruning the rest), and rsync the backup directory to a remote server.

#!/bin/sh
# MySQL backup script - do a backup and rsync to remote server

# mySQL
SQLROOTPASS=mysqlrootpassword
SQLDB=mysqldatabasename

# Backup Dirs
LOCALDIR=/home/backup/mysql
REMOTEHOST=myremoteserver
REMOTEDIR=/home/backup/mysql
NUMBACKUPS=10

# Create local dir
mkdir -p $LOCALDIR

# Do a mySQL backup
NOW=$(date +"%Y-%m-%d-%H-%M-%S")
echo "Doing a database backup to $LOCALDIR/$SQLDB-$NOW.sql.gz"
# Note: The following mysqldump command should be one line only!
mysqldump -u root -h localhost -p$SQLROOTPASS $SQLDB | gzip -9 > $LOCALDIR/$SQLDB-$NOW.sql.gz

# Prune old backups
FILES=$(ls -t $LOCALDIR/*.gz)
for FILE in $FILES
do
if [ $NUMBACKUPS -gt 0 ]
then
   echo Keeping $FILE
else
   echo Deleting $FILE
   rm $FILE
fi
NUMBACKUPS=$(expr $NUMBACKUPS - 1)
done

# Create remote dir
ssh $REMOTEHOST mkdir -p $REMOTEDIR

# Sync from local dir to remote dir
echo "RSyncing to $REMOTEHOST.."
rsync -rt --delete $LOCALDIR/ $REMOTEHOST:$REMOTEDIR/

Rather than explain the whole script, I’ll just point out some parts that may require more attention:

  • Unfortunately, this script does contain the mySQL root password in the clear. I haven’t figured out how to do a mysqldump as a non-root SQL user yet.
  • To avoid the need to include the remote server’s root password, this script will require that you have already established trust with the remote server. This avoids the need to input a password when using RSync, SSH, or SCP. See below for instructions on how to establish this trust.
  • When doing an assignment to a variable in the script, don’t insert spaces after the variable name. For example, “TEMPVAR=Hello” works but “TEMPVAR =Hello” won’t.
  • The format “TEMPVAR=$(…)” tells the script to execute the command inside and assign the result to the variable. This is equivalent to the format “TEMPVAR=`…`” using backward single quotes.
  • In order to configure RSync to delete files on the remote system (otherwise we’ll use up all the hard drive space), we must use the -r flag (recurse into subdirectories), –delete flag, and use a directory (“$LOCALDIR/” ending with a slash) as the source. The -t flag (persist file modification time) is necessary for RSync to do delta updates.

To establish trust with a remote server, do the following:

  1. Log into your server as the root user.
  2. Look for this file “/root/.ssh/id_rsa.pub”.
  3. If the file or directory doesn’t exist, then you will need to generate the RSA public/private key:
    ssh-keygen -t rsa

    Just take the default inputs and your RSA keys will be generated.

  4. Copy the public key “id_rsa.pub” to the remote server’s “/root/.ssh” directory.
    scp /root/.ssh/id_rsa.pub myremoteserver:/root/.ssh/my_id_rsa.pub

    If that directory does not exist, you may wish to generate the RSA public/private keys there also.

  5. You will need to add the contents of “my_id_rsa.pub” to the end of the remote server’s “/root/.ssh/authorized_keys” file (it contains keys from other servers which the remote server should trust). If the file doesn’t exist, then create it as an empty file.
    cp authorized_keys authorized_keys.original
    cat authorized_keys my_id_rsa.pub > authorized_keys
    rm my_id_rsa.pub
  6. You can test it out by making an SSH connection to the remote server.
    ssh root@myremoteserver

    If you are not prompted for the password, then the trust was successfully established.

Finally, let us schedule the mySQL backup to run every day at 3am using the Linux crontab:

crontab -e
0 3 * * * sh /root/sql_backup.sh > /tmp/sql_backup.output 2>&1

Some hints about using the crontab:

  • “crontab -e” will allow you to edit the crontab contents.
  • The crontab line format is:
    <minute=0-59> <hour=0-23> <day of month=1-31> <month=1-12> <day of week=0-6 (sun-sat)> <command>
  • Precede the crontab line with a # (pound) character to indicate a comment which crontab will ignore.
  • The “2>&1” will redirect STDERR to STDOUT, which in turn is redirected to “/tmp/sql_backup.output” above. (0=STDIN, 1=STDOUT, 2=STDERR)
  • “crontab -l” will list the crontab contents.

8 Comments

  1. Elanor

    I kept getting a permission denied error with the cat authorized keys command. Even though I was logged in as root 🙁

  2. Chanh

    The “cat” command just concatenates the contents of “id_rsa.pub” to the end of “authorized_keys” file and replaces it. You can manually open up “authorized_keys” in an editor like vi or emacs and paste the contents of “id_rsa.pub” to the end. Best is if the “authorized_keys” file doesn’t exist or is empty; in which case, you can just replace it with “id_rsa.pub” totally. Hope that helps.

  3. cdd

    The other user can see sql root password via command ps -efww

  4. Sanja

    How to make a backup in Windows?

  5. Elanor

    Hello!

    We have this working great on our webserver – problem now is that I want to automatically restore these files to a different server every day.

    I’ve figured out how to do that, but the filename changes with the date… how do I automate it to use the *most recent* file?

    • Chanh

      Sorry for the long delay. I meant to research this, but life intruded. Anyhow, for those who might need the same help, one suggestion is to sort the files by time with the command “ls -l –sort-time” which will list the files with most recent first. Filter for the files with the correct backup extension from the returned list of files and the first file should be the most recent.

  6. Very nice post. Information posted here is very helpful. I like the blog very much. Thanks to the admin of this blog.

Leave a Reply to Chanh Cancel reply

Your email address will not be published. Required fields are marked *