MySQL运维之神奇的参数MySQL运维之神奇的参数

sql_safe_updates <a href="http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_safe_updates">http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_safe_updates</a>

主要是针对大表的误操作。

如果只是更改了几条记录，那么说不定业务方可以很容易的根据日志进行恢复。即便没有，也可以通过找binlog，进行逆向操作恢复。

如果被误操作的表非常小，其实问题也不大，全备+binlog恢复 or 闪回 都可以进行很好的恢复。

but，如果你要恢复的表非常大，比如：100g，100t，对于这类型的误操作，恐怕神仙都难救。

所以，我们这里通过这个神奇的参数，可以避免掉80%的误操作场景。 ps: 不能避免100% ，下面的实战会告诉大家如何破解。

表结构

update 相关测试

update statements must have a where clause that uses a key or a limit clause, or both.

结论： 对于update，只有两种场景会被限制

无索引，无limit的情况

无where条件, 无limit的情况

delete相关测试

delete statements must have both

测试结果证明： 关于delete相关，官方文档描述有误。

结论： 对于delete，只有两种场景会被限制

综上所述：不管是update，还是delete ，被限制的前提只有两个

好了，通过以上的知识，大家都应该很了解，接下来就是实施的问题了。

对于新业务，新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 只针对两种场景是不允许的，那就是：

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

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

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

重新分析下我们的初心，我们的目的是啥？没错，我们的目的就是要先找到没有使用索引的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 了。

注意：

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

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

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

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

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