crontab
# crontab -l
# m h dom mon dow command
28 16 * * * /home/user/mysql_backup.sh
脚本如下
#!/bin/sh
# mysql_backup.sh: backup mysql databases and keep newest 5 days backup.
#
# last updated: 20 march 2006
# ----------------------------------------------------------------------
# this is a free shell script under gnu gpl version 2.0 or above
# copyright (c) 2006 sam tang
# feedback/comment/suggestions : http://www.real-blog.com/
# ----------------------------------------------------------------------
# your mysql login information
# db_user is mysql username
# db_passwd is mysql password
# db_host is mysql host
# -----------------------------
db_user=root
db_passwd=password
db_host=localhost
# the directory for story your backup file.
backup_dir=/home/mybackup
# date format for backup file (dd-mm-yyyy)
time=$(date +%d-%m-%y)
# mysql, mysqldump and some other bin's path
mysql=/usr/local/mysql/bin/mysql
mysqldump=/usr/local/mysql/bin/mysqldump
mkdir=/bin/mkdir
rm=/bin/rm
mv=/bin/mv
gzip=/bin/gzip
# check the directory for store backup is writeable
test ! -w $backup_dir && echo error: $backup_dir is un-writeable. && exit 0
# the directory for story the newest backup
test ! -d $backup_dir/backup.0/ && $mkdir $backup_dir/backup.0/
# get all databases
all_db=$($mysql -u $db_user -h $db_host -p$db_passwd -bse 'show databases')
for db in $all_db
do
$mysqldump -u $db_user -h $db_host -p$db_passwd $db | $gzip -9 > $backup_dir/backup.0/$time.$db.gz
done
# delete the oldest backup
test -d $backup_dir/backup.5/ && $rm -rf $backup_dir/backup.5
# rotate backup directory
for int in 4 3 2 1 0
do
if(test -d $backup_dir/backup.$int)
then
next_int=`expr $int + 1`
$mv $backup_dir/backup.$int $backup_dir/backup.$next_int
fi
done
exit 0;
备注：
mysql是以mysql用户身份运行的,对/home /mybackup不可写也会失败
chmod 777 /home/mybackup问题解决了bitscn.com