Home » Blogs » m1mic's blog

Setting Up Cron for mySQL backups

25 Nov 2008
Posted by m1mic

Problem: Having a Drupal site automatically run the cron.php script and automatically backup a mySQL database at regular intervals on a Slicehost slice that is running Ubuntu.

Solution: Shell Scripting and Crontab

Turns out, this wasn't so much of a problem after all and it has a really straightforward solution. Since I am using Ubuntu, it already has cron functionality built into it. To tap into this functionality, it is as easy as editing a text file and telling the system to tun the tasks you want (including shell scripts).

MySQL cron backup step-by-step:

  • SSH into your Slicehost Slice
  • Create a directory where you want your backups to be (e.g. /home/usr/my-baks/)
  • Create a new text file using the example script below in a text editor like VI or EMACS and save it using the .sh file extension (e.g. vi my-bak.sh)
    • Lines beginning with a pound sign (#) are used for comments
    • Change username, password, and databasename to your mySQL's username, password, and database name you are backing up

Example shell script:

# Telling the system that we want to us bash for this script.  Using the
# shebang (#!) syntax for this.
#!/bin/bash

# Telling the system which directory we want our backups to be in
cd /home/usr/my-baks/

# Generating a timestamp with the YearMonthDay format
now_timestamp=`date +%Y%m%d`

# Using mySQL's command line syntax to do a database dump and redirecting
# the output to a text file.  To help identify one backup from another,
# adding a timestamp to the filename (e.g. mysql-bak-20081125.sql)
mysqldump -u username -p password databasename > mysql-bak-$now_timestamp.sql

# To keep track of when this script has ran, creating a log file
# with a time stamp in Year-Month-Day-HourMinuteSecond format
# (e.g. 2008-11-25-130000) and redirecting the output to be the
# last line of a text file (>>) syntax
log_timestamp=`date +%Y-%m-%d-%H%M%S`
echo $log_timestamp -- "Backup Completed Successfully" >> /home/usr/my-baks/mysqlbak.log

  • Add the script to Crontab by using the crontab -e command
    • The crontab -e will open up the default editor
    • Note: Depending on your permissions, you may need to have this script run as the root user. To do so, use sudo crontab -e.
  • Add a line to the crontab text file like:
    • 00 00 * * * /home/user/my-bak.sh
      • The first five parameters you give it are minute (0-59), hour (0-23, 0 = midnight), day (1-31), month (1-12), weekday (0-6, 0 = Sunday) followed by the path to the shell script
      • In this case, we are telling the system to run our backup script at midnight (server time) every day
      • See https://help.ubuntu.com/community/CronHowto for more details about cron usage in Ubuntu
  • Since we already have crontab open, we need to add an entry to automatically run the cron.php script for our Drupal site. Add additional line like:
    • 00 * * * * /usr/bin/wget -O - -q http://www.example.com/cron.php
      • Change the URL to the one that you will be using
      • This command tells the system that we want to trigger the cron.php script at the top of the hour, every hour
      • We are telling cron to use the wget command to access our cron.php script on our site (example.com)
      • The wget -O option is used to download the file from our site
      • The second - option tells wget to treat the download as standard output (i.e. to not save the file)
      • The -q option tells wget to not print out what it is doing while it is processing
      • Combining all of this together is essentially like having a user click a link on a page to trigger the cron.php script to run.

Once this is in place, we no longer have to manually backup our database!

Tags: