网上很多这样的教程,mysql全备份,增量备份这样的,连脚本都有,这里只是做一个记录。
开启mysq二进制日志
在my.cnf中
log-bin=mysql-bin
expire_logs_days = 7
server_id = 1
#避免脚本中出现Warning: Using a password on the command line interface can be,所以加上
[client]
user=your_username
password=your_password
给mysql安装路径中给二进制日志文件权限
chmod 777 mysql-bin.index
全备份脚本
数据备份文件路径(每三日全量备份)
/home/profiles/LQJP
./fullbackup 备份数据(以年月日单位命名)
./full_mysqldump.sh 定时备份任务执行脚本
full_mysqldump.sh:
#!/bin/sh
###########
#########
#########DIR_INFO############
date=`date +%Y%m%d`
BACK_DIR="/home/profiles/LQJP/fullbackup/"
FILE_DIR="/home/profiles/LQJP"
#############################3MYSQL_BACK#########################
if [ ! -d $BACK_DIR$date ];then
mkdir -p $BACK_DIR$date
fi
/usr/bin/mysql --defaults-extra-file=/etc/my.cnf -h 127.0.0.1 -P 3306 -e "FLUSH TABLES WITH READ LOCK"
/usr/bin/mysqldump --defaults-extra-file=/etc/my.cnf -h 127.0.0.1 -P 3306 --default-character-set=gbk --opt --triggers -R --hex-blob --flush-logs --master-data=2 --all-databases > $BACK_DIR$date/fullbackup.sql
/usr/bin/mysql --defaults-extra-file=/etc/my.cnf -h 127.0.0.1 -P 3306 -e "UNLOCK TABLES"
cd $BACK_DIR$date
if [ ! -f fullbackup.sql ]; then
echo "mysqldump back file faild !!! please check"
exit 1
fi
/bin/tar zcf fullbackup_$date.tar.gz fullbackup.sql
添加定时任务
# crontab -e
# 22 2 */3 * * /home/profiles/LQJP/full_mysqldump.sh