!/bin/bash

export backup_home=/db_mysql/backup_mysql/backup/

获取当前日期时间

currdy=$(date +”%Y%m%d”)
currdt=$(date +”%Y%m%d_%H%M%S”)
myip=$(hostname -I | grep -oE ‘[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}’ | head -n 1)

定义路径和变量

export tgtbackup_file=$backup_home/backup${myip}_${currdy}_3306.tar.gz

export tgtclone_dir=$backup_home/backup_data${currdy}

export logfile=”$backuphome/logs/mysqlbackup${myip}_${currdy}_3306.log”

export binlog_path=/db_mysql/bin_log/

export tgtbinlog_backup_file=$backup_home/backup_log${currdy}_3306_lastbinlog.tar.gz

设置MySQL连接参数

bkdb_login_bin=”mysql -Danshen -N -s”
localdb_login_bin=”mysql —login-path=dbadmin”

日志记录函数

mylog() {
if [ $# -eq 2 ]; then
log_cont=$1
step=$2
else
log_cont=$1
step=’’
fi
echo “$(date +’%Y%m%d %H:%M:%S’):${log_cont}” >>$logfile
echo “${log_cont}” | $bkdb_login_bin -e “insert into mysql_backup_log(mysql_ip,state,batchno,step) values(‘${myip}’,’${log_cont}’,’${currdy}’,’${step}’)”
}

退出函数

myexit() {
mylog “EXIT NOW” “STEP_EXIT”
exit 1
}

检查配置和环境

check_env() {
if [ ! -d “$backup_home” ]; then
mylog “Target backup_home:$backup_home not exists”
myexit
fi
}

检查Clone插件状态

check_clone_plugin() {
if $localdb_login_bin -N -s -e “select plugin_status from information_schema.plugins where plugin_name=’clone’” | grep -w ‘ACTIVE’ > /dev/null; then
mylog “Clone plugin already in ACTIVE status”
else
$localdb_login_bin -N -s -e “INSTALL PLUGIN clone SONAME ‘mysql_clone.so’”
if $localdb_login_bin -N -s -e “select plugin_status from information_schema.plugins where plugin_name=’clone’” | grep -w ‘ACTIVE’ > /dev/null; then
mylog “Clone plugin installed and in ACTIVE status”
else
mylog “The clone plugin install failed. Do exit 1!”
myexit
fi
fi
}

数据库克隆

clone_db() {
mylog “Flush logs”
$localdb_login_bin -e “flush logs”

mylog "GTID info before backup:"
$localdb_login_bin -e "show master status/G" >>$logfile

mylog "Starting clone local data to $tgt_clone_dir ..."
$localdb_login_bin -e "clone local data directory = '$tgt_clone_dir'"

mylog "Clone command done , And current GTID info as fellow:"
$localdb_login_bin -e "show master status/G" >>$logfile

mylog "Clone state for backup:"
$localdb_login_bin -e "SELECT * from performance_schema.clone_status/G" >>$logfile

mylog "Clone progress for backup:"
$localdb_login_bin -e "SELECT * from performance_schema.clone_progress/G" >>$logfile

if $localdb_login_bin -N -s -e "SELECT state from performance_schema.clone_status where trim(TRAILING '/' FROM DESTINATION)='$tgt_clone_dir' order by BEGIN_TIME desc limit 1" | grep -w 'Completed' > /dev/null; then
    mylog "The clone operation completed successfully."
else
    mylog "The clone operation failed. Do exit 1!"
    myexit
fi

}

备份日志文件

backup_binlog() {
mylog “Full mysql binlog list:”
ls -trl ${binlog_path} >>$logfile
mylog “Starting backup binlogs generated within 1min ~ 25 hours …”

flush logs before backup binlog, for tar ERROR: file changed as we read it

$localdb_login_bin -e “flush logs”

within 1min ~ 25 hours binlog

cd ${binlog_path}
find . -name “mysql-bin.*” -mmin -1500 -mmin +1 | xargs tar -czvf ${tgt_binlog_backup_file} >/dev/null 2>>$logfile
cmd_ret=$?
if [ “x${cmd_ret}” = “x0” ]; then
mylog “Backup mysql binlog successfully.”
else
mylog “Backup mysql binlog failed. Do exit 1!”
myexit
fi
}

压缩备份文件

compress_backup_file() {
mylog “Starting compress clone file …”
if [ -d “$tgt_clone_dir” ]; then
cd “$tgt_clone_dir”
tar -czvf “$tgt_backup_file” . > /dev/null 2>>$logfile
if [ $? -eq 0 ]; then
cd “$backup_home”
mylog “Compress clone file successfully.”
test -d “$tgt_clone_dir” && rm -rf “$tgt_clone_dir”
else
mylog “Compress clone file failed. Do exit 1!”
myexit
fi
else
mylog “Target tgt_clone_dir:$tgt_clone_dir not a valid directory. Do exit 1!”
myexit
fi
}

主备份流程

main_backup_flow() {
check_env
check_clone_plugin
clone_db
backup_binlog
compress_backup_file
mylog “Backup process completed.”
}

执行主流程

main_backup_flow

结束日志记录

end_log() {
mylog “Backup and compress process completed at $(date +%Y-%m-%d/ %H:%M:%S)”
}

调用结束日志记录函数

end_log

json 标准输出

json_response() {
local code=$1
local message=$2
echo “{/”code/”:$code, /”message/”:/”$message/”}”
}

json_response 0 “Backup process completed successfully.”

签到成功

CNDBA社区

利用Mysql-clone插件备份MySQL数据库脚本