MySQL备份Bash脚本运行后内存占用过高不回落问题咨询

核心原因 #

• 脚本循环对60个数据库单独执行mysqldump，会和MySQL建立60次连接，每次备份都会把对应库的表数据加载到InnoDB缓冲池。MySQL缓冲池默认不会主动释放已经加载的冷数据页，只要服务器没有足够的内存压力触发页淘汰，缓冲池占用就会一直维持在高位，直到重启服务。
• 原脚本使用的mysqldump未加--quick参数，默认会将整表数据全部读取到内存后再输出，多次执行累计推高MySQL内存占用。
• 你的MySQL实例innodb_buffer_pool_size上限应该配置为5G左右，备份操作把缓冲池填充到了上限值，所以内存会稳定在5G不回落。

修复方案 #

1. 优化备份脚本 #

修改mysqldump命令，加上必要参数，同时改为一次性备份所有目标库，减少连接次数：

# 先筛选出所有需要备份的库名，拼成一行
backup_dbs=$(mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "|" | grep -v -E 'Database|information_schema|performance_schema|mysql|sys' | tr '\n' ' ')
# 一次性执行备份，添加--quick --single-transaction参数
date=`date +%Y-%m-%d`
if [ "$GZIP" -eq 0 ] ; then
  mysqldump -u $USER -p$PASSWORD --quick --single-transaction --databases $backup_dbs > $BACKUP_PATH/$date-all.sql
else
  mysqldump -u $USER -p$PASSWORD --quick --single-transaction --databases $backup_dbs | gzip -c > $BACKUP_PATH/$date-all.gz
fi

参数说明：

• --quick：强制mysqldump逐行读取表数据输出，不会把整表加载到内存，大幅降低备份时的内存开销
• --single-transaction：对InnoDB引擎生成一致性快照备份，无需锁表，同时减少不必要的内存占用

2. 调整MySQL配置避免缓冲池被备份数据占满 #

修改MySQL配置文件my.cnf添加以下参数，重启后生效：

# 数据页首次访问后需等待1000ms才会加入缓冲池热链，备份扫过的冷页不会占用缓冲池空间
innodb_old_blocks_time = 1000

配置生效后，备份操作扫描的表数据不会停留在缓冲池，不会挤掉正常业务的热数据，也不会导致缓冲池占用飙升。

3. 无需重启释放内存的临时方案 #

如果不想重启MySQL，可以动态调整缓冲池大小触发内存回收：

-- 先把缓冲池临时调小，根据你的实际内存情况设置，不要低于1G
SET GLOBAL innodb_buffer_pool_size = 1 * 1024 * 1024 * 1024;
-- 等待30-60秒，待缓冲池回收完成后，调回原来的2G
SET GLOBAL innodb_buffer_pool_size = 2 * 1024 * 1024 * 1024;

内容的提问来源于stack exchange，提问作者Saman