mysql/teledb备份脚本

#!/bin/bash
# 设置shell有任何变量未定义或错误立即退出
set -eu

# 参数配置区
# 数据库地址
DBHost=192.168.203.1
# 数据库用户名
DBUser=root
# 数据库密码
DBPasswd=Skycyan.cn
# 数据库端口
DBPort=3306
# 备份路径
backpath=/opt
# 日志文件
LOG_FILE=log/db_back.log
# 备份时最低磁盘空间要求(单位:GB)
min_space=1
# 过滤的表前缀
PREFIX_FILE="prefix.ini" 
# 获取备份时间 
date1=`date +%Y%m%d`

mkdir -p log

# 方法函数区
# 日志记录方法
log() {
    echo "[$(date '+%F %T')] $1" | tee -a $LOG_FILE
}

# 备份目录挂载点获取
get_mount_info() {
    local target_dir="$1"
    [[ -d "$target_dir" ]] || {
        log "错误：备份目录不存在 $target_dir"
        return 1
    }
    df -P "$target_dir" | awk 'NR==2 {print $6,$4}'
}
# 磁盘空间检查
check_disk_space() {
    # 第一步：执行并检查命令
    local output
    if ! output=$(get_mount_info "$backpath"); then
        log "磁盘检查失败,请检查备份路径是否存在"
        exit 1
    fi
    read mount_point available_kb <<< $(get_mount_info "$backpath")
    local available_gb
    available_gb=$((available_kb / 1024 / 1024 ))    
    log "挂载点检查: $mount_point"
    log "可用空间: ${available_gb}GB"
    if [[ $available_gb -lt $min_space ]];then
    log "ERROR:磁盘空间不足"
    exit 1
    fi
}
# 数据库名称获取
get_dbs() {
    if [ ! -e "db_list" ];then
    log "首次运行"
    mysql -h"$DBHost" -P"$DBPort" -u"$DBUser" -p"$DBPasswd" -NBe \
    "SELECT schema_name 
     FROM information_schema.schemata 
     WHERE schema_name NOT IN ('information_schema','mysql','performance_schema','sys')" > db_list
    fi
}

get_tables() {
    log "开始获取表名"
    mkdir -p tmp
    readarray -t DBList < "db_list"
    for DBName in ${DBList[@]};do
    log "开始获取$DBName库的表名列表"
    mysql -u$DBUser -p$DBPasswd --host=$DBHost --port=$DBPort -NBe "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$DBName';" 2>&1 > tmp/$DBName\_table_list &
    done    
}
# 全库备份模块
db_back() {
    check_disk_space
    # 获取数据库名
    get_dbs    
    # 获取备份时间
    date1=`date +%Y%m%d`

    # 创建每日文件夹
    mkdir -p $backpath/db/$date1
    declare -a DBList
    readarray -t DBList < "db_list"
    # 确保权限
    chmod -R 766 $backpath/db/$date1
    for DBName in ${DBList[@]};
    do
    log "开始备份 $DBName"
    set +eu
    mysqldump -u$DBUser -p$DBPasswd --host=$DBHost --port=$DBPort --skip-lock-tables --single-transaction  $DBName >$backpath/db/$date1/$DBName.sql 2>&1 &
    pid1=$!
    log "数据库后台备份进程PID:$pid1"
    wait $pid1
    backup_status=$?
    if [[ $backup_status -ne 0 ]]; then
        log "错误：$DBName 备份失败！"
    else
        log "$DBName 备份成功"
    fi
    done
    # 恢复错误退出
    set -eu
}
db_structure() {
    check_disk_space
    # 获取数据库名
    get_dbs    
    # 获取备份时间
    date1=`date +%Y%m%d`

    # 创建每日文件夹
    mkdir -p $backpath/structure/$date1
    declare -a DBList
    readarray -t DBList < "db_list"
    # 确保权限
    chmod -R 766 $backpath/structure/$date1
    for DBName in ${DBList[@]};
    do
    log "开始备份 $DBName"
    set +eu
    mysqldump -u$DBUser -p$DBPasswd --host=$DBHost --port=$DBPort --no-data --skip-lock-tables --single-transaction  $DBName >$backpath/structure/$date1/$DBName.sql 2>&1 &
    pid1=$!
    log "数据库后台备份进程PID:$pid1"
    wait $pid1
    backup_status=$?
    if [[ $backup_status -ne 0 ]]; then
        log "错误：$DBName 备份失败！"
    else
        log "$DBName 备份成功"
    fi
    done
    # 恢复错误退出
    set -eu
}
# 备份文件清理
clean_old_backups() {
    local backup_type="$1"
    local retain_days="$2"
    local backup_dir="$backpath/$backup_type"

    [[ ! -d "$backup_dir" ]] && return

    log "清理 $backup_dir 中超过 ${retain_days} 天的备份..."
    find "$backup_dir" -type f -name "*.sql" -mtime +"$retain_days" -exec rm -fv {} \; | while read -r file; do
        log "已删除: $file"
    done
}

