微信号:InsideMySQL

介绍:MySQL数据库发烧友群;《MySQL技术内幕》系列书籍官方帐号;不谈小道消息,只关注MySQL相关技术

MySQL运维之神奇的参数

2017-01-08 21:05 兰春 姜承尧


神奇的参数—— sql_safe_update

官方文档说明:

http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_safe_updates

注:兰春同学是安居客DBA Leader,负责数百台MySQL服务器的运维工作。对MySQL数据库非常有激情,一直与我不断在完善MySQL最优配置文件模板。

BTW:区区在网易内部推动过sql_safe_update,但是未能成功。诸多原因,想想很是可惜。

背景(why)

参数sql_safe_update主要用于防范大表的误操作。因为如果只是更改了几条记录,那么说不定业务方可以很容易的根据日志进行恢复。即便没有,也可以通过找二进制日志(binlog)进行逆向操作恢复。如果被误操作的表非常小,其实问题也不大,全备+binlog恢复 or 闪回(点击:拿走不谢,Flashback for MySQL 5.7)都可以进行很好的恢复。

But,如果你要恢复的表非常大,比如:100G,100T,对于这类型的误操作,恐怕神仙都难救。所以,这里通过这个神奇的参数,可以避免掉80%的误操作场景。 PS: 不能避免100% ,下面的实战会告诉大家如何破解。

生产环境的误操作案例分享


update xx set url_desc='防不胜防' 
WHERE 4918=4918 AND SLEEP(5)-- xYpp' where id=7046
若这表,线上有500G,一次误操作,要恢复500G的数据,需要中断服务很长时间。 如果设置了sql_safe_updates,此类事故就可以很华丽的避免掉了。


原理和实战


表结构:


dba:lc> show create table tb\G
******************* 1. row ****************
       Table: tb
