分享一个自动编写MySQL数据库备份脚本，值得收藏

其实主要是为了偷懒，所以就搞了一个自动编写MySQL数据库备份脚本，每次写备份脚本传参就可以了，仅供参考。

其实主要是为了偷懒，所以就搞了一个自动编写MySQL数据库备份脚本，每次写备份脚本传参就可以了，仅供参考。

1. MySQL备份模板(上传到下载平台)

#!/bin/bash
#################################
#copyrightbyhwb
#DATE:2020-12-03
#用途：MYSQL备份模板
#################################
#定义
db_host=localhost
db_port=3306
db_name=mysql_prod
db_user=root
db_pwd=password
backup_path="/data/backup"
#view,function,procedure,event,trigger
output_type='view,function,procedure,event,trigger'
today=`date+"%Y%m%d-%H%M%S"`
data_file=$backup_path/$db_name$today.sql
object_file="${backup_path}/obj_${db_name}$today.sql"
log_file="/home/scripts/mysql_backup.log"
mysql_cmd="mysql-u${db_user}-p${db_pwd}-h${db_host}-P${db_port}"
mysqldump_cmd="mysqldump-u${db_user}-p${db_pwd}-h${db_host}-P${db_port}$db_name"
#调用函数库
[-f/etc/init.d/functions]&&source/etc/init.d/functions
exportPATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
source/etc/profile
#Requireroottorunthisscript.
[$(id-u)-gt0]&&echo"请用root用户执行此脚本！"&&exit1
[-d$backup_path]||mkdir-p$backup_path
#[!-n"$5"]&&echo-e"\033[31mUsage:$0IP端口实例名用户名'密码'\033[0m"&&exit1
functionmysql_backup()
{
echo""
echo-e"\033[33m***********************************************mysql数据库备份****************************************************\033[0m"
echo-e"\033[36m**************备份数据库数据到$data_file**************\033[0m"
#ApartialdumpfromaserverthathasGTIDswillbydefaultincludetheGTIDsofalltransactions,eventhosethatchangedsuppressedpartsofthedatabase.Ifyoudon'twanttorestoreGTIDs,pass--set-gtid-purged=OFF.Tomakeacompletedump,pass--all-databases--triggers--routines--events
$mysqldump_cmd--single_transaction-R-E--flush-logs--master-data=2--set-gtid-purged=OFF>$data_file
if[$?-eq0];then
action"[$today]>>>完成数据库${db_name}数据备份"/bin/true
echo"[$today]>>>完成数据库${db_name}数据备份">>${log_file}
else
action"[$today]>>>数据库${db_name}备份失败，请检查相关配置！"/bin/false
echo"[$today]>>>数据库${db_name}备份失败，请检查相关配置！">>${log_file}
exit1
fi
echo-e"\033[36m*******备份${db_name}函数、视图等定义到$object_file***********\033[0m"
cat>$object_file<<EOF
ouputobject‘sdefinitionfordatabase"$db_name"
ouputtime:$(date"+%Y-%m-%d%H:%M:%S")
ouputobjecttype:$output_type
EOF
echo"">>$object_file
echo"">>$object_file
#视图
if[[$output_type==*"view"*]]
then
echo"--------------------------------------------------------------">>$object_file
echo"--views">>$object_file
echo"--------------------------------------------------------------">>$object_file
#让MySQL不输出列名可以用-N或者--skip-column-names参数
$mysql_cmd--skip-column-names\
-e"selectconcat('SHOWCREATEVIEW',table_schema,'.',table_name,';')frominformation_schema.viewswheretable_schema='$db_name'"|\
sed's/;/\\G/g'|$mysql_cmd$db_name|\
sed's/CreateView:/kk_begin\n/g'|sed's/[]*character_set_client:/;\nkk_end/g'|\
sed-n'/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}'>>$object_file
fi
#函数
if[[$output_type==*"function"*]]
then
echo"--------------------------------------------------------------">>$object_file
echo"--function">>$object_file
echo"--------------------------------------------------------------">>$object_file
$mysql_cmd--skip-column-names\
-e"selectconcat('SHOWCREATEFUNCTION',routine_schema,'.',routine_name,';')frominformation_schema.routineswhereroutine_schema='$db_name'andROUTINE_TYPE='FUNCTION'"|\
sed's/;/\\G/g'|$mysql_cmd$db_name|\
sed's/CreateFunction:/kk_begin\ndelimiter$$\n/g'|sed's/[]*character_set_client:/$$\ndelimiter;\nkk_end/g'|\
sed-n'/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}'>>$object_file
fi
#存储过程
if[[$output_type==*"procedure"*]]
then
echo"--------------------------------------------------------------">>$object_file
echo"--procedure">>$object_file
echo"--------------------------------------------------------------">>$object_file
$mysql_cmd--skip-column-names\
-e"selectconcat('SHOWCREATEPROCEDURE',routine_schema,'.',routine_name,';')frominformation_schema.routineswhereroutine_schema='$db_name'andROUTINE_TYPE='PROCEDURE'"|\
sed's/;/\\G/g'|$mysql_cmd$db_name|\
sed's/CreateProcedure:/kk_begin\ndelimiter$$\n/g'|sed's/[]*character_set_client:/$$\ndelimiter;\nkk_end/g'|\
sed-n'/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}'>>$object_file
fi
#事件
if[[$output_type==*"event"*]]
then
echo"--------------------------------------------------------------">>$object_file
echo"--event">>$object_file
echo"--------------------------------------------------------------">>$object_file
$mysql_cmd--skip-column-names\
-e"selectconcat('SHOWCREATEEVENT',EVENT_SCHEMA,'.',EVENT_NAME,';')frominformation_schema.eventswhereEVENT_SCHEMA='$db_name'"|\
sed's/;/\\G/g'|$mysql_cmd|\
sed's/CreateEvent:/kk_begin\ndelimiter$$\n/g'|sed's/[]*character_set_client:/$$\ndelimiter;\nkk_end/g'|\
sed-n'/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}'>>$object_file
fi
#触发器
if[[$output_type==*"trigger"*]]
then
echo"--------------------------------------------------------------">>$object_file
echo"--trigger">>$object_file
echo"--------------------------------------------------------------">>$object_file
$mysql_cmd--skip-column-names\
-e"selectconcat('SHOWCREATETRIGGER',TRIGGER_SCHEMA,'.',TRIGGER_NAME,';')frominformation_schema.triggerswhereTRIGGER_SCHEMA='$db_name';"|\
sed's/;/\\G/g'|$mysql_cmd$db_name|\
sed's/SQLOriginalStatement:/kk_begin\ndelimiter$$\n/g'|sed's/[]*character_set_client:/$$\ndelimiter;\nkk_end/g'|\
sed-n'/kk_begin/{:a;N;/kk_end/!ba;s/.*kk_begin\|kk_end.*//g;p}'>>$object_file
fi
#^M,youneedtotypeCTRL-VandthenCTRL-M
sed-i"s/\^M//g"$object_file
#清理过期备份
find${backup_path}-mtime+10-typef-name'*.sql'-execrm-f{}\;
if[$?-eq0];then
action"[$today]>>>完成数据库${db_name}过期备份清理"/bin/true
echo"[$today]>>>完成数据库${db_name}过期备份清理">>${log_file}
else
action"[$today]>>>数据库${db_name}过期备份清理失败，请检查相关配置！"/bin/false
echo"[$today]>>>数据库${db_name}过期备份清理失败，请检查相关配置！">>${log_file}
exit1
fi
echo-e"\033[33m**********************************************完成${db_name}数据库备份**********************************************\033[0m"
cat>/tmp/mysql_backup.log<<EOF
mysql地址：${db_host}
mysql端口：${db_port}
mysql实例名：${db_name}
数据备份文件：${data_file}
定义备份文件：${object_file}
EOF
cat/tmp/mysql_backup.log
echo-e"\e[1;31m以上信息保存在/tmp/mysql_backup.log文件下\e[0m"
echo-e"\033[33m*******************************************************************************************************************\033[0m"
echo""
}
mysql_backup

