一:定时备份数据库 backup.sh 内容:
(我写的第一个脚本(本人是开发,脚本内容不够优雅,主要学习总结)
1)指定HOST备份脚本backup.sh ,数据库和备份可以不在同台机器
#!/bin/sh
echo "<== [author:DY] MySQL备份脚本启动中...请勿中断!"
source /etc/profile
HOST="172.xx.xx.xx"
PWD="xxxxxx"
BACKUP_PATH="/opt/backup-auto/"
if [ ! -d "$BACKUP_PATH" ];then
mkdir "$BACKUP_PATH"
fi
ARRAY_DB=$(mysql -u root -h ${HOST} -p${PWD} -e "SHOW DATABASES"|awk 'NR>1')
#echo "即将备份的数据库包含:$ARRAY_DB"
for db in ${ARRAY_DB[@]}
do
if [ ! -d "$BACKUP_PATH${db}" ];then
mkdir "$BACKUP_PATH${db}"
fi
if [ "information_schema" = ${db} ];then
echo "跳过${db}"
else
/usr/local/mydumper-0.9.1/mydumper -u root -p $PWD -h $HOST -B ${db} -c -o $BACKUP_PATH${db}/`date +%Y%m%d%H%M%S`
echo "[${db}] 备份完成"
fi
done
echo "<==[author:DY] $HOST MySQL 备份完成 ==>"
二:每隔30分钟分钟执行一次
(注意这里是逻辑备份,生产环境建议选择每天夜间备份一次) 这里是全量备份
#crontab -e 中设置执行策略和执行脚本
*/30 * * * * /bin/sh /opt/backup.sh
#service crond restart 重启定时任务服务
三:恢复数据库脚本reback.sh
#./reback.sh all //恢复所有数据库
#./reback.sh [数据库名] //恢复指定数据库
1)恢复到指定机器上 reback.sh
#!/bin/sh
echo "<== [author:DY] MySQL备份恢复中...请勿中断!(默认从最新的一次备份中恢复)"
source /etc/profile
HOST="172.30.xx.xx"
PWD="DYxxxxx"
BACKUP_PATH="/opt/backup-auto/"
DB_ARRAY=`ls $BACKUP_PATH`
if echo "${DB_ARRAY[@]}" | grep -w "$1" &>/dev/null;
then echo "";
elif [ "all" = $1 ];
then echo "";
else
echo $"Ukonwn command: $1 ; it shoud be a db name"
exit 1
fi
for dir in ${DB_ARRAY}
do
NEW_BACKUP_PATH=`cd $BACKUP_PATH${dir} && ls | tail -1`
case "$1" in
"${dir}")
/usr/local/mydumper-0.9.1/myloader -u root -p $PWD -h $HOST -B ${dir} -o -d $BACKUP_PATH${dir}/$NEW_BACKUP_PATH
echo "${dir} 数据库还原成功"
;;
"all")
if [ "mysql" = ${dir} -o "sys" = ${dir} -o "performance_schema" = ${dir} -o "information_schema" = ${dir} ];then
echo "跳过 ${dir}"
else
/usr/local/mydumper-0.9.1/myloader -u root -p $PWD -h $HOST -B ${dir} -o -d $BACKUP_PATH${dir}/$NEW_BACKUP_PATH
echo "${dir}用户数据库还原成功"
fi
;;
esac
done
echo "<== [author:DY] $HOST MySQL ${1} 数据表恢复完成 ==>"