mysql备份脚本

#!/bin/bash
##### by jwgod 20230928
##### mysql全库/单库备份

day=`date +'%Y-%m-%d'`
anynowtime="date +'%Y-%m-%d %H:%M:%S'"
NOW="echo [\`$anynowtime\`][PID:$$]"

##### 可在脚本开始运行时调用，打印当时的时间戳及PID。
#function job_start
job_start()
{
   echo "`eval $NOW` job_start"
}

##### 可在脚本执行成功的逻辑处调用，打印当时的时间戳及PID。
job_success()
{
    MSG="$*"
    echo "`eval $NOW` job_success:[$MSG]"
    exit 0
}

##### 可在脚本执行失败的逻辑分支处调用，打印当时的时间戳及PID。
job_fail()
{
    MSG="$*"
    echo "`eval $NOW` job_fail:[$MSG]"
    exit 1
}

job_start

##### 可在此处开始编写您的脚本逻辑代码
# 设置mysql的登录用户名和密码(根据实际情况填写)
mysql_user="root"
mysql_password="xxxx@xxxx!"
mysql_host="192.168.1.xx"
mysql_port="3306"
mysql_charset="utf8mb4"

# 备份文件存放地址(根据实际情况填写)
backup_location=/home/sql_bak/`date +%Y%m%d%H`

test -d "$backup_location" || mkdir -p $backup_location

# 是否删除过期数据
expire_backup_delete="ON"
expire_days=30
backup_time=`date +%Y%m%d%H%M`
backup_dir=$backup_location
welcome_msg="Welcome to use MySQL backup tools!"

# 判断mysql实例是否正常运行
mysql_ps=`ps -ef |grep mysql |wc -l`
mysql_listen=`netstat -an |grep LISTEN |grep $mysql_port|wc -l`
if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then
        echo "ERROR:MySQL is not running! backup stop!"
        exit
else
        echo $welcome_msg
fi
##### 删除30天之前备份
del_bak()
{
  # 删除过期数据
  if [ "$expire_backup_delete" == "ON" -a  "$backup_location" != "" ];then
    `find $backup_location -type f -mtime +$expire_days | xargs rm -rf`
    if [ $? -eq 0 ] ; then
      echo "Expired backup data delete complete!"
      job_success "$*"
    else
      job_fail "$*"
    fi  
  fi
}

##### 全库备份
all_bak()
{
  # # 备份指定数据库中数据(此处假设数据库是all)
  # mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password  --all-databases --triggers --routines--events > $backup_dir/mysql_all_backup-$backup_time.sql
  # flag=`echo $?`
  # if [ $flag == "0" ];then
    # zip -jr $backup_dir/mysql_all_backup-$backup_time.zip $backup_dir/ --exclude "*.zip"
    # #打包成功后删除sql文件
    # if [ $? = 0 ]; then
      # rm -rf $backup_dir/mysql_all_backup-$backup_time.sql
    # fi
    # echo "database mysql_backup success backup to $backup_dir/mysql_backup-$backup_time.sql"
    # job_success "$*"
  # else
    # echo "database mysql_backup backup fail!"
    # job_fail "$*"
  # fi

  # 备份的数据库数组
  db_arr=$(echo "show databases;" | mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password |grep -v "sys"|grep -v "Database"|grep -v "information_schema" |grep -v "performance_schema")
  echo "show databases： $db_arr"
  # 不需要备份的单例数据库
  nodeldb="test"
  # 循环备份
  for dbname in ${db_arr}
  do
   if [ $dbname != $nodeldb ]; then
     echo "mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $dbname --master-data > $backup_dir/mysql_${dbname}_backup-$backup_time.sql"
     mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $dbname --master-data > $backup_dir/mysql_${dbname}_backup-$backup_time.sql
     flag=`echo $?`
     if [ $flag == "0" ];then
       # tar打包所有的sql文件
       #tar -zcPpf  $backup_dir/mysql_${dbname}_backup-$backup_time.zip --warning=no-file-changed --directory /   $backup_dir
       zip -jr $backup_dir/mysql_${dbname}_backup-$backup_time.zip $backup_dir/ --exclude "*.zip"
       #打包成功后删除sql文件
       if [ $? = 0 ]; then
         rm -rf $backup_dir/mysql_${dbname}_backup-$backup_time.sql
       fi
       echo "database mysql_backup success backup to $backup_dir/mysql_${dbname}_backup-$backup_time.sql"
       MSG="$*"
       echo "`eval $NOW` job_success:[$MSG]"
     else
       echo "database mysql_backup backup fail!"
       job_fail "$*"
     fi
   fi
  done
}

##### 单库备份
one_bak()
{
  # 备份指定数据库中数据(此处假设数据库是hseysh)
  mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $1 --master-data > $backup_dir/mysql_$1_backup-$backup_time.sql
  #echo "mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $1 --master-data > $backup_dir/mysql_$1_backup-$backup_time.sql"
  flag=`echo $?`
  if [ $flag == "0" ];then
    # tar打包所有的sql文件
    # echo "tar -zcPpf $backup_dir/mysql_$1_backup-$backup_time.zip --directory /   $backup_dir"
    # tar -zcPpf $backup_dir/mysql_$1_backup-$backup_time.zip --warning=no-file-changed --directory /   $backup_dir
    zip -jr $backup_dir/mysql_$1_backup-$backup_time.zip $backup_dir/ --exclude "*.zip"
    #打包成功后删除sql文件
    if [ $? = 0 ]; then
      echo "rm -rf $backup_dir/mysql_$1_backup-$backup_time.sql"
      rm -rf $backup_dir/mysql_$1_backup-$backup_time.sql
    fi
    echo "database mysql_backup success backup to $backup_dir/mysql_backup-$backup_time.sql"
    job_success "$*"
  else
    echo "database mysql_backup backup fail!"
    job_fail "$*"
  fi
}

job_shell()
{
case "$1" in
  0)
    del_bak
    ;;
  1)
    all_bak
    ;;   
  xxx_test)
    one_bak $1
    ;; 	
  nacos)
    one_bak $1
    ;; 	
  xxl_job)
    one_bak $1
  *)
    echo "Usage: $0 {0[删除30天之前备份]|1[全备]|单库名[ xxxx_test | nacos | xxl_job ]}"
    exit 1

esac
exit 0    
}

##### 作业平台中执行脚本成功和失败的标准只取决于脚本最后一条执行语句的返回值
##### 如果返回值为0，则认为此脚本执行成功，如果非0，则认为脚本执行失败
#read -r -p "请选择全库/单库备份：｛0[删除30天之前备份]|1[全备]|单库名[ xxxx_test | nacos | xxl_job]｝" bak_sh
#job_shell "$bak_sh"
all_bak

#echo "mysql备份的第一个版本! "
#echo "version: 1.0.0"