mysql如何实现定时自动备份_mysql crontab备份脚本编写

mysqldump 备份必须加 –single-transaction 才安全，它为 innodb 提供一致性快照；但对 myisam 无效，混用引擎需配合 –lock-all-tables 或确认引擎类型。

mysql dump 命令必须加 –single-transaction 才算安全

直接用 mysqldump 备份时，如果库正在写入，很可能导出不一致的数据——比如订单表和订单明细表不在同一事务快照里。加 --single-transaction 能让 InnoDB 表在备份开始时获取一致性快照，避免锁表又保证逻辑一致。

但注意：这个参数只对 InnoDB 有效，MyISAM 表仍会隐式加读锁；如果混用引擎，得配合 --lock-all-tables（代价高），或者干脆先确认引擎类型：select TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_db';

• 务必加 --routines 和 --events，否则存储过程、事件不会被导出
• 别漏 --set-gtid-purged=OFF（MySQL 5.7.6+ 默认 ON），否则 GTID 环境下恢复可能失败
• 导出文件建议用 .sql.gz 后缀，压缩前先检查磁盘空间：df -h /backup

crontab 时间表达式容易写错的三个位置

写成 0 2 * * * 是每天凌晨 2 点执行，但很多人误以为是“每两小时”，其实第二个字段是“小时”，不是间隔。真正易错的是日/周冲突和路径问题。

• 避免同时指定 day of month 和 day of week（如 0 2 1 * 1），cron 会按“或”逻辑执行，导致每月 1 号 + 每周一都触发
• 脚本里所有命令必须用绝对路径：/usr/bin/mysqldump，不能只写 mysqldump，因为 crontab 默认 PATH=/usr/bin:/bin
• 环境变量缺失：cron 不加载 ~/.bashrc，密码不能靠 ~/.my.cnf 自动读取（除非显式指定 --defaults-extra-file=/root/.my.cnf）

备份脚本必须检查 mysqldump 退出码再归档

mysqldump 失败时可能生成空文件或部分文件，但脚本若不判断退出码，后续 gzip 和清理动作照常执行，你会以为备份成功了，实际是空壳。

简单判断方式：

mysqldump -u user -p'pass' db_name > backup.sql 2>/dev/null if [ $? -ne 0 ]; then   echo "dump failed" | logger -t backup   exit 1 fi gzip backup.sql

• 用 logger 记系统日志比单纯 echo 到终端更可靠，crontab 输出默认丢弃
• 压缩后立即校验：gzip -t backup.sql.gz，防止压缩中途失败
• 保留最近 7 天备份：find /backup -name "*.sql.gz" -mtime +7 -delete，注意 -mtime +7 是“超过 7 天”，不是“7 天前”

恢复时最容易忽略的字符集和 SQL 模式

备份文件开头一般有 SET NAMES utf8mb4;，但如果源库用了 utf8mb4_0900_as_cs 这类新排序规则，而目标 MySQL 版本低于 8.0.1，mysqldump 默认不会兼容降级，恢复时会报错 Unknown collation: 'utf8mb4_0900_as_cs'。

• 导出时加 --skip-set-charset --default-character-set=utf8mb4，手动控制编码声明
• 恢复前先查目标库支持的排序规则：SHOW COLLATION LIKE 'utf8mb4%';
• 临时关闭严格模式再导入：mysql -e "SET session sql_mode='';" db_name

自动备份这事，核心不在“能不能跑起来”，而在“失败时有没有人知道”。日志、退出码、压缩校验、字符集兼容性——这些点没卡死，定时任务就是个幻觉。