centos7环境下使用xtrabackup备份mysql8.0自动化脚本
1.备份工具：percona-xtrabackup-8.0.7-Linux-x86_64
2.目录结构
[/usr/local/worksh/xtrabackup_cron]# tree
.
├── bin
│ ├── mysql_increment_hot_backup.sh
├── conf
│ └── mysql_increment_hot_backup.conf
├── log
│ └── full_2020-10-19_22-51-49_1.log
└── var
├── mysql_increment_hot_backup.err
├── mysql_increment_hot_backup.index
└── mysql_increment_hot_backup.index_2020-10-18
3.备份数据库脚本
# vim mysql_increment_hot_backup.sh
#!/usr/bin/env bash
# Program: MySQL 增量备份脚本 使用 percona xtrabackup
# Author : chenhao
# Date : 2015-12-29
##进入程序目录
## 脚本中变量存在空 就退出
set -u
cd /usr/local/worksh/xtrabackup_cron/bin
# 读取配置文件中的所有变量值, 设置为全局变量
# 配置文件
conf_file="../conf/mysql_increment_hot_backup.conf"
# mysql 用户
user=`sed '/^user=/!d;s/.*=//' $conf_file`
# mysql 密码
#password=`sed '/^password=/!d;s/.*=//' $conf_file`
password="pass"
#################
# 列出所存在的数据库
dbname=`/usr/local/mysql/bin/mysqlshow -u${user} -p${password} |grep -v schema |grep -v Databases |grep -v mysql |awk '{print $3$2}' |sed '/^$/d'`
# 相关变量
idc_name='eus'
# 当前需要备份db的服务器外网ip地址
server_ip='1.1.1.2'
server_name=`hostname`
server_port='3306'
instance_role='slave'
#bk_tools='xtrabackup'
bk_tools=`cat /etc/passwd |grep root| cut -f 1 -d : |xargs -I {} crontab -l -u {}|grep xtrabackup_cron|sed 's/[ ][ ]*/|/g'`
remarks='backup by crontab'
start_time=`date +"%Y-%m-%d %H:%M:%S"`
finish_time=`date +"%Y-%m-%d %H:%M:%S"`
#################
# mysql 备份目录
backup_dir=`sed '/^backup_dir=/!d;s/.*=//' $conf_file`
# percona-xtrabackup 备份软件路径
xtrabackup_dir=`sed '/^xtrabackup_dir=/!d;s/.*=//' $conf_file`
# 全备是在一周的第几天
full_backup_week_day=`sed '/^full_backup_week_day=/!d;s/.*=//' $conf_file`
# mysql 全备前缀标识
full_backup_prefix=`sed '/^full_backup_prefix=/!d;s/.*=//' $conf_file`
# mysql 增量备前缀标识
increment_prefix=`sed '/^increment_prefix=/!d;s/.*=//' $conf_file`
# mysql 配置文件
mysql_conf_file=`sed '/^mysql_conf_file=/!d;s/.*=//' $conf_file`
# 备份错误日志文件
error_log=`sed '/^error_log=/!d;s/.*=//' $conf_file`
# 备份索引文件
index_file=`sed '/^index_file=/!d;s/.*=//' $conf_file`
# 备份日期
backup_date=`date +%F`
# 备份日期
backup_time=`date +%H-%M-%S`
# 备份日期
backup_week_day=`date +%u`
# 设置备份线程数
backup_thread=5
# 创建相关目录
log_dir=../log
var_dir=../var
mkdir -p $backup_dir
mkdir -p $log_dir
mkdir -p $var_dir
####################
# 采集备份信息状态入库
function collect_backup_info() {
# 定义插入数据到服务端时使用的用户名和密码
remote_dbuser='backup_manager_user'
remote_dbpasswd='pass'
remote_port="3306"
remote_host="1.1.1.1"
mysql_link="/usr/local/mysql/bin/mysql -u${remote_dbuser} -h${remote_host} -P${remote_port} -p${remote_dbpasswd}"
# 拼接入库SQL
aget_exec_sql="INSERT INTO monitors_alisz.mysql_backup_info(idc_name,server_ip,server_name,server_port,dbname,bk_type,backup_file_size,backup_file_path,backup_file,bk_status,instance_role,bk_tools, start_time,finish_time,remarks) VALUES ('${idc_name}', '${server_ip}', '${server_name}', '${server_port}', '${dbname}', '${bk_type}','${backup_file_size}','${backup_dir}/${backup_folder}', '${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day}','${bk_status}', '${instance_role}', '${bk_tools}', '${start_time}', '${finish_time}', '${remarks}' );"
echo ${aget_exec_sql}
${mysql_link} -e "${aget_exec_sql}"
}
####################
# 全量备份
function full_backup() {
backup_folder=${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day}
mkdir -p $backup_dir/$backup_folder
$xtrabackup_dir/bin/xtrabackup --defaults-file=$mysql_conf_file --user=$user --password=$password --parallel=$backup_thread --slave-info --safe-slave-backup --no-timestamp --backup --target-dir=$backup_dir/$backup_folder > $log_dir/${backup_folder}.log 2>&1
return $?
}
# 增量备份
function increment_backup() {
backup_folder=${increment_prefix}_${backup_date}_${backup_time}_${backup_week_day}
incr_base_folder=`sed -n '$p' $index_file | \
awk -F '[, {}]*' '{print $3}' | \
awk -F ':' '{print $2}'`
mkdir -p $backup_dir/$backup_folder
$xtrabackup_dir/bin/xtrabackup \
--defaults-file=$mysql_conf_file \
--user=$user \
--password=$password \
--no-timestamp \
--incremental --backup \
--target-dir=$backup_dir/$backup_folder \
--incremental-basedir=$backup_dir/$incr_base_folder > $log_dir/${backup_folder}.log 2>&1
return $?
}
# 删除之前的备份(一般在全备完成后使用)
function delete_before_backup() {
cat $index_file | awk -F '[, {}]*' '{print $3}' | \
awk -v backup_dir=$backup_dir -F ':' '{if($2!=""){printf("rm -rf %s/%s\n", backup_dir, $2)}}' | \
/bin/bash
cat $index_file | awk -F '[, {}]*' '{print $3}' | \
awk -v log_dir=$log_dir -F ':' '{if($2!=""){printf("rm -rf %s/%s.log\n", log_dir, $2)}}' | \
/bin/bash
}
# 备份索引文件
function backup_index_file() {
cp $index_file ${index_file}_$(date -d "1 day ago" +%F)
}
# 备份索引文件
function send_index_file_to_remote() {
echo 'send index file ok'
}
# 添加索引, 索引记录了当前最新的备份
function append_index_to_file() {
echo "{week_day:$backup_week_day, \
dir:${1}_${backup_date}_${backup_time}_${backup_week_day}, \
type:${1}, \
date:${backup_date}}" >> $index_file
}
# 记录 错误消息到文件
function logging_backup_err() {
echo "{week_day:$backup_week_day, \
dir:${1}_${backup_date}_${backup_time}_${backup_week_day}, \
type:${1}, \
date:${backup_date}}" >> $error_log
}
# 清空索引
function purge_index_from_file() {
> $index_file
}
# 清空错误日志信息
function purge_err_log() {
> $error_log
}
# 打包备份
function tar_backup_file() {
echo "tar $1 ok"
}
# 发送备份到远程
function send_backup_to_remote() {
echo "send $1 remote ok"
}
# 判断是应该全备还是增量备份
# 0:full, 1:incr
function get_backup_type() {
full_backup_week_day=`sed '/^full_backup_week_day=/!d;s/.*=//' $conf_file`
backup_type=0
if [ "$full_backup_week_day" -eq `date +%u` ]; then
backup_type=0
bk_type='full'
else
backup_type=1
bk_type='incr'
fi
if [ ! -n "`cat $index_file`" ]; then
backup_type=0
bk_type='full'
fi
return $backup_type
return $bk_type
}
# 测试配置文件正确性
function test_conf_file() {
# 判断每个变量是否在配置文件中有配置，没有则退出程序
if [ ! -n "$user" ]; then echo 'fail: configure file user not set'; exit 2; fi
if [ ! -n "$password" ]; then echo 'fail: configure file password not set'; exit 2; fi
if [ ! -n "$backup_dir" ]; then echo 'fail: configure file backup_dir not set'; exit 2; fi
if [ ! -n "$full_backup_week_day" ]; then echo 'fail: configure file full_backup_week_day not set'; exit 2; fi
if [ ! -n "$full_backup_prefix" ]; then echo 'fail: configure file full_backup_prefix not set'; exit 2; fi
if [ ! -n "$increment_prefix" ]; then echo 'fail: configure file increment_prefix not set'; exit 2; fi
if [ ! -n "$mysql_conf_file" ]; then echo 'fail: configure file mysql_conf_file not set'; exit 2; fi
if [ ! -n "$error_log" ]; then echo 'fail: configure file error_log not set'; exit 2; fi
if [ ! -n "$index_file" ]; then echo 'fail: configure file index_file not set'; exit 2; fi
}
# 执行
function run() {
# 检测配置文件值
test_conf_file
# 判断是执行全备还是曾量备份
get_backup_type
backup_type=$?
case $backup_type in
0 )
# 全量备份
start_time=$start_time
full_backup
backup_ok=$?
if [ 0 -eq "$backup_ok" ]; then
# 全备成功
# # 打包最新备份
# tar_backup_file $full_backup_prefix
# # 将tar备份发送到远程
# send_backup_to_remote $full_backup_prefix
# 备份索引文件
backup_index_file
# # 发送索引文件到远程
# send_index_file_to_remote
# 清除之前的备份
delete_before_backup
# 清除索引文件
purge_index_from_file
# 添加索引, 索引记录了当前最新的备份
append_index_to_file $full_backup_prefix
# 计算备份集大小和md5
backup_file_size=`du -lhs ${backup_dir}/${backup_folder}|awk '{print $1}'`
# 执行信息入库
bk_status='successful'
finish_time=`date +"%Y-%m-%d %H:%M:%S"`
#collect_backup_info
else
# 全备失败
# 删除备份目录
rm -rf ${backup_dir}/${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day}
# 记录错误日志
logging_backup_err $full_backup_prefix
# 执行信息入库函数
bk_status='failure'
finish_time=`date +"%Y-%m-%d %H:%M:%S"`
#collect_backup_info
fi
;;
1 )
# 增量备份
start_time=$start_time
increment_backup
backup_ok=$?
if [ 0 -eq "$backup_ok" ]; then
# 增量备份成功
# # 打包最新备份
# tar_backup_file $increment_prefix
# # 将tar备份发送到远程
# send_backup_to_remote $increment_prefix
# 添加索引, 索引记录了当前最新的备份
append_index_to_file $increment_prefix
# 执行信息入库函数
bk_status='failure'
#finish_time=${finish_time}
finish_time=`date +"%Y-%m-%d %H:%M:%S"`
#collect_backup_info
else
# 增量备份失败
# 删除备份目录
rm -rf ${backup_dir}/${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day}
# 记录错误日志
logging_backup_err $increment_prefix
# 执行信息入库函数
bk_status='failure'
#finish_time=${finish_time}
finish_time=`date +"%Y-%m-%d %H:%M:%S"`
#collect_backup_info
fi
;;
esac
}
run
4.备份配置
# cat conf/mysql_increment_hot_backup.conf
# mysql 用户名
##使用专用备份用户进行备份
## MYSQL>create user xtrabackup@'localhost' identified by 'pass';
## MYSQL>grant reload,lock tables,replication client,create tablespace,process,super on *.* to xtrabackup@'localhost' ;
## MYSQL>FLUSH PRIVILEGES;
## 可去掉 grant create,insert,select on percona_schema.* to xtrabackup@'localhost' ;
#
# mysql 用户名
user=xtrabackup
# mysql 密码
password="pass"
# 备份存放路径
backup_dir=/data/MySQL_Data_Backup
# percona-xtrabackup 备份软件路径
xtrabackup_dir=/usr/local/xtrabackup
# 全备是在一周的第几天
full_backup_week_day=1
# 全量备信息名称 前缀
full_backup_prefix=full
# 增量备信息名称 前缀
increment_prefix=incr
# mysql配置文件
mysql_conf_file=/etc/my.cnf
# 错误日志文件(更具此文件知道备份是否成功)
# format:
# {week_day:1,dir:full/incr_2015-12-29_00-00-00_7,type:full/incr,date:2015-12-30}
error_log=../var/mysql_increment_hot_backup.err
# 索引文件
# format:
# {week_day:1,dir:full/incr_2015-12-29_00-00-00_7,type:full/incr}
index_file=../var/mysql_increment_hot_backup.index
5.mysql8添加备份权限的用户
DROP USER 'xtrabackup'@'localhost';
# 创建用户并授权
create user xtrabackup@'localhost' identified by "pass";
grant selecton *.* to xtrabackup@'localhost';
grant file on *.* to xtrabackup@'localhost';
grant show view on *.* to xtrabackup@'localhost';
grant lock tables on *.* to xtrabackup@'localhost';
grant trigger on *.* to xtrabackup@'localhost';
grant EVENT on *.* to xtrabackup@'localhost';
grant reload on *.* to xtrabackup@'localhost';
GRANT BACKUP_ADMIN ON *.* TO xtrabackup@'localhost';
grant process on *.* to xtrabackup@'localhost';
grant super on *.* to xtrabackup@'localhost';
grant Replication client on *.* to xtrabackup@'localhost';
GRANT SELECT ON performance_schema.variables_info TO 'xtrabackup'@'localhost'; # For release 8.0.16 and later
GRANT SELECT ON performance_schema.* TO 'xtrabackup'@'localhost'; # For release 8.0.16 and later
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'xtrabackup'@'localhost';
GRANT CREATE, INSERT, DROP, UPDATE, SELECT, ALTER ON mysql.backup_history TO 'xtrabackup'@'localhost';
GRANT SELECT ON performance_schema.replication_group_members TO 'xtrabackup'@'localhost';
ALTER USER xtrabackup@'localhost' IDENTIFIED WITH mysql_native_password BY 'pass';
ALTER USER `xtrabackup`@`localhost` PASSWORD EXPIRE NEVER;
ALTER USER `xtrabackup`@`localhost` WITH MAX_USER_CONNECTIONS 20;
flush privileges;
6.计划任务
#backup mysql databases
04 10 * * * /bin/bash /usr/local/worksh/xtrabackup_cron/bin/mysql_increment_hot_backup.sh > /dev/null 2>&1
