Here's how I automatically backup my mysql databases on my DreamHost account. I use three shell scripts and a cron job to run them at regular intervals. The databases get backed up hourly. The backups are saved hourly, daily, and weekly. The weekly archive gets a unique name and is therefore not overwritten.
mysql_hourly.sh
#!/bin/bash
# This script backs up mysql databases; appends hour 00 to 23
# Overwritten daily
# Run from crontab ever hour on 30-minute mark (30 * * * *)
# Requires mysql_daily script
suffix=$(date +%H)
# Repeat for each database
mysqldump --opt -u USER -pPASSWORD -h HOST DATABASE > ~/backups/hourly/my_mysql_db.$suffix
Note that I use the short option form (-p), so you cannot have a space between the option and the password.
Complete documentation for mysqldump.
mysql_daily.sh
#!/bin/bash
# This script copies the last backup of the day to a daily archive 00 to 06
# overwritten weekly
# Run at hour 23 hours 40 minutes every day (40 23 * * *)
# Requires mysql_hourly script
suffix=$(date +%d)
cp ~/backups/hourly/my_mysql_db.23 ~/backups/daily/my_mysql_db.$suffix
mysql_weekly.sh
#!/bin/bash
# This script copies the last backup on friday to a weekly archive
# Never overwritten
# Run at hour 23 hours 45 minutes every friday. (45 23 * * 5)
# Requires mysql_hourly script
suffix=$(date +%Y%m%d)
cp ~/backups/hourly/my_mysql_db.23 ~/backups/weekly/my_mysql_db.$suffix
The crontab runs the scripts on a regular schedule. Here is how I run the scripts above. By the way, don't forget to make the scripts ececutable (chmod +x from the command line or use your ftp program).
my dreamhost crontab
# see http://wiki.dreamhost.com/index.php/Crontab
# minute (0-59),
# | hour (0-23),
# | | day of the month (1-31),
# | | | month of the year (1-12),
# | | | | day of the week (0-6 with 0=Sunday).
# | | | | | commands
MAILTO=your@email.com
# Runs the mysqlbackup script hourly (on the half hour), and the daily at 11:45pm every day
30 * * * * ~/lib/scripts/mysql_hourly.sh
45 23 * * * ~/lib/scripts/mysql_daily.sh
45 23 * * 5 ~/lib/scripts/mysql_weekly.sh
Of course put your email address on the MAILTO line.
DreamHost has a very nice cron job front end in its "Goodies" section of the control panel. However, it runs the hourlys on the hour, and the dailys at midnight. I like to set the time to the minute, so I use ssh from the terminal to log in and st up the cron job. Here is the drill (commands shown in double quotes - don't type the double quotes} :
First login to your server via SSH in terminal(enable ssh from the "Users" web panel): "ssh username@mydomain.com"
Once logged into your server, list your current crontab file: "crontab -l"
Set your editor to vim (you can also put this in your bashrc or bash_profile): "export EDITOR='vim'"
now edit the crontab: "crontab -e"
type "i" to enter insert mode, type or cut and paste your code in (if you cut and paste, use notepad or textedit or other real text editor, not MS Word, etc.) I think you need to have a line return (blank line) at the end of your code.
Hit the "esc" button to exit insert mode, then type ":wq!" or ":x" to save and exit. Type "crontab -l" to check your work, then "exit" to log out from ssh.
Restoring from your backup: "mysql -u user_name -p -h hostname database_name < dumped_file"
More Docs from the DreamHost Wiki:
Backup your database.
Restore your database.
Labels: mysql, webdev
