MySQL备份失败告警的排查与解决方法

【报警中】服务器 <db-host>
====================
告警规则：mysql全库备份
告警级别：warning
主机名称：<db-host>
告警详情：mysql备份失败
告警状态：firing
触发时间：t 日（定时检查时刻）
发送时间：t 日（同上）

# 备份任务：每周固定一天，凌晨执行一次
<备份调度>   /path/to/mysql_backup.sh

# 检查任务：同一天，上午和下午各检查一次
<检查调度>   /path/to/mysql_backup_monitor.sh

# 基础环境
date; hostname; uptime; free -m
# 磁盘与 inode（最高频元凶）
df -ht
df -i
# mysql 是否健在
systemctl status mysqld --no-pager
ps -ef | grep -ei "mysqld|mysqldump|xtrabackup" | grep -v grep
ss -lntp | grep 3306
# oom 迹象
dmesg -t | grep -i -e "oom|killed process" | tail -10
# 计划任务与脚本定位
crontab -l
grep -ril -e "mysqldump|xtrabackup" /etc/cron* /root /opt /scripts 2>/dev/null

 load average: 7.5, 7.4, 7.2
              total   used   free  shared buff/cache  available
mem:           7980   7320    133      15        526        385
swap:          2047    660   1387

t+0s        备份脚本启动
t+0s        ★ 脚本第一件事：删除上一周的备份目录
t+16s       清理完成
t+18s       xtrabackup 启动
t+20s       executing lock instance for backup ...
t+20s → t+34s    14 秒空档（初始化本地 innodb、创建 logfile）
t+36s       ★ mysqld 被内核 oom killer 杀死
t+38s       xtrabackup 报错：104 / 2013 连接中断
t+38s       备份脚本判定失败
t+8h        监控脚本检查发现失败，推送告警

[ <pid> ]  <uid>  <pid>  total_vm   rss    nr_ptes  swapents  oom_score_adj  name
[ <pid> ]   <uid>  <pid>   <...>   1832259   5008     416460         0        mysqld
                                    ↑rss(页)          ↑swapents(页)

free swap  = 0kb            ← 2 gb swap 一个字节都不剩
inactive_anon:312434        ← 1.19 gb 想换出去但没地方放

真实内存需求 = rss + vmswap

# 正确的取值方法
pid=$(cat /path/to/mysql.pid)
grep -e "vmrss|vmswap" /proc/$pid/status

swap cache stats: add ~1.6e8, delete ~1.6e8

分数 ≈ 该进程占用内存的比例 × 1000  +  oom_score_adj

# 保护当前 mysqld 进程
pid=$(cat /path/to/mysql.pid)
echo -500 > /proc/$pid/oom_score_adj

# 保护 mysqld_safe（守护壳），让未来自动重启的 mysqld 继承
echo -500 > /proc/<mysqld_safe_pid>/oom_score_adj

mysqld_safe (设为 -500)
   └── mysqld 崩溃 → mysqld_safe 自动重新拉起新的 mysqld
        └── 新 mysqld 自动继承 -500 ✅

mysqld         约 7 gb   ← 唯一的"大块头"
其余所有进程   约 0.2 gb ← 全是几十 mb 的小角色

echo 500 > /proc/$$/oom_score_adj     # 备份进程主动当替罪羊

mysqld    : -500   → 尽量别杀我
备份进程  : +500   → 我是可以牺牲的

select @@innodb_buffer_pool_size/1024/1024 as bufpool_mb;
show global status where variable_name in
 ('innodb_buffer_pool_pages_data','innodb_buffer_pool_pages_free','innodb_buffer_pool_pages_total');

bufpool_mb     = 4096
pages_total    = 262144     × 16 kb = 4096 mb
pages_data     = 261744     × 16 kb = 4090 mb
pages_free     =    175     × 16 kb =  2.7 mb

set global innodb_buffer_pool_size = 3221225472;   -- 3 gb

