mysql本地服务器设置（mysql服务器的设置文件名是什么）「mysql服务器的配置文件名」

　　神奇的参数——sql_safe_update

　　官方文档阐明：

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

注：兰春同砚是安居客DBALeader，负责数百台MySQL服务器的运维工作。对MySQL数据库非常有豪情，不停与我不绝在美满MySQL最优设置文件模板。

　　BTW：戋戋在网易内部推动过sql_safe_update，但是未能乐成。诸多缘故起因，想想非常痛惜。

　　配景(why)

　　参数sql_safe_update重要用于防范大表的误操纵。由于假如只是更改了几条记录，那么说不定业务方可以很轻易的根据日记举行规复。即便没有，也可以通过找二进制日记（binlog）举行逆向操纵规复。假如被误操纵的表非常小，着实题目也不大，全备+binlog规复or闪回（点击：拿走不谢，FlashbackforMySQL5.7）都可以举行很好的规复。

　　But，假如你要规复的表非常大，比如：100G，100T，对于这范例的误操纵，恐怕神仙都难救。以是，这里通过这个神奇的参数，可以克制掉80%的误操纵场景。PS:不能克制100%，下面的实战会告诉各人怎样破解。

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

updatexxseturl_desc='防不胜防'

　　WHERE4918=4918ANDSLEEP(5)--xYpp'whereid=7046

　　若这表，线上有500G，一次误操纵，要规复500G的数据，必要停止服务很长时间。假如设置了sql_safe_updates，此类变乱就可以很富丽的克制掉了。

原理和实战

　　表布局：

　　dba:lcshowcreatetabletbG

　　*******************1.row****************

　　Table:tb

　　CreateTable:CREATETABLE`tb`(

　　`id`int(11)NOTNULL,

　　`id_2`int(11)DEFAULTNULLCOMMENT'lc33',

　　`id_3`text,

　　PRIMARYKEY(`id`),

　　KEY`idx_2`(`id_2`)

　　)ENGINE=InnoDBDEFAULTCHARSET=utf8

　　1rowinset(0.01sec)

UPDATE相干测试

　　*不带where条件

　　dba:lcupdatetbsetid_2=2;ERROR1175(HY000):YouareusingsafeupdatemodeandyoutriedtoupdateatablewithoutaWHEREthatusesaKEYcolumn

　　*where条件有索引，但是没有limit

　　dba:lcupdatetbsetid_3='bb'whereid0;^C^C--queryabortedERROR1317(70100):Queryexecutionwasinterrupted

　　*where条件无索引，也没有limit

　　dba:lcupdatetbsetid_3='bb'whereid_3='0';ERROR1175(HY000):YouareusingsafeupdatemodeandyoutriedtoupdateatablewithoutaWHEREthatusesaKEYcolumn

　　*where条件有索引，有limit

　　dba:lcupdatetbsetid_3='bb'whereid0limit1;QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0

　　*where条件无索引，有limit

　　dba:lcupdatetbsetid_3='bb'whereid_30limit1;QueryOK,0rowsaffected(0.26sec)Rowsmatched:0Changed:0Warnings:0

　　结论：对于UPDATE操纵，只有两种场景会被限定：

无索引，无limit的环境

无where条件,无limit的环境

DELETE相干测试

　　*不带where条件dba:lcdeletefromtb;

　　ERROR1175(HY000):YouareusingsafeupdatemodeandyoutriedtoupdateatablewithoutaWHEREthatusesaKEYcolumn*where条件有索引，但是没有limitdba:lcdeletefromtbwhereid=0;QueryOK,0rowsaffected(0.00sec)dba:lcdeletefromtbwhereid0;^C^C--queryabortedERROR1317(70100):

　　Queryexecutionwasinterrupteddba:lcdeletefromtbwhereid_20;^C^C--queryaborted^C^C--queryaborted

　　ERROR1317(70100):Queryexecutionwasinterrupted*where条件无索引，也没有limitdba:lcdeletefromtbwhereid_3='a';

