DAY10也是我把MySQL从会写SQL往敢动数据推的关键一天。前面9天基本都在折腾增删改查、索引、表结构跑的都是测试库出点问题删了重来就行。但今天学习的内容完全不一样——事务和数据库备份这两个话题里任何一步操作走神都可能把线上数据搞没或者搞乱连后悔药都没有。我花了整整一天时间把ACID、隔离级别、mysqldump、恢复演练全刷了一遍中间翻过车也把很多平时不会注意到的细节想明白了。这篇学习日记就记录一下这一天的收获重点是我走过的弯路和最后留下的结论给正在学MySQL的朋友当个参考。这一天适合谁看如果你已经掌握了基本的增删改查和索引却对怎么保证数据不丢怎么把误删的数据找回来没有概念那这篇DAY10的笔记应该对你有用。如果你恰好也在按学习计划推进MySQL接下来的内容可以直接照着操作大部分命令和场景我都在本地环境验证过。1. 为什么学习MySQL必须优先啃下事务和备份这两块硬骨头1.1 从一次不敢点确认的删除操作说起具体场景是这样的某天我接到一个数据修复的任务要在一张账号表里清理一批异常注册的数据。修复方案很简单一条DELETE加一个WHERE条件就能搞定。但就在我准备敲回车的时候突然意识到这条SQL一旦执行就是不可逆的。当时脑子里闪过的是如果没有提前备份这条DELETE删错了我能拿什么恢复如果这条DELETE影响了下游报表有没有人能帮忙回滚这个问题让当时的我彻底愣住了。前9天的学习里SQL执行错了无所谓数据库随时可以重建数据可以重新插入。但真实项目里数据每天都在积累一旦丢失重建的成本高到难以估量。那一刻我才明白事务和备份不是进阶课程而是保命课程。事务解决的是操作过程的安全感备份解决的是操作之后的后悔药两者一个管运行中一个管事故后正好把数据库数据的安全边界补齐。对初学者来说事务和备份往往被当作相对后期才需要碰的东西实际上它们才是从会SQL跨到能上手真实项目的分水岭。我身边不少朋友学MySQL学到索引就停了觉得事务是理论货、备份是运维活结果一上真实场景就蒙圈。这条弯路真的没必要亲自去踩。1.2 备份不是DBA的专属工作很多开发觉得备份是DBA的事自己只要写好SQL就行。我原来也是这么想的直到某次联调环境的数据被我一通操作弄乱才发现自己连最基本的备份还原都不会。备份的本质是给数据拍一张照片而拍照片这件事谁都有资格做。哪怕只是每天把关键表导出一份SQL文件恢复时也能省下大把时间。更重要的是只有亲手做过一次备份和恢复才会真正理解备份文件不是拿来存着好看的而是要能还原出能用的数据这个道理。我身边有两位同学就是反例一位从来没有备份过自己负责的模块上线前临时让DBA帮忙导了一份后来误更新了线上数据恢复时发现备份文件是空的最后只能靠日志手工补数据另一位备份倒是每天定时跑但从来没做过恢复验证等真需要恢复的时候才发现mysqldump命令在目标版本上不兼容。这俩例子虽然很琐碎但让我对备份必须自己掌握、必须验证这句话印象特别深。DAY10的学习安排正是从这两个基础概念开始的。2. 事务的底层逻辑我用一个转账用例把ACID拆开看2.1 转账场景中的要么成功要么失败学习事务最经典的开场就是银行转账A账户扣掉100B账户加上100这两条UPDATE必须打包执行不能出现只扣不加的情况。我把这个场景搬到了本地库建了一张简单的账户表来回跑了十几遍才真正理解事务不是MySQL自带的神秘功能而是一个明确的执行边界。这里给出一条示例CREATE TABLE account ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32) NOT NULL, balance DECIMAL(10, 2) NOT NULL DEFAULT 0 ) ENGINEInnoDB; INSERT INTO account(name, balance) VALUES (账户A, 1000), (账户B, 500);然后做一个标准转账事务START TRANSACTION; UPDATE account SET balance balance - 100 WHERE name 账户A; UPDATE account SET balance balance 100 WHERE name 账户B; COMMIT;这两条UPDATE在一个事务里只有COMMIT之后才会永久生效执行过程中有任何一条报错直接ROLLBACK就能回到转账前状态。关键在于这两条UPDATE在没有提交之前其他连接默认是看不到的只有在COMMIT之后数据修改才对所有人可见。这个看不见的机制是由隔离级别和MVCC共同决定的下面我一个个拆开看。2.2 ACID四个特性分别管住了什么问题原子性、一致性、隔离性、持久性这四个名词在教材里看过无数遍但只有亲自操作过才明白它们各自盯着一个环节。特性解决的问题通俗理解实现机制InnoDB原子性一组操作不能只执行一部分像转账要么全成功要么全失败undo log一致性数据在事务前后都满足约束账户总额不能变应用逻辑数据库约束隔离性并发事务相互干扰一个事务看不到另一个没提交的数据锁MVCC持久性提交后的数据不会丢关机重启数据还在redo logACID四个特性不是互相独立的而是配合InnoDB的日志体系一起工作的。undo log负责把执行过的修改撤销回去redo log负责保证提交后的修改不会因为宕机而丢失锁和MVCC负责隔离约束和应用逻辑负责一致性。看代码时可能只看到SQL语句但底层每一步都有日志和状态机在兜底。学事务不能只背名词得知道每个词对应什么底层动作。2.3 隔离级别脏读、不可重复读、幻读的实测对比隔离级别是事务这块最容易让人懵的点。我一开始完全分不清脏读、不可重复读、幻读后来用两个终端同时开着MySQL会话反复实测才算理清楚。隔离级别从宽松到严格依次是读未提交、读已提交、可重复读、串行化。读未提交可能读到别的事务还没提交的数据脏读风险最高读已提交只能读到已提交数据避免脏读但同一查询在不同时间可能得到不同结果叫不可重复读可重复读则保证同一个事务里多次读取同一数据结果一致串行化最严格事务排队执行并发性能最差。MySQL默认隔离级别是可重复读这个和很多数据库默认的读已提交不一样。为什么MySQL要选可重复读简单说InnoDB的MVCC在可重复读下可以通过间隙锁避免幻读能在保证一致性读取的同时兼顾并发。这个细节面试常问实际使用中更重要的是知道当前环境默认隔离级别是什么和业务对一致性的要求是什么。查看当前隔离级别可以用这条命令SHOW VARIABLES LIKE transaction_isolation;2.4 亲手在MySQL里制造一次回滚理论说得再多不如亲手测一次。我模拟了一个场景从账户A转100给账户B但故意在两条UPDATE之间加一条会报错的SQL比如更新一个不存在的字段。START TRANSACTION; UPDATE account SET balance balance - 100 WHERE name 账户A; UPDATE account SET balance balance 100 WHERE nam 账户B; -- 故意写错触发报错 ROLLBACK; SELECT * FROM account; -- 数据回到转账前状态执行到报错时先用SELECT看账户金额发现账户A的钱已经少了但还没有提交。这时候输入ROLLBACK再查数据金额又恢复原样了。这个简单的验证让我彻底明白原子性为什么关键——已经执行的操作不等于已经生效的操作。InnoDB在执行每一条语句之前就已经为回滚留好了后路所以即使第一条UPDATE已经生效事务仍然可以被完整撤销。2.5 隐式提交与容易被忽略的事务边界基础用法之外真正让我涨教训的是隐式提交。MySQL里有些语句一旦执行会强制结束当前事务比如CREATE TABLE、DROP TABLE、ALTER TABLE、GRANT这类DDL语句还有LOCK TABLES等。这意味着如果你在一个事务里先做了UPDATE又执行了一条DDL再想回滚UPDATE已经没机会了。START TRANSACTION; UPDATE account SET balance balance - 50 WHERE name 账户A; CREATE TABLE temp_table (id INT); ROLLBACK;执行完这串代码UPDATE是不会被ROLLBACK撤销的因为CREATE TABLE已经让事务隐式提交了。我第一次踩到这个坑的时候还没反应过来愣是以为ROLLBACK失效了。后来查了文档才发现MySQL对DDL语句的处理里就包含了隐式提交。这也解释了为什么在真实业务逻辑里事务边界必须严格设计不能把DDL和DML混在一个事务里。另外默认autocommit1也就是单条SQL自动提交只有显示执行START TRANSACTION或BEGIN之后连续的多条SQL才会在同一个事务里待命。这个细节如果不清楚很容易以为多写了几个分号就是事务了。3. 数据库备份与恢复从mysqldump翻车到恢复演练跑通的完整链路3.1 备份方式的划分逻辑备份、物理备份、热备、冷备先搞清楚概念才不会在选工具的时候发懵。分类维度类型特点常见做法导出内容逻辑备份导出成SQL语句可读、可跨版本迁移恢复较慢mysqldump导出内容物理备份直接拷贝数据文件恢复快但依赖版本和平台文件拷贝/专业工具数据库状态冷备份停止服务再备份最一致但影响大停库后拷贝数据库状态热备份在线进行对业务影响小mysqldump --single-transaction数据库状态温备份只读锁定期间备份锁表后拷贝对入门阶段逻辑备份的mysqldump最实用因为生成的文件能看到内容恢复门槛低也最容易用来理解备份的本质。物理备份更适合超大数据量的生产环境但依赖较多先放在后面再学。3.2 mysqldump的参数到底该怎么选最常用的命令就是把库导出成SQL文件mysqldump -u root -p mydb mydb_backup.sql这个命令导出的文件里包含建表语句和INSERT语句恢复的时候直接用mysql客户端执行就行mysql -u root -p mydb mydb_backup.sql如果库多、表多还可以直接导出所有库mysqldump -u root -p --all-databases all_db_backup.sql还有几个常用参数值得记一下--single-transactionInnoDB引擎专用备份时通过一个事务拿到一致性快照不锁表适合在线备份。--master-data2记录binlog文件名和位置方便做增量恢复。--default-character-set指定字符集避免导入导出时转码导致中文乱码。还有一点容易被忽略命令行里不要直接暴露密码建议交互输入或者使用配置文件防止密码泄露到shell历史和进程列表里。这些参数看起来不多但选错一个就能让你在恢复阶段头大。3.3 我踩过的坑不加参数备份导致锁表这是我今天最深刻的翻车现场。当时给一个项目库的压测数据做备份直接用了最简单的mysqldump命令。命令执行到一半发现另一个正在执行的INSERT一直卡住等了几分钟都没反应。查了进程才发现mysqldump默认会在备份过程中对表加元数据锁保证备份期间结构不变化结果写操作就被阻塞了。SHOW PROCESSLIST;看到一个INSERT的State是Waiting for table metadata lock。对于一个小表的测试库锁表影响可能还能忍但放到线上库场景里这种操作极可能直接拖停业务。那次之后我记住了用mysqldump备份InnoDB表加上--single-transaction至少能有一个一致性的快照同时不长时间锁住写入。如果是混合引擎还需要了解每张表的引擎类型避免备份快照不一致。另外如果库里有MyISAM表--single-transaction不会生效这类表的备份还是要小心通常建议把引擎统一改成InnoDB再谈热备份。3.4 恢复不是执行命令而是一次完整演练备份只是上半场恢复才是下半场。我第一次做恢复演练是在一个模拟库上先建备份再故意删掉一张表然后用备份文件恢复。过程比想象中简单但有个细节一定要提——恢复前最好先确认备份文件里有没有包含目标库的CREATE DATABASE语句。# 备份 mysqldump -u root -p --single-transaction --databases mydb mydb_full.sql # 模拟删除数据危险操作仅测试环境 DROP TABLE mydb.orders; # 恢复 mysql -u root -p mydb_full.sql # 验证 SELECT COUNT(*) FROM mydb.orders;如果mysqldump只导出了一个库且没有加--databases参数恢复时需要先手动建库再导入表。恢复完验证表数量和数据条数这一步非常关键。没有验证恢复工作等于白做因为谁也不能保证备份文件一定完整可用。我后来给自己定了个规矩每次做完备份至少随机挑一张表先恢复到一个临时库检查数据条数和关键字段再决定是否真正把备份文件归档。恢复本身也是一次危险操作恢复前最好把目标库当前状态也备份一份防止恢复动作覆盖掉本来可用的数据。这个点特别容易忽略毕竟恢复是带着抢救心态去的越着急越容易出岔子。3.5 定时备份与binlog时间点恢复定时备份是保证数据安全的底线。最简单的方式就是写一个shell脚本配合操作系统的定时任务每天凌晨执行。脚本里除了mysqldump还要把备份文件按日期命名并且保留最近N份避免磁盘被备份文件塞满。时间点恢复是备份方案的进阶玩法。全量备份只能恢复到备份那一刻备份之后新产生的数据变化要靠binlog补回来。在mysqldump加--master-data2之后备份文件里会记录binlog位置恢复时可以定位到某个具体时间点把误删或误改的数据找回。这块内容入门阶段可以先有个概念但知道全量备份binlog配合是生产环境最主流的恢复思路之一。等后面把binlog机制吃透再回来补这块的实操理解会更扎实。4. 事务和备份如何协同单事务备份的一致性问题4.1 为什么备份一个正在被写的库会缺胳膊少腿如果备份过程中数据库一直有写入简单粗暴地一张表一张表导出那么先导出的表和后导出的表可能处于不同的时间点整个备份文件整体上是不一致的。比如说订单表和支付流水表本来是一对关联数据备份时订单表已经查到新版本支付表还停留在旧版本恢复出来的数据就对不上账。这种问题在备份工作中叫非一致性快照。避免它的核心手段就是让备份期间读取到的数据都来自同一个时间点。InnoDB的多版本机制让开启一个事务读所有表成为可能这就是--single-transaction的工作原理。如果备份工具没有这种机制那就只能用锁让备份期间的数据库处于静止状态代价是写入被阻塞。4.2 --single-transaction 与全局锁怎么选--single-transaction在备份开始时开启一个事务利用MVCC让备份任务看到的是事务开始那一刻的数据库快照。之后即使有其他事务在写入备份导出的数据也不会混入新数据而且全程不阻塞业务写入。这是InnoDB表的主力备份方案。但如果是MyISAM表、或者一些非事务表MVCC无法提供统一快照mysqldump会退化为对表加锁用FLUSH TABLES WITH READ LOCK把所有表变成只读完成备份后再释放。这种方案能拿到一致性快照但会阻塞写操作。所以选择哪种方式取决于你的表引擎类型和业务能不能接受短时间写入暂停。如果库是混合引擎一个常见的做法是把非InnoDB表迁到InnoDB这样整个库可以用一份单事务备份脚本完成。如果实在迁不了就只能接受备份期间的短暂锁表窗口并尽量把备份放到业务低谷期执行。这个取舍是备份方案设计里必须面对的现实问题。4.3 一个入门级备份脚本的实测记录我最后写了这样一个备份脚本跑了几轮测试算是把上面的理论都落到了实处#!/bin/bash BACKUP_DIR/data/backup DATE$(date %Y%m%d%H%M) DB_USERroot DB_PASSyour_password mysqldump -u$DB_USER -p$DB_PASS \ --single-transaction \ --master-data2 \ --databases mydb \ $BACKUP_DIR/mydb_$DATE.sql # 删除超过7天的备份 find $BACKUP_DIR -name *.sql -mtime 7 -delete # 简单验证备份文件大小 BACKUP_SIZE$(stat -c%s $BACKUP_DIR/mydb_$DATE.sql) if [ $BACKUP_SIZE -lt 1024 ]; then echo 备份文件异常请检查 fi这个脚本是入门版用来理解思路完全没问题。实测时我观察到的现象是加了--single-transaction之后同一时刻的并发写入没有被阻塞备份前后写入的延迟几乎无感--master-data2让备份文件头部多了一行注释记录了binlog文件名和位置。删除7天前备份的find命令要注意路径避免误删其他目录下的文件。备份文件大小的检查只是最低级校验更稳妥的做法是把备份文件哈希值记录下来恢复前比对或者定期拿一份备份做恢复演练。5. 学完DAY10后我立刻调整的几个数据库使用习惯5.1 写增删改之前先问自己这条语句有没有后悔药以前写DELETE、UPDATE特别随意只要执行不报错就觉得任务完成。现在养成的习惯是动手前先问三个问题当前连接是否已经开启事务误操作后能否回滚如果已经提交且没有备份数据能否重建哪怕只是操作本地测试库这句话我也会在脑子里过一遍。顺带分享一个小技巧在测试环境里删除数据之前先执行SELECT用同样的WHERE条件看一眼结果集确认删的是自己想删的。这个习惯成本极低收益极高。我这一年因为多看了一眼WHERE条件躲过了至少两次误删。这个技巧不算今天新学的但和事务结合之后我才真正把它当成铁律对待。5.2 把备份验证写进流程而不只是挂在嘴边以后凡是新建项目环境第一件事就是把备份脚本配上并且做一次完整的备份-删除-恢复演练。只备份不验证约等于没有备份。判断备份是否有效的唯一标准就是拿它恢复出来的数据能不能直接用。更具体的做法是在上线清单里加一条备份已配置和恢复演练已通过的检查项。这个方法我前9天学MySQL完全没意识到直到今天亲手做过一次恢复才真正认同。与其等事故出现了再到处找方案不如在最开始就把这条流程焊死。5.3 顺手翻了翻项目里的事务隔离级别平时的业务代码看起来是单线程执行的但数据库面对的是并发的多条请求隔离级别决定了事务之间的可见性和相互干扰程度。如果项目默认是MySQL的可重复读某些统计报表在长事务里可能读不到其他事务最新提交的数据如果某个应用主动把隔离级别调成读已提交又要小心同一事务内的多次查询结果不一致。我在学完隔离级别之后做的第一件事就是把项目配置里的事务隔离级别翻出来看了一遍确认环境到底是默认值还是被修改过。这个动作不需要很深的知识但能避免很多只有生产环境才会暴露的诡异问题。毕竟大多数MySQL初学者包括我在内平时只盯着单条SQL的执行结果很少关心并发场景下的数据视图问题。说回DAY10这一天最大的感受还不是多会了几条命令而是动手操作之前开始有敬畏感了。事务和备份都不是能被感觉没问题替代的东西所有兜底能力都来自实际验证过的流程。接下来我准备把binlog和增量恢复再刷一遍把今天留下的模糊概念一点点补齐不过那就又是新的日记了。