mysql运维洞察_MySQL运维之神奇的参数(终结篇)

摘要：一、主要内容 生产前的测试方案 生产环境如何平滑实施 生产坏境中遇到哪些困难 我们的解决方案 价值与意义 二、背景 这个项目的起源，来源于生产环境中的N次误删数据，所以才有他的姊妹篇文章，一个神奇的参数前传 三、生产前的测试方案 3.

一、主要内容

生产前的测试方案

生产环境如何平滑实施

生产坏境中遇到哪些困难

我们的解决方案

价值与意义

二、背景

这个项目的起源，来源于生产环境中的N次误删数据，所以才有他的姊妹篇文章，一个神奇的参数前传

三、生产前的测试方案

3.1 why

为什么要做测试方案

1.大家都知道设置sql_safe_update=1会拒绝掉很多你想不到的SQL，这样会导致业务出问题，服务中断，影响非常严重2.我们需要测试出哪些SQL语句会被拒绝？3.我们需要知道已经上线的SQL语句中，哪些会被拒绝？总之，我们需要无缝升级，怎么样才能既加强安全防范，又不影响业务呢？这就是我们的SQL防火墙系统的升级改造之路

3.2 如何测试

非常感谢DBA团队袁俊敏同学的细心测试

1. 根据官方文档的提示，以及之前碰壁的经验，我们详细的设计了各种SQL方案a. 单键索引a.1update语句a.2delete语句a.3replace into系列a.4有limita.5无limita.6有where条件a.7无where条件a.8隐式类型字符转换a.9SQL带有函数方法b. 组合索引b.1update语句b.2delete语句b.3replace into系列b.4有limitb.5无limitb.6有where条件b.7无where条件b.8隐式类型字符转换b.9SQL带有函数方法    等等

3.3 哪些语句会触发sql_safe_update报错

1. 有where 条件且没有使用索引,且没有limit语句--触发2. 没有where 条件 ， 有limit，delete语句--触发3.没有where条件 ， 没有limit，delete+update语句--触发总结： 没有使用索引的DML语句，都会被触发

四、生产环境如何平滑实施

log_queries_not_using_indexes=on

long_query_time = 10000

log_queries_not_using_indexes 无长连接的概念,立即对所有链接生效

通过log_queries_not_using_indexes=on+ long_query_time =10000可以抓出所有我们需要的dml，解决掉这些sql，我们的目的就达到了

五、生产坏境中遇到哪些困难

这边说一个典型的坑

你们真的以为设置了sql_safe_updates就一定能够拒绝没有使用索引的SQL吗？

1.首先：log_queries_not_using_indexes=on，的确是可以抓出所有没有使用索引的DML2.但是：再设置sql_safe_updates=1之前，如果这个connection已经存在了，那么sql_safe_updates=1对早已经存在的connection是不起作用的

不可预见的问题

1.online目前long_query_time=0.1，本来打算当场设置long_query_time =10000, 来排除掉slow的因素，然后设置log_queries_not_using_indexes=on 将所有没有使用索引的SQL抓出来。2.结果出乎意料的是：将使用索引的DML也抓了出来3.后来通过slow发现这些dml都是大于100ms的，才得知结论：长连接还在使用long_query_time=0.1的参数，对于刚刚设置的参数不生效。4.所以，long_query_time 对长连接无效。

目前总结下来：这里面有两个关键参数对长连接无效

1.long_query_time2.sql_safe_updates

故障一

1.master由于对于长连接不生效，所以全表更新dml在master执行了，但是在slave却不能执行，导致主从同步失败(MIXED,STATEMENT)2. 以上情况ROW模式不受影响，因为Row模式已经是对每一行记录进行更改，不可能不安全

故障二

1.master由于对于长连接不生效，所以全表更新dml在master执行了，那么意味着，你以为可以保障MySQL安全，其实只是自欺欺人而已

六、我们的解决方案

解决长连接问题

删掉所有链接

1.有人说，那简单，我们直接全部删掉所有链接就好了。的确，全部删除，的确可以做到，但是是不是有点粗暴呢？2.那就让业务方将所有长连接应用重启这。。。业务方会很崩溃，也不可能停掉所有的长连接服务

只kill具有dml权限的长连接

* 如何找到长连接1.长连接的特点：长2.那么MySQL里面的show processlist有两个非常重要的属性    Id: session id    Time:commandstatustime3.误区    这里有大部分人会根据Time来识别这个链接是不是长连接，那么他一定不理解time的含义    它并不是链接的时间长短，而是command某个状态的时间而已4.大家已经猜到，最终的方案就是通过session id来判断识别长连接4.0先在master上设置sql_safe_update=on4.1然后假设10:00show processlist，记录下所有的id4.2那么明天10:00show processlist，与上一次的id进行匹配，如果匹配中了，那么说明这个connection已经存在一天，那么可以认为他是长连接了4.3判断这些id对应的用户权限，只读账号忽略4.4kill掉这些长连接即可(注意：repl，systemuser 这些系统进程不要被误删掉了，否则哭都来不及)4.5可以根据host:port告知业务方，一起配合重启和kill之后的观察

价值和意义

目前我们已经完成了N组DB集群的设置

这里有很多人有疑问：

花这么大的代价，只是为了设置这样的一个参数，值得吗？

万一搞不好，弄出问题来，岂不是没事找事，给自己找罪受？

这样操作，开发会支持你吗？你们老大支持你吗？

我是这么理解的：

刚开始的时候，的确难度非常大，后来我们经过无数次测试和技术方案演练，还是决定冒着枪林弹雨，只为以后的数据安全

一切以用户为中心，我们必须用我们专业的判断对用户负责

final：我将这件事看做： '功在当代，利在千秋' 的一件事