xtrabackup是MySQL的一种物理备份工具,相对于mysqldump,备份和还原速度更快 , 我写了一份可以进行备份 + 还原的脚本
#!bin/bash
all_bak_path="/opt/my_bak/xbak" # 主备目录
add_bak_path="/opt/my_bak/add" # 增量备份目录
mysql_data_path="/data/mysql/data" # 数据库数据路径
mysql_name="mysqld" # 数据库名称
mysql_cnf="/etc/my.cnf" # MySQL 配置文件
userName="root" # 数据库用户名称
userPasswd="root" # 数据库用户密码
# 获取mysql.sock文件路径
# sock_path=$(find / -name mysql.sock) # 第一次获取时使用
sock_path="/usr/local/mysql/mysql.sock"
mysql_port="3306"
innobackupex_options="
--defaults-file=${mysql_cnf} \
--socket=${sock_path} \
--user=${userName} --password=${userPasswd} \
--port=${mysql_port}"
# 0.创建全量备份和增来那个备份路径
create_path(){
if [ ! -d ${all_bak_path} ] ; then
mkdir -p ${all_bak_path}
echo "创建/opt/my_bak/xbak/目录完成"
fi
if [ ! -d ${add_bak_path} ] ; then
mkdir -p ${add_bak_path}
echo "创建/opt/my_bak/add/目录完成"
fi
}
# 1.安装xtrabackup
ins_xtra(){
ins_app="libev-4.03-3.el6.x86_64.rpm percona-xtrabackup-24-2.4.29-1.el7.x86_64.rpm"
yum -y install $ins_app
if [ $? -ne 0 ]; then
echo "安装xtrabackup失败"
exit 1
fi
echo "安装xtrabackup完成"
data_dir=$(cat ${mysql_cnf} | grep datadir)
if [ -z "$data_dir" ]; then
cat >> ${mysql_cnf} <<EOF
datadir=${mysql_data_path}/ # 选择备份的数据库目录
EOF
systemctl restart ${mysql_name}
if [ $? -ne 0 ]; then
echo "重启mysql失败"
exit 1
fi
echo -e "修改${mysql_cnf}文件完成"
fi
}
# 2.对数据库进行全量备份
all_backup(){
innobackupex ${innobackupex_options} ${all_bak_path}
}
# 3.对数据进行增量备份
add_backup(){
# 检测是否有全量备份
if [ -z "$(find $all_bak_path -maxdepth 1 -type d)" ]; then
echo "没有全量备份,需先全量备份"
exit 1
fi
# 执行增量备份部分语句
add_half="innobackupex ${innobackupex_options} \
--incremental "${add_bak_path}" "
#最新的全量备份
new_all_backup_path=$(find ${all_bak_path}/*_* -maxdepth 0 -type d | sort -r | head -1)
#没有更新的话,上一次目录路径选择全量目录
if [ -z "$(find $add_bak_path -mindepth 1 -maxdepth 1)" ]; then
$add_half --incremental-basedir="${new_all_backup_path}"
echo "增量执行完成,在全量备份后备份"
else
# 获取所有增量备份
new_add_backup_path=($(find ${add_bak_path}/*_* -maxdepth 0 -type d | sort | cut -d ":" -f 1))
# 获取全量备份文件名
tmp_all_path=$(echo $new_all_backup_path | cut -d "/" -f 5)
# 将文件名格式化为数字格式
all_time=$(echo "${tmp_all_path//[-_]}/" | cut -d "/" -f 1)
# 获取增量备份数量
add_length=${#new_add_backup_path[@]}
# 获取全量备份以后得增量备份
for i in ${new_add_backup_path[@]} ; do # 遍历
tmp_add_path=$(echo $i | cut -d "/" -f 5) # 获取文件名
add_time=$(echo "${tmp_add_path//[-_]}/" | cut -d "/" -f 1) # 将文件名格式化为数字格式
if [ $all_time -le $add_time ] ; then # 判断增量备份名称是否相对于全量备份日期更新
$add_half --incremental-basedir="${new_add_backup_path[add_length-1]}"
echo -e "增量执行完成,在上一次增量备份后备份,上一次备份位置${new_add_backup_path[add_length-1]}"
break
else
$add_half --incremental-basedir="${new_all_backup_path}"
echo "增量执行完成,在全量备份后备份"
break
fi
done
fi
}
# 4.对全量备份进行合并
merge_backup(){
#最新的全量备份
new_all_backup_path=$(find ${all_bak_path}/*_* -maxdepth 0 -type d | sort -r | head -1)
# 倒序获取所有增量备份
new_add_backup_path=($(find ${add_bak_path}/*_* -maxdepth 0 -type d | sort | cut -d ":" -f 1))
# 获取全量备份文件名
tmp_all_path=$(echo $new_all_backup_path | cut -d "/" -f 5)
# 将文件名格式化为数字格式
all_time=$(echo "${tmp_all_path//[-_]}/" | cut -d "/" -f 1)
# 获取增量备份数量
add_length=${#new_add_backup_path[@]}
# 执行合并部分语句
apply_half="innobackupex --apply-log"
echo "准备全量备份"
${apply_half} --redo-only $new_all_backup_path
if [ $? -eq 0 ] ; then
echo -e "准备全量备份完成,位置为${new_all_backup_path}"
else
echo "准备全量备份报错,位置为${new_all_backup_path}"
exit 1
fi
# 将增量备份脚本从后向前进行合并
if [ -n $new_add_backup_path ] ; then
# 获取全量备份以后得增量备份
for i in ${new_add_backup_path[@]} ; do # 遍历
tmp_add_path=$(echo $i | cut -d "/" -f 5) # 获取文件名
add_time=$(echo "${tmp_add_path//[-_]}/" | cut -d "/" -f 1) # 将文件名格式化为数字格式
if [ $all_time -le $add_time ] ; then # 判断增量备份名称是否相对于全量备份日期更新
if [ $i == ${new_add_backup_path[add_length - 1]} ] ; then
$apply_half $new_all_backup_path --incremental-dir=${new_add_backup_path[add_length - 1]}
if [ $? -eq 0 ] ; then
echo -e "最后一次合并完成,位置为${i}"
else
echo "最后一次合并报错,位置为${i}"
exit 1
fi
else
$apply_half --redo-only $new_all_backup_path --incremental-dir=$i
if [ $? -eq 0 ] ; then
echo -e "增量合并至全量完成,位置为${i}"
else
echo "增量合并至全量报错,位置为${i}"
echo -e "语句为: $apply_half --redo-only $new_all_backup_path --incremental-dir=${i}"
exit 1
fi
fi
fi
done
else
echo "没有增量备份"
fi
echo "最后一次准备全量备份"
${apply_half} $new_all_backup_path
if [ $? -eq 0 ] ; then
echo -e "准备全量备份完成,位置为${new_all_backup_path}"
else
echo "准备全量备份报错,位置为${new_all_backup_path}"
exit 1
fi
}
# 5.对数据库进行还原
db_restore(){
#最新的全量备份
new_all_backup_path=$(find ${all_bak_path}/*_* -maxdepth 0 -type d | sort -r | head -1)
innobackupex --copy-back ${new_all_backup_path}
echo "还原数据完成"
chown -R mysql.mysql ${mysql_data_path}
echo "修改mysql数据库权限完成"
systemctl restart ${mysql_name}
echo "重启数据库完成"
echo "数据库连接测试: "
mysql -u${userName} -p${userPasswd} -e "show databases"
mysql -u${userName} -p${userPasswd} -e "select * from test.testlog"
}
options=("全量备份" "增量备份" "还原数据" "安装xtrabackup" "点击任意键退出")
create_path
select i in ${options[@]} ;do
case $i in
${options[0]})
all_backup
;;
${options[1]})
add_backup
;;
${options[2]})
merge_backup
db_restore
;;
${options[3]})
ins_xtra
;;
*)
echo "退出"
break 2
;;
esac
done
``
标签:脚本,备份,sock,xtrabackup,mysql,path,bak
From: https://www.cnblogs.com/ramiro/p/18061680