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.

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:

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.