Simple MySQL Automated Backup

These are just a set of simple scripts for backing up small databases with MyISAM tables. For more information on backups, see http://dev.mysql.com/doc/refman/5.0/en/disaster-prevention.html.

These scripts were written for a package install of MySQL 5.0.33-log on OpenBSD 4.1. For other installs, you may have to change the database and bin directories. The directory for the backup files is strictly up to you, just make sure that it is accessible by the user who owns the cron job. Create a MySQL user with SELECT, RELOAD, and LOCK TABLES privileges on all databases and tables to be backed up. Then replace the user and password below as appropriate.

Crontab entries:

# MySQL hourly backup.
0 * * * * /path_to_script/mysql_bak_hour.sh
# MySQL daily backup.
0 0 * * * /path_to_script/mysql_bak_day.sh

mysql_bak_hour.sh:

for i in `ls -p /var/mysql/ | grep \/ | sed 's/\///'`
do /usr/local/bin/mysqldump -uuser -ppassword --opt $i \
> /var/mysql_backups/$i.sql
done

mysql_bak_day.sh:

# Delete files older than 2 days.
for i in `find /var/mysql_backups \! -mtime 1 \! -mtime 2 -print`
do rm $i
done
# Set the backup date.
bk_date=`date "+%y-%m-%d_"`
# Creat the backups.
for i in `ls -p /var/mysql/ | grep \/ | sed 's/\///'`
do /usr/local/bin/mysqldump -uuser -ppassword --opt $i \
> /var/mysql_backups/$bk_date$i.sql
done

Comments