2. 自动编写MySQL数据库备份脚本

#!/bin/bash
###################################################################
#copyrightbyhwb
#DATE:2020-12-04
#用途：自动编写MySQL备份脚本
###################################################################
#脚本外变量
mysql_path="/home/scripts"
script_name="mysql_backup.sh"
mysql_date=`date+"%Y-%m-%d-%H:%M:%S"`
#脚本内变量
db_host="localhost"
db_port="53306"
db_name="dbname"
db_user="root"
db_pwd="xxxx"
backup_path="/data/backup"
bk_backup_path=`echo$backup_path|sed's:\/:\\\/:g'`
bk_db_pwd=`echo$db_pwd|sed's:\/:\\\/:g'`
#调用函数库
[-f/etc/init.d/functions]&&source/etc/init.d/functions
exportPATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
source/etc/profile
#root用户执行脚本
[$(id-u)-gt0]&&echo"请用root用户执行此脚本！"&&exit1
#判断目录是否存在
[-d$mysql_path]||mkdir-p$mysql_path
[-d$backup_path]||echo"mysql数据库备份目录[$backup_path]不存在，请确认参数是否正确！"
[-d$backup_path]||exit1
functionbk_mysqlbackup(){
echo""
echo-e"\033[33m**************************自动配置mysql数据库备份脚本[$mysql_path/$script_name]**************************\033[0m"
echo""
echo-e"\033[36mUsage:$0\033[0m"
echo""
[-f$mysql_path/$script_name]&&echo"${mysql_path}已存在脚本[${script_name}],请检查相关配置！"&&exit1
wgethttps://app.fslgz.com/portal/api/public/fs/association/file/downLoad?uploadId=784001405093478400-O/opt/mysql_backup_template.sh
mv/opt/mysql_backup_template.sh$mysql_path/$script_name
#处理windows传linux的脚本格式问题(注意空格位置不能多不能少)
vi+':w++ff=unix'+':q'${mysql_path}/${script_name}
sed-i"s/localhost/${db_host}/g"$mysql_path/$script_name
sed-i"s/3306/${db_port}/g"$mysql_path/$script_name
sed-i"s/mysql_prod/${db_name}/g"$mysql_path/$script_name
sed-i"s/root/${db_user}/g"$mysql_path/$script_name
sed-i"s/password/${bk_db_pwd}/g"$mysql_path/$script_name
sed-i"s#\/data\/backup#${bk_backup_path}#g"$mysql_path/$script_name
action"完成mysql备份脚本[$mysql_path/$script_name]编写！"/bin/true
chmodu+x$mysql_path/$script_name
#配置定时任务
echo"3011***/bin/bash${mysql_path}/${script_name}">>/var/spool/cron/root
if[$?-eq0];then
echo""
action"[$mysql_date]>>>完成数据库备份定时任务配置"/bin/true
else
echo""
action"[$mysql_date]>>>定时任务配置失败，请检查相关配置！"/bin/false
fi
echo""
echo"|------------------------------------定时任务内容------------------------------------|"
crontab-l
echo""
echo"|---------------mysql备份脚本[$mysql_path/$script_name]内容如下---------------|"
cat$mysql_path/$script_name
echo-e"\033[33m************************************完成mysql数据库备份脚本配置*****************************************\033[0m"
echo""
}
bk_mysqlbackup

3. 基于蓝鲸平台测试自动编写备份脚本

测试成功..

4. 测试备份的脚本是否有效

成功完成备份..