61
技術社區[雲棲]
MySQL運維之神奇的參數(終結篇)
一、主要內容
- 生產前的測試方案
- 生產環境如何平滑實施
- 生產壞境中遇到哪些困難
- 我們的解決方案
- 價值與意義
二、背景
這個項目的起源，來源於生產環境中的N次誤刪數據，所以才有他的姊妹篇文章，一個神奇的參數前傳
三、生產前的測試方案
3.1 why
- 為什麼要做測試方案
1. 大家都知道設置sql_safe_update=1 會拒絕掉很多你想不到的SQL，這樣會導致業務出問題，服務中斷，影響非常嚴重
2. 我們需要測試出哪些SQL語句會被拒絕？
3. 我們需要知道已經上線的SQL語句中，哪些會被拒絕？
總之，我們需要無縫升級，怎麼樣才能既加強安全防範，又不影響業務呢？
這就是我們的SQL防火牆係統的升級改造之路
3.2 如何測試
非常感謝DBA團隊袁俊敏同學的細心測試
1. 根據官方文檔的提示，以及之前碰壁的經驗，我們詳細的設計了各種SQL方案
a. 單鍵索引
a.1 update語句
a.2 delete語句
a.3 replace into係列
a.4 有limit
a.5 無limit
a.6 有where條件
a.7 無where條件
a.8 隱式類型字符轉換
a.9 SQL帶有函數方法
b. 組合索引
b.1 update語句
b.2 delete語句
b.3 replace into係列
b.4 有limit
b.5 無limit
b.6 有where條件
b.7 無where條件
b.8 隱式類型字符轉換
b.9 SQL帶有函數方法
等等
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，我們的目的就達到了
五、生產壞境中遇到哪些困難
這邊說一個典型的坑
- 你們真的以為設置了log_queries_not_using_indexes就一定能夠抓出我們需要的DML嗎？
1. 首先：log_queries_not_using_indexes=on，的確是可以抓出所有沒有使用索引的DML
2. 但是：再設置log_queries_not_using_indexes之前，這個connection已經存在了，那麼log_queries_not_using_indexes對早已經存在的connection是不起作用的
- 故障一
1. master 由於對於長連接不生效，所以全表更新dml在master執行了，但是在slave卻不能執行，導致主從同步失敗
- 故障二
1. master 由於對於長連接不生效，所以全表更新dml在master執行了，那麼意味著，你以為可以保障MySQL安全，其實隻是自欺欺人而已
六、我們的解決方案
解決長連接問題
- 刪掉所有鏈接
1. 有人說，那簡單，我們直接全部刪掉所有鏈接就好了。
的確，全部刪除，的確可以做到，但是是不是有點粗暴呢？
2. 那就讓業務方將所有長連接應用重啟
這。。。業務方會很崩潰，也不可能停掉所有的長連接服務
- 隻kill具有dml權限的長連接
* 如何找到長連接
1. 長連接的特點：長
2. 那麼MySQL裏麵的show processlist有兩個非常重要的屬性
Id: session id
Time: command status time
3. 誤區
這裏有大部分人會根據Time來識別這個鏈接是不是長連接，那麼他一定不理解time的含義
它並不是鏈接的時間長短，而是command某個狀態的時間而已
4. 大家已經猜到，最終的方案就是通過session id來判斷識別長連接
4.0 先在master上設置sql_safe_update=on
4.1 然後假設10:00 show processlist，記錄下所有的id
4.2 那麼明天10:00 show processlist，與上一次的id進行匹配，如果匹配中了，那麼說明這個connection已經存在一天，那麼可以認為他是長連接了
4.3 判斷這些id對應的用戶權限，隻讀賬號忽略
4.4 kill掉這些長連接即可（注意：repl，system user 這些係統進程不要被誤刪掉了，否則哭都來不及）
4.5 可以根據host:port告知業務方，一起配合重啟和kill之後的觀察
價值和意義
目前我們已經完成了N組DB集群的設置
這裏有很多人有疑問：
- 花這麼大的代價，隻是為了設置這樣的一個參數，值得嗎？
- 萬一搞不好，弄出問題來，豈不是沒事找事，給自己找罪受？
- 這樣操作，開發會支持你嗎？你們老大支持你嗎？
我是這麼理解的：
- 剛開始的時候，的確難度非常大，後來我們經過無數次測試和技術方案演練，還是決定冒著槍林彈雨，隻為以後的數據安全
- 一切以用戶為中心，我們必須用我們專業的判斷對用戶負責
- final：我將這件事看做： '功在當代，利在千秋' 的一件事
最後更新：2017-06-30 21:51:03