　　ERROR1175(HY000):YouareusingsafeupdatemodeandyoutriedtoupdateatablewithoutaWHEREthatusesaKEYcolumn*where条件有索引，有limitdba:lcdeletefromtbwhereid=205limit1;QueryOK,1rowaffected(0.00sec)*where条件无索引，有limitdba:lcdeletefromtbwhereid_3='aaaaa'limit1;

　　QueryOK,1rowaffected(0.00sec)

　　测试结果：关于DELETE操纵相干，官方文档形貌有误

　　结论：对于DELETE操纵，只有两种场景会被限定

　　1.无索引，无limit的环境

　　2.无where条件,无limit的环境

　　综上所述：不管是UPDATE，还是DELETE，被限定的条件只有两个

　　1.无索引，无limit的环境

　　2.无where条件,无limit的环境

　　好了，通过以上的知识，各人都应该很相识，接下来就是实行的题目了。对于新业务，新DB，直接设置如许的参数就好了，再测试环境也设置，如许开辟在测试环境就能发现题目，不会在新业务上产生如许伤害的语句。

　　对于老业务，怎么办呢？我们的做法：由于我们的MySQL是5.6，以是别的一个神奇的功能就是P_S（performanceschema）,通过P_S，我们可以获取哪些query语句是没有利用索引的。

　　这里又会引发别的一个题目，大概是Performanceschema的bug，它竟然无法统计DML操纵是否利用索引。颠末大量的测试后证明：events_statements_summary_by_digest表内里的SUM_NO_INDEX_USED，SUM_NO_GOOD_INDEX_USED，对DML操纵无效。

　　既然云云，我们所幸对DML语句本身举行分析，将DML转换成对应的SELECT语句。比如：updatetbsetid=Swhereid=S;转换成select*fromtbwhereid='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.判定dmlSQL有无where条件假如没有，则直接拒绝--（没有where，没有limit，肯定是全表扫描的更新，直接拒绝）假如有，则继承往下判定3.判定where背面的字段是否符合索引前缀原理假如符合，则答应实行--（where条件背面字段有索引，无limit，答应通过）假如不符合，则拒绝--（where条件背面字段无索引，无limit，直接拒绝）

　　恩，如许分析下来，是不是感觉很美满了？还是那句话，抱负和实际总有差距，那么来几条牛逼的漏网之鱼看看呗

　　1.范例转换导致的题目updatetbsetid=2whereid_change=1;

　　--留意：字段id_change是varchar范例。

　　2.函数UPDATEpay_log_idSETid=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)deletefromtb_1;--会被sql_safe_updates拒绝，不会记录到slowlog中

　　2)updatetb_1setid=1;--会被sql_safe_updates拒绝，同时也会被记录到slowlog中以上就是两者的区别，善用

　　总结

　　假如线上设置sql_safe_updates=1后，业务尚有零散的dml被拒绝，业务方可以思量如下办理方案：

　　1）假如你确保你的SQL语句没有任何题目，可以如许：setsql_safe_updates=0;但是开辟必须思量到如许做的结果。

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

　　3）为什么这边没有让各人利用limit呢？由于在大多数场景下，dml+limit=不确定的SQL。很大概导致主从不同等。(dml+limit的方式，是线上克制的)

　　各位看官，以上神器请各人渐渐享用。关于PS和sys，假如各人有更加新奇的想法，可以一起讨论研究。

猜你喜好

一触即发，2017年，数据库天下的诸神之战

年薪50万？那你得先知道每天破晓三点的样子

MySQLGroupReplication性能测试，星辰大海还是前路茫茫？

为什么我不再看好MariaDB

包涵我这么稚子，以是才会喜好你这麼久#MySQL#

从你的全天下途经

夫君找个支持你抱负的老婆，少奋斗25年

Galera将死——MySQLGroupReplication正式发布

• 服务器是什么
• 服务器租用平台
• 服务器租用多少钱一年
• 服务器怎么搭建
• 服务器异常怎么解决
• 服务器下载安装
• 服务器回收
• 服务器品牌前十大排名
• 服务器图片
• 服务器地址