Create Table: CREATE TABLE `tb` (
  `id` int(11) NOT NULL,
  `id_2` int(11) DEFAULT NULL COMMENT 'lc33',
  `id_3` text,
  PRIMARY KEY (`id`),
  KEY `idx_2` (`id_2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)


UPDATE相关测试


* 不带where 条件
dba:lc> update tb set id_2=2 ; ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

* where 条件有索引,但是没有limit
dba:lc> update tb set id_3 = 'bb' where id > 0; ^C^C -- query aborted ERROR 1317 (70100): Query execution was interrupted

* where 条件无索引,也没有limit
dba:lc> update tb set id_3 = 'bb' where id_3 = '0'; ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

* where 条件有索引,有limit
dba:lc> update tb set id_3 = 'bb' where id > 0 limit 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0

* where 条件无索引,有limit
dba:lc> update tb set id_3 = 'bb' where id_3 > 0 limit 1; Query OK, 0 rows affected (0.26 sec) Rows matched: 0  Changed: 0  Warnings: 0


结论: 对于UPDATE操作,只有两种场景会被限制:

  • 无索引,无limit的情况

  • 无where条件, 无limit的情况



DELETE相关测试


* 不带where条件
dba:lc> delete from tb ;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column * where 条件有索引,但是没有limit dba:lc> delete from tb where id = 0 ; Query OK, 0 rows affected (0.00 sec) dba:lc> delete from tb where id > 0 ; ^C^C -- query abortedERROR 1317 (70100):
Query execution was interrupted dba:lc> delete from tb where id_2 > 0 ; ^C^C -- query aborted ^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted * where 条件无索引,也没有limit dba:lc> delete from tb where id_3 = 'a' ;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column * where 条件有索引,有limit dba:lc> delete from tb where id = 205 limit 1 ; Query OK, 1 row affected (0.00 sec) * where 条件无索引,有limit dba:lc> delete from tb where id_3 = 'aaaaa' limit 1 ;
Query OK, 1 row affected (0.00 sec)


测试结果: 关于DELETE操作相关,官方文档描述有误

结论: 对于DELETE操作,只有两种场景会被限制

1. 无索引,无limit的情况
2. 无where条件, 无limit的情况


综上所述:不管是UPDATE,还是DELETE,被限制的前提只有两个

1. 无索引,无limit的情况

2. 无where条件, 无limit的情况



好了,通过以上的知识,大家都应该很了解,接下来就是实施的问题了。对于新业务,新DB,直接设置这样的参数就好了,再测试环境也设置,这样开发在测试环境就能发现问题,不会在新业务上产生这样危险的语句。

对于老业务,怎么办呢?我们的做法:因为我们的MySQL是5.6,所以另外一个神奇的功能就是P_S(performance schema), 通过P_S,我们可以获取哪些query语句是没有使用索引的。

这里又会引发另外一个问题,可能是Performance schema的bug,它竟然无法统计DML操作是否使用索引。经过大量的测试后证明:events_statements_summary_by_digest 表里面的SUM_NO_INDEX_USED,SUM_NO_GOOD_INDEX_USED ,对DML操作无效。

既然如此,我们所幸对DML语句自己进行分析,将DML转换成对应的SELECT语句。比如: update tb set id = S where id = S; 转换成 select * from tb where id = '1' ...... 然后根据SELECT语句,进行EXPLAIN分析,如果type=ALL表示没有使用索引,这样的语句就是我们认为的全表DML语句了。

然而,理想很丰满,现实很骨感。这样的做法很快就出现了问题, 因为这里需要自己构造真实的SQL,由于数据分布以及构造的语句不可能真实,所以得到的执行计划谬之千里,type=None。所以,以上方法很可能导致全表的DML没有被抓取出来,so 我们开始想其他办法。

说来也简单,参数sql_safe_udpates 只针对两种场景是不允许的,那就是:

1. where条件后面 无索引,无limit的情况

2. 无where条件的情况 , 无limit的情况


那么我们就获取dml语句后面的字段和关键字,用来构造我们的全表DML:

1. 检查dml 是否是带有limit的语句
    如果有,允许通过 -- ( 有limit , 肯定可以执行  )
    如果没有,则往下继续判断

2. 判断dml SQL有无where条件
    如果没有, 则直接拒绝  -- (没有where,没有limit,肯定是全表扫描的更新,直接拒绝 )
    如果有,则继续往下判断

3. 判断where后面的字段是否符合索引前缀原理
    如果符合,则允许执行  -- ( where条件后面字段有索引,无limit, 允许通过 )
    如果不符合,则拒绝   -- ( where条件后面字段无索引,无limit,直接拒绝  )


恩,这样分析下来,是不是感觉很完美了? 还是那句话,理想和现实总有差距,那么来几条牛逼的漏网之鱼看看呗

1. 类型转换导致的问题update tb set id=2 where id_change = 1;  
-- 注意:字段id_change是varchar类型。

2. 函数UPDATE pay_log_id SET id=LAST_INSERT_ID(id + 1)


至少以上两种类型是抓不到的,所以,还是有问题,那么继续找方法。

重新分析下我们的初心,我们的目的是啥?没错,我们的目的就是要先找到没有使用索引的DML,突然脑海中飘来一句话,MySQL自身是否可以打印出没有使用索引的语句呢?

果然,去官方文档上一搜index关键字,结果log_queries_not_using_indexes就是我们迫切需要的,但是它会将select也打印出来,不过没关系,我们将select过滤掉即可。

so,最后的终极解决方案就是:在测试环境加上log_queries_not_using_indexes=1(long_query_time=1000,这样可以不用混淆),然后测试环境跑一个月,将没有使用索引的DML语句统统抓住来解决掉,这样就可以安心的上线sql_safe_updates=1 了。


注意:

当log_queries_not_using_indexes=1 和 sql_safe_updates=1 同时设置的时候:

1) delete from tb_1 ;  --会被sql_safe_updates拒绝,不会记录到slow log中
2) update tb_1 set id = 1; --会被sql_safe_updates拒绝,同时也会被记录到slow log中以上就是两者的区别,
善用


总结


如果线上设置sql_safe_updates = 1 后,业务还有零星的dml被拒绝,业务方可以考虑如下解决方案:

1)如果你确保你的SQL语句没有任何问题,可以这样: set sql_safe_updates=0; 但是开发必须考虑到这样做的后果。

2) 可以改写SQL语句,让其使用上索引字段。

3)为什么这边没有让大家使用limit呢?因为在大多数场景下,dml + limit = 不确定的SQL 。 很可能导致主从不一致。 ( dml + limit 的方式,是线上禁止的)

各位看官,以上神器请大家慢慢享用。 关于PS和sys,如果大家有更加新奇的想法,可以一起讨论研究。

猜你喜欢



 
InsideMySQL 更多文章 网易蜂巢云数据库新版本发布,除了DB,我们还有诗与远方 一触即发,2017年,数据库世界的诸神之战 MySQL Group Replication性能测试,星辰大海还是前路茫茫? 年薪50万?那你得先知道每天凌晨三点的样子 原谅我这么幼稚,所以才会喜欢你这麼久 #MySQL#
猜您喜欢 如何学好C/C++ 互联网生态逻辑下导购型APP的生存之道 云自动化会让运维人员失业吗? 简书以及未来垂直社区的自出版会走向何方? 人生是什么?| 朋友圈超百万人分享的漫画