其实主要是想偷懒，所以自己创建了一个MySQL数据库自动备份脚本。我每次写备份脚本，只需要传递参数即可，仅供参考。1.MySQL备份模板（上传至下载平台）#####################################copyrightbyhwb#DATE:2020-12-03#Purpose:MYSQL备份模板##################################定义db_host=localhostdb_port=3306db_name=mysql_proddb_user=rootdb_pwd=passwordbackup_path="/data/backup"#view,function,procedure,event,triggeroutput_type='view,function,procedure,event,trigger'today=`date+"%Y%m%d-%H%M%S"`data_file=$backup_path/$db_name$today.sqlobject_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/functionsexportPATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/binsource/etc/profile#Requireroottorunthisscript.[$(id-u)-gt0]&&echo"请使用root用户执行此脚本！"&&exit1[-d$backup_path]||mkdir-p$backup_path#[!-n"$5"]&&echo-e"\033[31mUsage:$0IP端口实例名用户名'password'\033[0m"&&exit1functionmysql_backup(){echo""echo-e"\033[33m**************************************************mysql数据库备份*******************************************************\033[0m“echo-e”\033[36m**************将数据库数据备份到$data_file**************\033[0m"#ApartialdumpfromaserverthathasGTIDswillbydefaultincludetheGTIDsoffalltransactions,eventhosethatchangedsuppressedpartssofthedatabase.Ifyoudon'twanttotorestoreGTIDs,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_fileif[$?-eq0];thenaction[$today]>>>完成数据库${db_name}数据备份"/bin/trueecho[$today]>>>完成数据库${db_name}数据备份">>${log_file}elseaction[$today]>>>数据库${db_name}备份失败，请检查相关配置！"/bin/falseecho"[$today]>>>数据库${db_name}备份失败，请检查相关配置configuration!">>${log_file}exit1fiecho-e"\033[36m*******备份${db_name}函数、视图等定义到$object_file*************\033[0m"cat>$object_file<>$object_fileecho"">>$object_file#viewif[[$output_type==*"view"*]]thenecho"------------------------------------------------------">>$object_fileecho"--views">>$object_fileecho"------------------------------------------------------------》>>$object_file#MySQL不输出列名可以使用-N或--skip-column-names参数$mysql_cmd--skip-column-名称\-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_filefi#函数if[[$output_type==*"函数"*]]thenecho"--------------------------------------------------------------">>$object_fileecho"--函数">>$object_fileecho"----------------------------------------------------------">>$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_filefi#存储过程if[[$output_type==*"程序"*]]thenecho"--------------------------------------------------------------">>$object_fileecho"--过程">>$object_fileecho"------------------------------------------------------------">>$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_filefi#事件if[[$output_type==*"事件"*]]thenecho"--------------------------------------------------------------">>$object_fileecho"--事件">>$object_fileecho"------------------------------------------------------------">>$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_filefi#触发器if[[$output_type==*"触发"*]]thenecho"--------------------------------------------------------------">>$object_fileecho"--触发器">>$object_fileecho"----------------------------------------------------------">>$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_filefi#^M,youneedtotypeCTRL-VandthenCTRL-Msed-i"s/\^M//g"$object_file#Cleanupexpiredbackupfind${backup_path}-mtime+10-typef-name'*.sql'-execrm-f{}\;if[$?-eq0];thenaction[$today]>>>completedatabase${db_name}expiredbackupcleanup"/bin/trueecho"[$today]>>>完成数据库${db_name}过期备份清理">>${log_file}elseaction[$today]>>>数据库${db_name}过期备份清理失败，请检查相关configuration!"/bin/falseecho"[$today]>>>数据库${db_name}过期备份清理失败，请检查相关配置！">>${log_file}exit1fiecho-e"\033[33m************************************************已完成${db_name}数据库备份******************************************************\033[0m"cat>/tmp/mysql_backup.log<>/var/spool/cron/rootif[$?-eq0];thenecho""action"[$mysql_date]>>>完成数据库备份定时任务配置"/bin/truelseecho""action"[$mysql_date]>>>定时任务配置失败，请检查相关配置！"/bin/falsefiecho""echo"|------------------------------------定时任务内容---------------------------------------|"crontab-lecho""echo"|---------------Mysql备份脚本[$mysql_path/$script_name]内容如下--------------|"cat$mysql_path/$script_nameecho-e"\033[33米***********************************完成mysql数据库备份脚本配置*****************************************\033[0m"echo""}bk_mysqlbackup3.基于蓝鲸平台测试自动备份脚本测试成功..4.测试备份脚本是否正常有效且成功完成备份..
