Table of Contents

概述

其實(shí)主要是為了偷懶，所以就搞了一個(gè)自動(dòng)編寫MySQL數(shù)據(jù)庫(kù)備份腳本，每次寫備份腳本傳參就可以了，僅供參考。

1、MySQL備份模板（上傳到下載平臺(tái)）

#!/bin/bash
#################################
# copyright by hwb
# 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 "


#調(diào)用函數(shù)庫(kù)
[ -f /etc/init.d/functions ] && source /etc/init.d/functions
export PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
source /etc/profile

#Require root to run this script.
[ $(id -u) -gt 0 ] && echo "請(qǐng)用root用戶執(zhí)行此腳本！" && exit 1


[ -d $backup_path ] || mkdir -p $backup_path


#[ ! -n "$5" ] && echo -e "33[31m Usage: $0 IP 端口 實(shí)例名 用戶名 '密碼'  33[0m" && exit 1


function mysql_backup() 
{
  echo ""
  echo -e "33[33m***********************************************mysql數(shù)據(jù)庫(kù)備份****************************************************33[0m" 
  
  echo -e "33[36m**************備份數(shù)據(jù)庫(kù)數(shù)據(jù)到$data_file**************33[0m"
  #A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events
  $mysqldump_cmd --single_transaction -R -E --flush-logs --master-data=2 --set-gtid-purged=OFF > $data_file
  
  if [ $? -eq 0 ];then
    action "[$today]>>>完成數(shù)據(jù)庫(kù)${db_name}數(shù)據(jù)備份" /bin/true
	echo "[$today]>>>完成數(shù)據(jù)庫(kù)${db_name}數(shù)據(jù)備份" >> ${log_file}
  else 
    action "[$today]>>>數(shù)據(jù)庫(kù)${db_name}備份失敗，請(qǐng)檢查相關(guān)配置！" /bin/false
	echo "[$today]>>>數(shù)據(jù)庫(kù)${db_name}備份失敗，請(qǐng)檢查相關(guān)配置！" >> ${log_file}
	exit 1
  fi
  
  
  echo -e "33[36m*******備份${db_name}函數(shù)、視圖等定義到$object_file***********33[0m"
  cat > $object_file<<EOF
ouput object‘s definition for database "$db_name"
ouput time: $(date "+%Y-%m-%d %H:%M:%S")
ouput object type: $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參數(shù)
	$mysql_cmd  --skip-column-names 
	-e "select concat('SHOW CREATE VIEW ',table_schema,'.',table_name,';') from information_schema.views where table_schema='$db_name'" |
	sed 's/;/\G/g' | $mysql_cmd $db_name |
	sed 's/Create View: /kk_beginn/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
 
  # 函數(shù)
  if [[ $output_type == *"function"* ]]
  then
	echo "-- ------------------------------------------------------------" >> $object_file
	echo "-- function" >> $object_file
	echo "-- ------------------------------------------------------------" >> $object_file
	$mysql_cmd --skip-column-names 
	-e "select concat('SHOW CREATE FUNCTION ',routine_schema,'.',routine_name,';') from information_schema.routines where routine_schema='$db_name' and ROUTINE_TYPE='FUNCTION'" |
	sed 's/;/\G/g' | $mysql_cmd $db_name |
	sed 's/Create Function: /kk_beginndelimiter $$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
 
  # 存儲(chǔ)過程
  if [[ $output_type == *"procedure"* ]]
  then
	echo "-- ------------------------------------------------------------" >> $object_file
	echo "-- procedure" >> $object_file
	echo "-- ------------------------------------------------------------" >> $object_file
	$mysql_cmd --skip-column-names 
	-e "select concat('SHOW CREATE PROCEDURE ',routine_schema,'.',routine_name,';') from information_schema.routines where routine_schema='$db_name' and ROUTINE_TYPE='PROCEDURE'" |
	sed 's/;/\G/g' | $mysql_cmd  $db_name |
	sed 's/Create Procedure: /kk_beginndelimiter $$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 "select concat('SHOW CREATE EVENT ',EVENT_SCHEMA,'.',EVENT_NAME,';') from information_schema.events where EVENT_SCHEMA='$db_name'" |
	sed 's/;/\G/g' | $mysql_cmd |
	sed 's/Create Event: /kk_beginndelimiter $$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
 
  # 觸發(fā)器
  if [[ $output_type == *"trigger"* ]]
  then
	echo "-- ------------------------------------------------------------" >> $object_file
	echo "-- trigger" >> $object_file
	echo "-- ------------------------------------------------------------" >> $object_file
	$mysql_cmd --skip-column-names 
	-e "select concat('SHOW CREATE TRIGGER ',TRIGGER_SCHEMA,'.',TRIGGER_NAME,';') from information_schema.triggers where TRIGGER_SCHEMA='$db_name';" |
	sed 's/;/\G/g' | $mysql_cmd $db_name|
	sed 's/SQL Original Statement: /kk_beginndelimiter $$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, you need to type CTRL-V and then CTRL-M
  sed -i "s/^M//g" $object_file

  #清理過期備份
  find ${backup_path}  -mtime +10  -type f -name '*.sql' -exec rm -f {} ;
  
  if [ $? -eq 0 ];then
    action "[$today]>>>完成數(shù)據(jù)庫(kù)${db_name}過期備份清理" /bin/true
	echo "[$today]>>>完成數(shù)據(jù)庫(kù)${db_name}過期備份清理" >> ${log_file}
  else 
    action "[$today]>>>數(shù)據(jù)庫(kù)${db_name}過期備份清理失敗，請(qǐng)檢查相關(guān)配置！" /bin/false
	echo "[$today]>>>數(shù)據(jù)庫(kù)${db_name}過期備份清理失敗，請(qǐng)檢查相關(guān)配置！" >> ${log_file}
	exit 1
  fi
  
  echo -e "33[33m**********************************************完成${db_name}數(shù)據(jù)庫(kù)備份**********************************************33[0m"
cat > /tmp/mysql_backup.log  << EOF
mysql地址：${db_host}
mysql端口：${db_port}
mysql實(shí)例名：${db_name}
數(shù)據(jù)備份文件：${data_file}
定義備份文件：${object_file}
EOF
  cat /tmp/mysql_backup.log
  echo -e "e[1;31m 以上信息保存在/tmp/mysql_backup.log文件下 e[0m"
  echo -e "33[33m*******************************************************************************************************************33[0m"
  echo ""
}


