泪奔,同事执行 update 语句没有添加 where 条件!

SQL herman 584浏览
公告:“业余草”微信公众号提供免费CSDN下载服务(只下Java资源),关注业余草微信公众号,添加作者微信:xttblog,发送下载链接帮助你免费下载!
本博客日IP超过1800,PV 2600 左右,急需赞助商。
极客时间所有课程通过我的二维码购买后返现24元微信红包,请加博主新的微信号:xttblog,之前的微信号好友位已满,备注:返现
所有面试题(java、前端、数据库、springboot等)一网打尽,请关注文末小程序
视频教程免费领

过年也不能安稳啊,最近几天大家都没心工作了,工作量也少。但是一些同事高兴过度了,执行 update 语句竟然没有添加 where 条件!

What?竟然这么牛X,日天了。这样的同事长的帅吗?

我的内心是崩溃的

微信群里,一连串的反映,我看了都要崩溃。还好,这次事故是发生在测试环境!

不幸中的万幸

但是测试环境也不能乱操作不是,你把测试环境搞坏了,测试团队还怎么进行!这个锅谁来背?

虽然,同事在操作上有些问题,但这个锅不能全扣他头上,我给大家的权限太大了也是一个问题。所以,我先恢复事故现场,然后又配置了一点安全限制,update、delete 相关操作,没有 where 条件,不能执行 SQL。

为了演示我的恢复操作,我先给大家简化一下我的数据表。

create table xttblog (
    id int unsigned not null auto_increment,
    name char(20) not null,
    sex enum('f','m') not null default 'm',
    address varchar(30) not null,
    primary key(id)
);

假设里面现在有 10 万条数据。现在需要将 id 等于 88 的用户的地址改为“上海”,但是 update 时没有添加 where 条件。

update xttblog set address = '上海';

事故发生后,我们现在开始恢复,在线上的话,应该比较复杂,要先进行锁表,以免数据再次被污染。锁表,就是查看正在写哪个二进制日志文件。

lock tables xttblog read;
-- 查看状态
show master status;

show master status

分析二进制日志 mysql-bin.000024,并且在其中找到相关记录,在更新时是 address='上海',我们可以在日志中过滤出来。

mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000024 | grep -B 15 '上海'

过滤内容如下:

# at 1629
# at 1679
#140305 10:52:24 server id 1  end_log_pos 1679  Table_map: `db01`.`xttblog` mapped to number 38
#140305 10:52:24 server id 1  end_log_pos 1825  Update_rows: table id 38 flags: STMT_END_F
### UPDATE db01.xttblog
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='广州' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='上海' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### UPDATE db01.xttblog
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */
###   @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='shanghai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */
###   @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='上海' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### UPDATE db01.xttblog
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='北京' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
###   @4='珠海' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

可以看见里面记录了每一行的变化,这也是 binglog 格式要一定是 row 才行的原因。其中 @1,@2,@3,@4 分别对应表中 id,name,sex,address 字段。相信大家看到这里有点明白了吧,我们只需要将相关记录转换为 sql 语句,重新导入数据库即可完成数据恢复。

关于过滤处理成 SQL 语句的内容,我就补贴了。我是通过 shell 来实现的,你们也可以使用 binlog2sql 工具,这个工具现在回滚更加方便。

过滤完了之后,我们在导入数据。解锁表。

source xttblog_recovery.sql;
unlock tables;

完美,现在数据全部都恢复回来了。

MySQL 数据恢复

但是权限问题,还要加以限制。于是我在 MySQL 的配置文件中加入了 safe-updates 配置。这样,再有不加条件的更新和删除操作,都会失败。提示:ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column。

业余草公众号

最后,欢迎关注我的个人微信公众号:业余草(yyucao)!可加QQ1群:135430763(2000人群已满),QQ2群:454796847(已满),QQ3群:187424846(已满)。QQ群进群密码:xttblog,想加微信群的朋友,之前的微信号好友已满,请加博主新的微信号:xttblog,备注:“xttblog”,添加博主微信拉你进群。备注错误不会同意好友申请。再次感谢您的关注!后续有精彩内容会第一时间发给您!原创文章投稿请发送至532009913@qq.com邮箱。商务合作可添加助理微信进行沟通!

本文原文出处:业余草: » 泪奔,同事执行 update 语句没有添加 where 条件!