分享一個自動編寫MySQL數據庫備份腳本，值得收藏

作者：波波說運維 2020-12-11 07:00:42

其實主要是為了偷懶，所以就搞了一個自動編寫MySQL數據庫備份腳本，每次寫備份腳本傳參就可以了，僅供參考。

1. MySQL備份模板(上傳到下載平臺)

#!/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 " 
 
 
#調用函數庫 
[ -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 "請用root用戶執行此腳本！" && exit 1 
 
 
[ -d $backup_path ] || mkdir -p $backup_path 
 
 
#[ ! -n "$5" ] && echo -e "\033[31m Usage: $0 IP 端口 實例名 用戶名 '密碼'  \033[0m" && exit 1 
 
 
function mysql_backup()  
{ 
  echo "" 
  echo -e "\033[33m***********************************************mysql數據庫備份****************************************************\033[0m"  
   
  echo -e "\033[36m**************備份數據庫數據到$data_file**************\033[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]>>>完成數據庫${db_name}數據備份" /bin/true 
    echo "[$today]>>>完成數據庫${db_name}數據備份" >> ${log_file} 
  else  
    action "[$today]>>>數據庫${db_name}備份失敗，請檢查相關配置！" /bin/false 
    echo "[$today]>>>數據庫${db_name}備份失敗，請檢查相關配置！" >> ${log_file} 
    exit 1 
  fi 
   
   
  echo -e "\033[36m*******備份${db_name}函數、視圖等定義到$object_file***********\033[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參數 
    $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_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 "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_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 "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_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 "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_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 "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_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, 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]>>>完成數據庫${db_name}過期備份清理" /bin/true 
    echo "[$today]>>>完成數據庫${db_name}過期備份清理" >> ${log_file} 
  else  
    action "[$today]>>>數據庫${db_name}過期備份清理失敗，請檢查相關配置！" /bin/false 
    echo "[$today]>>>數據庫${db_name}過期備份清理失敗，請檢查相關配置！" >> ${log_file} 
    exit 1 
  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 
################################################################### 
# copyright by hwb 
# 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 
export PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin 
source /etc/profile 
 
 
#root用戶執行腳本 
[ $(id -u) -gt 0 ] && echo "請用root用戶執行此腳本！" && exit 1 
 
 
#判斷目錄是否存在 
[ -d $mysql_path ] || mkdir -p $mysql_path 
[ -d $backup_path ] || echo "mysql數據庫備份目錄[$backup_path]不存在，請確認參數是否正確！" 
[ -d $backup_path ] || exit 1 
 
 
function bk_mysqlbackup(){ 
  echo "" 
  echo -e "\033[33m**************************自動配置mysql數據庫備份腳本[$mysql_path/$script_name]**************************\033[0m" 
  echo "" 
  echo -e "\033[36m Usage: $0   \033[0m" 
  echo "" 
   
  [ -f $mysql_path/$script_name ] && echo "${mysql_path}已存在腳本[${script_name}],請檢查相關配置！" && 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 
   
  #配置定時任務 
  echo "30 11 * * * /bin/bash  ${mysql_path}/${script_name} " >> /var/spool/cron/root 
   
  if [ $? -eq 0 ];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