mysql_backup

mysql備份腳本怎么寫（如何對(duì)數(shù)據(jù)庫(kù)進(jìn)行備份）

2、自動(dòng)編寫MySQL數(shù)據(jù)庫(kù)備份腳本

#!/bin/bash#################################################################### copyright by hwb# DATE: 2020-12-04# 用途：自動(dòng)編寫MySQL備份腳本####################################################################腳本外變量mysql_path="/home/scripts"script_name="mysql_backup.sh"mysql_date=`date +"%Y-%m-%d-%H:%M:%S"`#腳本內(nèi)變量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'`#調(diào)用函數(shù)庫(kù)[ -f /etc/init.d/functions ] && source /etc/init.d/functionsexport PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/binsource /etc/profile#root用戶執(zhí)行腳本[ $(id -u) -gt 0 ] && echo "請(qǐng)用root用戶執(zhí)行此腳本！" && exit 1#判斷目錄是否存在[ -d $mysql_path ] || mkdir -p $mysql_path[ -d $backup_path ] || echo "mysql數(shù)據(jù)庫(kù)備份目錄[$backup_path]不存在，請(qǐng)確認(rèn)參數(shù)是否正確！"[ -d $backup_path ] || exit 1function bk_mysqlbackup(){  echo ""  echo -e "33[33m**************************自動(dòng)配置mysql數(shù)據(jù)庫(kù)備份腳本[$mysql_path/$script_name]**************************33[0m"  echo ""  echo -e "33[36m Usage: $0   33[0m"  echo ""    [ -f $mysql_path/$script_name ] && echo "${mysql_path}已存在腳本[${script_name}],請(qǐng)檢查相關(guān)配置！" && exit 1  wget https://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  chmod u+x $mysql_path/$script_name    #配置定時(shí)任務(wù)  echo "30 11 * * * /bin/bash  ${mysql_path}/${script_name} " >> /var/spool/cron/root    if [ $? -eq 0 ];then    echo ""    action "[$mysql_date]>>>完成數(shù)據(jù)庫(kù)備份定時(shí)任務(wù)配置" /bin/true  else     echo ""    action "[$mysql_date]>>>定時(shí)任務(wù)配置失敗，請(qǐng)檢查相關(guān)配置！" /bin/false  fi    echo ""  echo  "|------------------------------------定時(shí)任務(wù)內(nèi)容------------------------------------|"   crontab -l  echo ""  echo  "|---------------mysql備份腳本[$mysql_path/$script_name]內(nèi)容如下---------------|"   cat  $mysql_path/$script_name  echo -e "33[33m************************************完成mysql數(shù)據(jù)庫(kù)備份腳本配置*****************************************33[0m"  echo ""}bk_mysqlbackup

3、基于藍(lán)鯨平臺(tái)測(cè)試自動(dòng)編寫備份腳本

測(cè)試成功..

4、測(cè)試備份的腳本是否有效

成功完成備份..

后面會(huì)分享更多devops和DBA方面內(nèi)容，感興趣的朋友可以關(guān)注下..

版權(quán)聲明：本文內(nèi)容由互聯(lián)網(wǎng)用戶自發(fā)貢獻(xiàn)，該文觀點(diǎn)僅代表作者本人。本站僅提供信息存儲(chǔ)空間服務(wù)，不擁有所有權(quán)，不承擔(dān)相關(guān)法律責(zé)任。如發(fā)現(xiàn)本站有涉嫌抄襲侵權(quán)/違法違規(guī)的內(nèi)容，請(qǐng)發(fā)送郵件至2705686032@qq.com 舉報(bào)，一經(jīng)查實(shí)，本站將立刻刪除。原文轉(zhuǎn)載：原文出處：