MySQL常見故障處理手冊
http://www.housong.net/mysql-normal-trouble-shooting.html
一、性能故障
1. 數據庫主機LOAD飆高
(1)一般導致MySQL服務器LOAD突然飆高，可能的五種情況：
– 全表掃描的SQL語句；
– SELECT操作語句的執行計劃走錯；
– 存在UPDATE/DELETE 語句沒有索引可選擇，而導致堵塞其他SQL語句的執行；
– 存在修改表結構或OPTIMIZE 語句執行；
– 大數據量的導入或導出，尤其數據庫的邏輯備份操作；
– 業務量大到超過服務器處理能力（我們大家都高度關注業務發展，以及公司業務特點，
– 還有與開發和運營保持良好聯系，很難出現未知的業務突然爆發性增長）；
(2)要解決LOAD飆高，必須先找到造成飆高的真實原因，請登陸數據庫服務器后，執行命令：
1 | SHOW PROCESSLIST; |
2 | 或 |
3 | SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND <> ‘sleep’ AND TIME>100; |
(3)若一直處在執行狀態，且執行時間比較久，可以分析下SQL語句執行計劃：
1 | mysql>EXPLAIN SQL語句 |
mysql重啟命令。(4)檢查io請求狀態
使用Linux命令: iostat
查看r/s(讀請求),w/s(寫請求),avgrq-sz(平均請求大小),await(IO等待), svctm(IO響應時間)
若執行計劃不合理，則可以根據SQL類型選擇是否與應用負責人聯系。首先，查找造成服務器LOAD飆高的PID，特別是DELETE 或UPDATE等會堵住其他SQL語句的PID，然后進入MySQL命令行工具中，對一些SQL先記錄下來，再適情考慮執行：KILL SQL PID
2. 請求處理波動，應用大量請求超時失敗
(1)檢查數據庫的響應狀態,使用tcprstat
1 | tcprstat -l $ip_addr -t $interval -n $count |
按照匯報結果，切分是否為數據庫端問題還是非數據庫端問題.
(2)分析關鍵指標
com_select/insert/update/delete
innodb_rows_read/update/delete/insert
innodb_logicial_read/physical read
使用SHOW STATUS和 orzdba -com 可以獲取采樣指標
MySQL null、(3)轉向分析slow query， 檢查可疑SQL
1 | EXPLAIN SQL語句; |
如有長時間運行異常SQL, 可以Kill SQL PID.
(4)重點關注索引的使用問題
二、復制故障
1. 復制中斷
(1)備機無法連接到主服務器，可能是網絡問題，也可能是主服務器的mysqld已停止；
(2)主鍵沖突；
(3)主從服務器數據不一致；
(4)其他原因；
為使復制繼續，我們可以進行如下處理：
(1)stop slave ;
(2)start slave;
(3)檢查服務是否正常：show slave status\G
MySQL啟動不了、若是主健沖突或數據不一致的情況，則需要額外處理：
(1)停止slave進程
1 | STOP SLAVE; |
(2)設置事務號，事務號從Retrieved_Gtid_Set獲取
1 | SET @@SESSION.GTID_NEXT= 'xxxxxxxxxxx' |
(3)設置空事務
1 | BEGIN; COMMIT; |
(4)恢復事務號
1 | SET SESSION GTID_NEXT = AUTOMATIC; |
(5)啟動slave進程
1 | START SLAVE; |
三、實例故障
MySQLdelete刪除的數據回滾、1.MySQL實例假死
(1)再次確認mysql的運行狀態
1 | SHOW PROCESSLIST;是否有大量請求在等待處理 |
此時，為校驗是否真處于MysQL假死狀態，那么可以用test庫中任意執行創建表或更新數據的語句，若回車鍵后沒有響應，則一般可以斷定MySQL 是否已經處于假死狀態。
(2)檢查mysql實例的進程的內存/交換分區狀態
使用linux 命令: free -m
使用linux 命令: top
結合當前業務壓力，檢查內存消耗現狀，消耗速度，是否開始使用交換分區,如內存消耗過快且業務壓力不大，并發不高，轉向對slow query的定位。
(3)檢查mysql實例的進程分區使用和IO狀態
使用Linux命令: df -hT
使用Linux命令: iostat -x
確認分區有足夠空間，如異常，則進入對應目錄，檢查實例的數據和日志存放和增長情況，特別是對日志進行清理處理，或通過調整邏輯卷的大小解決問題。
MySQL server？檢查iostat匯報值中 %user, %iowait, %idle 值是否異常，如異常且當前業務壓力不大，轉向對slow query的定位，同時要求主機運維人員協查物理存儲的健康狀況。
(4)檢查mysqld的CPU使用情況
使用Linux命令: top
使用Linux命令:ps -eo pid,user,comm,pcpu,pmem,vsz | grep mysqld
結合當前業務壓力和連接數，確認mysql進程的CPU占用率是否異常，如當前業務壓力不大，并發不高，轉向對slow query的定位。
(5)檢查實例主機網絡通信
使用Linux命令: ping 檢測到應用主機，到復制從機的RTT
使用Linux命令: iptraf 確認實例主機的網卡帶寬和速度
如以上操作發現異常，需要主機運維人員使用tcpdump進行抓包分析
(6) Slow query 分析
打印slow query日志，分析和定位造成故障的SQL, 必要時，kill 掉出現問題的SQL ID
2.應用報連接池滿
(1) 檢查io請求狀態
使用Linux命令: iostat
查看r/s(讀請求),avgrq-sz(平均請求大小),svctm(IO響應時間)
(2)運行orzdba對照SQL執行數量(QPS-sel欄和TPS-iud欄)和邏輯讀消耗(Hit%-lor欄)
(3) 轉向slow query分析查看行掃描Query_time和Rows_examined欄，如無明顯定位和結論，進入下一步全面分析
(4)使用pt-query-digest分析全面slow query，著重分析索引的合法性
(5) 檢查General log
(6) 檢查Binlog, 對于DML操作, 通過mysqlbinlog工具解析binlog檢查
(7) 使用Linux命令 tcpdump抓包分析，配合pt-query-digest做進一步分析排查
MySQL無響應、3. MySQL請求線程堵塞
(1)檢查 processlist
1 | processlist 主要查看 Time, State 欄的匯報值 |
(2)使用pstack 保存當前mysqld實例的棧調用，以備進一步分析
四、其它故障
1. 各種日志的標準存放路徑和查看方法
(1)常規后臺進程報錯日志log-error路徑
1 | show variables like '%log_error%'; |
2 | | log_error???? | /paic/mymon/data/mysqldata/mymon/mysql.err | |
文本文件，可以直接查看，類似oracle的alert日志。
(2)慢查詢日志slow_query_log_file，其設定值long_query_time
1 | show variables like '%slow_query_log_file%'; |
2 | | slow_query_log_file | /paic/mymon/data/mysqldata/mymon/cnsh230234-slow.log | |
3 | show variables like '%long_query_time%'; |
4 | | long_query_time | 10.000000 | |
文本文件，可以看出sql文本及sql的執行時間
(3)數據庫二進制日志文件binlog
查看方法mysqlbinlog mysql-bin.000001 |more
可以直接看到事務號和sql腳本
2. mysql進程報錯，類似ORA-
例如：071221 11:12:12 [ERROR] Got error 127 when reading table ‘./download_utf8/source_uesrdown’
查看127報錯原因
1 | cnsh230234: >perror 127 |
2 | OS error code 127:? Key has expired |
3 | MySQL error code 127: Record file is crashed |
4 | 參考處理辦法 Repair Table source_uesrdown ; Repair Table userday_money; |
3. 忘記root密碼，重置方法
(1)/etc/init.d/mysql stop
(2)mysqld_safe –skip-grant-tables &
(3)mysql -uroot -p
(4)update mysql.user set password=password(“”yourpasswd”") where user=”"root”"
(5)flush privileges
(6)quit
