mysql备份脚本
1、创建保存mysql备份状态的表。
CREATE TABLE `mysql_backup_status` ( `id` int(11) NOT NULL AUTO_INCREMENT comment '自增id', `mysql_host` varchar(20) NOT NULL DEFAULT 'x' COMMENT '备份mysql主机IP', `mysql_port` int(11) DEFAULT NULL COMMENT '备份myql实例端口', `start_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' comment '备份开始时间', `end_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' comment '备份完成时间', `backup_file_name` varchar(100) DEFAULT NULL COMMENT '备份文件名字', `backup_file_size` int(11) NOT NULL DEFAULT '0' COMMENT '备份文件大小，单位Mb', `backup_remote_server` varchar(20) NOT NULL COMMENT '远端备份服务IP', `backup_file_dir_remote` varchar(64) DEFAULT NULL COMMENT 'mysql备份远程存放目录：/backup', `transfer_end_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '备份文件传至备份服务器端完成时间', `backup_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '备份状态是否成功:0,失败，1，成功，2，文件传输失败，3文件传输成功', PRIMARY KEY (`id`), KEY `idx_start_time` (`start_time`), KEY `idx_host_port` (`mysql_host`,`mysql_port`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='mysql数据库备份状态表';
2、备份配置文件示例
mysql_host=192.168.1.1 mysql_port=3306 mysql_username=root mysql_password=root mysql_socket=/home/data/my3306/socket/mysqld.sock run_thread_num=4 compress_thread_num=4 mysql_conf_file=/etc/mysql/my3306.cnf mysql_backup_dir=/backup from_email=329003748@qq.com mail_auth_code=xxxxxxx smtp_server=smtp.qq.com to_email=616793474@qq.com save_num=1 BWLIMIT=50000 remote_backup_host=192.168.1.2 rsync_dir=mysql meta_db_host=192.168.1.3 meta_db_port=9999 meta_db=db_backup meta_user=bak meta_password=bak
说明：save_num表示保留最新成功的备份文件在本地
4、备份脚本
export PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/local/mysql5729/bin
#备份开始前设置备份状态为0,表示失败
backup_status=0
MY=$(which mysql)
function get_backup_info()
{
db_backups_conf="/opt/db_backups_conf.txt"
if [ -f "${db_backups_conf}" ];then
echo $(date +'%Y-%m-%d %H:%M:%S')"数据库配置信息文件存在，开始进行数据备份" >> mysqlbak-${mysql_host}-${mysql_port}-${backup_date}.log
#获取等号前内容，作为map中的Key值
dbArrOne=($(awk -F'[=]' '{print $1}' ${db_backups_conf}))
#获取等号后内容，作为map中的value值
dbArrTwo=($(awk -F'[=]' '{print $2}' ${db_backups_conf}))
#创建一个空map
declare -A map=()
#通过循环，将db_backups_conf配置文件中的信息存储在map中
for((i=0;i<${#dbArrOne[@]};i++))
do
map[${dbArrOne[i]}]=${dbArrTwo[i]}
done
fi
##########获取备份相关信息#################
mysql_host=${map["mysql_host"]}
mysql_port=${map["mysql_port"]}
mysql_username=${map["mysql_username"]}
mysql_password=${map["mysql_password"]}
mysql_socket=${map["mysql_socket"]}
run_thread_num=${map["run_thread_num"]}
compress_thread_num=${map["compress_thread_num"]}
mysql_conf_file=${map["mysql_conf_file"]}
mysql_backup_dir=${map["mysql_backup_dir"]}
##########获取邮件告警相关信息#################
from_email=${map["from_email"]}
mail_auth_code=${map["mail_auth_code"]}
smtp_server=${map["smtp_server"]}
to_email=${map["to_email"]}
##########备份文件传到远端备份服务器相关信息##########
save_num=${map["save_num"]}
BWLIMIT=${map["BWLIMIT"]}
remote_backup_host=${map["remote_backup_host"]}
rsync_dir=${map["rsync_dir"]}
#########元数据库相关信息#################
meta_db_host=${map["meta_db_host"]}
meta_db_port=${map["meta_db_port"]}
meta_db=${map["meta_db"]}
meta_user=${map["meta_user"]}
meta_password=${map["meta_password"]}
}
function update_meta_info()
{
MYSQL_CLIENT="$MY -h$meta_db_host -P$meta_db_port -u$meta_user -p$meta_password $meta_db -N"
}
function get_mysql_role()
{
check_read_only=$($MY -u${mysql_username} -p${mysql_password} -S ${mysql_socket} -Nse "select @@read_only;")
if [[ $check_read_only -eq 1 ]];then
mysql_role="slave"
else
mysql_role="master"
fi
}
function exec_mysql_backup()
{
backup_date=$(date +"%F")
start_time=$(date "+%F %T")
transfer_file=mysqlbak-${mysql_role}-${mysql_host}-${mysql_port}-${backup_date}.xbstream
sql1="insert into mysql_backup_status(mysql_host,mysql_port,start_time,backup_file_name)
values(\"$mysql_host\",\"$mysql_port\",\"$start_time\",\"$transfer_file\");"
echo "$sql1" | $MYSQL_CLIENT
if [[ $mysql_role == "master" ]];then
INNOBACKUPEX=$(which innobackupex)
$INNOBACKUPEX --defaults-file=${mysql_conf_file} --user=${mysql_username} --password=${mysql_password} \
--socket=${mysql_socket} --parallel=${run_thread_num} \
--compress --compress-threads=${compress_thread_num} \
--no-version-check \
--stream=xbstream >mysqlbak-${mysql_host}-${mysql_port}-${backup_date}.log 2>&1 ${mysql_backup_dir} > ${mysql_backup_dir}/${transfer_file}
else
INNOBACKUPEX=$(which innobackupex)
$INNOBACKUPEX --defaults-file=${mysql_conf_file} --user=${mysql_username} --password=${mysql_password} \
--socket=${mysql_socket} --slave-info --parallel=${run_thread_num} \
--compress --compress-threads=${compress_thread_num} \
--no-version-check \
--stream=xbstream >mysqlbak-${mysql_host}-${mysql_port}-${backup_date}.log 2>&1 ${mysql_backup_dir} > ${mysql_backup_dir}/${transfer_file}
fi
if [[ $? -eq 0 ]];then
end_time=$(date "+%F %T")
file_size=$(du -sm ${mysql_backup_dir}/${transfer_file} | awk '{print $1}')
sql2="update mysql_backup_status set end_time=\"$end_time\",backup_file_size=$file_size
,backup_status=1,backup_remote_server=\"$remote_backup_host\",backup_file_dir_remote=\"$rsync_dir\"
where mysql_host=\"$mysql_host\" and mysql_port=\"$mysql_port\" and start_time=\"$start_time\";"
echo "$sql2" | $MYSQL_CLIENT
fi
}
function send_mail_notice()
{
if [[ $1 -eq 0 ]];then
title="MySQL-${mysql_host}:${mysql_port}数据库备份异常告警"
body="MySQL-${mysql_host}:${mysql_port}数据库备份异常告警"
elif [[ $1 -eq 2 ]];then
title="MySQL-${mysql_host}:${mysql_port}数据库备份正常,远端传输文件失败"
body="MySQL-${mysql_host}:${mysql_port}数据库备份正常,远端传输文件失败"
elif [[ $1 -eq 3 ]];then
title="MySQL-${mysql_host}:${mysql_port}数据库备份正常,远端传输文件成功"
body="MySQL-${mysql_host}:${mysql_port}数据库备份正常,远端传输文件成功"
fi
sendEmail -o tls=yes \
-f "$from_email" -s "$smtp_server" -u "$title" \
-o message-content-type=text -o message-charset=utf8 \
-t "$to_email" -xu "$from_email" -xp "$mail_auth_code" -m "$body"
}
function rsync_remote_server()
{
RSYNC=$(which rsync)
echo "传输备份文件${mysql_backup_dir}/${transfer_file}到备份服务器"
$RSYNC -av --bwlimit=$BWLIMIT ${mysql_backup_dir}/${transfer_file} root@${remote_backup_host}::${rsync_dir}/
if [[ $? -eq 0 ]];then
backup_status=3
else
backup_status=2
fi
transfer_end_time=$(date "+%F %T")
echo "更新传输备份文件到备份服务器后元数据信息" >> mysqlbak-${mysql_host}-${mysql_port}-${backup_date}.log
sql3="update mysql_backup_status set transfer_end_time=\"$transfer_end_time\",backup_status=$backup_status
where mysql_host=\"$mysql_host\" and mysql_port=\"$mysql_port\" and start_time=\"$start_time\";"
echo "$sql3" | $MYSQL_CLIENT
}
function del_expire_backup()
{
let del_num=save_num+1
ls -lt ${mysql_backup_dir}/mysqlbak-${mysql_host}-${mysql_port}-*.xbstream |\
grep -v '^total' | sed -n ''$del_num',$p' | awk '{print $9}' > files
if [[ ! -s files ]];then
echo "当前时间$(date +"%F"),没有过期的备份文件可以删除" >> mysqlbak-${mysql_host}-${mysql_port}-${backup_date}.log
else
for f in `cat files`
do
rm -rf $f
echo "当前时间$(date +"%F"),已删除删除备份$f" >> mysqlbak-${mysql_host}-${mysql_port}-${backup_date}.log
done
fi
}
function main()
{
get_backup_info
get_mysql_role
update_meta_info
exec_mysql_backup
if [[ $? -eq 0 ]];then
backup_status=1
rsync_remote_server
if [[ $? -eq 0 ]];then
backup_status=3
send_mail_notice $backup_status
else
backup_status=2
send_mail_notice $backup_status
fi
else
send_mail_notice $backup_status
fi
del_expire_backup
}
main
欢迎多交流（QQ:616793474/329003748），谢谢！
浙公网安备 33010602011771号