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