本文为墨天轮数据库管理服务团队第162期技术分享，内容原创，作者为技术顾问闫建，如需转载请联系小墨（VX：modb666）并注明来源。如需查看更多文章可关注【墨天轮】公众号。脚本功能此脚本是专门用于MySQL8.0逻辑备份的 MySQL Shell备份脚本，它包含了备份数据库实例的所有对象，是整个实例级别的备份，也是当下取代传统mysqldump工具的最优替代方案。脚本内容该脚本名称为mysqlshell\_full\_backup.sh#!/bin/bash ######################################## # MySQL Shell 自动备份脚本 (MySQL 8.0+) # 功能: 全量逻辑备份 + 错误处理 + 日志记录 + 自动清理 + 时间统计 # 备份文件名格式: full_mysqlshell_backup_时间 # 使用方式: 直接修改脚本中的变量值，然后执行 ./mysqlshell_full_backup.sh ######################################## ################### 配置参数 ################### # MySQL Shell命令绝对路径（重要：请根据实际安装路径修改） MYSQLSH_CMD="/data/soft/mysqlshell8044/bin/mysqlsh" # 备份保留天数（默认15天） RETAIN_DAYS=${RETAIN_DAYS:-15} # 并行度（默认4线程） PARALLEL=${PARALLEL:-4} # 排除的数据库（逗号分隔，默认空） EXCLUDE_SCHEMAS=${EXCLUDE_SCHEMAS:-""} # 备份存储根目录 BACKUP_ROOT="/data/backup" # 时间戳格式：YYYYMMDD_HHMMSS TIMESTAMP=$(date +%Y%m%d_%H%M%S) # 备份目录名称（按您要求的格式） BACKUP_DIR_NAME="full_mysqlshell_backup_${TIMESTAMP}" BACKUP_DIR="${BACKUP_ROOT}/${BACKUP_DIR_NAME}" # MySQL连接参数（请根据实际情况修改） MYSQL_USER="root" MYSQL_PASSWORD="mysql" MYSQL_HOST="localhost" MYSQL_PORT="3306" # 日志文件路径 LOG_FILE="${BACKUP_ROOT}/backup.log" ############################################ ########## 函数：记录日志 ########## log() { local level="$1" local message="$2" local timestamp=$(date '+%Y-%m-%d %H:%M:%S') local log_entry="[${timestamp}] [${level}] ${message}" # 输出到标准输出并写入日志文件 echo "${log_entry}" | tee -a "${LOG_FILE}" } ########## 函数：错误处理并退出 ########## error_exit() { log "ERROR" "$1" end_time=$(date +%s) total_runtime=$((end_time - start_time_global)) log "INFO" "脚本异常退出，总运行时间: $(format_duration $total_runtime)" exit 1 } ########## 函数：计算和格式化运行时间 ########## format_duration() { local seconds=$1 local hours=$((seconds / 3600)) local minutes=$(( (seconds % 3600) / 60 )) local secs=$((seconds % 60)) if [ $hours -gt 0 ]; then echo "${hours}小时${minutes}分${secs}秒" elif [ $minutes -gt 0 ]; then echo "${minutes}分${secs}秒" else echo "${secs}秒" fi } ########## 函数：检查依赖工具 ########## check_dependencies() { # 检查MySQL Shell是否存在且可执行 if [ ! -x "$MYSQLSH_CMD" ]; then error_exit "MySQL Shell未在指定路径找到或不可执行: $MYSQLSH_CMD" fi # 检查其他依赖工具 local deps=("du") for dep in "${deps[@]}"; do if ! command -v "$dep" &> /dev/null; then error_exit "所需工具 $dep 未安装或未在PATH中" fi done log "INFO" "依赖检查通过，MySQL Shell路径: $MYSQLSH_CMD" } ########## 函数：检查MySQL连接 ########## check_mysql_connection() { log "INFO" "检查MySQL数据库连接..." if "$MYSQLSH_CMD" --log-level=2 --uri="${MYSQL_USER}:${MYSQL_PASSWORD}@${MYSQL_HOST}:${MYSQL_PORT}" --sql --execute "select 1" >/dev/null 2>&1; then log "INFO" "MySQL数据库连接成功" else error_exit "MySQL数据库连接失败，请检查连接参数" fi } ########## 函数：检查磁盘空间 ########## check_disk_space() { local available_space=$(df "$BACKUP_ROOT" | awk 'NR==2 {print $4}') local min_space=$((1024 * 1024)) # 至少保留1GB空间 if [ "$available_space" -lt "$min_space" ]; then error_exit "磁盘空间不足，剩余空间: ${available_space}KB，至少需要: ${min_space}KB" fi log "INFO" "磁盘空间检查通过，剩余空间: ${available_space}KB" } ########## 主函数：执行备份 ########## perform_backup() { log "INFO" "开始执行数据库备份..." # 构建excludeSchemas参数 local exclude_param="" if [ -n "$EXCLUDE_SCHEMAS" ]; then local exclude_jsons=() IFS=',' read -ra DB_ARRAY <<< "$EXCLUDE_SCHEMAS" for db in "${DB_ARRAY[@]}"; do db_clean=$(echo "$db" | sed -e 's/^[[:space:]]*//' -e 's/[[:space:]]*$//' -e "s/'/\\\\'/g") if [ -n "$db_clean" ]; then exclude_jsons+=("'$db_clean'") fi done if [ ${ #exclude _jsons[@]} -gt 0 ]; then exclude_param="excludeSchemas: [$(IFS=,; echo "${exclude_jsons[*]}")]," log "INFO" "排除数据库: ${EXCLUDE_SCHEMAS}" fi fi # 构建备份命令 local backup_cmd="util.dumpInstance('$BACKUP_DIR', { threads: $PARALLEL, ${exclude_param} consistent: true, compression: 'zstd', ocimds: true, compatibility: ['strip_restricted_grants','strip_definers','strip_tablespaces','ignore_missing_pks'] })" log "INFO" "执行MySQL Shell备份命令，并行度: $PARALLEL" # 执行备份（使用变量化的MYSQLSH_CMD） if "$MYSQLSH_CMD" --log-level=3 --uri="${MYSQL_USER}:${MYSQL_PASSWORD}@${MYSQL_HOST}:${MYSQL_PORT}" \ --execute "$backup_cmd" >> "$LOG_FILE" 2>&1; then log "INFO" "MySQL Shell备份命令执行完成" return 0 else return 1 fi } ########## 主函数：清理过期备份 ########## cleanup_old_backups() { log "INFO" "开始清理超过 ${RETAIN_DAYS} 天的旧备份..." local deleted_count=0 while IFS= read -r -d '' old_backup; do if [ -n "$old_backup" ] && [ "$old_backup" != "$BACKUP_ROOT" ]; then log "INFO" "删除过期备份: $(basename "$old_backup")" rm -rf "$old_backup" ((deleted_count++)) fi done < <(find "$BACKUP_ROOT" -maxdepth 1 -type d -name "full_mysqlshell_backup_*" -mtime "+$((RETAIN_DAYS-1))" -print0 2>/dev/null) log "INFO" "清理完成，共删除 $deleted_count 个过期备份" } ########## 主执行流程 ########## main() { local start_time_global=$(date +%s) log "INFO" "=== MySQL备份作业开始 ===" log "INFO" "备份目录: $BACKUP_DIR" log "INFO" "保留天数: $RETAIN_DAYS天, 并行度: $PARALLEL" log "INFO" "排除数据库: ${EXCLUDE_SCHEMAS:-无}" log "INFO" "MySQL Shell路径: $MYSQLSH_CMD" # 初始化检查 check_dependencies check_disk_space check_mysql_connection # 创建备份目录 if ! mkdir -p "$BACKUP_DIR"; then error_exit "无法创建备份目录: $BACKUP_DIR" fi log "INFO" "备份目录创建成功: $BACKUP_DIR" # 执行备份 local backup_start=$(date +%s) if perform_backup; then local backup_end=$(date +%s) local backup_runtime=$((backup_end - backup_start)) log "INFO" "数据库备份成功完成" log "INFO" "备份耗时: $(format_duration $backup_runtime)" else error_exit "数据库备份执行失败，详情请查看日志: $LOG_FILE" fi # 清理过期备份 local cleanup_start=$(date +%s) cleanup_old_backups local cleanup_end=$(date +%s) local cleanup_runtime=$((cleanup_end - cleanup_start)) # 最终统计 local end_time_global=$(date +%s) local total_runtime=$((end_time_global - start_time_global)) log "INFO" "=== 备份作业统计 ===" log "INFO" "备份文件位置: $BACKUP_DIR" log "INFO" "备份大小: $(du -sh "$BACKUP_DIR" 2>/dev/null | cut -f1 || echo "未知")" log "INFO" "各阶段耗时详情:" log "INFO" " - 备份阶段: $(format_duration $backup_runtime)" log "INFO" " - 清理阶段: $(format_duration $cleanup_runtime)" log "INFO" " - 总计耗时: $(format_duration $total_runtime)" log "INFO" "日志文件: $LOG_FILE" log "INFO" "=== MySQL备份作业完成 ===" } ########## 脚本执行入口 - 直接执行主函数 ########## main重点说明1、关于备份时的参数说明# 构建备份命令 local backup_cmd="util.dumpInstance('$BACKUP_DIR', { threads: $PARALLEL, ${exclude_param} consistent: true, compression: 'zstd', ocimds: true, compatibility: ['strip_restricted_grants','strip_definers','strip_tablespaces','ignore_missing_pks'] })"上面构建备份语句中，有一个兼容性参数设置compatibility，该参数需要额外说明：strip\_restricted\_grants 移除备份文件中，目标数据库服务不允许授予的高级权限，避免因权限问题导致导入失败strip\_definers 从视图、存储过程等对象定义中移除 DEFINER=子句，避免因原始定义者不存在而导致的权限错误strip\_tablespaces 从建表语句中移除 TABLESPACE=子句，此选项可防止因指定不存在的表空间而导致建表失败ignore\_missing\_pks 忽略（跳过检查）没有主键的表，而不为它们自动创建主键，用于容忍没有主键的表，但不会自动修复。如需自动添加主键，应使用 create\_invisible\_pks选项 2、关于备份软件的说明MySQL数据库软件并不自带MySQLShell功能，MySQL Shell软件需要提前下载准备好，下载链接如下：https://dev.mysql.com/downloads/shell/MySQLShell软件下载到本地服务器后，解压即可使用非常简单方便。使用方法如采用压缩备份，此步骤为必须执行步骤，非压缩备份，此步1、保存脚本并赋予执行权限[root@VM-8-4-opencloudos backup]# chmod +x mysqlshell_full_backup.sh2、可选手动执行备份[root@VM-8-4-opencloudos backup]# ./mysqlshell_full_backup.shtail -100 /data/backup/backup.log3. 可配置定时任务（每日凌晨1点执行）# 编辑crontab：crontab -e 添加如下内容并保存 01 * * * /path/to/mysqlshell_full_backup.sh恢 复作为DBA，恢复工作需要严谨的操作流程。以下是基于该备份的详细恢复步骤和指南。1、确认备份文件的完整性在开始恢复前，务必检查备份目录是否完整。一个成功的 util.dumpInstance备份通常会包含一个 @.done.json文件以及每个数据库对应的 .tsv.zst数据文件和 .sql元数据文件。您可以使用以下命令快速查看备份根目录下的内容：[root@VM-8-4-opencloudos backup]# ls -l full_mysqlshell_backup_20251113_111713 total 23624 -rw-r----- 1 root root 549 Nov 13 11:17 @.done.json -rw-r----- 1 root root 1400 Nov 13 11:17 @.json -rw-r----- 1 root root 240 Nov 13 11:17 @.post.sql -rw-r----- 1 root root 240 Nov 13 11:17 @.sql -rw-r----- 1 root root 9 Nov 13 11:17 testdb@a@@0.tsv.zst -rw-r----- 1 root root 8 Nov 13 11:17 testdb@a@@0.tsv.zst.idx -rw-r----- 1 root root 612 Nov 13 11:17 testdb@a.json -rw-r----- 1 root root 750 Nov 13 11:17 testdb@a.sql -rw-r----- 1 root root 9 Nov 13 11:17 testdb@b@@0.tsv.zst -rw-r----- 1 root root 8 Nov 13 11:17 testdb@b@@0.tsv.zst.idx -rw-r----- 1 root root 562 Nov 13 11:17 testdb@b.json -rw-r----- 1 root root 644 Nov 13 11:17 testdb@b.sql -rw-r----- 1 root root 585 Nov 13 11:17 testdb.json -rw-r----- 1 root root 23971894 Nov 13 11:17 testdb@large_table@@0.tsv.zst -rw-r----- 1 root root 544 Nov 13 11:17 testdb@large_table@@0.tsv.zst.idx -rw-r----- 1 root root 1036 Nov 13 11:17 testdb@large_table.json -rw-r----- 1 root root 1418 Nov 13 11:17 testdb@large_table.sql -rw-r----- 1 root root 92884 Nov 13 11:17 testdb@my_table@@0.tsv.zst -rw-r----- 1 root root 8 Nov 13 11:17 testdb@my_table@@0.tsv.zst.idx -rw-r----- 1 root root 890 Nov 13 11:17 testdb@my_table.json -rw-r----- 1 root root 1349 Nov 13 11:17 testdb@my_table.sql -rw-r----- 1 root root 4038 Nov 13 11:17 testdb.sql -rw-r----- 1 root root 14 Nov 13 11:17 testdb@tt@@0.tsv.zst -rw-r----- 1 root root 8 Nov 13 11:17 testdb@tt@@0.tsv.zst.idx -rw-r----- 1 root root 583 Nov 13 11:17 testdb@tt.json -rw-r----- 1 root root 19 Nov 13 11:17 testdb@tt_new@@0.tsv.zst -rw-r----- 1 root root 8 Nov 13 11:17 testdb@tt_new@@0.tsv.zst.idx -rw-r----- 1 root root 587 Nov 13 11:17 testdb@tt_new.json -rw-r----- 1 root root 686 Nov 13 11:17 testdb@tt_new.sql -rw-r----- 1 root root 674 Nov 13 11:17 testdb@tt.sql -rw-r----- 1 root root 4344 Nov 13 11:17 @.users.sql2、准备恢复环境目标MySQL实例：确保用于恢复的MySQL服务已启动并可以正常连接。它最好与源服务器版本一致或兼容，以避免潜在问题。权限检查：用于执行恢复操作的MySQL账户需要具备足够的权限，例如 SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, 甚至可能需要 RELOAD或 SUPER权限。磁盘空间：确保目标实例的磁盘有足够空间容纳要恢复的数据。决策：完全恢复还是部分恢复？ 想清楚是需要恢复整个实例，还是只恢复其中的一个或多个特定数据库。这决定了后续使用的工具和命令。## 说明：在导入数据之前，建议检查参数local_infile是否开启，如未开启，需要进行提前设置。 root@localhost:(none) 02:01:54 > show global variables like '%local_infile%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | local_infile | OFF | +---------------+-------+ 1 row in set (0.11 sec) root@localhost:(none) 02:01:46 >set global local_infile=1; Query OK, 0 rows affected (0.00 sec)3、恢复操作步骤MySQL Shell工具软件进行备份，恢复时候也必须使用MySQL Shell进行恢复，这个是必须满足的基本条件。恢复方式一：在服务器上执行以下命令。请务必将 备份目录路径、用户名、密码、主机和 端口替换为您的实际信息。mysqlsh -u root -p --host localhost --port 3306 --js --进入 MySQL Shell 的 JavaScript 模式后，执行： util.loadDump("/data/backup/full_mysqlshell_backup_20251113_103022", { threads: 4, // 并行线程数，可调整 skipBinlog: false, // 如果恢复过程不想记录到二进制日志，可设为true ignoreVersion: true, // 忽略MySQL版本差异警告（谨慎使用） resetProgress: true // 如果重新开始一个恢复，重置进度 });恢复方式二：mysqlsh -u root -p -h localhost -P 3306 --js -e "util.loadDump('/data/backup/full_mysqlshell_backup_20251113_103022', {threads: 4, skipBinlog: false})"4、监控恢复过程恢复过程中，util.loadDump会显示进度信息。您也可以在另一个会话中连接到MySQL，使用 sql模式查看正在创建的表或进程。总 结该脚本提供了一个生产环境使用MySQL Shell 工具对 MySQL8.0版本进行逻辑备所需的完整步骤，包括错误处理、日志记录、自动清理和耗时统计以及最后的恢复步骤。数据库运维人员可以根据实际环境调整配置参数，特别是备份路径和保留天数设置等一些常用功能的设置。墨天轮从乐知乐享的数据库技术社区蓄势出发，全面升级，提供多类型数据库管理服务。墨天轮数据库管理服务旨在为用户构建信赖可托付的数据库环境，并为数据库厂商提供中立的生态支持。墨天轮数据库服务官网：https://www.modb.pro/service
本文为墨天轮数据库管理服务团队第159期技术分享，内容原创，作者为技术顾问闫建，如需转载请联系小墨（VX：modb666）并注明来源。如需查看更多文章可关注【墨天轮】公众号。脚本功能此脚本是专门用于MySQL8.0物理备份的xtrabackup脚本,并且xtrabackup版本为8.0的最新版，它包含了备份数据库（包含全量备份，错误处理，日志记录，自动清理，耗时统计）的完整配置。脚本内容该脚本名称为xtrabackup8.0\_full.sh#!/bin/bash # =============================================== # MySQL XtraBackup 全量备份脚本 (默认未启用压缩) # 功能：全量备份 + 错误处理 + 日志记录 + 自动清理 + 耗时统计 # 版本：1.0 # 日期：2025-11-04 # =============================================== # ------------------------------- # 可配置变量（修改此处适应环境） # ------------------------------- # 基础路径 BACKUP_BASE_DIR="/data/backup" # 备份根目录 LOG_DIR="${BACKUP_BASE_DIR}/logs" # 日志目录 FULL_BACKUP_DIR="${BACKUP_BASE_DIR}/full" # 全量备份目录 # MySQL 连接配置 MYSQL_USER="root" # 备份专用用户（需提前授权） MYSQL_PASSWORD="mysql" # 用户密码 MYSQL_SOCKET="/data/mysql/3306/run/mysql.sock" # MySQL Socket路径 MYSQL_CNF="/etc/my.cnf" # MySQL配置文件路径 # 备份参数 RETENTION_DAYS=7 # 备份保留天数（默认7天） COMPRESS_ENABLED=0 # 是否启用压缩（0-禁用，1-启用，默认禁用） COMPRESS_ALGORITHM="zstd" # 压缩算法（zstd/lz4/quicklz，默认zstd，如果采用zstd压缩方式，在解压的时候建议在OS上检查是否安装 zstd软件，如未安装，请提前安装yum install zstd） COMPRESS_THREADS=4 # 压缩线程数（默认4） PARALLEL=4 # 备份并行线程数（默认4） # XtraBackup路径（若不在PATH中，需指定完整路径，注意：该路径需要提前修改为符合实际环境的绝对路径） XTRABACKUP_PATH="/data/soft/xtrabackup8.0.35-34-glibc2.36/bin/xtrabackup" # ------------------------------- # 函数定义 # ------------------------------- # 日志记录函数 log_message() { local level="$1" local message="$2" local timestamp=$(date '+%Y-%m-%d %H:%M:%S') local log_entry="[${timestamp}] [${level}] ${message}" # 输出到标准输出并写入日志文件 echo "${log_entry}" | tee -a "${CURRENT_LOG_FILE}" } # 错误处理函数 error_exit() { log_message "ERROR" "脚本执行失败: $1" exit 1 } # 计算和格式化时间差函数 calculate_duration() { local start_seconds="$1" local end_seconds="$2" local total_seconds=$((end_seconds - start_seconds)) # 转换为天、小时、分钟、秒的易读格式 local days=$((total_seconds / 86400)) local hours=$(( (total_seconds % 86400) / 3600 )) local minutes=$(( (total_seconds % 3600) / 60 )) local seconds=$((total_seconds % 60)) local duration_str="" if [ $days -gt 0 ]; then duration_str="${days}天" fi if [ $hours -gt 0 ] || [ -n "$duration_str" ]; then duration_str="${duration_str}${hours}小时" fi if [ $minutes -gt 0 ] || [ -n "$duration_str" ]; then duration_str="${duration_str}${minutes}分钟" fi duration_str="${duration_str}${seconds}秒" echo "$duration_str" } # 清理旧备份函数 cleanup_old_backups() { log_message "INFO" "开始清理超过 ${RETENTION_DAYS} 天的旧备份..." local deleted_count=0 # 删除过期全量备份及相关日志 if find "${BACKUP_BASE_DIR}" -name "full_*" -type d -mtime +${RETENTION_DAYS} | grep -q .; then find "${BACKUP_BASE_DIR}" -name "full_*" -type d -mtime +${RETENTION_DAYS} -exec rm -rf {} \; deleted_count=$(find "${BACKUP_BASE_DIR}" -name "full_*" -type d -mtime +${RETENTION_DAYS} | wc -l) log_message "INFO" "已清理 ${deleted_count} 个过期全量备份" else log_message "INFO" "未找到需要清理的过期备份" fi # 清理旧日志文件（保留30天） find "${LOG_DIR}" -name "backup_*.log" -type f -mtime +30 -delete } # 检查依赖项 check_dependencies() { local deps=("${XTRABACKUP_PATH}" "mysql") for cmd in "${deps[@]}"; do if ! command -v "${cmd}" &> /dev/null; then error_exit "未找到所需命令: ${cmd}，请确保已安装并配置PATH" fi done # 检查备份目录权限 if [ ! -w "${BACKUP_BASE_DIR}" ]; then error_exit "备份目录不可写: ${BACKUP_BASE_DIR}" fi } # 备份前置检查 pre_backup_checks() { log_message "INFO" "开始备份前置检查..." # 检查MySQL连接 if ! mysql --user="${MYSQL_USER}" --password="${MYSQL_PASSWORD}" --socket="${MYSQL_SOCKET}" -e "SELECT 1;" &> /dev/null; then error_exit "MySQL连接测试失败，请检查凭据和Socket路径" fi # 检查XtraBackup版本 local xtrabackup_version xtrabackup_version=$(${XTRABACKUP_PATH} --version 2>&1 | tail -n1 || echo "未知") log_message "INFO" "使用XtraBackup版本: ${xtrabackup_version}" # 检查磁盘空间（至少保留10GB） local available_space available_space=$(df "${BACKUP_BASE_DIR}" | awk 'NR==2 {print $4}') if [ "${available_space}" -lt 10485760 ]; then # 10GB in KB error_exit "磁盘空间不足10GB，当前可用: ${available_space}KB" fi } # ------------------------------- # 主脚本逻辑 # ------------------------------- main() { # 初始化变量 local backup_timestamp=$(date '+%Y%m%d_%H%M%S') local backup_name="full_${backup_timestamp}" local current_backup_dir="${FULL_BACKUP_DIR}/${backup_name}" CURRENT_LOG_FILE="${LOG_DIR}/backup_${backup_timestamp}.log" # 记录备份开始时间（秒级时间戳）[6,8](@ref) local backup_start_time=$(date +%s) local backup_start_readable=$(date '+%Y-%m-%d %H:%M:%S') # 创建目录 mkdir -p "${FULL_BACKUP_DIR}" "${LOG_DIR}" log_message "INFO" "=== MySQL全量备份开始 ===" log_message "INFO" "备份开始时间: ${backup_start_readable}" log_message "INFO" "备份名称: ${backup_name}" log_message "INFO" "备份目录: ${current_backup_dir}" log_message "INFO" "保留天数: ${RETENTION_DAYS}" # log_message "INFO" "压缩启用: ${COMPRESS_ENABLED} (算法: ${COMPRESS_ALGORITHM}, 线程: ${COMPRESS_THREADS})" # 执行检查 check_dependencies pre_backup_checks # 构建备份命令 local backup_cmd="${XTRABACKUP_PATH} --defaults-file=${MYSQL_CNF} --backup" backup_cmd+=" --target-dir=${current_backup_dir}" backup_cmd+=" --user=${MYSQL_USER} --password=${MYSQL_PASSWORD}" backup_cmd+=" --socket=${MYSQL_SOCKET} --parallel=${PARALLEL}" # 压缩配置 if [ "${COMPRESS_ENABLED}" -eq 1 ]; then log_message "INFO" "启用压缩算法: ${COMPRESS_ALGORITHM}, 线程数: ${COMPRESS_THREADS}" backup_cmd+=" --compress=${COMPRESS_ALGORITHM} --compress-threads=${COMPRESS_THREADS}" else log_message "INFO" "备份未启用压缩" fi # 执行备份 log_message "INFO" "开始执行 XtraBackup 全量备份..." log_message "DEBUG" "备份命令: ${backup_cmd//--password=* /--password=*** }" # 屏蔽密码显示 # 记录备份操作开始时间 local backup_operation_start=$(date +%s) if eval "${backup_cmd}" >> "${CURRENT_LOG_FILE}" 2>&1; then local backup_operation_end=$(date +%s) local backup_duration=$(calculate_duration $backup_operation_start $backup_operation_end) log_message "INFO" "XtraBackup全量备份完成，备份操作耗时: ${backup_duration}" # 验证备份完整性 if [ -f "${current_backup_dir}/xtrabackup_checkpoints" ]; then local backup_type backup_type=$(grep "backup_type" "${current_backup_dir}/xtrabackup_checkpoints" | cut -d= -f2) log_message "INFO" "备份类型验证: ${backup_type}" else error_exit "备份完整性检查失败: xtrabackup_checkpoints文件缺失" fi else error_exit "XtraBackup备份过程失败，请检查日志: ${CURRENT_LOG_FILE}" fi # 自动清理旧备份 cleanup_old_backups # 计算总耗时 local backup_end_time=$(date +%s) local backup_end_readable=$(date '+%Y-%m-%d %H:%M:%S') local total_duration=$(calculate_duration $backup_start_time $backup_end_time) # 计算备份大小 local backup_size backup_size=$(du -sh "${current_backup_dir}" | awk '{print $1}') log_message "INFO" "备份完成: ${backup_name} (大小: ${backup_size})" log_message "INFO" "备份开始: ${backup_start_readable}" log_message "INFO" "备份结束: ${backup_end_readable}" log_message "INFO" "备份总耗时: ${total_duration}" log_message "INFO" "备份日志: ${CURRENT_LOG_FILE}" log_message "INFO" "=== MySQL全量备份结束 ===" } # 异常处理（捕获中断信号） trap 'log_message "ERROR" "脚本被用户中断"; exit 2;' INT TERM # 脚本入口点 main "$@"重点说明在脚本最开始部分需要根据实际数据库环境来配置，包括用户名，密码，路径等详细信息，参考如下：BACKUP_BASE_DIR="/data/backup" # 备份根目录 LOG_DIR="${BACKUP_BASE_DIR}/logs" # 日志目录 FULL_BACKUP_DIR="${BACKUP_BASE_DIR}/full" # 全量备份目录 # MySQL 连接配置 MYSQL_USER="root" # 备份专用用户（需提前授权） MYSQL_PASSWORD="mysql" # 用户密码 MYSQL_SOCKET="/data/mysql/3306/run/mysql.sock" # MySQL Socket路径 MYSQL_CNF="/etc/my.cnf" # MySQL配置文件路径 # 备份参数 RETENTION_DAYS=7 # 备份保留天数（默认7天） COMPRESS_ENABLED=0 # 是否启用压缩（0-禁用，1-启用，默认禁用） COMPRESS_ALGORITHM="zstd" # 压缩算法（zstd/lz4/quicklz，默认zstd，如果采用zstd压缩方式，在解压的时候建议在OS上检查是否安装 zstd软件，如未安装，请提前安装yum install zstd） COMPRESS_THREADS=4 # 压缩线程数（默认4） PARALLEL=4 # 备份并行线程数（默认4） # XtraBackup路径（若不在PATH中，需指定完整路径，注意：该路径需要提前修改为符合实际环境的绝对路径） XTRABACKUP_PATH="/data/soft/xtrabackup8.0.35-34-glibc2.36/bin/xtrabackup"使用方法保脚本并赋予执行权限chmod +x xtrabackup8.0_full.sh手动执行备份[root@VM-8-4-opencloudos backup]# ./xtrabackup8.0_full.sh [2025-11-04 17:34:34] [INFO] === MySQL全量备份开始 === [2025-11-04 17:34:34] [INFO] 备份名称: full_20251104_173434 [2025-11-04 17:34:34] [INFO] 备份目录: /data/backup/full/full_20251104_173434 [2025-11-04 17:34:34] [INFO] 保留天数: 7 [2025-11-04 17:34:34] [INFO] 开始备份前置检查... [2025-11-04 17:34:35] [INFO] 使用XtraBackup版本: /data/soft/xtrabackup8.0.35-34-glibc2.36/bin/xtrabackup version 8.0.35-34 based on MySQL server 8.0.35 Linux (x86_64) (revision id: c8a25ff9) [2025-11-04 17:34:35] [INFO] 启用压缩算法: zstd, 线程数: 4 [2025-11-04 17:34:35] [INFO] 开始执行XtraBackup全量备份... [2025-11-04 17:34:35] [DEBUG] 备份命令: /data/soft/xtrabackup8.0.35-34-glibc2.36/bin/xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/full/full_20251104_173434 --user=root --password=*** --compress-threads=4 [2025-11-04 17:34:41] [INFO] XtraBackup全量备份完成 [2025-11-04 17:34:41] [INFO] 备份类型验证: full-backuped [2025-11-04 17:34:41] [INFO] 开始清理超过 7 天的旧备份... [2025-11-04 17:34:41] [INFO] 未找到需要清理的过期备份 [2025-11-04 17:34:41] [INFO] 备份完成: full_20251104_173434 (大小: 43M) [2025-11-04 17:34:41] [INFO] 备份日志: /data/backup/logs/backup_20251104_173434.log [2025-11-04 17:34:41] [INFO] === MySQL全量备份结束 === [root@VM-8-4-opencloudos backup]# 配置定时任务（每日凌晨1点执行）# 编辑crontab：crontab -e 添加如下内容并保存 0 1 * * * /path/to/xtrabackup8.0_full.sh 恢 复1、解压缩（如采用压缩备份，此步骤为必须执行步骤，非压缩备份，此步骤忽略）/data/soft/xtrabackup8.0.35-34-glibc2.36/bin/xtrabackup --decompress --remove-original --target-dir=/data/backup/full/full_20251104_173434 2025-11-04T17:40:22.986591+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --server-id=13045 --innodb_io_capacity=2000 --datadir=/data/mysql/3306/data --log_bin=/data/mysql/3306/binlogs/mysql-bin --tmpdir=/data/mysql/3306/tmp --innodb_buffer_pool_size=1G --innodb_data_file_path=ibdata1:200M;ibdata2:200M:autoextend --innodb_flush_method=O_DIRECT --innodb_adaptive_hash_index=0 2025-11-04T17:40:22.986824+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --port=3306 --socket=/data/mysql/3306/run/mysql.sock --decompress=1 --remove-original=1 --target-dir=/data/backup/full/full_20251104_173434 /data/soft/xtrabackup8.0.35-34-glibc2.36/bin/xtrabackup version 8.0.35-34 based on MySQL server 8.0.35 Linux (x86_64) (revision id: c8a25ff9) 2025-11-04T17:40:22.988436+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./xtrabackup_logfile.zst 2025-11-04T17:40:23.000940+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./xtrabackup_logfile.zst 2025-11-04T17:40:23.001025+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./ibdata1.zst 2025-11-04T17:40:23.330061+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./ibdata1.zst 2025-11-04T17:40:23.344696+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./ibdata2.zst 2025-11-04T17:40:23.903032+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./ibdata2.zst 2025-11-04T17:40:23.903125+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./testdb/large_table.ibd.zst 2025-11-04T17:40:24.496119+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./testdb/large_table.ibd.zst 2025-11-04T17:40:24.594530+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./testdb/my_table.ibd.zst 2025-11-04T17:40:24.608587+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./testdb/my_table.ibd.zst 2025-11-04T17:40:24.616871+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./testdb/tt_new.ibd.zst 2025-11-04T17:40:24.629940+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./testdb/tt_new.ibd.zst 2025-11-04T17:40:24.630190+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./testdb/tt.ibd.zst 2025-11-04T17:40:24.635994+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./testdb/tt.ibd.zst 2025-11-04T17:40:24.636477+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./testdb/a.ibd.zst 2025-11-04T17:40:24.643492+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./testdb/a.ibd.zst 2025-11-04T17:40:24.644126+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./testdb/b.ibd.zst 2025-11-04T17:40:24.650521+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./testdb/b.ibd.zst 2025-11-04T17:40:24.650961+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./sys/sys_config.ibd.zst 2025-11-04T17:40:24.655719+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./sys/sys_config.ibd.zst 2025-11-04T17:40:24.656597+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./mysql.ibd.zst 2025-11-04T17:40:24.695278+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./mysql.ibd.zst 2025-11-04T17:40:24.699656+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./undo_002.zst 2025-11-04T17:40:24.722718+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./undo_002.zst 2025-11-04T17:40:24.722825+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./undo_001.zst 2025-11-04T17:40:24.755290+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./undo_001.zst 2025-11-04T17:40:24.755392+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./mysql/general_log_213.sdi.zst 2025-11-04T17:40:24.760715+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./mysql/general_log_213.sdi.zst 2025-11-04T17:40:24.760830+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./mysql/general_log.CSM.zst 2025-11-04T17:40:24.766136+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./mysql/general_log.CSM.zst 2025-11-04T17:40:24.766202+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./mysql/general_log.CSV.zst 2025-11-04T17:40:24.771030+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./mysql/general_log.CSV.zst 2025-11-04T17:40:24.771184+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./mysql/slow_log_214.sdi.zst 2025-11-04T17:40:24.776180+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./mysql/slow_log_214.sdi.zst 2025-11-04T17:40:24.776240+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./mysql/slow_log.CSM.zst 2025-11-04T17:40:24.781393+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./mysql/slow_log.CSM.zst 2025-11-04T17:40:24.781449+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./mysql/slow_log.CSV.zst 2025-11-04T17:40:24.785794+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./mysql/slow_log.CSV.zst 2025-11-04T17:40:24.785890+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/cond_instances_82.sdi.zst 2025-11-04T17:40:24.790641+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/cond_instances_82.sdi.zst 2025-11-04T17:40:24.790708+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/error_log_83.sdi.zst 2025-11-04T17:40:24.795417+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/error_log_83.sdi.zst 2025-11-04T17:40:24.795508+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_waits_his_85.sdi.zst 2025-11-04T17:40:24.800525+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_waits_his_85.sdi.zst 2025-11-04T17:40:24.800589+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_waits_cur_84.sdi.zst 2025-11-04T17:40:24.804941+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_waits_cur_84.sdi.zst 2025-11-04T17:40:24.805008+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_waits_his_86.sdi.zst 2025-11-04T17:40:24.809624+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_waits_his_86.sdi.zst 2025-11-04T17:40:24.809685+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_waits_sum_87.sdi.zst 2025-11-04T17:40:24.814165+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_waits_sum_87.sdi.zst 2025-11-04T17:40:24.814218+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_waits_sum_88.sdi.zst 2025-11-04T17:40:24.818631+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_waits_sum_88.sdi.zst 2025-11-04T17:40:24.818700+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_waits_sum_89.sdi.zst 2025-11-04T17:40:24.823346+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_waits_sum_89.sdi.zst 2025-11-04T17:40:24.823434+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_waits_sum_90.sdi.zst 2025-11-04T17:40:24.828325+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_waits_sum_90.sdi.zst 2025-11-04T17:40:24.828459+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_waits_sum_91.sdi.zst 2025-11-04T17:40:24.832657+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_waits_sum_91.sdi.zst 2025-11-04T17:40:24.832719+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_waits_sum_92.sdi.zst 2025-11-04T17:40:24.837125+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_waits_sum_92.sdi.zst 2025-11-04T17:40:24.837186+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/file_instances_93.sdi.zst 2025-11-04T17:40:24.841260+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/file_instances_93.sdi.zst 2025-11-04T17:40:24.841325+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/file_summary_by__94.sdi.zst 2025-11-04T17:40:24.845642+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/file_summary_by__94.sdi.zst 2025-11-04T17:40:24.845708+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/file_summary_by__95.sdi.zst 2025-11-04T17:40:24.850048+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/file_summary_by__95.sdi.zst 2025-11-04T17:40:24.850109+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/host_cache_96.sdi.zst 2025-11-04T17:40:24.854740+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/host_cache_96.sdi.zst 2025-11-04T17:40:24.854804+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/mutex_instances_97.sdi.zst 2025-11-04T17:40:24.859652+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/mutex_instances_97.sdi.zst 2025-11-04T17:40:24.859725+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/objects_summary__98.sdi.zst 2025-11-04T17:40:24.864826+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/objects_summary__98.sdi.zst 2025-11-04T17:40:24.864905+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/performance_time_99.sdi.zst 2025-11-04T17:40:24.869709+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/performance_time_99.sdi.zst 2025-11-04T17:40:24.869779+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/rwlock_instances_101.sdi.zst 2025-11-04T17:40:24.874756+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/rwlock_instances_101.sdi.zst 2025-11-04T17:40:24.874869+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/processlist_100.sdi.zst 2025-11-04T17:40:24.879190+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/processlist_100.sdi.zst 2025-11-04T17:40:24.879289+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/setup_actors_102.sdi.zst 2025-11-04T17:40:24.886036+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/setup_actors_102.sdi.zst 2025-11-04T17:40:24.886108+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/setup_consumers_103.sdi.zst 2025-11-04T17:40:24.890831+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/setup_consumers_103.sdi.zst 2025-11-04T17:40:24.890957+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/setup_instrument_104.sdi.zst 2025-11-04T17:40:24.895561+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/setup_instrument_104.sdi.zst 2025-11-04T17:40:24.895670+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/setup_objects_105.sdi.zst 2025-11-04T17:40:24.899829+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/setup_objects_105.sdi.zst 2025-11-04T17:40:24.899946+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/setup_threads_106.sdi.zst 2025-11-04T17:40:24.904601+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/setup_threads_106.sdi.zst 2025-11-04T17:40:24.904658+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/table_io_waits_s_107.sdi.zst 2025-11-04T17:40:24.909639+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/table_io_waits_s_107.sdi.zst 2025-11-04T17:40:24.909709+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/table_io_waits_s_108.sdi.zst 2025-11-04T17:40:24.914081+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/table_io_waits_s_108.sdi.zst 2025-11-04T17:40:24.914148+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/table_lock_waits_109.sdi.zst 2025-11-04T17:40:24.918343+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/table_lock_waits_109.sdi.zst 2025-11-04T17:40:24.918421+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/threads_110.sdi.zst 2025-11-04T17:40:24.922998+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/threads_110.sdi.zst 2025-11-04T17:40:24.923064+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_stages_cu_111.sdi.zst 2025-11-04T17:40:24.927213+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_stages_cu_111.sdi.zst 2025-11-04T17:40:24.927276+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_stages_hi_112.sdi.zst 2025-11-04T17:40:24.932218+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_stages_hi_112.sdi.zst 2025-11-04T17:40:24.932280+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_stages_hi_113.sdi.zst 2025-11-04T17:40:24.937361+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_stages_hi_113.sdi.zst 2025-11-04T17:40:24.937457+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_stages_su_114.sdi.zst 2025-11-04T17:40:24.941918+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_stages_su_114.sdi.zst 2025-11-04T17:40:24.941991+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_stages_su_115.sdi.zst 2025-11-04T17:40:24.946597+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_stages_su_115.sdi.zst 2025-11-04T17:40:24.946667+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_stages_su_116.sdi.zst 2025-11-04T17:40:24.951119+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_stages_su_116.sdi.zst 2025-11-04T17:40:24.951190+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_stages_su_117.sdi.zst 2025-11-04T17:40:24.956165+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_stages_su_117.sdi.zst 2025-11-04T17:40:24.956236+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_stages_su_118.sdi.zst 2025-11-04T17:40:24.960626+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_stages_su_118.sdi.zst 2025-11-04T17:40:24.960698+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_statement_119.sdi.zst 2025-11-04T17:40:24.964982+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_statement_119.sdi.zst 2025-11-04T17:40:24.965041+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_statement_120.sdi.zst 2025-11-04T17:40:24.969978+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_statement_120.sdi.zst 2025-11-04T17:40:24.970090+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_statement_121.sdi.zst 2025-11-04T17:40:24.974550+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_statement_121.sdi.zst 2025-11-04T17:40:24.974613+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_statement_122.sdi.zst 2025-11-04T17:40:24.978736+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_statement_122.sdi.zst 2025-11-04T17:40:24.978800+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_statement_123.sdi.zst 2025-11-04T17:40:24.987207+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_statement_123.sdi.zst 2025-11-04T17:40:24.987434+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_statement_124.sdi.zst 2025-11-04T17:40:25.012604+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_statement_124.sdi.zst 2025-11-04T17:40:25.013300+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_statement_125.sdi.zst 2025-11-04T17:40:25.019070+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_statement_125.sdi.zst 2025-11-04T17:40:25.019146+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_statement_127.sdi.zst 2025-11-04T17:40:25.025291+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_statement_127.sdi.zst 2025-11-04T17:40:25.025419+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_statement_126.sdi.zst 2025-11-04T17:40:25.031834+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_statement_126.sdi.zst 2025-11-04T17:40:25.031916+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_statement_128.sdi.zst 2025-11-04T17:40:25.036534+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_statement_128.sdi.zst 2025-11-04T17:40:25.036608+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_statement_129.sdi.zst 2025-11-04T17:40:25.041247+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_statement_129.sdi.zst 2025-11-04T17:40:25.041317+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_statement_130.sdi.zst 2025-11-04T17:40:25.045871+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_statement_130.sdi.zst 2025-11-04T17:40:25.045942+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_transacti_131.sdi.zst 2025-11-04T17:40:25.050464+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_transacti_131.sdi.zst 2025-11-04T17:40:25.050536+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_transacti_132.sdi.zst 2025-11-04T17:40:25.055547+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_transacti_132.sdi.zst 2025-11-04T17:40:25.055674+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_transacti_133.sdi.zst 2025-11-04T17:40:25.060633+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_transacti_133.sdi.zst 2025-11-04T17:40:25.060754+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_transacti_134.sdi.zst 2025-11-04T17:40:25.065871+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_transacti_134.sdi.zst 2025-11-04T17:40:25.065989+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_transacti_135.sdi.zst 2025-11-04T17:40:25.071009+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_transacti_135.sdi.zst 2025-11-04T17:40:25.071126+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_transacti_136.sdi.zst 2025-11-04T17:40:25.075979+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_transacti_136.sdi.zst 2025-11-04T17:40:25.076093+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_transacti_137.sdi.zst 2025-11-04T17:40:25.080663+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_transacti_137.sdi.zst 2025-11-04T17:40:25.080721+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_transacti_138.sdi.zst 2025-11-04T17:40:25.084942+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_transacti_138.sdi.zst 2025-11-04T17:40:25.085063+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_errors_su_139.sdi.zst 2025-11-04T17:40:25.089051+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_errors_su_139.sdi.zst 2025-11-04T17:40:25.089176+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_errors_su_140.sdi.zst 2025-11-04T17:40:25.093221+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_errors_su_140.sdi.zst 2025-11-04T17:40:25.093344+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_errors_su_141.sdi.zst 2025-11-04T17:40:25.097822+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_errors_su_141.sdi.zst 2025-11-04T17:40:25.097901+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_errors_su_142.sdi.zst 2025-11-04T17:40:25.102195+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_errors_su_142.sdi.zst 2025-11-04T17:40:25.102260+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/events_errors_su_143.sdi.zst 2025-11-04T17:40:25.106506+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/events_errors_su_143.sdi.zst 2025-11-04T17:40:25.106581+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/users_144.sdi.zst 2025-11-04T17:40:25.111263+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/users_144.sdi.zst 2025-11-04T17:40:25.111318+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/accounts_145.sdi.zst 2025-11-04T17:40:25.115990+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/accounts_145.sdi.zst 2025-11-04T17:40:25.116105+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/hosts_146.sdi.zst 2025-11-04T17:40:25.120821+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/hosts_146.sdi.zst 2025-11-04T17:40:25.120958+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/socket_instances_147.sdi.zst 2025-11-04T17:40:25.124883+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/socket_instances_147.sdi.zst 2025-11-04T17:40:25.125002+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/socket_summary_b_148.sdi.zst 2025-11-04T17:40:25.129788+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/socket_summary_b_148.sdi.zst 2025-11-04T17:40:25.129929+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/socket_summary_b_149.sdi.zst 2025-11-04T17:40:25.134033+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/socket_summary_b_149.sdi.zst 2025-11-04T17:40:25.134151+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/session_connect__150.sdi.zst 2025-11-04T17:40:25.138105+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/session_connect__150.sdi.zst 2025-11-04T17:40:25.138158+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/session_account__151.sdi.zst 2025-11-04T17:40:25.142150+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/session_account__151.sdi.zst 2025-11-04T17:40:25.142285+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/keyring_keys_152.sdi.zst 2025-11-04T17:40:25.146409+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/keyring_keys_152.sdi.zst 2025-11-04T17:40:25.146473+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/memory_summary_g_153.sdi.zst 2025-11-04T17:40:25.151175+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/memory_summary_g_153.sdi.zst 2025-11-04T17:40:25.151298+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/memory_summary_b_154.sdi.zst 2025-11-04T17:40:25.155247+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/memory_summary_b_154.sdi.zst 2025-11-04T17:40:25.155535+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/memory_summary_b_155.sdi.zst 2025-11-04T17:40:25.159725+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/memory_summary_b_155.sdi.zst 2025-11-04T17:40:25.159776+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/memory_summary_b_156.sdi.zst 2025-11-04T17:40:25.164259+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/memory_summary_b_156.sdi.zst 2025-11-04T17:40:25.164397+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/memory_summary_b_157.sdi.zst 2025-11-04T17:40:25.168750+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/memory_summary_b_157.sdi.zst 2025-11-04T17:40:25.168825+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/table_handles_158.sdi.zst 2025-11-04T17:40:25.173560+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/table_handles_158.sdi.zst 2025-11-04T17:40:25.173620+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/metadata_locks_159.sdi.zst 2025-11-04T17:40:25.177949+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/metadata_locks_159.sdi.zst 2025-11-04T17:40:25.178012+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/data_locks_160.sdi.zst 2025-11-04T17:40:25.182902+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/data_locks_160.sdi.zst 2025-11-04T17:40:25.182965+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/data_lock_waits_161.sdi.zst 2025-11-04T17:40:25.187544+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/data_lock_waits_161.sdi.zst 2025-11-04T17:40:25.187609+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/replication_conn_162.sdi.zst 2025-11-04T17:40:25.192416+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/replication_conn_162.sdi.zst 2025-11-04T17:40:25.192485+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/replication_grou_163.sdi.zst 2025-11-04T17:40:25.197412+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/replication_grou_163.sdi.zst 2025-11-04T17:40:25.197478+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/replication_conn_164.sdi.zst 2025-11-04T17:40:25.201947+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/replication_conn_164.sdi.zst 2025-11-04T17:40:25.202015+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/replication_appl_165.sdi.zst 2025-11-04T17:40:25.206022+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/replication_appl_165.sdi.zst 2025-11-04T17:40:25.206082+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/replication_appl_166.sdi.zst 2025-11-04T17:40:25.210440+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/replication_appl_166.sdi.zst 2025-11-04T17:40:25.210503+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/replication_appl_167.sdi.zst 2025-11-04T17:40:25.214871+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/replication_appl_167.sdi.zst 2025-11-04T17:40:25.214936+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/replication_appl_168.sdi.zst 2025-11-04T17:40:25.219740+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/replication_appl_168.sdi.zst 2025-11-04T17:40:25.219814+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/replication_grou_169.sdi.zst 2025-11-04T17:40:25.224172+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/replication_grou_169.sdi.zst 2025-11-04T17:40:25.224245+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/replication_appl_170.sdi.zst 2025-11-04T17:40:25.228351+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/replication_appl_170.sdi.zst 2025-11-04T17:40:25.228468+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/replication_appl_171.sdi.zst 2025-11-04T17:40:25.233203+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/replication_appl_171.sdi.zst 2025-11-04T17:40:25.233319+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/replication_asyn_172.sdi.zst 2025-11-04T17:40:25.238933+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/replication_asyn_172.sdi.zst 2025-11-04T17:40:25.239094+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/replication_asyn_173.sdi.zst 2025-11-04T17:40:25.246187+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/replication_asyn_173.sdi.zst 2025-11-04T17:40:25.246284+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/log_status_174.sdi.zst 2025-11-04T17:40:25.253874+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/log_status_174.sdi.zst 2025-11-04T17:40:25.253967+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/prepared_stateme_175.sdi.zst 2025-11-04T17:40:25.272402+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/prepared_stateme_175.sdi.zst 2025-11-04T17:40:25.272496+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/user_variables_b_176.sdi.zst 2025-11-04T17:40:25.280533+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/user_variables_b_176.sdi.zst 2025-11-04T17:40:25.280617+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/status_by_accoun_177.sdi.zst 2025-11-04T17:40:25.288632+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/status_by_accoun_177.sdi.zst 2025-11-04T17:40:25.288727+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/status_by_host_178.sdi.zst 2025-11-04T17:40:25.296271+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/status_by_host_178.sdi.zst 2025-11-04T17:40:25.296471+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/status_by_thread_179.sdi.zst 2025-11-04T17:40:25.303218+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/status_by_thread_179.sdi.zst 2025-11-04T17:40:25.303416+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/status_by_user_180.sdi.zst 2025-11-04T17:40:25.315732+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/status_by_user_180.sdi.zst 2025-11-04T17:40:25.315917+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/global_status_181.sdi.zst 2025-11-04T17:40:25.321558+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/global_status_181.sdi.zst 2025-11-04T17:40:25.321637+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/session_status_182.sdi.zst 2025-11-04T17:40:25.325961+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/session_status_182.sdi.zst 2025-11-04T17:40:25.326037+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/variables_by_thr_183.sdi.zst 2025-11-04T17:40:25.330807+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/variables_by_thr_183.sdi.zst 2025-11-04T17:40:25.330892+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/global_variables_184.sdi.zst 2025-11-04T17:40:25.335674+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/global_variables_184.sdi.zst 2025-11-04T17:40:25.335749+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/session_variable_185.sdi.zst 2025-11-04T17:40:25.340530+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/session_variable_185.sdi.zst 2025-11-04T17:40:25.340655+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/variables_info_186.sdi.zst 2025-11-04T17:40:25.345478+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/variables_info_186.sdi.zst 2025-11-04T17:40:25.345584+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/persisted_variab_187.sdi.zst 2025-11-04T17:40:25.349818+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/persisted_variab_187.sdi.zst 2025-11-04T17:40:25.349892+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/user_defined_fun_188.sdi.zst 2025-11-04T17:40:25.354039+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/user_defined_fun_188.sdi.zst 2025-11-04T17:40:25.354102+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/binary_log_trans_189.sdi.zst 2025-11-04T17:40:25.358649+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/binary_log_trans_189.sdi.zst 2025-11-04T17:40:25.358752+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/tls_channel_stat_190.sdi.zst 2025-11-04T17:40:25.362754+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/tls_channel_stat_190.sdi.zst 2025-11-04T17:40:25.362826+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/keyring_componen_191.sdi.zst 2025-11-04T17:40:25.366995+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/keyring_componen_191.sdi.zst 2025-11-04T17:40:25.367058+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/clone_status_406.sdi.zst 2025-11-04T17:40:25.371839+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/clone_status_406.sdi.zst 2025-11-04T17:40:25.371910+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./performance_schema/clone_progress_407.sdi.zst 2025-11-04T17:40:25.376354+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./performance_schema/clone_progress_407.sdi.zst 2025-11-04T17:40:25.376483+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./mysql-bin.000025.zst 2025-11-04T17:40:25.380522+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./mysql-bin.000025.zst 2025-11-04T17:40:25.380581+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./mysql-bin.index.zst 2025-11-04T17:40:25.384675+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./mysql-bin.index.zst 2025-11-04T17:40:25.384736+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./xtrabackup_binlog_info.zst 2025-11-04T17:40:25.389292+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./xtrabackup_binlog_info.zst 2025-11-04T17:40:25.389350+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./ib_buffer_pool.zst 2025-11-04T17:40:25.393580+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./ib_buffer_pool.zst 2025-11-04T17:40:25.393640+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./backup-my.cnf.zst 2025-11-04T17:40:25.398334+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./backup-my.cnf.zst 2025-11-04T17:40:25.398411+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./xtrabackup_info.zst 2025-11-04T17:40:25.402541+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./xtrabackup_info.zst 2025-11-04T17:40:25.402600+08:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./xtrabackup_tablespaces.zst 2025-11-04T17:40:25.407024+08:00 0 [Note] [MY-011825] [Xtrabackup] removing ./xtrabackup_tablespaces.zst 2025-11-04T17:40:25.510589+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK! 2、prepare步骤xtrabackup --prepare --target-dir=/data/backup/full/full\_20251104\_173434prepare是物理备份过程中进行恢复前必做的一个环节，它的作用是将备份时处于不一致状态的数据文件，处理成一个具有数据一致性的、可供数据库直接启动和使用的完整备份集。简单来说，如果不执行 --prepare步骤，直接使用 --backup得到的原始备份文件来启动数据库，InnoDB存储引擎会检测到数据文件内部不一致（例如页面LSN不匹配），并将其视为损坏的数据文件，从而拒绝启动。这是因为XtraBackup在备份期间，为了尽可能减少对数据库性能的影响，是采用类似“快照”的方式拷贝InnoDB数据文件（.ibd）的。拷贝过程中，数据库可能仍在处理事务，这就导致备份集内的数据文件在同一时刻的状态可能并不一致。–prepare步骤正是通过应用备份期间同时拷贝的redo日志，来修复这种不一致性，确保数据恢复到备份操作完成那一刻的一致性状态。[root@VM-8-4-opencloudos backup]# /data/soft/xtrabackup8.0.35-34-glibc2.36/bin/xtrabackup --prepare --target-dir=/data/backup/full/full_20251104_173434 2025-11-04T17:44:13.715402+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:200M;ibdata2:200M:autoextend --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=13045 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0 2025-11-04T17:44:13.715565+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --target-dir=/data/backup/full/full_20251104_173434 /data/soft/xtrabackup8.0.35-34-glibc2.36/bin/xtrabackup version 8.0.35-34 based on MySQL server 8.0.35 Linux (x86_64) (revision id: c8a25ff9) 2025-11-04T17:44:13.715602+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /data/backup/full/full_20251104_173434/ 2025-11-04T17:44:13.716155+08:00 0 [Note] [MY-011825] [Xtrabackup] This target seems to be not prepared yet. 2025-11-04T17:44:13.730583+08:00 0 [Note] [MY-011825] [Xtrabackup] xtrabackup_logfile detected: size=8388608, start_lsn=(395899109) 2025-11-04T17:44:13.743037+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery: 2025-11-04T17:44:13.743072+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = . 2025-11-04T17:44:13.743084+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:200M;ibdata2:200M:autoextend 2025-11-04T17:44:13.743119+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = . 2025-11-04T17:44:13.743127+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 1 2025-11-04T17:44:13.743135+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 8388608 2025-11-04T17:44:13.744761+08:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded 2025-11-04T17:44:13.745043+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery: 2025-11-04T17:44:13.745062+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = . 2025-11-04T17:44:13.745069+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:200M;ibdata2:200M:autoextend 2025-11-04T17:44:13.745089+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = . 2025-11-04T17:44:13.745100+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 1 2025-11-04T17:44:13.745110+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 8388608 2025-11-04T17:44:13.745125+08:00 0 [Note] [MY-011825] [Xtrabackup] Starting InnoDB instance for recovery. 2025-11-04T17:44:13.745137+08:00 0 [Note] [MY-011825] [Xtrabackup] Using 104857600 bytes for buffer pool (set by --use-memory parameter) 2025-11-04T17:44:13.746141+08:00 0 [Note] [MY-012932] [InnoDB] PUNCH HOLE support available 2025-11-04T17:44:13.746162+08:00 0 [Note] [MY-012944] [InnoDB] Uses event mutexes 2025-11-04T17:44:13.746170+08:00 0 [Note] [MY-012945] [InnoDB] GCC builtin __atomic_thread_fence() is used for memory barrier 2025-11-04T17:44:13.746183+08:00 0 [Note] [MY-012948] [InnoDB] Compressed tables use zlib 1.2.13 2025-11-04T17:44:13.746475+08:00 0 [Note] [MY-012951] [InnoDB] Using hardware accelerated crc32 and polynomial multiplication. 2025-11-04T17:44:13.746898+08:00 0 [Note] [MY-012203] [InnoDB] Directories to scan './' 2025-11-04T17:44:13.746943+08:00 0 [Note] [MY-012204] [InnoDB] Scanning './' 2025-11-04T17:44:13.753807+08:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 10 files. 2025-11-04T17:44:13.756116+08:00 0 [Note] [MY-012955] [InnoDB] Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M 2025-11-04T17:44:13.765015+08:00 0 [Note] [MY-012957] [InnoDB] Completed initialization of buffer pool 2025-11-04T17:44:13.770906+08:00 0 [Note] [MY-011951] [InnoDB] page_cleaner coordinator priority: -20 2025-11-04T17:44:13.771119+08:00 0 [Note] [MY-011954] [InnoDB] page_cleaner worker priority: -20 2025-11-04T17:44:13.771183+08:00 0 [Note] [MY-011954] [InnoDB] page_cleaner worker priority: -20 2025-11-04T17:44:13.771564+08:00 0 [Note] [MY-011954] [InnoDB] page_cleaner worker priority: -20 2025-11-04T17:44:13.824205+08:00 0 [Note] [MY-013883] [InnoDB] The latest found checkpoint is at lsn = 395899109 in redo log file ./ #innodb _redo/ #ib _redo0. 2025-11-04T17:44:13.824287+08:00 0 [Note] [MY-012560] [InnoDB] The log sequence number 395730540 in the system tablespace does not match the log sequence number 395899109 in the redo log files! 2025-11-04T17:44:13.824305+08:00 0 [Note] [MY-012551] [InnoDB] Database was not shutdown normally! 2025-11-04T17:44:13.824312+08:00 0 [Note] [MY-012552] [InnoDB] Starting crash recovery. 2025-11-04T17:44:13.824504+08:00 0 [Note] [MY-013086] [InnoDB] Starting to parse redo log at lsn = 395898899, whereas checkpoint_lsn = 395899109 and start_lsn = 395898880 2025-11-04T17:44:13.824519+08:00 0 [Note] [MY-012550] [InnoDB] Doing recovery: scanned up to log sequence number 395899109 2025-11-04T17:44:13.843504+08:00 0 [Note] [MY-013083] [InnoDB] Log background threads are being started... 2025-11-04T17:44:13.851994+08:00 0 [Note] [MY-012532] [InnoDB] Applying a batch of 0 redo log records ... 2025-11-04T17:44:13.852026+08:00 0 [Note] [MY-012535] [InnoDB] Apply batch completed! 2025-11-04T17:44:13.952255+08:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed... 2025-11-04T17:44:13.957800+08:00 0 [Note] [MY-013888] [InnoDB] Upgrading redo log: 1032M, LSN=395899109. 2025-11-04T17:44:13.957898+08:00 0 [Note] [MY-012968] [InnoDB] Starting to delete and rewrite redo log files. 2025-11-04T17:44:13.957956+08:00 0 [Note] [MY-011825] [InnoDB] Removing redo log file: ./ #innodb _redo/ #ib _redo0 2025-11-04T17:44:14.004652+08:00 0 [Note] [MY-011825] [InnoDB] Creating redo log file at ./ #innodb _redo/ #ib _redo0_tmp with file_id 0 with size 33554432 bytes 2025-11-04T17:44:14.008311+08:00 0 [Note] [MY-011825] [InnoDB] Renaming redo log file from ./ #innodb _redo/ #ib _redo0_tmp to ./ #innodb _redo/ #ib _redo0 2025-11-04T17:44:14.011183+08:00 0 [Note] [MY-012893] [InnoDB] New redo log files created, LSN=395899404 2025-11-04T17:44:14.011284+08:00 0 [Note] [MY-013083] [InnoDB] Log background threads are being started... 2025-11-04T17:44:14.023796+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_001'. 2025-11-04T17:44:14.025816+08:00 0 [Note] [MY-013252] [InnoDB] Using undo tablespace './undo_002'. 2025-11-04T17:44:14.027533+08:00 0 [Note] [MY-012910] [InnoDB] Opened 2 existing undo tablespaces. 2025-11-04T17:44:14.027698+08:00 0 [Note] [MY-011980] [InnoDB] GTID recovery trx_no: 1044267 2025-11-04T17:44:14.226936+08:00 0 [Note] [MY-013776] [InnoDB] Parallel initialization of rseg complete 2025-11-04T17:44:14.226983+08:00 0 [Note] [MY-013777] [InnoDB] Time taken to initialize rseg using 2 thread: 199297 ms. 2025-11-04T17:44:14.229195+08:00 0 [Note] [MY-012923] [InnoDB] Creating shared tablespace for temporary tables 2025-11-04T17:44:14.229302+08:00 0 [Note] [MY-012265] [InnoDB] Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2025-11-04T17:44:14.260630+08:00 0 [Note] [MY-012266] [InnoDB] File './ibtmp1' size is now 12 MB. 2025-11-04T17:44:14.262027+08:00 0 [Note] [MY-013627] [InnoDB] Scanning temp tablespace dir:'./ #innodb _temp/' 2025-11-04T17:44:14.287708+08:00 0 [Note] [MY-013018] [InnoDB] Created 128 and tracked 128 new rollback segment(s) in the temporary tablespace. 128 are now active. 2025-11-04T17:44:14.291471+08:00 0 [Note] [MY-012976] [InnoDB] 8.0.35 started; log sequence number 395899414 2025-11-04T17:44:14.292531+08:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 1 for sys/sys_config, old maximum was 0 2025-11-04T17:44:14.301258+08:00 0 [Note] [MY-011825] [Xtrabackup] Completed loading of 8 tablespaces into cache in 0.00972792 seconds 2025-11-04T17:44:14.332275+08:00 0 [Note] [MY-011825] [Xtrabackup] Time taken to build dictionary: 0.0309666 seconds 2025-11-04T17:44:15.336933+08:00 0 [Note] [MY-011825] [Xtrabackup] starting shutdown with innodb_fast_shutdown = 1 2025-11-04T17:44:15.337061+08:00 0 [Note] [MY-012330] [InnoDB] FTS optimize thread exiting. 2025-11-04T17:44:16.337034+08:00 0 [Note] [MY-013072] [InnoDB] Starting shutdown... 2025-11-04T17:44:16.440671+08:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed... 2025-11-04T17:44:16.461318+08:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 395899414 2025-11-04T17:44:16.476016+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!当执行prapare过程中 最后出现 completed OK! 关键字时，表明操作成功。3、停止MySQL服务并清空数据目录为确保恢复过程顺利，首先需要停止MySQL服务，并清空其数据目录（datadir）。这是恢复操作的关键前提-- 停止MySQL服务 systemctl stop mysqld -- 或者登录MySQL数据库 执行 shutdown; 命令重要提示：在执行rm -rf命令前，务必确认目录路径正确，最好对原有数据做备份。然后清空数据目录：# 清空MySQL数据目录（请先确认你的datadir路径，假设是/var/lib/mysql） rm -rf /var/lib/mysql/* 4、执行数据恢复使用 --copy-back或 --move-back命令将预备好的备份数据恢复到MySQL的数据目录xtrabackup --copy-back --target-dir=/path/to/prepared_backup 说明： --copy-back：将备份文件复制到数据目录。这是最安全常用的方式，保留原始备份 --move-back：将备份文件移动到数据目录。更节省空间，但原始备份会消失5、修改文件权限恢复的数据文件可能不属于mysql用户，需要更改属主和权限以确保MySQL有权限读写chown -R mysql:mysql /var/lib/mysql 此命令将数据目录及其下所有文件的所有者和组设置为mysql6、启动MySQL并确认权限设置好以后，就可以启动MySQL服务了systemctl start mysqld -- 或者使用mysqld_safe --defaults-file=/etc/my.cnf --user=mysql & 方式启动启动后，务必检查MySQL的错误日志，并使用客户端连接，验证数据库和表是否正常。总 结该脚本提供了一个生产环境进行MySQL8.0物理备份所需的完整步骤，包括错误处理、日志记录、自动清理和耗时统计。数据库运维人员可以根据实际环境调整配置参数，特别是备份路径和保留天数设置以及是否采用压缩等一些常用功能的设置。墨天轮从乐知乐享的数据库技术社区蓄势出发，全面升级，提供多类型数据库管理服务。墨天轮数据库管理服务旨在为用户构建信赖可托付的数据库环境，并为数据库厂商提供中立的生态支持。墨天轮数据库服务官网：https://www.modb.pro/service
直播预告 | 全景解析 KAT 数据库智能体工具
小公司开发有什么需要避免的操作吗
目前背景: 是个小公司的后端开发,服务器,数据库,git,公司路由器都在我手里,感觉责任重大,想知道有什么需要避免的操作吗,主要是害怕万一一周或者几个月我不在,避免公司业务出问题以下是我自己想到的几点,想知道还有没有其他没想到的方案1.数据库的分区表,特别是和时间相关的,不能手动生成,要不然后面人不在没交代的话会直接给服务器崩掉2.给本地调试的服务器端口白名单,不能图方便给网段开白名单,要不然后面会有脚本小子或者勒索病毒来扫3.一些公共库,如果有不符合业务需求的地方,不要把人家的源码拉下来自己改然后编译成引用放在库里,(比如说 ef core upsert 的库之前不支持 ef10)否则后面更新时候会炸掉4.单元测试不要埋雷,一些当调试用的或临时跑点小任务单元测试不能放在例行单元测试的文件夹里面,污染数据库
pgBackRest简介pgBackRest 旨在提供一个简单可靠，容易纵向扩展的 PostgreSQL 备份恢复系统。pgBackRest 并不依赖像 tar 和 rsync 这样的传统备份工具，而是通过在内部实现所有备份功能，并使用自定义协议来与远程系统进行通信。 消除对 tar 和 rsync 的依赖可以更好地解决特定于数据库的备份问题。 自定义远程协议提供了更多的灵活性，并限制执行备份所需的连接类型，从而提高安全性。相关网站pgBackRest 主页：http://pgbackrest.org手册：pgBackRest User Guide - RHELpgBackRestGithub 主页：GitHub - pgbackrest/pgbackrest: Reliable PostgreSQL Backup & RestorepgbackRest 特征并行备份和还原本地或远程操作完整，增量和差异备份备份轮换和存档到期备份完整性页面校验和备份恢复流压缩和校验和增量还原并行，异步 WAL Push＆Get表空间和链接支持S3、Azure 和 GCS 兼容对象存储支持加密与 PostgreSQL > = 8.3 的兼容性pgbackRest 安装 ip软件角色 192.168.1.11postgres,pgbackrestprimary192.168.1.12postgres,pgbackreststandby192.168.1.13postgres,standby192.168.1.16pgbackrest远程备份工具端 ubuntu (所有节点) ：sudo apt-get install pgbackrest 创建所需目录并赋予权限su - root mkdir -p -m 770 /var/log/pgbackrest chown postgres.postgres /var/log/pgbackrest/ chown postgres.postgres -R /etc/pgbackrest/ 主从 pgbackrest 配置文件su postgres vim /etc/pgbackrest.conf [pg_5433] pg1-port=5433 pg1-path=/data/postgresql/pgdata/pg-5433/ pg1-socket-path=/var/run/postgresql/ [global] repo1-host=192.168.28.14 repo1-host-user=postgres repo1-path=/var/lib/pgbackrest repo1-retention-full=2 process-max=3 [global:archive-push] compress-level=3 主库参数解释：[pg_5433] 部分 这个 pg_5433 是备份集群的名字，多个备份集群可以添加 比如：[test-1],[test-2] 等等 pg1-path=/data/postgresql/pgdata/pg-5433/: 指定 PostgreSQL 数据库实例的数据目录路径。这是 pgBackRest 需要备份的主要内容所在的位置。 pg1-socket-path=/var/run/postgresql/: 指定 PostgreSQL 服务器的套接字文件（socket file）路径。pgBackRest 使用这个路径来通过 UNIX 套接字连接到数据库。 pg1-user=postgres: 定义 pgBackRest 连接到 PostgreSQL 实例时应该使用的用户名。这个用户需要有足够的权限来读取数据库文件和执行备份相关的操作。 [global] 部分 这部分的配置适用于 pgBackRest 的全局设置，影响所有备份和恢复操作。 repo1-host=192.168.28.14: 指定远程备份仓库的主机地址。这表明备份数据将被存储在指定 IP 地址的服务器上。pgBackRest 支持多个备份仓库，这里的 repo1 表示第一个仓库。 repo1-host-user=postgres: 定义访问远程备份仓库主机时使用的用户名。postgres 将以这个用户的身份在远程主机上执行操作。 log-level-file=detail: 设置文件日志记录的详细级别。detail 级别会记录更详细的操作信息，有助于故障排查和监控备份过程。 log-path=/var/log/pgbackrest: 指定日志文件的存储路径。pgBackRest 会将运行日志写入这个目录下，便于后续的日志分析和问题定位。 远程备份工具端配置文件su postgres vim /etc/pgbackrest.conf [pg_5433] pg1-path=/data/postgresql/pgdata/pg-5433/ pg1-port=5433 pg1-socket-path=/var/run/postgresql/ pg1-host-config=/etc/pgbackrest.conf pg1-user=postgres pg1-host=192.168.28.11 pg1-host-port=22 pg1-host-user=postgres pg2-path=/data/postgresql/pgdata/pg-5433/ pg2-port=5433 pg2-socket-path=/var/run/postgresql/ pg2-host-config=/etc/pgbackrest.conf pg2-user=postgres pg2-host=192.168.28.12 pg2-host-port=22 pg2-host-user=postgres pg3-path=/data/postgresql/pgdata/pg-5433/ pg3-port=5433 pg3-socket-path=/var/run/postgresql/ pg3-host-config=/etc/pgbackrest.conf pg3-user=postgres pg3-host=192.168.28.13 pg3-host-port=22 pg3-host-user=postgres [global] backup-standby=y process-max=3 start-fast=y repo1-path=/var/lib/pgbackrest repo1-retention-full=2 backup-user=postgres log-level-console=info log-level-file=debug buffer-size=16MiB compress-type=gz [global:archive-push] compress-level=3 工具端参数解释[pg_5433] 这个 pg_5433 是备份集群的名字，多个备份集群可以添加 比如：[test-1],[test-2] 等等 pg1-path ：指定了数据库实例的数据目录路径。 pg1-port ：定义了实例的端口号，通常 PostgreSQL 默认端口是 5432。 pg1-socket-path：指定了 UNIX 套接字文件的路径，用于本地连接。 pg1-user ：定义 pgBackRest 连接到各个 PostgreSQL 实例时使用的用户名。 pg1-host-config-path ：指定远程主机上 pgBackRest 配置文件的路径。 pg1-host：定义了各实例所在的主机地址。 pg1-host-port ：指定了用于 SSH 连接的端口号，默认为 22。 pg1-host-user：定义了 SSH 连接时使用的用户名。 [global] 部分 这部分定义了全局备份策略和行为。 backup-standby=y：启用备份从备用服务器进行，这有助于减少对生产数据库的性能影响。 process-max=3：定义了 pgBackRest 同时执行任务的最大进程数。 start-fast=y：启用快速启动模式，尝试减少备份期间的停机时间。 repo1-path：指定了备份仓库的路径。 repo1-retention-full=2：定义了完整备份的保留数量，超过这个数量的旧备份将被删除。 backup-user：指定执行备份操作的用户。 log-level-console：设置控制台日志级别。 log-level-file：设置文件日志的详细级别。 buffer-size：定义了缓冲区大小，用于优化性能。 compress-type=gz：指定了压缩类型，这里使用的是 gzip。 [global:archive-push] 部分 这部分专门用于配置归档推送操作。 compress-level=3：定义了压缩级别，数值越高，压缩效果越好，但需要更多的 CPU 资源。 ssh 免密登陆配置在各个服务器上，生成 postgres 的 ssh 密钥su postgres ssh-keygen -t rsa -b 4096 -f ~/.ssh/id_rsa -N "" 在主从服务器上 postgres 用户 配置备份服务器 postgres 的用户公钥登录备份服务器 192.168.28.14su postgres cd ~ cat ./.ssh/id_rsa.pub ssh-rsa [binary] postgres@k8s-node01 在主从服务器上添加备份服务器的公钥root@k8s-master01:~# su postgres postgres@k8s-master01:/root$cd ~ postgres@k8s-master01:~$vim ./.ssh/authorized_keys 同样的在备份服务器的 postgres 的用户上配置主从服务器上 postgres 用户的公钥设置 postgresql 归档在主库上修改 archive 归档patronictl -c /etc/patroni/patroni-5433.yaml edit-config archive_mode = on archive_command = 'pgbackrest --stanza=pg_5433 archive-push %p' max_wal_senders = 3 stanza 名需要和配置文件一致修改完之后，通过 patronictl 重启所有数据库patronictl -c /etc/patroni/patroni-5433.yaml restart pg_patroni_etcd pg_patroni_5433_01 patronictl -c /etc/patroni/patroni-5433.yaml restart pg_patroni_etcd pg_patroni_5433_02 patronictl -c /etc/patroni/patroni-5433.yaml restart pg_patroni_etcd pg_patroni_5433_03 初始化备份在备份服务器上执行su postgres pgbackrest --stanza=pg_5433 --log-level-console=info stanza-create #删除方式 pgbackrest --stanza=pg_5433 stop pgbackrest --stanza=pg_5433 stanza-delete --force 检查配置pgbackrest --stanza=pg_5433 --log-level-console=info check #执行结果如下 postgres@k8s-node01:~$ pgbackrest --stanza=pg_5433 --log-level-console=info check 2025-10-27 15:40:57.655 P00 INFO: check command begin 2.50: --backup-standby --buffer-size=16MiB --exec-id=3943474-ea8cda56 --log-level-console=info --log-level-file=debug --pg1-host=192.168.28.25 --pg1-host-config=/etc/pgbackrest.conf --pg1-host-port=22 --pg1-host-user=postgres --pg1-path=/data/postgresql/pgdata/pg-5433/ --pg1-port=5433 --pg1-user=postgres --repo1-path=/var/lib/pgbackrest --stanza=pg_5433 WARN: option 'backup-standby' is enabled but standby is not properly configured 2025-10-27 15:41:00.980 P00 INFO: check repo1 configuration (primary) 2025-10-27 15:41:01.387 P00 INFO: check repo1 archive for WAL (primary) 2025-10-27 15:41:06.703 P00 INFO: WAL segment 000000230000000000000059 successfully archived to '/var/lib/pgbackrest/archive/pg_5433/16-1/0000002300000000/000000230000000000000059-0c80b7903193612ee31642ac12c2548bba36bc1b.gz' on repo1 2025-10-27 15:41:06.806 P00 INFO: check command end: completed successfully (9159ms) 完全备份在备份机上操作即可 pgbackrest --stanza=pg_5433 --log-level-console=info backup --type=full 增量备份pgbackrest pgbackrest --stanza=pg_5433 --log-level-console=info backup --type=incr 差异备份pgbackrest pgbackrest --stanza=pg_5433 --log-level-console=info backup --type=diff 查看备份信息备份恢复这个恢复操作的一台新的 postgresql 主机上操作，并配置好 pgbackrest.conf 文件 和 备份机可以免密登陆 ssh 登陆 # 全量恢复 $ pgbackrest --stanza=pg_5433 restore --pg1-path=/data/postgresql/pgdata/pg-5433/ # 指定某个备份恢复 pgbackrest --stanza=pg_5433 --set=20251027-134949F restore # 基于lsn恢复 # 指定备份策略，获取对应的lsn：lsn start/stop pgbackrest --stanza=pg_5433 --set=20250713-195747F_20250713-195909I info pgbackrest --stanza=pg_5433 --type=lsn --target="0/41000028" restore # 基于时间点恢复 pgbackrest --stanza=pg_5433 --delta --type=time "--target=2025-10-27 14:11:02+08" restore 常用命令# 创建存储库 pgbackrest --stanza=pg_5433 stanza-create # 删除存储库 pgbackrest --stanza=pg_5433 stanza-delete # 更新存储库 pgbackrest --stanza=pg_5433 stanza-upgrade # 启用备份 pgbackrest --stanza=pg_5433 start # 停用备份 pgbackrest --stanza=pg_5433 stop # 备份数据 pgbackrest --stanza=pg_5433 backup # 恢复备份 pgbackrest --stanza=pg_5433 restore # 查看备份 pgbackrest --stanza=pg_5433 info # 检查备份是否过期 pgbackrest expire --stanza=pg_5433 pgbackrest expire --set=20250713-195747F_20250713-195909I --stanza=demo # 检查配置 pgbackrest --stanza=pg_5433 check # 获取存储库信息 # 疑似有bug，执行报错：ERROR: [032]: unable to determine cipher passphrase for '' pgbackrest --stanza=pg_5433 --config=/pg14/pgbackrest/etc/pgbackrest.conf repo-get /pg14/pgbackrest/lib pgbackrest_exporter 监控备份状态一般我们可以通过 pgbackrest info 去查看备份状态，但是这样并不直观所以可以借助 exporter 获取 pgbackrest 的状态，从而及时监控到备份信息安装 pgbackrest_exporterpgbackrest_exporter 支持二进制运行，同时也支持 docker 运行，此处用二进制服务运行的方式下载对应服务器版本的二进制包wget https://ghfast.top/https://github.com/woblerr/pgbackrest_exporter/releases/download/v0.21.0/pgbackrest_exporter-0.21.0-linux-x86_64.tar.gz tar -zxvf pgbackrest_exporter-0.21.0-linux-x86_64.tar.gz mv pgbackrest_exporter-0.21.0-linux-x86_64/pgbackrest_exporter /usr/local/bin/pgbackrest_exporter chown postgres:postgres /usr/local/bin/pgbackrest_exporter 创建 pgbackrest_exporter.servicetouch /usr/lib/systemd/system/pgbackrest-exporter.service vim /usr/lib/systemd/system/pgbackrest-exporter.service [Unit] Description=pgbackrest-exporter Service After=network.target [Service] Type=simple User=postgres ExecStart=/usr/local/bin/pgbackrest_exporter --backrest.config=/etc/pgbackrest.conf Restart=on-failure RestartSec=10 LimitNOFILE=65536 [Install] WantedBy=multi-user.target Alias=pgbackrest-exporter.service 启动 pgbackrest_exporter.servicesystemctl daemon-reload systemctl start pgbackrest-exporter.service systemctl enable pgbackrest-exporter.service 通过 grafana 展示页面grafana id：17709ON_Backs 通知 脚本#!/usr/bin/env python3 """ Patroni钉钉通知脚本 - 增强诊断和错误修复版 主要解决：日志显示发送成功但实际未收到消息的问题 作者：资深SRE/数据库高可用架构师 """ import os import string import subprocess import sys import json import logging import requests import socket import traceback from datetime import datetime import time # 新增时间模块用于重试 import patroni # 钉钉Webhook配置 - 添加详细的验证逻辑 DINGTALK_WEBHOOK = 'https://oapi.dingtalk.com/robot/send?access_token=********' if not DINGTALK_WEBHOOK: print("错误：钉钉Webhook环境变量未设置，请配置 DINGTALK_WEBHOOK_URL", file=sys.stderr) sys.exit(1) # 高级日志配置 - 确保诊断信息完整 logger = logging.getLogger('patroni-dingtalk') logger.setLevel(logging.DEBUG) formatter = logging.Formatter('%(asctime)s [%(levelname)s] %(message)s') # 文件日志 file_handler = logging.FileHandler("/var/log/patroni/dingtalk_diagnostic.log") file_handler.setFormatter(formatter) logger.addHandler(file_handler) # 控制台日志 stdout_handler = logging.StreamHandler(sys.stdout) stdout_handler.setFormatter(formatter) logger.addHandler(stdout_handler) def verify_dingtalk_network(): """验证网络连通性到钉钉服务器""" target_host = "oapi.dingtalk.com" target_port = 443 try: # 创建socket连接检查 sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM) sock.settimeout(3) result = sock.connect_ex((target_host, target_port)) sock.close() if result == 0: logger.info(f"网络连通性检查: 可访问 {target_host}:{target_port}") return True else: logger.error(f"无法连接钉钉服务器 {target_host}:{target_port}, 错误代码: {result}") return False except socket.gaierror: logger.error("DNS解析故障 - 无法解析钉钉服务器地址") return False except Exception as e: logger.exception(f"网络检查异常: {str(e)}") return False def send_with_retry(message_data): """带重试机制的消息发送函数 - 解决偶发网络问题""" headers = {'Content-Type': 'application/json'} max_retries = 3 retry_delay = 2 # seconds for attempt in range(max_retries): try: # 详细记录实际发送的内容 logger.debug( f"发送请求 (尝试 #{attempt + 1}):\nURL: {DINGTALK_WEBHOOK}\nBody: {json.dumps(message_data, indent=2)}") response = requests.post( DINGTALK_WEBHOOK, json=message_data, headers=headers, timeout=(3, 5) # 连接超时3秒，读取超时5秒 ) # 详细记录响应信息 logger.debug(f"钉钉响应状态码: {response.status_code}") logger.debug(f"钉钉响应体: {response.text[:500]}...") # 只记录前500字符 # 钉钉成功响应格式: {"errcode":0,"errmsg":"ok"} if response.status_code == 200: json_response = response.json() if json_response.get('errcode') == 0: logger.info(f"钉钉API确认消息已发送! (尝试#{attempt + 1})") return True else: # 钉钉API业务错误 logger.error(f"钉钉API错误: [{json_response.get('errcode')}] {json_response.get('errmsg')}") # 特殊处理常见错误码 if json_response.get('errcode') == 130101: logger.error("常见原因: 钉钉机器人关键词不匹配 - 检查消息中是否包含设置的keyword") elif json_response.get('errcode') in [310000, 310001]: logger.error("常见原因: 被钉钉限流 - 请降低通知频率或添加特殊关键词") else: logger.warning(f"HTTP错误码: {response.status_code}") except requests.exceptions.RequestException as e: logger.exception(f"网络请求异常 (尝试 #{attempt + 1}): {str(e)}") except json.JSONDecodeError: logger.error(f"响应JSON解析失败: {response.text[:200]}") # 如果不是最后一次尝试则延时重试 if attempt < max_retries - 1: logger.info(f"{retry_delay}秒后将重试...") time.sleep(retry_delay) return False def parse_event_args(): """解析Patroni回调参数-增强容错""" if len(sys.argv) < 4: logger.error(f"错误: 参数不足! 期望至少4个参数，实际收到 {len(sys.argv)}") logger.info(f"完整参数列表: {sys.argv}") return None try: # 解析基础参数 ([0]=脚本路径, [1]=事件类型, [2]=角色, [3]=集群名) PATRONI_LEADER=subprocess.run("/usr/local/bin/patronictl -c /etc/patroni/patroni-5433.yaml dsn -r leader", shell=True, capture_output=True, text=True).stdout.strip() PATRONI_MEBERS=json.loads(subprocess.run("/usr/local/bin/patronictl -c /etc/patroni/patroni-5433.yaml list -f json", shell=True, capture_output=True, text=True).stdout.strip()) PATRONI_MEBERS_HOST=[] for i in PATRONI_MEBERS: PATRONI_MEBERS_HOST.append(i.get('Host')) PATRONI_MEBERS_HOST=str(PATRONI_MEBERS_HOST) len_members=len(PATRONI_MEBERS) leader_num=0 replica_num=0 for i in PATRONI_MEBERS: if i.get('Role')=="Leader": i.get('State')=="running" leader_num+=1 elif i.get('Role')=="Replica": i.get('State')=="streaming" replica_num+=1 else: continue if len_members>=2 and leader_num==1 and replica_num==len_members: PATRONI_HA_STATE='green' elif leader_num==1 and replica_num>=1 and replica_num < len_members: PATRONI_HA_STATE='yellow' else: PATRONI_HA_STATE='red' event_info = { 'script_path': sys.argv[0], 'event_type': sys.argv[1], 'node_role': sys.argv[2], 'cluster_name': sys.argv[3], 'leader': PATRONI_LEADER, 'mebers': PATRONI_MEBERS_HOST, 'len_members': len_members, 'hostname': socket.gethostname(), 'ipaddress': socket.gethostbyname(socket.gethostname()), 'timestamp': datetime.now().strftime("%Y-%m-%d %H:%M:%S"), 'ha_state': PATRONI_HA_STATE, 'old_role': os.getenv('PATRONI_OLD_ROLE', None) # 角色变更时存在 } logger.debug(f"解析事件成功: {json.dumps(event_info, indent=2)}") return event_info except Exception as e: logger.exception(f"参数解析错误: {str(e)}") return None def build_safe_message(event): """构建安全的消息格式 - 兼容钉钉要求""" if not event: logger.error("无法构建消息: 事件数据为空") return None # 确保包含关键词 (避免钉钉关键词检查失败) # 根据事件类型设置不同的标题和颜色 if event['event_type'] in ['stop', 'failover']: title= f"****🔴 Patroni故障事件****" elif event['event_type'] in ['start', 'promote']: title = f"****🟢 Patroni恢复事件****" elif event['event_type'] in ['reload', 'restart']: title = f"****🟡 Patroni维护事件****" else: title = f"****🔔 Patroni状态变更****" # 使用最简单的markdown格式确保兼容性 message_content = f""" ### {title} - **事件类型**: {event['event_type']} - **集群名称**: {event['cluster_name']} - **集群当前Leader**: {event['leader']} - **集群当前成员**: {event['mebers']} - **集群成员数量**: {len(event['mebers'].split(','))} - **集群状态**: **{event['ha_state']}** - **当前节点角色**: {event['node_role']} - **当前主机名称**: {event['hostname']} - **当前主机IP**: {event['ipaddress']} - **集群状态**: {event['ha_state']} - **发生时间**: {event['timestamp']} """ # 如果是角色变更事件，添加额外信息 if event.get('old_role'): message_content += f"- **变更前角色**: {event['old_role']}\n" # 必须包含关键字"Patroni"两次以上避免误过滤 message_content += "\n> **Patroni数据库高可用系统**" return { "msgtype": "markdown", "markdown": { "title": title, "text": message_content }, "at": { "isAtAll": False # 不@所有人 } } if __name__ == "__main__": logger.info("=" * 60) logger.info("🚦 Patroni钉钉通知脚本启动 | 诊断模式开启") logger.info(f"收到参数: {sys.argv}") logger.info(f"环境变量 WEBHOOK={DINGTALK_WEBHOOK[:20]}...") # 部分展示避免泄露 # 步骤1: 网络连通性验证 if not verify_dingtalk_network(): logger.critical("网络诊断失败! 消息无法发送 - 请检查网络连接或防火墙设置") sys.exit(10) # 步骤2: 解析事件 event_data = parse_event_args() if not event_data: logger.error("无法解析事件数据，消息发送中止") sys.exit(20) # 步骤3: 构建消息（安全格式） message_payload = build_safe_message(event_data) if not message_payload: logger.error("消息体构建失败") sys.exit(30) # 步骤4: 发送消息（带重试） logger.info("尝试发送消息到钉钉...") success = send_with_retry(message_payload) if success: logger.info("✅ 消息发送确认成功") else: logger.error("❌ 消息发送失败 - 请查看上述诊断信息") logger.info("=" * 60) sys.exit(0 if success else 40) # 非0退出码便于外部监控 📌 转载信息来源： https://linux.do/t/topic/1485327原作者： xxdtb转载时间： 2026/1/19 17:45:53