select @@innodb_buffer_pool_size/1024/1024 as bufpool_mb;
show status like 'innodb_buffer_pool_resize_status';

buffer pool 7 : withdrawing blocks. (8177/8192)

预测归还：65,136 页 × 16 kb = 1,017 mb
实际归还：1,053,856 kb       = 1,029 mb

cp -a /etc/my.cnf /etc/my.cnf.bak.$(date +%y%m%d_%h%m%s)
sed -i 's/^innodb-buffer-pool-size[[:space:]]*=.*/innodb-buffer-pool-size = 3g/' /etc/my.cnf
# 确认只改了一行
diff $(ls -t /etc/my.cnf.bak.* | head -1) /etc/my.cnf
# ★ 确认 mysql 能正确解析（防止文件写坏导致下次起不来）
/usr/local/mysql/bin/my_print_defaults mysqld | grep -i "buffer-pool-size"

/data/backup/data/<当天日期>/
├── my.cnf              14,298 b   ← 是脚本拷过来的配置文件
└── xtrabackup_logfile  12.7 mb    ← 只有日志文件

[<时间>]  removing expired backup data: /data/backup/data/<上周日期>

for target in $(find "${base_path}/data" -maxdepth 1 -mindepth 1 -type d -mtime +5); do
    rsync -a --delete "$empty_dir/" "$target/"
    rmdir "$target"
done

❌ 不是「备份失败」级别的问题
✅ 是「数据保护空窗」级别的问题

extended timeouts to 3600s. original read: unknown, write: unknown

last_line=$(tail -n 1 "$log_file" 2>/dev/null)
case "$last_line" in
  *"mysql full_backup failed"*)  cur_status="failed"  ;;
  *"mysql full_backup succeed"*) cur_status="success" ;;
  *)                             cur_status="success" ;;   # ← 危险
esac

[ -f "$log_file" ] || exit 0     # 日志不存在就静默退出

mv /data/backup/data/<当天日期> /data/backup/data/<当天日期>.failed

备份增量 ≈ 1.8 gb（按上次 oom 的差额估算）
当前余量 = available 1.3 gb + swap 空闲 1.3 gb ≈ 2.6 gb

cat > /tmp/run_backup_safe.sh <<'eof'
#!/bin/bash
# 1) 内存爆时优先杀我（子进程继承），保 mysqld
echo 500 > /proc/$$/oom_score_adj
# 2) 内存闸门
avail=$(awk '/memavailable/{print int($2/1024)}' /proc/meminfo)
echo "[$(date '+%f %t')] 启动前 memavailable = ${avail} mb"
if [ "$avail" -lt 1000 ]; then
    echo "[$(date '+%f %t')] 【中止】可用内存仅 ${avail}mb（阈值 1000mb），放弃备份以免打挂数据库"
    exit 1
fi
free -m
echo "[$(date '+%f %t')] ===== 备份开始 ====="
nice -n 19 ionice -c2 -n7 /bin/bash /data/backup/mysql_backup.sh
rc=$?
echo "[$(date '+%f %t')] ===== 备份结束，退出码 = $rc ====="
free -m
exit $rc
eof
chmod +x /tmp/run_backup_safe.sh
nohup /tmp/run_backup_safe.sh > /tmp/backup_run_$(date +%m%d_%h%m).log 2>&1 &

mysqld         oom_score_adj = -500     ← 数据库被保护
mysqld_safe    oom_score_adj = -500     ← 自动重启也继承保护
备份脚本(bash)  oom_score_adj =  500     ← 备份主动当替罪羊
xtrabackup     oom_score_adj =  500     ← 子进程继承

backup_type = full-backuped        ← 全量，非增量
from_lsn    = 0                    ← 从 lsn 0 开始 = 完整全量
to_lsn      = <有具体数值>          ← 备份一致性点
last_lsn    = <大于 to_lsn，正常>   ← redo 复制到的位置
flushed_lsn = <有具体数值>
redo_memory = 0
redo_frames = 0

