MySQL 自動バックアップ

MySQLバックアップの忘備録

1．バックアップ用シェル

#!/bin/sh

# Set mask for other user cann't read
#umask 077

# backup file
# Retention period
period=31

# directory for backup files
dirpath='/home/pi/backup/mysql'

# backup file naming
filename=`/bin/date +%y%m%d%H`

# excute
/usr/bin/mysqldump --opt --all-databases --events --default-character-set=binary -u root --password=xxxxx| /bin/gzip > $dirpath/$filename.sql.gz
#/usr/bin/mysqldump --opt --all-databases --events --default-character-set=binary -u root --password=xxxxx > /tmp/filename.sql

/bin/cp $dirpath/$filename.sql.gz $dirpath/today

# delete old backup files
oldfile=`/bin/date --date "$period days ago" +%y%m%d%H`
/bin/rm -f $dirpath/$oldfile.sql.gz

2．crontabの設定

0 2 * * *  /home/pi/prj/sql/mysql_backup.sh > /tmp/cron-bk.stdout 2 > /tmp/cron-bk.stderr || [ $? -eq 1 ]
15 12 * * *  /home/pi/prj/sql/mysql_backup.sh > /tmp/cron-bk.stdout 2 > /tmp/cron-bk.stderr || [ $? -eq 1 ]

Peace!!