1. mysqldump到底解决了什么问题备份的第一道防线做MySQL运维的都知道数据备份这件事平时没人觉得重要出一次事故就全想起来了。我见过太多项目因为一次误删或磁盘损坏整天都在想办法救数据那种感觉确实不好受。mysqldump是MySQL自带的逻辑备份工具它的核心工作方式简单说就是把数据库里的表结构、数据、触发器、存储过程、事件、视图定义等信息按照既定的规则转换成一套SQL语句文本保存下来。这套SQL文件就是你数据库在备份时刻的快照需要恢复的时候直接把这个文件喂给MySQL执行数据库就能回到当时的模样。拿生活中常见的场景来类比mysqldump的备份像是给房间里的每件物品拍了一张照片再列一份清单恢复的时候照着单子重新摆一遍。它在备份时依次读取表并生成语句恢复时也是一条条执行这种方式决定了它的几个天然特性第一它生成的是通用性很高的SQL文本跨版本、跨平台恢复的灵活性都不错比如MySQL 5.7备份的文件基本可以在MySQL 8.0里恢复物理备份直接拷贝数据文件就没这么灵活。第二它的备份速度比不上直接拷贝文件因为它是逻辑层面的转换数据量越大耗时越长。第三它对存储引擎敏感。MyISAM表备份时如果想保持一致性基本只能靠锁表InnoDB表则可以借助事务特性做到不用锁表也能拿到一致快照。理解这三条底层特性后面所有命令参数的取舍就都有依据了。很多人上来就背参数不看原理遇到问题只能瞎猜。2. 核心命令拆解参数背后的取舍逻辑2.1 基础命令与连接参数先看最基础的一条命令mysqldump -u用户名 -p密码 -h主机地址 -P端口号 数据库名 备份文件.sql这里有个细节值得注意直接用-p密码写在命令行里很容易留下安全隐患比如在shell的历史记录里直接暴露密码。最佳实践是不写密码执行后手动输入或者写进配置文件~/.my.cnf并设置好权限。实操时我通常这样写[client] userbackup_user password你的密码然后备份命令只需要mysqldump --defaults-extra-file/路径/.my.cnf 数据库名 备份文件.sql这在自动化脚本里尤其重要脚本里不出现明文密码安全很多。手册里还有个细节-p参数与密码之间千万不能有空格很多人第一次用就栽在这里提示输入密码后总是报认证失败其实就是多了一个空格。2.2 最关键的一致性参数--single-transaction这是InnoDB备份里最重要的一个参数没有之一。它的原理是备份开始时先开启一个大事务然后利用InnoDB的多版本并发控制MVCC机制在事务持续的这段时间里拿到一个一致性快照。你可以把它理解为备份开始那一刻数据库状态被拍了一个照片后面其他应用继续正常写入、修改都不会影响你正在读的这个快照因为InnoDB靠undo log维护着历史版本。结合日常例子就好比你在人多的食堂排队想记住前面第10个人长什么样先拍张照片再慢慢看后面的队伍怎么动都不影响你研究照片里的人。有了--single-transaction你就不用加--lock-tables去锁全表了线上业务不会因为备份被短暂阻塞。这也是InnoDB表备份的标准做法。不过要记住一个容易被忽视的细节--single-transaction只管InnoDB表的一致性对于MyISAM表它仍然无法做到不锁表而拿到一致快照。如果库里混着MyISAM表备份期间这些表还是会被锁可能影响写入。有些人对备份的理解就是备份时不锁表是理所当然的真要用起来才会发现存储引擎不同行为天差地别。2.3 内容完整性参数--routines、--triggers、--events这三个参数分别表示备份存储过程和函数、触发器、定时事件。默认情况下mysqldump只备份表结构和表数据两样东西存储过程、触发器和事件默认不备份新版加了一些默认行为的调整但在实践中最稳妥的做法还是显式把这三个参数带上。我处理过的恢复事故里最典型的就是开发说库恢复了业务还是报错排查半天发现是存储过程全没了。所以只要库里有任何程序逻辑对象备份命令里这三个参数就必须带上mysqldump --single-transaction --routines --triggers --events \ -u备份用户 -p 数据库名 backup.sql这里的--triggers还有一个特殊点它是LOW_PRIORITY的也就是说不管你有没有显式加触发器都会跟着表结构一起备份。但存储过程和事件就没有这种默认跟随的特性尤其是事件很多人以为只要备份了表就万事大吉结果定时任务在灾难恢复后全部消失类似的教训希望不要再发生。2.4 过滤与压缩参数有时不需要备份整个库只需要部分表可以在数据库名后面直接跟表名mysqldump -u用户名 -p 数据库名 表1 表2 部分表.sql反过来不想要某张表可以用--ignore-table数据库名.表名这个参数可以重复使用mysqldump --ignore-table数据库名.log_table \ --ignore-table数据库名.tmp_table 数据库名 备份.sql日志表、临时表通常不参与核心业务恢复排除掉可以显著缩短备份时间、减少备份文件体积。但需要注意你用--ignore-table排除的只是表数据不代表备份文件里完全没有这张表相关的任何记录具体情况在恢复场景里容易踩到细节问题。压缩方面最直接的方式是管道配用压缩工具mysqldump ... | gzip backup.sql.gz压缩比通常能达到5到10倍一个1GB的备份文件压缩后可能只有150MB左右磁盘空间紧张时这个方案非常实用。我自己的习惯是1GB以下的备份直接gzip超过5GB则先考虑分库或分表备份再压缩避免压缩过程占用太多CPU影响线上业务。2.5 其他实用参数的定位--opt这是一组优化参数的集合包含quick、single-transaction针对InnoDB场景、lock-tables针对MyISAM场景等默认就是开启状态很多时候你不需要管它。但如果你要自定义组合显式写出来会更放心。--quick逐行检索数据而不是全部缓存到内存里再输出。对大库来说性能差异很明显默认开启。--flush-logs在备份前刷新binlog日志文件可以配合binlog做基于时间点的恢复但要注意这操作会切割日志。--master-data2在备份文件里记录当前binlog文件名和位置这个信息在做主从复制或者说备份后继续追binlog恢复增量场景下很关键。2时这条语句只是注释不会在执行时产生副作用所以一般用2更稳妥。--default-character-setutf8mb4字符集一定要和源库保持一致否则恢复后中文可能出现乱码。这个坑我后来几乎每次都主动避开备份时不指定恢复时才发现各种编码问题。3. 备份脚本编写实战一份可以直接使用的Shell脚本3.1 脚本需求拆解一个生产环境可用的备份脚本至少要满足这几个条件备份文件名带时间戳保留多少份能灵活配置。自动创建按日期组织的目录方便归档。执行过程中有日志输出出问题能排查。支持多个数据库批量备份每个库单独生成一个备份文件。自动清理过期备份防止磁盘被塞满。3.2 完整脚本示例我放一份实际项目在用的简化版本你可以在这个基础上改。这个脚本是在CentOS 7环境下调试通过的其他Linux发行版基本通用#!/bin/bash # MySQL 数据备份脚本基于 mysqldump 实现 # 配置区域 MYSQL_HOST127.0.0.1 MYSQL_PORT3306 MYSQL_USERbackup_user MYSQL_PASSWORD这里不写明文读配置文件更安全 CONFIG_FILE/etc/my_backup.cnf BACKUP_BASE_DIR/data/mysql_backup BACKUP_KEEP_DAYS7 LOG_FILE/var/log/mysql_backup.log DATE_TAG$(date %Y%m%d_%H%M%S) BACKUP_DAY_DIR${BACKUP_BASE_DIR}/$(date %Y%m%d) # 需要备份的数据库用空格分隔 DATABASESdb_shop db_user db_log # 创建目录 mkdir -p ${BACKUP_DAY_DIR} # 定义日志函数 log_info() { echo [$(date %Y-%m-%d %H:%M:%S)] [INFO] $* ${LOG_FILE} } log_error() { echo [$(date %Y-%m-%d %H:%M:%S)] [ERROR] $* ${LOG_FILE} } # 备份单个数据库 backup_single_db() { local db_name$1 local backup_file${BACKUP_DAY_DIR}/${db_name}_${DATE_TAG}.sql.gz log_info 开始备份数据库: ${db_name} mysqldump \ --defaults-extra-file${CONFIG_FILE} \ --single-transaction \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ --default-character-setutf8mb4 \ ${db_name} | gzip ${backup_file} if [ $? -eq 0 ]; then local file_size$(du -sh ${backup_file} | awk {print $1}) log_info 备份成功: ${backup_file} 文件大小: ${file_size} else log_error 备份失败: ${db_name} # 删除可能产生的半截文件 rm -f ${backup_file} fi } # 清理过期备份 clean_old_backup() { log_info 开始清理 ${BACKUP_KEEP_DAYS} 天前的备份 find ${BACKUP_BASE_DIR} -maxdepth 1 -type d -name 20* \ -mtime ${BACKUP_KEEP_DAYS} -exec rm -rf {} \; } # 主流程 log_info 备份任务开始 for db in ${DATABASES}; do backup_single_db ${db} done clean_old_backup log_info 备份任务结束 3.3 脚本设计里值得注意的几个细节--set-gtid-purgedOFF这个参数在开启了GTID全局事务标识符的MySQL实例上备份时很有必要。如果不指定备份文件里会写入SET GLOBAL.GTID_PURGED语句这个语句在部分场景尤其是不同实例间数据迁移和恢复时会引发冲突。对纯粹做本地备份恢复来说加上它更省心。配置文件里统一管理密码脚本本身不出现明文密码这个做法要养成习惯。脚本权限建议设为700配置文件权限设为600。清理逻辑用find的-mtime判断目录修改时间按天归档的目录结构下清理整个目录很干净。这里有个细节如果用-mtime 7清理的是7天前生成且之后没有新文件写入的目录所以归档目录和备份文件生成时间最好统一在同一天。4. 定时备份配置cron的使用心得4.1 定时任务基本配置脚本写好了定时执行用cron是最常见的方式。编辑当前用户的crontabcrontab -e我习惯让备份任务在凌晨业务低峰期执行比如凌晨2点0 2 * * * /usr/bin/bash /opt/scripts/mysql_backup.sh /var/log/mysql_backup_cron.log 21这里有几个关键点分享下/usr/bin/bash用绝对路径。cron环境里的PATH变量通常很精简直接用bash有时候找不到命令。同理脚本里的mysqldump最好也写成绝对路径可以用which mysqldump查一下实际位置。输出重定向到日志文件便于事后排查。我见过太多人cron里只写脚本路径不重定向结果脚本报错一点痕迹都没有。4.2 cron执行过程和手动执行的表现差异cron环境下是一个非交互式登录shell环境很多环境变量比如PATH、LD_LIBRARY_PATH和手动终端不一样。举个例子如果你用源码方式安装MySQLmysqldump安装目录可能没被写进默认PATH直接写mysqldump命令手动执行没问题但cron执行就找不着命令。这类问题排查逻辑很简单先手动执行脚本再查cron日志再看命令路径。再有一个容易被忽视的脚本里如果用了相对路径cron执行时的工作目录默认是用户家目录并不是脚本所在目录。所以脚本里一切路径最好都是绝对路径避免此类问题反复出现。cron日志默认写在/var/log/cronCentOS上脚本执行情况可以配合自己的日志文件一起看。我自己排查cron不执行问题时固定顺序是systemctl status crond确认cron服务本身正常。tail -f /var/log/cron查看定时任务触发记录。检查脚本能不能手动跑通。检查脚本有没有写日志写日志内容是什么。5. 踩坑记录binlog与mysqldump配合的恢复问题5.1 场景还原有一次某项目模拟演练完整还原一次故障恢复流程。备份策略是每天凌晨2点用mysqldump全量备份数据库开启了binlog。结果在故障发生前数据文件异常需要恢复到当天下午3点的状态。第一步用凌晨2点的备份文件恢复数据库回到了凌晨2点的状态。第二步要用2点到3点之间的binlog重放这段时间的增量数据。本来计划没问题真正执行时才发现恢复用的备份文件里没有master-data信息根本不知道该从哪个binlog文件的哪个偏移量开始重放。如果当初备份时写上--master-data2备份文件里会记录binlog文件名和Position-- CHANGE MASTER TO MASTER_LOG_FILEmysql-bin.000023, MASTER_LOG_POS452876;恢复后用这一行信息就能确定binlog重放的起点。演练最后是通过日志时间推算起点硬试解决的过程很痛苦。5.2 这个场景给备份策略带来的启发后来我的备份脚本里凡是可能涉及增量恢复的场景都会加上--master-data2。这个参数放在全量备份场景下几乎就是成本为零、收益极大备份文件里多了几行注释不影响实际恢复但需要基于时间点恢复时它就是一把钥匙。顺着这个思路我对备份之后怎么办的认知也调整了mysqldump备份的只是某个时间点的全量数据备份之后到故障发生之间所有的数据变化都需要binlog来补。如果你所在的项目对数据丢失容忍度很低那binlog的保留时间、定期归档、异地存储都需要纳入备份体系一起规划。备份不是一锤子买卖而是一条数据保护链。6. 恢复实测备份能不能用只有试过才知道6.1 恢复的基本操作mysqldump产生的SQL文件恢复方式相当直接mysql -u用户名 -p数据库名 backup.sql或者进入mysql交互环境后执行source命令source /路径/backup.sql;这里有一个很重要的习惯要养成恢复前先确认目标库是空的或者你明确知道要覆盖现有数据。执行恢复过程时会先建表再插数据但源库如果有同样的表可能出现重复插入报错或覆盖行为恢复前最好评估清楚当前库的状态。如果备份文件是压缩过的恢复时要先解压gunzip backup.sql.gz | mysql -u用户名 -p数据库名这个管道方式效率很高不用在磁盘上解压出完整的SQL文件再导入也不会临时占双份磁盘。6.2 建议定期做恢复演练备份文件生成出来不代表万事大吉。在我见过的各种故障中恢复不了的备份原因五花八门文件权限不对、备份命令早就因为某个表被锁而失败、脚本逻辑写得有问题导致备份文件是空文件等等。我个人的做法是每季度至少做一次完整恢复演练把最近的备份文件恢复到一台新的实例上对比关键表数据量确认表数量、存储过程、触发器都齐全。这个过程频率可以根据程序对数据恢复速度的要求来调整但绝不能省略。有一个小技巧可以快速抽查备份文件内容是否正常# 看备份文件里包含哪些表 grep ^-- Table structure for table backup.sql | awk {print $5} # 检查文件是不是空的 ls -lh backup.sql # 确认备份文件有条理不是半截状态 head -20 backup.sql通过这些手段我能快速判断备份文件是否健康不需要真的把整个库恢复出来。完整演练当然不可替代但日常监控用这种轻量检查可以有效提早发现问题。6.3 一个真实的恢复异常案例有一回恢复一个线上库的备份执行到一半报错ERROR 1235 (42000) at line X: This version of MySQL doesnt yet support multiple triggers with the same action time and event for one table原因是备份的源库MySQL版本较老备份文件里包含了在同一张表上定义多个同一触发时机触发器的情况而恢复目标库的版本不同校验方式不一致导致执行中断。处理方式有两种一是换一个和目标库版本相符的实例恢复二是手动评估触发器的必要性在恢复时跳过或调整。这种错发生在恢复过程中容易让人手忙脚乱所以备份、恢复两端的MySQL版本差异在下手之前就要搞清楚。版本差异不仅是触发器存储过程的语法兼容性、排序规则、字符集差异都可能成为恢复路上的意外状况。7. 进阶优化大库备份的分拆与并行策略7.1 单库变多库多文件的必要性数据库超过几十GB时单库备份文件整体恢复时间会越来越长。这时有个常用的优化思路不按库备份改成按表备份每张表生成独立文件。用一个简单的循环脚本实现按表备份TABLES$(mysql --defaults-extra-file/etc/my_backup.cnf \ -N -e SELECT TABLE_NAME FROM information_schema.TABLES \ WHERE TABLE_SCHEMAdb_shop;) for table in $TABLES; do mysqldump --defaults-extra-file/etc/my_backup.cnf \ --single-transaction \ --routines \ --triggers \ --events \ db_shop $table | gzip db_shop_${table}.sql.gz done这样做的好处是恢复时只需恢复出问题的表不用全库恢复对大型业务很有价值。坏处也很明显表数量会带来大量文件管理负担变大。7.2 并行备份的思路和风险用GNU parallel工具可以把上面的按表备份并行化原理很简单每个表开启一个独立的分派进程多个备份同时进行。mysql --defaults-extra-file/etc/my_backup.cnf -N -e SELECT TABLE_NAME FROM ... \ | parallel -j 4 \ mysqldump --defaults-extra-file/etc/my_backup.cnf \ --single-transaction db_shop {} | gzip db_shop_{}.sql.gz-j 4表示同时跑4个备份任务能把CPU和I/O用起来。但这个方案需要谨慎并行任务对服务器I/O的冲击是有代价的生产环境必须在低峰期或者对系统负载有充分评估的前提下使用。我见过有人开-j 8结果备份期间数据库响应明显变慢幸好业务量不大才没有出事故。7.3 备份期间的服务器资源监控如果是线上核心库备份期间记得留意三件事磁盘空间、CPU使用率、I/O负载。磁盘空间这块最要命备份文件占空间是一回事如果mysqldump出错来不及清理会产生半截文件日积月累也会拖满磁盘。建议脚本在开始时先检查剩余空间例如AVAIL_GB$(df -BG ${BACKUP_BASE_DIR} | awk NR2 {print $4} | sed s/G//) if [ ${AVAIL_GB} -lt 20 ]; then log_error 磁盘剩余空间不足备份终止 exit 1 fi8. 关于数据安全性的最后几条补充回到基础逻辑上备份的本质是在灾难发生时有一段可靠的、救得回来的数据。围绕这个本质我会额外关注以下几个方面备份文件的性质。备份文件的权限、存储位置、存储介质都需要留意。同一台服务器磁盘上的备份如果机器本身出了问题备份一样会丢失。所以备份文件定期复制到异地或者推送到独立的存储服务已经是基本要求。备份验证不能省。我见过有同事的备份脚本里只有生成文件那一步从来没有验证步骤。真到恢复时才发现文件损坏或只有几KB完全无法使用。至少要在备份后检查文件大小是否合理、文件头是否正常这一步成本很低但价值很高。恢复策略要有文档。备份做完了恢复步骤是什么在哪个目录找备份恢复之后需要怎么验证这类操作手册最好整理成文档。实际操作时我建议把恢复手册分几个步骤恢复环境说明、备份文件读取指引、恢复步骤、验证标准。越具体越好。我的习惯一直没变过做完备份定期做一次恢复演练。这跟给家里装灭火器是一个道理平时觉得占地方真着火时就是救命的东西。备份文件如果从来没恢复过在故障发生前它只是一种心理安慰在故障发生后它就是一堆占用磁盘的文件而已。只有那一次恢复成功它才真正成为备份。
