分享一个 mysqldump 备份 MySQL 数据库的脚本

说明 请参考 分享一个 MySQL Shell 备份数据库的脚本

这次是使用 mysqldump 备份

#!/bin/bash

# =========================================================================
# (C) Copyright 2003-2033 hoplite
# =========================================================================
# Script Purpose : backup mysql database by mysqldump
# -------------------------------------------------------------------------
# instructions
# 1.setup login-path
#   create a login-ptah: mysql_config_editor set --login-path=bak -h localhost -P 3306 -uroot -p
#   list login-path: mysql_config_editor print --all
#   login test: mysql --login-path=bak
# 2.setup cron job
#   example: dump databases one by one every night
#   0 2 * * * /u01/backup/mysql_dump.sh bak /u01/backup/dump 7

# -------------------------------------------------------------------------
# Version Date      Description
# -------------------------------------------------------------------------
# V1.0              Initial version
# V1.1 2023-02-14   fix bug
# v1.2 2023-03-15   fix bug: add --hex-blob
# =========================================================================
# NOTE
# version upon v8.0.26,suggest to use --source-data to replace --master-data

[ -f ~/.bash_profile ] && . ~/.bash_profile
set -e -u -o pipefail

show_usage ()
{
  echo "usage: `basename $0` login-path target-basedir rotate"
  echo "eg.    `basename $0` bak /u01/backup/dump 7"
}

if [ $# -lt 3 ] || [ $# -gt 3 ]; then
   show_usage
   exit 0
else
  start_time=`date +%F" "%T`

  p_loginpath=$1
  p_targetbasedir=$2
  p_rotate_factor=$3

  v_id=`date +%Y%m%d%H%M%S`
  v_hash=`echo -n ${p_loginpath}|md5sum|cut -f1 -d" "`
  [ -d ${p_targetbasedir} ] || mkdir -p ${p_targetbasedir}

  stusfile=${p_targetbasedir}/.status.${v_hash} #file to record last backup status
  rotafile=${p_targetbasedir}/.rotate.${v_hash} #file to record rotate number
  log_file=${p_targetbasedir}/backup.log
  vdbsfile=${p_targetbasedir}/.vdbs.${v_hash}.${v_id} #file to record databases

  #check last backup result
  if [ -f ${stusfile} ]; then
    if [ `cat ${stusfile}` = "running" ]; then
      loginfo="`date +%F" "%T` ...ERROR:last backup fail, check it,then drop status file and rerun." ; echo ${loginfo} ; echo ${loginfo} >> ${log_file}
      exit 1
    fi
  else
    echo "running" > ${stusfile}
  fi

  #rotate number,+1 every time
  if [ -f ${rotafile} ]; then
    v_last_id=`cat ${rotafile}`
    v_incr_id=`expr ${v_last_id} + 1`
    v_curr_id=$(( ${v_incr_id} % ${p_rotate_factor} ))
  else
    v_curr_id=0
  fi
  echo ${v_curr_id} > ${rotafile}
  
  v_targetdir=${p_targetbasedir}/${v_curr_id}
  [ -d ${v_targetdir} ] || mkdir -p ${v_targetdir}
  cd ${v_targetdir}
  loginfo="`date +%F" "%T` ...start backup to ${v_targetdir}" ; echo ${loginfo} ; echo ${loginfo} >> ${log_file}

mysql --login-path=${p_loginpath}<<EOF
tee ${vdbsfile}
SELECT schema_name FROM information_schema.SCHEMATA;
notee  
EOF

  filtered_dbs=("SCHEMA_NAME" "TABLE_SCHEMA" "mysql" "sys" "information_schema" "performance_schema" "the_database_you_do_not_want_to_dump")
  while read v_db
  do
    is_in_filtered_db="N"
    for fd in ${filtered_dbs[*]} ; do
      if [ "${fd}" == "${v_db}" ]; then
        is_in_filtered_db="Y"
        break
      fi
    done

    if [[ "${is_in_filtered_db}" == "N" ]]; then
      loginfo="`date +%F" "%T` ...backup database ${v_db}" ; echo ${loginfo} ; echo ${loginfo} >> ${log_file}
      mysqldump --login-path=${p_loginpath} --hex-blob --set-gtid-purged=OFF -R -E --single-transaction --master-data=2 -F -B ${v_db} > ./${v_db}.sql 2>./${v_db}.err
      gzip -f ./${v_db}.sql
    fi
  done < ${vdbsfile}
  loginfo="`date +%F" "%T` ...end backup to ${v_targetdir}" ; echo ${loginfo} ; echo ${loginfo} >> ${log_file}
  rm "${vdbsfile:-unset}"
  echo "completed" > ${stusfile}

  end_time=`date +%F" "%T`
  t_start_time=`date -d "${start_time}" +%s`
  t_end_time=`date -d "${end_time}" +%s`
  t_diff_secs=$((${t_end_time} - ${t_start_time}))
  t_hours=$((${t_diff_secs}/3600))
  t_mins=$(($((${t_diff_secs}-${t_hours}*3600))/60))
  t_secs=$((${t_diff_secs}%60))

  echo
  echo ==================================================
  echo "File Name:`basename $0`    Start Time:${start_time}    End Time:${end_time}    Total Cost:${t_hours}:${t_mins}:${t_secs}"
  echo ==================================================
  echo
fi