[root@data-20-2 ~]
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:XRASsJgUYiLUuJqXe5zw5QKz3PjqocvQ5dBwsRpHui8 root@data-20-2
The key's randomart image is:
+---[RSA 2048]----+
|+.= =...o.o. |
|.+ * = . . . |
| = * . . |
| . O . . |
|..+.o S . |
|o.*= . |
|.oE@o+ |
|o.=oB . |
|o+o+.. |
+----[SHA256]-----+
[root@data-20-2 ~]# ssh-copy-id mysqlbackup@192.168.20.3
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.20.3 (192.168.20.3)' can't be established.
ECDSA key fingerprint is SHA256:rRjbzDPuw8l3yj+XYvN6oqpijFKOqZSPWHCDR2KGwJM.
ECDSA key fingerprint is MD5:c3:d8:f8:5d:d5:17:be:d7:66:7f:34:d2:34:3d:64:58.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
mysqlbackup@192.168.20.3's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'mysqlbackup@192.168.20.3'"
and check to make sure that only the key(s) you wanted were added.
[root@data-20-2 ~]# ssh mysqlbackup@192.168.20.3 "date"
Tue Mar 20 02:04:20 CST 2018
# 新增脚本
[root@data-20-2 ~]# cd /alidata/xtrabackup_cron/
[root@data-20-2 xtrabackup_cron]# ls
bin conf log readme.md var
[root@data-20-2 xtrabackup_cron]# cd bin
[root@data-20-2 bin]# ls
mysql_increment_hot_backup.sh
[root@data-20-2 bin]# cp mysql_increment_hot_backup.sh mysql_increment_hot_backup2.sh
[root@data-20-2 bin]# vim mysql_increment_hot_backup2.sh
[root@data-20-2 bin]# > mysql_increment_hot_backup2.sh
[root@data-20-2 bin]# vim mysql_increment_hot_backup2.sh
[root@data-20-2 bin]# cat mysql_increment_hot_backup2.sh
#!/usr/bin/env bash
# Program: MySQL 增量备份脚本 使用 percona xtrabackup
# Date : 2018-03-19
cd /alidata/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`
# 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`
# 创建相关目录
log_dir=../log
var_dir=../var
mkdir -p $backup_dir
mkdir -p $log_dir
mkdir -p $var_dir
# 全量备份
function full_backup() {
backup_folder=${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day}
mkdir -p $backup_dir/$backup_folder
$xtrabackup_dir/bin/innobackupex \
--defaults-file=$mysql_conf_file \
--user=$user \
--password=$password \
--no-timestamp \
$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/innobackupex \
--defaults-file=$mysql_conf_file \
--user=$user \
--password=$password \
--no-timestamp \
--incremental \
$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)
}
# send索引文件
function send_index_file_to_remote() {
scp $index_file mysqlbackup@192.168.20.3:/alidata/backup/mysql_increment_hot_backup.index_${backup_date}_${backup_time}_${backup_week_day}
}
# 添加索引, 索引记录了当前最新的备份
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() {
tar -jcf ${1}.tar.bz2 $1
}
# 发送备份到远程
function send_backup_to_remote() {
scp $1 mysqlbackup@192.168.20.3:/alidata/backup/
}
# 判断是应该全备还是增量备份
# 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
else
backup_type=1
fi
if [ ! -n "`cat $index_file`" ]; then
backup_type=0
fi
return $backup_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 )
# 全量备份
full_backup
backup_ok=$?
if [ 0 -eq "$backup_ok" ]; then
# 全备成功
# 打包最新备份
tar_backup_file ${backup_dir}/${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day}
#echo ${backup_dir}/${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day}
# 将tar备份发送到远程
send_backup_to_remote ${backup_dir}/${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day}.tar.bz2
# delete tar
rm -rf ${backup_dir}/${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day}.tar.bz2
# 备份索引文件
backup_index_file
# 清除之前的备份
delete_before_backup
# 清除索引文件
purge_index_from_file
# 添加索引, 索引记录了当前最新的备份
append_index_to_file $full_backup_prefix
# 发送索引文件到远程
send_index_file_to_remote
else
# 全备失败
# 删除备份目录
rm -rf ${backup_dir}/${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day}
# 记录错误日志
logging_backup_err $full_backup_prefix
fi
;;
1 )
# 增量备份
increment_backup
backup_ok=$?
if [ 0 -eq "$backup_ok" ]; then
# 增量备份成功
# 打包最新备份
tar_backup_file ${backup_dir}/${increment_prefix}_${backup_date}_${backup_time}_${backup_week_day}
#echo ${backup_dir}/${increment_prefix}_${backup_date}_${backup_time}_${backup_week_day}
# 将tar备份发送到远程
send_backup_to_remote ${backup_dir}/${increment_prefix}_${backup_date}_${backup_time}_${backup_week_day}.tar.bz2
# delete tar
rm -rf ${backup_dir}/${increment_prefix}_${backup_date}_${backup_time}_${backup_week_day}.tar.bz2
# 添加索引, 索引记录了当前最新的备份
append_index_to_file $increment_prefix
# 发送索引文件到远程
send_index_file_to_remote
else
# 增量备份失败
# 删除备份目录
rm -rf ${backup_dir}/${increment_prefix}_${backup_date}_${backup_time}_${backup_week_day}
# 记录错误日志
logging_backup_err $increment_prefix
fi
;;
esac
}
run
# 测试新脚本运行
[root@data-20-2 bin]# ll /alidata/backup/
total 4
drwxr-xr-x. 22 root root 4096 Mar 19 01:59 full_2018-03-19_01-59-02_1
[root@data-20-2 bin]# cat ../var/mysql_increment_hot_backup.index
{week_day:1, dir:full_2018-03-19_01-59-02_1, type:full, date:2018-03-19}
[root@data-20-2 bin]# bash mysql_increment_hot_backup2.sh
tar: Removing leading `/' from member names
full_2018-03-19_22-08-04_1.tar.bz2 100% 1398KB 88.7MB/s 00:00
mysql_increment_hot_backup.index 100% 100 133.0KB/s 00:00
[root@data-20-2 bin]
total 4
drwxr-xr-x. 22 root root 4096 Mar 19 22:08 full_2018-03-19_22-08-04_1
[root@data-20-2 bin]
{week_day:1, dir:full_2018-03-19_22-08-04_1, type:full, date:2018-03-19}
[root@data-20-2 bin]
Mon Mar 19 22:08:50 CST 2018
[mysqlbackup@app-20-3 backup]$ ll
total 1404
-rw-r--r--. 1 mysqlbackup mysqlbackup 1431932 Mar 20 02:09 full_2018-03-19_22-08-04_1.tar.bz2
-rw-r--r--. 1 mysqlbackup mysqlbackup 100 Mar 20 02:09 mysql_increment_hot_backup.index_2018-03-19_22-08-04_1
[root@data-20-2 bin]
59 01 * * * /bin/bash /alidata/xtrabackup_cron/bin/mysql_increment_hot_backup2.sh
|