MySQL自动备份

1. 添加备份用户

mysql> insert into user(user,host,password) values('backup','localhost',password('backup'));
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 

2. 备份用户授权

mysql> grant Select, Lock tables, Show view, Trigger on *.* to backup@localhost;        
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 

3. 编写备份脚本: 0.bakdb

DATE=$(date +'%Y-%m-%d-%H-%M-%S')

DBBAK_BASE_DIR=/home/xxx/dbbak

SQL_FILE_NAME=xxx_db_bak_$DATE.sql
#SQL_FILE_PATH=$DBBAK_BASE_DIR/$SQL_FILE_NAME
SQL_FILE_PATH=$SQL_FILE_NAME

TAR_FILE_NAME=$SQL_FILE_NAME.tar.gz
#TAR_FILE_PATH=$DBBAK_BASE_DIR/$TAR_FILE_NAME
TAR_FILE_PATH=$TAR_FILE_NAME

echo 备份时间:$DATE
echo SQL文件名:$SQL_FILE_NAME
echo TAR文件名:$TAR_FILE_NAME
echo

echo 调转到备份目录:$DBBAK_BASE_DIR
cd $DBBAK_BASE_DIR

echo 备份开始:

mysqldump -ubackup -pbackup xxx_db > $SQL_FILE_PATH

echo 备份结束

echo 压缩开始

tar -zcvf $TAR_FILE_PATH $SQL_FILE_PATH

echo 压缩结束

echo 发送邮件开始

/usr/local/bin/sendEmail -f xxx@163.com -s smtp.163.com -o message-content-type=html -o message-charset=utf8 -u "xxx数据库备份($DATE)" -xu xxx@163.com -xp xxx_password -m "xxx数据库备份($DATE)" -cc xxx@qq.com -cc xxx@gmail.com -a "$TAR_FILE_PATH"

echo 发送邮件结束

 

DATE=$(date +'%Y-%m-%d-%H-%M-%S')

DBBAK_BASE_DIR=/home/xxx/Backup

SQL_FILE_NAME=xxx_db_bak_$DATE.sql
#SQL_FILE_PATH=$DBBAK_BASE_DIR/$SQL_FILE_NAME
SQL_FILE_PATH=$SQL_FILE_NAME

XZ_FILE_NAME=$SQL_FILE_NAME.xz
#TAR_FILE_PATH=$DBBAK_BASE_DIR/$TAR_FILE_NAME
XZ_FILE_PATH=$XZ_FILE_NAME

echo 备份时间:$DATE
echo SQL文件名:$SQL_FILE_NAME
echo XZ文件名:$XZ_FILE_NAME
echo 

echo 调转到备份目录:$DBBAK_BASE_DIR
cd $DBBAK_BASE_DIR

echo 备份开始:

mysqldump -ubackup -pbackup xxx_db > $SQL_FILE_PATH

echo 备份结束

echo 压缩开始

xz -z -k $SQL_FILE_PATH

echo 压缩结束

echo 删除原始SQL文件开始
rm -rf $SQL_FILE_PATH
echo 删除原始SQL文件结束

echo 发送邮件开始

/usr/local/bin/sendEmail -f xxx@163.com -s smtp.163.com -o message-content-type=html -o message-charset=utf8 -u "xxx数据库备份($DATE)" -xu xxx@163.com -xp xxx_password -m "xxx数据库备份($DATE)" -cc xxx@qq.com -cc xxx@gmail.com -a "$XZ_FILE_PATH" 

echo 发送邮件结束

 

4. 添加定时任务

$ crontab -l
0 0 * * * /home/xxx/bin/0.bakdb