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
- 00 00 * * * /home/user/my-bak.sh
- 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.
- 00 * * * * /usr/bin/wget -O - -q http://www.example.com/cron.php
Once this is in place, we no longer have to manually backup our database!