tables_bak() {
    local retain_days="${1:-7}"
    check_disk_space
    # 获取数据库名
    get_dbs
    # 获取数据表名
    get_tables
    # 创建目录检查权限
    mkdir -p $backpath/tables/$date1/
    chmod 755 $backpath/tables/$date1/

    declare -a DBList
    readarray -t DBList < "db_list"

    # 预加载过滤前缀（过滤空行和注释）
    declare -a filter_prefixes
    if [[ -f "${PREFIX_FILE}" ]]; then
        readarray -t filter_prefixes < <(
            grep -Ev '^[[:space:]]*($|#)' "${PREFIX_FILE}"  # 过滤空行和注释
        )
        log "已加载过滤前缀: ${filter_prefixes[*]}"
    else
        log "错误：过滤文件 ${PREFIX_FILE} 不存在"
        return 1
    fi

    # 并发控制参数
    local max_jobs=4 current_jobs=0

    for DBName in "${DBList[@]}"; do
        log "===== 处理数据库: $DBName ====="
        declare -a TBList=()
        local table_list_file="tmp/${DBName}_table_list"

        # 文件存在性检查
        if [[ ! -f "$table_list_file" ]]; then
            log "错误：表列表文件 $table_list_file 不存在"
            continue
        fi

        readarray -t TBList < "$table_list_file"

        # 遍历表名
        for TBName in "${TBList[@]}"; do
            local should_skip=false

            # 检查是否匹配任意前缀
            for prefix in "${filter_prefixes[@]}"; do
                if [[ "$TBName" == "${prefix}"* ]]; then  # 严格前缀匹配
                    log "跳过 $DBName.$TBName（匹配前缀: $prefix）"
                    should_skip=true
                    break
                fi
            done

            # 仅在不匹配时备份
            if [[ "$should_skip" != "true" ]]; then
                # 启动备份任务
                mysqldump -u"$DBUser" -p"$DBPasswd" \
                          --host="$DBHost" \
                          --port="$DBPort" \
                          --skip-lock-tables \
                          --single-transaction \
                          "$DBName" "$TBName" \
                          > "$backpath/tables/$date1/$DBName.$TBName.sql" 2>/dev/null  &
                local pid=$!
                log "启动备份 $DBName.$TBName (PID: $pid)"
                wait $pid
            fi
        done
    done
    log "所有备份任务已完成"
    clean_old_backups "tables" "$retain_days"
}

del() {
    echo $2
}
# 主方法，通过参数判断备份类型
main() {
    local action="${1:-}"
    local retain_days="${2:-7}"  # 默认保留7天

    # 参数校验
    if ! [[ "$retain_days" =~ ^[0-9]+$ ]]; then
        log "错误：保留天数必须为整数"
        show_usage
        exit 1
    fi

    case "$action" in
        -d) db_back "$retain_days" ;;
        -t) tables_bak "$retain_days" ;;
        -s) db_structure "$retain_days" ;;
        help|--help|-h) show_usage ;;
        *) show_usage; exit 1 ;;
    esac
}

# 专用帮助信息函数
show_usage() {
    echo -e "------------------------------------------------------------"
    echo -e "\tAuthor DHD\t\t2025-03-13"                     
    echo -e "\t使用方法: sh ${0##*/} [参数]"                     
    echo -e "------------------------------------------------------------"
    echo -e "参数1:\t备份模式"
    echo -e "-d\t全库备份\t自动识别业务库，自动跳过系统默认表，自动判断空间"
    echo -e "-t\t全表备份\t自动识别业务表，按照 prefix.ini 进行表头过滤"
    echo -e "-s\t表结构备份\t按库名分割存储"
    echo -e "参数2:\t备份数据保留时间，单位:天"
    echo -e "参数：help\t帮助\t显示本帮助信息"
}

# 程序入口
main "$@"