1.备份工具：percona-xtrabackup-8.0.7-Linux-x86_642.目录结构[/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-183.备份数据库脚本# vim mysql_increment_hot_backup.sh#!/usr/bin/env bash# Program: MySQL 增量备份脚本 使用 percona xtrabackup# Author : chenhao# Date : 2015-12-29##进入程序目录## 脚本中变量存在空 就退出set -ucd /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=../logvar_dir=../varmkdir -p $backup_dirmkdir -p $log_dirmkdir -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}"# 拼接入库SQLaget_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:incrfunction 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}run4.备份配置# 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.index5.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 laterGRANT SELECT ON performance_schema.* TO 'xtrabackup'@'localhost'; # For release 8.0.16 and laterGRANT 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 databases04 10 * * * /bin/bash /usr/local/worksh/xtrabackup_cron/bin/mysql_increment_hot_backup.sh > /dev/null 2>&1