xtrabackup --prepare --use-memory=256m --target-dir=/data/backup/data/<当天日期>

mysqld 实际内存            6.13 gb   (rss 5.85 + swap 0.28)
  ├─ innodb_buffer_pool    2.99 gb   （195,781 页 × 16kb）
  └─ buffer pool 之外      3.14 gb   ← 问题所在

select event_name, current_alloc
from sys.memory_global_by_current_bytes
order by current_alloc desc limit 20;

memory/innodb/buf_buf_pool                    3.07 gib    ← buffer pool（正常）
memory/mysys/io_cache                         1.09 gib    ← 🔴 头号异常
memory/performance_schema/table_handles       72.50 mib
memory/sql/table                              70.16 mib
memory/temptable/physical_ram                 61.00 mib
memory/performance_schema/* (其余 8 项)       ~233 mib
memory/innodb/* （log_buffer 等）             ~128 mib
memory/sql/dd::* / table_share                 ~48 mib

select event_name,
       current_count_used                             as 块数,
       round(current_number_of_bytes_used/1048576,1)  as 总计mb,
       round(current_number_of_bytes_used/current_count_used/1024,1) as 平均kb,
       round(high_number_of_bytes_used/1048576,1)     as 历史峰值mb
from performance_schema.memory_summary_global_by_event_name
where event_name like '%io_cache%';

memory/mysys/io_cache  |  约 1600 块  |  ~1.1 gb  |  平均约 690 kb  |  历史峰值 ~1.5 gb

累计分配   约 68 万块   /  约 35 gb
累计释放   约 67.7 万块 /  约 34 gb
当前持有   约 1600 块   /  ~1.1 gb

select round(sum(current_number_of_bytes_used)/1048576,1)
from performance_schema.memory_summary_by_thread_by_event_name
where event_name='memory/mysys/io_cache';

全局mb     = 约 1160
线程合计mb = null      ← null = 零行！

全局 io_cache                              ~1.1 gb
所有线程内存合计（全部类型）                 ~170 mb
                                          ─────────
                                   约 900 mb 无归属

select left(digest_text,60) as 语句, count_star as 执行次数,
       sum_sort_merge_passes as 归并排序次数,
       sum_created_tmp_disk_tables as 磁盘临时表次数,
       sum_sort_rows as 排序行数
from performance_schema.events_statements_summary_by_digest
order by sum_sort_merge_passes desc limit 10;

select `id`,`account_id`,`account_name`,`city_id`...  |  约 154 万次

某时刻  →  [select sleep(300)]  →  now() 只前进了 60 秒

mysqld 总内存 − buffer pool 真实占用 = 待解释的部分

被保护的（数据库）  : oom_score_adj = -500
可牺牲的（备份进程）: oom_score_adj = +500

cp -a /etc/my.cnf /etc/my.cnf.bak.$(date +%y%m%d_%h%m%s)
# ... 修改 ...
/usr/local/mysql/bin/my_print_defaults mysqld | grep -i <改动的参数>

{
echo "================ 内存体检 $(date '+%f %t') ================"
echo; echo "【1】整机内存"; free -m
echo; echo "【2】mysqld 实际内存"
pid=$(cat /path/to/mysql.pid)
grep -e "vmrss|vmswap|vmsize" /proc/$pid/status
rss=$(grep vmrss  /proc/$pid/status | awk '{print $2}')
swp=$(grep vmswap /proc/$pid/status | awk '{print $2}')
awk -v r=$rss -v s=$swp 'begin{printf ">>> 合计 %.2f gb\n", (r+s)/1048576}'
echo; echo "【3】swap 抖动"; vmstat 1 3
echo; echo "【4】oom 记录"; grep -h "killed process" /var/log/messages | tail -10
echo; echo "【5】内存 top10"; ps aux --sort=-rss | head -11
}

-- 全局内存分布
select event_name, current_alloc
from sys.memory_global_by_current_bytes
order by current_alloc desc limit 20;

-- 底层表版本（避免 sys 视图列名差异）
select event_name,
       round(current_number_of_bytes_used/1048576,1) as mb,
       current_count_used as blocks
from performance_schema.memory_summary_global_by_event_name
where current_number_of_bytes_used > 0
order by current_number_of_bytes_used desc limit 20;

-- buffer pool 真实占用
select @@innodb_buffer_pool_size/1024/1024 as bufpool_mb;
show global status where variable_name in
 ('innodb_buffer_pool_pages_data','innodb_buffer_pool_pages_free','innodb_buffer_pool_pages_total');

-- 连接数与峰值
show global status where variable_name in
 ('threads_connected','threads_running','max_used_connections','uptime');

-- 连接来源分布
select user, host, count(*) as conns
from information_schema.processlist
group by user, host order by conns desc limit 20;

-- 排序/临时表大户
select left(digest_text,60) as stmt, count_star,
       sum_sort_merge_passes, sum_created_tmp_disk_tables, sum_sort_rows
from performance_schema.events_statements_summary_by_digest
order by sum_sort_merge_passes desc limit 10;

# 查看
for p in $(pgrep -f 'mysqld|mysqld_safe'); do
  printf "%-15s pid=%-7s adj=%s\n" "$(cat /proc/$p/comm)" "$p" "$(cat /proc/$p/oom_score_adj)"
done

# 设置
pid=$(cat /path/to/mysql.pid)
echo -500 > /proc/$pid/oom_score_adj

# 每分钟自愈脚本（防重启失效）
cat > /usr/local/sbin/mysql_oom_guard.sh <<'eof'
#!/bin/bash
pidf=/path/to/mysql.pid
adj=-500
pid=$(cat "$pidf" 2>/dev/null)
[ -n "$pid" ] && [ -d "/proc/$pid" ] || exit 0
cur=$(cat "/proc/$pid/oom_score_adj" 2>/dev/null)
if [ "$cur" != "$adj" ]; then
    echo "$adj" > "/proc/$pid/oom_score_adj" 2>/dev/null
    logger -t mysql_oom_guard "mysqld($pid) oom_score_adj: ${cur:-na} -> $adj"
fi
eof
chmod +x /usr/local/sbin/mysql_oom_guard.sh

-- 调整（注意必须是 chunk_size × instances 的整数倍，本例为 1 gb）
set global innodb_buffer_pool_size = 3221225472;   -- 3 gb

-- 确认
select @@innodb_buffer_pool_size/1024/1024 as bufpool_mb;
show status like 'innodb_buffer_pool_resize_status';

# 持久化 + 验证
cp -a /etc/my.cnf /etc/my.cnf.bak.$(date +%y%m%d_%h%m%s)
sed -i 's/^innodb-buffer-pool-size[[:space:]]*=.*/innodb-buffer-pool-size = 3g/' /etc/my.cnf
grep -n "innodb-buffer-pool-size" /etc/my.cnf
/usr/local/mysql/bin/my_print_defaults mysqld | grep -i "buffer-pool-size"

# 进程
pgrep -af 'xtrabackup|mysql_backup' || echo "已结束"

# 任务退出码
tail -6 /tmp/backup_run_*.log

# 脚本日志末行（必须是 succeed）
tail -5 /data/backup/log/$(date +%y%m%d)/mysql_backup_3306.log

# ★ 完整性校验
cat /data/backup/data/$(date +%y%m%d)/xtrabackup_checkpoints
grep -i "completed ok" /data/backup/log/$(date +%y%m%d)/mysql_backup_3306.log
ls /data/backup/data/$(date +%y%m%d)/ | wc -l
du -sh /data/backup/data/$(date +%y%m%d)