據(jù)庫(kù)運(yùn)維常用腳本實(shí)戰(zhàn)：從備份恢復(fù)到性能優(yōu)化)
入職這些年我發(fā)現(xiàn)自己干得最多的活不是寫(xiě)業(yè)務(wù)代碼也不是搭架構(gòu)而是跟一堆數(shù)據(jù)庫(kù)常用腳本打交道。無(wú)論是幫業(yè)務(wù)部門(mén)查一條慢SQL還是半夜被叫起來(lái)看主從延遲手里沒(méi)幾個(gè)趁手的腳本真就寸步難行。今天這篇不整虛的把我實(shí)際工作中反復(fù)在用、踩過(guò)坑之后修正過(guò)的腳本整理出來(lái)按場(chǎng)景分類(lèi)講清楚每個(gè)腳本為什么這么寫(xiě)、參數(shù)怎么調(diào)、遇到什么問(wèn)題怎么改都一并交代。適合剛接觸數(shù)據(jù)庫(kù)運(yùn)維的開(kāi)發(fā)者也適合想把手頭腳本整理得更規(guī)范的DBA。1. 信息查詢(xún)與元數(shù)據(jù)腳本先把家底盤(pán)清楚1.1 三分鐘摸清實(shí)例整體狀況接手一臺(tái)新的數(shù)據(jù)庫(kù)服務(wù)器第一件事不是急著優(yōu)化而是先搞清楚這臺(tái)實(shí)例到底什么配置、什么負(fù)載、什么水位。我習(xí)慣先跑一套體檢三連-- 查看實(shí)例版本、運(yùn)行時(shí)長(zhǎng)、字符集等基礎(chǔ)信息 SELECT VERSION() AS version, NOW() AS current_time, hostname AS host_name, port AS port, character_set_server AS charset, collation_server AS collation; -- 查看關(guān)鍵運(yùn)行狀態(tài)值重點(diǎn)看連接數(shù)和慢查詢(xún) SHOW GLOBAL STATUS LIKE Threads_connected; SHOW GLOBAL STATUS LIKE Threads_running; SHOW GLOBAL STATUS LIKE Slow_queries; SHOW GLOBAL STATUS LIKE Uptime; -- 查看InnoDB buffer pool命中率相關(guān)指標(biāo) SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads;這幾個(gè)腳本看著簡(jiǎn)單但信息密度很高。版本和端口就不說(shuō)了Threads_connected和Threads_running這兩個(gè)值一定要配合看前者代表當(dāng)前建立的連接數(shù)后者代表真正在執(zhí)行SQL的線(xiàn)程數(shù)。如果連接數(shù)飆到幾百但Threads_running一直不高說(shuō)明大部分連接是閑置連接問(wèn)題多半出在應(yīng)用側(cè)連接池配置上而不是數(shù)據(jù)庫(kù)本身。InnoDB buffer pool命中率有個(gè)經(jīng)驗(yàn)公式1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)這個(gè)值常年低于99%說(shuō)明內(nèi)存配置或查詢(xún)模式有問(wèn)題光靠加內(nèi)存未必管用可能得從SQL層面找原因。1.2 快速定位大表與膨脹空間剛接手一個(gè)庫(kù)業(yè)務(wù)方問(wèn)我哪些表最大、哪些表膨脹得厲害我從來(lái)不看information_schema因?yàn)槟莻€(gè)統(tǒng)計(jì)信息在數(shù)據(jù)量大的時(shí)候本身就不準(zhǔn)掃描也慢。直接用系統(tǒng)表查快得多-- 查看指定庫(kù)中各表的行數(shù)、數(shù)據(jù)大小、索引大小MySQL 8.0 SELECT table_name, table_rows, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(index_length / 1024 / 1024, 2) AS index_mb, ROUND((data_length index_length) / 1024 / 1024, 2) AS total_mb FROM information_schema.tables WHERE table_schema your_db ORDER BY total_mb DESC LIMIT 20;這里有個(gè)細(xì)節(jié)必須提醒table_rows是估算值InnoDB引擎下誤差可能很大尤其頻繁增刪的表。真要精確行數(shù)還是得SELECT COUNT(*)但大表別在生產(chǎn)上直接跑低峰期再操作。另外我還會(huì)加一個(gè)查詢(xún)看哪些表的數(shù)據(jù)量和索引量比例失調(diào)。曾遇到一張表數(shù)據(jù)才200MB索引卻占了1.5GB查下來(lái)是建了一堆冗余的聯(lián)合索引刪掉三個(gè)索引后空間直接釋放寫(xiě)入性能也提升了。索引不是越多越好這個(gè)后面專(zhuān)門(mén)講。1.3 找出從未被使用的索引索引是有維護(hù)成本的每次寫(xiě)入都要更新索引結(jié)構(gòu)。很多系統(tǒng)上線(xiàn)久了當(dāng)初拍腦袋建的索引根本沒(méi)人用白白拖累寫(xiě)入性能。查索引使用情況MySQL有現(xiàn)成的統(tǒng)計(jì)表-- 查看索引使用統(tǒng)計(jì)需開(kāi)啟performance_schemaMySQL 5.7 SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR, COUNT_READ, COUNT_WRITE FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA your_db AND INDEX_NAME IS NOT NULL ORDER BY COUNT_READ ASC LIMIT 20;COUNT_READ長(zhǎng)期為0的索引基本就是死索引。但刪之前務(wù)必確認(rèn)兩點(diǎn)第一查詢(xún)計(jì)劃里是否真的沒(méi)用到有些統(tǒng)計(jì)值低但偶爾高峰用到的索引不能動(dòng)第二有沒(méi)有唯一性約束依賴(lài)這個(gè)索引。我習(xí)慣把候選索引先標(biāo)記為INVISIBLEMySQL 8.0支持觀察一周確認(rèn)沒(méi)有報(bào)錯(cuò)再真正DROP這個(gè)操作可以說(shuō)非常穩(wěn)妥。2. 備份與恢復(fù)腳本平時(shí)用不上出事保命2.1 邏輯備份腳本的正確姿勢(shì)說(shuō)到備份很多人第一反應(yīng)就是mysqldump一把梭。但我見(jiàn)過(guò)太多因?yàn)閰?shù)沒(méi)加對(duì)導(dǎo)致備份恢復(fù)失敗的案例。我最常用的一套邏輯備份腳本長(zhǎng)這樣#!/bin/bash # 按庫(kù)備份保留最近7天帶時(shí)間戳 BACKUP_DIR/data/backup/mysql DATE$(date %Y%m%d_%H%M%S) DB_USERbackup_user DB_PASSyour_password DB_LISTdb1 db2 db3 for DB in $DB_LIST; do mysqldump \ --single-transaction \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ -u$DB_USER -p$DB_PASS $DB \ | gzip $BACKUP_DIR/${DB}_${DATE}.sql.gz done # 清理7天前的備份 find $BACKUP_DIR -name *.sql.gz -mtime 7 -delete這幾個(gè)參數(shù)每個(gè)都有講究--single-transaction不加這個(gè)InnoDB表備份時(shí)會(huì)對(duì)全表加鎖線(xiàn)上業(yè)務(wù)直接卡住。加上后基于MVCC一致性快照不鎖表。--routines --triggers --events不加這三個(gè)存儲(chǔ)過(guò)程、觸發(fā)器、事件調(diào)度器全部丟失。很多人恢復(fù)后發(fā)現(xiàn)跑批任務(wù)不見(jiàn)了就是栽在這。--set-gtid-purgedOFFGTID模式下的老坑。不關(guān)掉這個(gè)問(wèn)題備份文件里帶著GTID信息恢復(fù)到新實(shí)例時(shí)會(huì)因?yàn)槭聞?wù)ID沖突直接報(bào)錯(cuò)。注意--single-transaction對(duì)MyISAM表無(wú)效MyISAM依舊會(huì)鎖表。如果庫(kù)里還有MyISAM表建議要么遷移到InnoDB要么備份安排在業(yè)務(wù)低谷期執(zhí)行。2.2 更穩(wěn)的物理備份xtrabackupmysqldump在數(shù)據(jù)量超過(guò)幾十GB時(shí)就不太合適了備份慢、恢復(fù)更慢而且邏輯備份恢復(fù)時(shí)重放SQL耗時(shí)很長(zhǎng)。這時(shí)候我直接用物理備份工具做全量增量備份#!/bin/bash # 全量物理備份腳本 BACKUP_BASE/data/backup/xtra DATE$(date %Y%m%d) FULL_DIR$BACKUP_BASE/full_$DATE xtrabackup \ --backup \ --target-dir$FULL_DIR \ --userbackup_user \ --passwordyour_password \ --parallel4 \ --compress # 備份完成后再做一步prepare保證備份集可用于恢復(fù) xtrabackup --prepare --target-dir$FULL_DIR很多人備份完直接拷走恢復(fù)的時(shí)候才發(fā)現(xiàn)xtrabackup --prepare這步?jīng)]做數(shù)據(jù)文件處于不一致?tīng)顟B(tài)根本起不來(lái)。--prepare的原理類(lèi)似InnoDB崩潰恢復(fù)把redo log回放到數(shù)據(jù)文件里這步必須執(zhí)行且prepare后的備份集不能再增量備份。物理備份的恢復(fù)動(dòng)作也比邏輯備份簡(jiǎn)單直接把備份集解壓拷回?cái)?shù)據(jù)目錄改好屬主權(quán)限啟動(dòng)實(shí)例即可。我實(shí)測(cè)過(guò)100GB的數(shù)據(jù)xtrabackup備份加恢復(fù)全程不到半小時(shí)mysqldump可能要折騰兩個(gè)小時(shí)以上。2.3 備份不等于高枕無(wú)憂(yōu)恢復(fù)演練腳本這是我最想強(qiáng)調(diào)的一點(diǎn)不做恢復(fù)演練的備份等于沒(méi)有備份。遇到過(guò)不止一次平時(shí)備份任務(wù)天天跑真到恢復(fù)那天發(fā)現(xiàn)備份文件損壞、參數(shù)不兼容、磁盤(pán)空間不夠全抓瞎。我現(xiàn)在的習(xí)慣是每月至少做一次完整的恢復(fù)演練腳本固定如下#!/bin/bash # 恢復(fù)到臨時(shí)實(shí)例并校驗(yàn)數(shù)據(jù)完整性 RESTORE_DIR/data/restore_test PORT3307 DATA_DIR$RESTORE_DIR/data # 1. 解壓最新全量備份 LATEST_BACKUP$(ls -t /data/backup/mysql/*.sql.gz | head -1) gunzip -c $LATEST_BACKUP $RESTORE_DIR/restore.sql # 2. 直接初始化一個(gè)新實(shí)例 mysqld --no-defaults \ --datadir$DATA_DIR \ --port$PORT \ --socket$RESTORE_DIR/mysql.sock \ --initialize-insecure # 3. 導(dǎo)入數(shù)據(jù) mysql --socket$RESTORE_DIR/mysql.sock -uroot $RESTORE_DIR/restore.sql # 4. 關(guān)鍵表行數(shù)校驗(yàn) mysql --socket$RESTORE_DIR/mysql.sock -uroot -e SELECT COUNT(*) FROM your_db.key_table;恢復(fù)演練不只是驗(yàn)證備份文件可用還能順便驗(yàn)證備份腳本里的參數(shù)變化是否兼容當(dāng)前版本。比如之前從MySQL 5.7升級(jí)到8.0后老的備份恢復(fù)腳本就因?yàn)閟ql_mode差異跑掛過(guò)演練第一個(gè)發(fā)現(xiàn)問(wèn)題。3. 巡檢與監(jiān)控腳本日常保健勝過(guò)臨時(shí)搶救3.1 連接與線(xiàn)程狀態(tài)實(shí)時(shí)巡檢數(shù)據(jù)庫(kù)出問(wèn)題十有八九先反應(yīng)在連接和線(xiàn)程上。我寫(xiě)了一個(gè)一鍵巡檢腳本每分鐘跑一次把異常情況直接打到日志里-- 檢查當(dāng)前連接數(shù)是否超過(guò)閾值這里是200 SHOW STATUS WHERE variable_name Threads_connected; -- 查看當(dāng)前所有非Sleep狀態(tài)的SQL SELECT id, user, host, db, command, time, state, LEFT(info, 120) AS sql_text FROM information_schema.processlist WHERE command ! Sleep ORDER BY time DESC; -- 查看處于鎖等待的會(huì)話(huà) SELECT * FROM information_schema.innodb_trx WHERE trx_state LOCK WAIT;information_schema.processlist這個(gè)表是排查問(wèn)題的第一現(xiàn)場(chǎng)?？吹絫ime特別長(zhǎng)的Query別急著KILL先看state字段如果是Sending data可能真的是大查詢(xún)?nèi)绻荳aiting for table metadata lock多半是DDL和DML打架了如果是Waiting for handler commit可能是磁盤(pán)刷盤(pán)太慢。有一次線(xiàn)上業(yè)務(wù)卡死我用這個(gè)腳本查出來(lái)一個(gè)time2400的ALTER TABLE在等元數(shù)據(jù)鎖后面堵了一串查詢(xún)。找到根源后把那個(gè)一直沒(méi)提交的長(zhǎng)事務(wù)KILL掉業(yè)務(wù)立刻恢復(fù)。沒(méi)有這個(gè)巡檢思路光看連接數(shù)飚高是定位不到根因的。3.2 慢查詢(xún)與錯(cuò)誤日志分析腳本慢查詢(xún)?nèi)罩疽欢ㄒ_(kāi)但開(kāi)了不分析等于白開(kāi)。我習(xí)慣把慢查詢(xún)?nèi)罩竞湾e(cuò)誤日志都統(tǒng)一到一個(gè)目錄然后用腳本做每日分析#!/bin/bash # 每日慢查詢(xún)統(tǒng)計(jì)輸出Top10耗時(shí)SQL SLOW_LOG/var/log/mysql/slow-query.log TODAY$(date %Y%m%d) # 統(tǒng)計(jì)當(dāng)天慢查詢(xún)總數(shù) TOTAL$(grep -c Time: $SLOW_LOG || true) echo $TODAY slow query total: $TOTAL # 提取慢查詢(xún)中的SQL文本統(tǒng)計(jì)出現(xiàn)頻率 grep -A2 Query_time $SLOW_LOG | \ grep ^SELECT\|^UPDATE\|^DELETE\|^INSERT | \ sort | uniq -c | sort -rn | head -20這個(gè)腳本的思路是先看量再看質(zhì)。慢查詢(xún)多不一定是壞事要看是否集中在少數(shù)幾條SQL上。如果每天都有一批SQL穩(wěn)定出現(xiàn)在慢查詢(xún)列表且執(zhí)行計(jì)劃沒(méi)變說(shuō)明SQL本身有問(wèn)題需要從索引和改寫(xiě)兩個(gè)方向處理。有個(gè)經(jīng)驗(yàn)分享慢查詢(xún)?nèi)罩纠锏腞ows_examined遠(yuǎn)比Query_time重要。Rows_examined幾十萬(wàn)但只返回幾行說(shuō)明掃描了大量數(shù)據(jù)索引沒(méi)走對(duì)或者索引選擇性差。優(yōu)化這類(lèi)SQL收益大且確定。3.3 磁盤(pán)與表空間水位監(jiān)控?cái)?shù)據(jù)庫(kù)的磁盤(pán)滿(mǎn)了是真的會(huì)寫(xiě)不進(jìn)去任何數(shù)據(jù)的而且一旦滿(mǎn)盤(pán)恢復(fù)起來(lái)極其痛苦。所以磁盤(pán)監(jiān)控我寧可多寫(xiě)幾個(gè)腳本也不愿意半夜被叫起來(lái)。#!/bin/bash # 磁盤(pán)與關(guān)鍵目錄監(jiān)控超過(guò)80%告警 THRESHOLD80 ALERT_FILE/data/monitor/disk_alert.log df -h | grep -E ^/dev/(sd|vd) | while read line; do USAGE$(echo $line | awk {print $5} | sed s/%//) MOUNT_POINT$(echo $line | awk {print $6}) if [ $USAGE -ge $THRESHOLD ]; then echo $(date %Y-%m-%d %H:%M:%S) WARN: $MOUNT_POINT usage $USAGE% $ALERT_FILE fi done # InnoDB表空間大小分布Top10 mysql -u monitor_user -p$PASSWORD -e SELECT table_schema, SUM(data_length index_length) / 1024 / 1024 / 1024 AS total_gb FROM information_schema.tables GROUP BY table_schema ORDER BY total_gb DESC; $ALERT_FILE告警閾值80%是我個(gè)人的習(xí)慣不一定適合所有人。如果業(yè)務(wù)有大量臨時(shí)排序或日志寫(xiě)入建議把閾值調(diào)到70%給臨時(shí)文件留出緩沖。數(shù)據(jù)目錄和日志目錄最好分開(kāi)監(jiān)控binlog目錄漲速快一天幾個(gè)GB很正常單獨(dú)看才不會(huì)忽略。另外提醒一個(gè)容易忽略的點(diǎn)監(jiān)控腳本本身也要做清理監(jiān)控日志文件無(wú)限增長(zhǎng)會(huì)把磁盤(pán)反過(guò)來(lái)寫(xiě)滿(mǎn)。logrotate配一下或者用find ... -mtime 30 -delete做輪轉(zhuǎn)。4. 性能優(yōu)化輔助腳本找到瓶頸、對(duì)癥下藥4.1 直擊性能瓶頸的SQL定位線(xiàn)上系統(tǒng)變慢最忌諱的就是憑感覺(jué)調(diào)參數(shù)。我的做法是先把壓在庫(kù)上的SQL揪出來(lái)分析執(zhí)行計(jì)劃再?zèng)Q定怎么改。下面這個(gè)腳本是我最常用的SQL分析三板斧-- 1. 開(kāi)啟執(zhí)行計(jì)劃分析 EXPLAIN ANALYZE SELECT order_id, user_id, amount FROM orders WHERE user_id 12345 AND status PAID AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 20;EXPLAIN ANALYZE是MySQL 8.0才有的它不只是給出執(zhí)行計(jì)劃還會(huì)真實(shí)執(zhí)行SQL并返回每一步的耗時(shí)和掃描行數(shù)。對(duì)于一條慢SQL先用它看瓶頸在哪一步是索引掃描行數(shù)太多還是排序耗時(shí)太長(zhǎng)還是回表次數(shù)太多。我遇到過(guò)一條SQL加了個(gè)索引之后從2秒提升到50毫秒以為是索引生效了但用EXPLAIN ANALYZE一查發(fā)現(xiàn)真正的問(wèn)題是ORDER BY create_time DESC觸發(fā)了filesort。后來(lái)把索引改成(user_id, status, create_time)讓索引天然有序徹底消除filesort才真正解決問(wèn)題。沒(méi)有EXPLAIN ANALYZE我只能看到加索引變快了看不到完整的優(yōu)化鏈路。4.2 大表碎片整理腳本InnoDB表在頻繁更新和刪除后會(huì)產(chǎn)生大量碎片。表現(xiàn)就是表的數(shù)據(jù)量沒(méi)怎么漲但物理文件越來(lái)越大全表掃描越來(lái)越慢。整理碎片的方法很直接-- 查看表碎片率 SELECT table_name, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(data_free / 1024 / 1024, 2) AS fragmented_mb, ROUND(data_free / data_length * 100, 2) AS fragment_pct FROM information_schema.tables WHERE table_schema your_db AND data_free 100 * 1024 * 1024 -- 只列出碎片超過(guò)100MB的表 ORDER BY fragment_pct DESC LIMIT 20;對(duì)于碎片率高的表執(zhí)行ALTER TABLE table_name ENGINEInnoDB即可重建表、回收碎片空間。但這里有幾個(gè)必須注意的坑這個(gè)操作會(huì)鎖表盡管MySQL 5.6是Online DDL但數(shù)據(jù)量巨大的表仍可能引發(fā)長(zhǎng)時(shí)間元數(shù)據(jù)鎖務(wù)必安排在維護(hù)窗口。整理碎片前先看磁盤(pán)剩余空間重建表需要約等于原表大小的額外空間。MySQL 8.0之后這個(gè)動(dòng)作可以用ALTER TABLE ... FORCE代替效果更明確。有個(gè)取巧的辦法如果表上有定期歸檔任務(wù)歸檔后碎片自然產(chǎn)生建議把碎片整理加入每月的自動(dòng)任務(wù)比一次性整理大表風(fēng)險(xiǎn)低得多。4.3 關(guān)鍵參數(shù)調(diào)優(yōu)自查腳本網(wǎng)上到處是MySQL優(yōu)化必須改這10個(gè)參數(shù)的文章但盲目調(diào)參翻車(chē)的案例我見(jiàn)得太多了。參數(shù)調(diào)優(yōu)最靠譜的依據(jù)是自己的監(jiān)控?cái)?shù)據(jù)我寫(xiě)了一個(gè)自查腳本輸出當(dāng)前實(shí)際運(yùn)行情況對(duì)應(yīng)的參數(shù)建議-- 查看緩存命中率的真實(shí)數(shù)據(jù) SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%; SHOW GLOBAL STATUS LIKE Innodb_row_lock_current_waits; SHOW GLOBAL STATUS LIKE Innodb_row_lock_time; -- 查看當(dāng)前實(shí)際生效的參數(shù)值 SHOW VARIABLES LIKE innodb_buffer_pool_size; SHOW VARIABLES LIKE innodb_flush_log_at_trx_commit; SHOW VARIABLES LIKE max_connections; SHOW VARIABLES LIKE transaction_isolation;這里我最想展開(kāi)講的是innodb_flush_log_at_trx_commit。這個(gè)參數(shù)只有三個(gè)取值0、1、2很多優(yōu)化文章建議改成0或2來(lái)提升性能但代價(jià)是事務(wù)提交時(shí)可能丟失最多1秒的日志這在金融、訂單等場(chǎng)景是絕對(duì)不可接受的。我的原則是核心業(yè)務(wù)必須保持1非核心報(bào)表庫(kù)可以考慮20絕不推薦。調(diào)參之前先問(wèn)自己三個(gè)問(wèn)題改這個(gè)參數(shù)要解決的問(wèn)題是什么改完如何驗(yàn)證確實(shí)解決了回滾方案是什么答不上來(lái)任何一個(gè)就不要?jiǎng)印?. 常見(jiàn)問(wèn)題與排查技巧實(shí)錄5.1 連接數(shù)打滿(mǎn)排查現(xiàn)象Threads_connected達(dá)到max_connections上限新連接直接報(bào)Too many connections。排查順序先看Threads_running如果這個(gè)值不高說(shuō)明大量連接是Sleep狀態(tài)應(yīng)用連接池沒(méi)釋放。查information_schema.processlist看Sleep連接來(lái)自哪些host定位是哪個(gè)應(yīng)用?？磻?yīng)用連接池配置maxPoolSize是不是設(shè)置得過(guò)大、idleTimeout是不是沒(méi)生效。如果是突發(fā)流量導(dǎo)致臨時(shí)調(diào)大max_connections能救急但根治要看應(yīng)用側(cè)連接復(fù)用。我以前遇到過(guò)一個(gè)案例某服務(wù)每執(zhí)行一次查詢(xún)就新建連接用完不歸還連接池形同虛設(shè)。排查完發(fā)現(xiàn)是應(yīng)用框架的配置項(xiàng)被誤改了maximumPoolSize寫(xiě)成了1000而數(shù)據(jù)庫(kù)max_connections才500必然打滿(mǎn)。腳本只是定位工具根因分析還得靠對(duì)業(yè)務(wù)側(cè)的理解。5.2 鎖等待問(wèn)題處理現(xiàn)象業(yè)務(wù)報(bào)錯(cuò)里出現(xiàn)Lock wait timeout exceeded或者Deadlock found。處理思路-- 查看當(dāng)前所有事務(wù) SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx; -- 查看鎖等待關(guān)系 SELECT waiting_trx_id, waiting_thread, waiting_query, blocking_trx_id, blocking_thread, blocking_query FROM sys.innodb_lock_waits;sys.innodb_lock_waits是MySQL 5.7及以上版本提供的視圖直接列出誰(shuí)在等鎖、誰(shuí)持有鎖。優(yōu)先處理持有鎖的那個(gè)長(zhǎng)事務(wù)如果業(yè)務(wù)上允許直接把它KILL掉。但KILL是治標(biāo)根源通常在兩條事務(wù)沒(méi)及時(shí)提交應(yīng)用層忘記commit、或者事務(wù)范圍過(guò)大一個(gè)事務(wù)里做了太多查詢(xún)和計(jì)算。死鎖的話(huà)MySQL會(huì)自動(dòng)回滾其中一個(gè)事務(wù)應(yīng)用側(cè)要有重試機(jī)制。這個(gè)事情別依賴(lài)數(shù)據(jù)庫(kù)幫你想辦法應(yīng)用層做重試才是正解。5.3 主從延遲排查主從延遲是另一種常見(jiàn)的數(shù)據(jù)庫(kù)沒(méi)事但業(yè)務(wù)很慢的場(chǎng)景。查主從狀態(tài)用下面的腳本SHOW SLAVE STATUS\G -- 關(guān)鍵字段 -- Seconds_Behind_Master: 從庫(kù)落后主庫(kù)的秒數(shù) -- Slave_IO_Running: IO線(xiàn)程是否正常必須是Yes -- Slave_SQL_Running: SQL線(xiàn)程是否正常必須是Yes -- Last_IO_Error / Last_SQL_Error: 有值必查這是報(bào)錯(cuò)現(xiàn)場(chǎng)Seconds_Behind_Master為0不代表沒(méi)延遲它只是估算值主庫(kù)大事務(wù)執(zhí)行期間這個(gè)值可能顯示0因?yàn)閺膸?kù)根本沒(méi)追上主庫(kù)當(dāng)前的binlog位置。更可靠的判斷標(biāo)準(zhǔn)是對(duì)比主從的Master_Log_File/Read_Master_Log_Pos與從庫(kù)執(zhí)行到的Relay_Master_Log_File/Exec_Master_Log_Pos。主從延遲最常見(jiàn)的原因按優(yōu)先級(jí)排列大事務(wù)一條SQL更新幾百萬(wàn)行、從庫(kù)硬件性能弱、從庫(kù)上跑著分析查詢(xún)搶資源、DDL在從庫(kù)被打斷。解決方向也很明確把大事務(wù)拆小、控制從庫(kù)上的分析負(fù)載、保障主從硬件規(guī)格一致。5.4 快速定位性能拐點(diǎn)最后一個(gè)腳本也是我調(diào)優(yōu)時(shí)候最重要的參考依據(jù)通過(guò)performance_schema拿到真實(shí)的等待事件分布。-- 等待事件Top10MySQL 5.7 SELECT EVENT_NAME, COUNT_STAR, ROUND(SUM_TIMER_WAIT / 1000000000, 2) AS wait_seconds FROM performance_schema.events_waits_summary_global_by_event_name WHERE SUM_TIMER_WAIT 0 ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;看到wait_seconds最大的等待事件基本就鎖定了瓶頸所在。io/sql/file類(lèi)等待大說(shuō)明磁盤(pán)I/O扛不住wait/synch/mutex類(lèi)等待大說(shuō)明并發(fā)爭(zhēng)用嚴(yán)重可能需要拆分熱點(diǎn)或升級(jí)配置idle類(lèi)占比高說(shuō)明數(shù)據(jù)庫(kù)很閑問(wèn)題大概率在應(yīng)用側(cè)。這個(gè)腳本的價(jià)值在于把感覺(jué)慢變成數(shù)據(jù)說(shuō)話(huà)。我優(yōu)化完某個(gè)參數(shù)或SQL之后都會(huì)跑一次這個(gè)查詢(xún)做前后對(duì)比用數(shù)據(jù)確認(rèn)優(yōu)化是否真的有效而不是感覺(jué)似乎快了一點(diǎn)。6. 數(shù)據(jù)訂正與批量操作腳本小心駛得萬(wàn)年船6.1 大批量數(shù)據(jù)更新的安全寫(xiě)法日常工作中總有改數(shù)據(jù)的需求比如訂正一條狀態(tài)、批量覆蓋一個(gè)字段。這類(lèi)操作看著簡(jiǎn)單翻車(chē)概率卻極高尤其是幾百萬(wàn)行的大表直接UPDATE鎖表能把整個(gè)業(yè)務(wù)拖死。我現(xiàn)場(chǎng)更的每條大語(yǔ)句都遵循同一個(gè)模式-- 分批更新每批1000條循環(huán)執(zhí)行 DELIMITER $$ CREATE PROCEDURE batch_update() BEGIN DECLARE v_count INT DEFAULT 1; WHILE v_count 0 DO UPDATE orders SET status ARCHIVED WHERE status OLD AND id IN ( SELECT id FROM ( SELECT id FROM orders WHERE status OLD LIMIT 1000 ) tmp ); SET v_count ROW_COUNT(); -- 每批之間歇一下給其他事務(wù)留機(jī)會(huì) DO SLEEP(0.1); END WHILE; END$$ DELIMITER ; CALL batch_update();外層套一層SELECT id FROM (...)子查詢(xún)是因?yàn)镸ySQL不允許在UPDATE的子查詢(xún)里直接查同一張表。SLEEP(0.1)是為了讓出部分鎖資源避免長(zhǎng)事務(wù)囤積大量行鎖。這種方式雖然慢但安全度完全不同實(shí)測(cè)200萬(wàn)行也就跑十幾分鐘比起鎖死核心表讓整個(gè)業(yè)務(wù)癱瘓這點(diǎn)時(shí)間完全值得。6.2 誤更新后的快速恢復(fù)方案批量更新的另一面是萬(wàn)一條件寫(xiě)錯(cuò)了怎么辦。我給自己留的后手是定期維護(hù)一份關(guān)鍵表的變更前快照-- 大操作前先備份目標(biāo)表到備份表 CREATE TABLE orders_backup_20240101 AS SELECT * FROM orders; -- 如果操作出錯(cuò)用備份表恢復(fù) -- 先刪掉錯(cuò)誤數(shù)據(jù)再按主鍵從備份表恢復(fù) DELETE FROM orders; INSERT INTO orders SELECT * FROM orders_backup_20240101;這個(gè)方案雖然簡(jiǎn)單粗暴但在關(guān)鍵時(shí)刻能救命。注意幾點(diǎn)備份表本身也占空間大表要確認(rèn)磁盤(pán)夠用恢復(fù)是物理復(fù)制業(yè)務(wù)側(cè)如果在這期間有新寫(xiě)入會(huì)丟失這部分?jǐn)?shù)據(jù)所以誤操作發(fā)生后第一時(shí)間要停止業(yè)務(wù)寫(xiě)入再執(zhí)行恢復(fù)。其實(shí)最理想的方案是開(kāi)啟binlog并用binlog2sql這類(lèi)工具做行級(jí)回滾但這類(lèi)工具的部署和學(xué)習(xí)成本較高。如果團(tuán)隊(duì)剛起步備份表方案先頂上,后續(xù)再逐步完善。6.3 主鍵沖突與唯一鍵沖突處理批量導(dǎo)入數(shù)據(jù)時(shí)最容易碰到的就是主鍵或唯一鍵沖突。常見(jiàn)場(chǎng)景從A庫(kù)導(dǎo)入B庫(kù)、重新跑批任務(wù)、數(shù)據(jù)回刷。我通常用INSERT ... ON DUPLICATE KEY UPDATE來(lái)平滑處理INSERT INTO user_profile (user_id, nickname, level, update_time) VALUES (10001, 張三, 5, NOW()) ON DUPLICATE KEY UPDATE nickname VALUES(nickname), level VALUES(level), update_time NOW();這個(gè)寫(xiě)法的含義是有沖突就更新沒(méi)沖突就插入。用起來(lái)要格外小心它會(huì)覆蓋目標(biāo)記錄的全部字段如果只想更新部分字段就只列出那幾列。還有一個(gè)容易被忽視的點(diǎn)這個(gè)語(yǔ)句對(duì)唯一鍵沖突同樣生效不一定非主鍵不可。如果是純導(dǎo)入場(chǎng)景不想覆蓋原有數(shù)據(jù)那就用INSERT IGNORE遇到?jīng)_突直接跳過(guò)。兩種寫(xiě)法差異很大先想清楚業(yè)務(wù)意圖再選我見(jiàn)過(guò)因?yàn)橛缅e(cuò)導(dǎo)致數(shù)據(jù)被覆蓋寫(xiě)壞的教訓(xùn)很深刻。7. 腳本管理的工程化實(shí)踐腳本寫(xiě)得再好沒(méi)有一套管理規(guī)范時(shí)間一長(zhǎng)就變成一團(tuán)亂麻。我自己是從下面幾件事里受益最多的分享出來(lái)。版本管理。數(shù)據(jù)庫(kù)腳本也是代碼必須進(jìn)版本庫(kù)。有些公司把SQL腳本扔在共享文件夾里版本靠文件名標(biāo)注v2_final_最終版這種管理方式遲早出事。我的習(xí)慣是每個(gè)數(shù)據(jù)庫(kù)變更腳本按日期和序號(hào)命名比如20240115_01_add_index_orders.sql提交到統(tǒng)一的代碼倉(cāng)庫(kù)和代碼一起評(píng)審、一起發(fā)布。命名規(guī)范。腳本命名建議包含三個(gè)要素操作對(duì)象、操作類(lèi)型、日期。db_backup_20240115.sh比backup.sh好在哪一年后你翻出來(lái)不用打開(kāi)文件就知道這個(gè)腳本是干什么的、什么時(shí)候?qū)懙?、針?duì)哪個(gè)庫(kù)的。敏感信息處理。腳本里直接寫(xiě)數(shù)據(jù)庫(kù)密碼是大忌。我現(xiàn)在的基本做法是腳本從環(huán)境變量讀取密碼環(huán)境變量由部署平臺(tái)注入代碼庫(kù)里只保留模板。雖然多了一道工序但密碼泄露的風(fēng)險(xiǎn)大幅降低。執(zhí)行前評(píng)審。涉及生產(chǎn)庫(kù)的DDL和不常見(jiàn)DML無(wú)論多緊急都要先走評(píng)審。哪怕是DBA自己執(zhí)行的腳本也先在測(cè)試環(huán)境完整跑一遍確認(rèn)影響行數(shù)在預(yù)期范圍內(nèi)再上生產(chǎn)。這個(gè)習(xí)慣幫我擋掉了至少三次足以釀成大事故的操作。我個(gè)人還有一個(gè)小習(xí)慣每個(gè)核心腳本開(kāi)頭都用注釋寫(xiě)清楚這腳本什么時(shí)候用、怎么用、踩過(guò)哪些坑。這個(gè)習(xí)慣救過(guò)我很多次。半年后你面對(duì)一個(gè)陌生或者陌生的腳本注釋里那三行字比任何文檔都管用。有一次團(tuán)隊(duì)里一位同事半夜調(diào)備份恢復(fù)正是靠著腳本頭部注釋里的注意該腳本依賴(lài)/data/backup/.env文件存在快速定位了問(wèn)題少走了不少?gòu)澛?。做?shù)據(jù)庫(kù)運(yùn)維這些年最大的體會(huì)是所謂常用腳本不是一份到處都能抄的標(biāo)準(zhǔn)答案而是你在一次次故障處理、一次次數(shù)據(jù)恢復(fù)、一次次深夜值班中沉淀下來(lái)的自己的答案。今天的這些腳本是我實(shí)踐中的一部分積累希望能給你的腳本庫(kù)帶來(lái)一些靈感。你在實(shí)際工作里積累了什么好用的腳本或者踩過(guò)什么獨(dú)特的坑歡迎一起聊聊也許你踩過(guò)的那個(gè)坑正好是我下一個(gè)腳本要解決的問(wèn)題。 數(shù)據(jù)庫(kù)運(yùn)維常用腳本實(shí)戰(zhàn)：從備份恢復(fù)到性能優(yōu)化
入職這些年我發(fā)現(xiàn)自己干得最多的活不是寫(xiě)業(yè)務(wù)代碼也不是搭架構(gòu)而是跟一堆數(shù)據(jù)庫(kù)常用腳本打交道。無(wú)論是幫業(yè)務(wù)部門(mén)查一條慢SQL還是半夜被叫起來(lái)看主從延遲手里沒(méi)幾個(gè)趁手的腳本真就寸步難行。今天這篇不整虛的把我實(shí)際工作中反復(fù)在用、踩過(guò)坑之后修正過(guò)的腳本整理出來(lái)按場(chǎng)景分類(lèi)講清楚每個(gè)腳本為什么這么寫(xiě)、參數(shù)怎么調(diào)、遇到什么問(wèn)題怎么改都一并交代。適合剛接觸數(shù)據(jù)庫(kù)運(yùn)維的開(kāi)發(fā)者也適合想把手頭腳本整理得更規(guī)范的DBA。1. 信息查詢(xún)與元數(shù)據(jù)腳本先把家底盤(pán)清楚1.1 三分鐘摸清實(shí)例整體狀況接手一臺(tái)新的數(shù)據(jù)庫(kù)服務(wù)器第一件事不是急著優(yōu)化而是先搞清楚這臺(tái)實(shí)例到底什么配置、什么負(fù)載、什么水位。我習(xí)慣先跑一套體檢三連-- 查看實(shí)例版本、運(yùn)行時(shí)長(zhǎng)、字符集等基礎(chǔ)信息 SELECT VERSION() AS version, NOW() AS current_time, hostname AS host_name, port AS port, character_set_server AS charset, collation_server AS collation; -- 查看關(guān)鍵運(yùn)行狀態(tài)值重點(diǎn)看連接數(shù)和慢查詢(xún) SHOW GLOBAL STATUS LIKE Threads_connected; SHOW GLOBAL STATUS LIKE Threads_running; SHOW GLOBAL STATUS LIKE Slow_queries; SHOW GLOBAL STATUS LIKE Uptime; -- 查看InnoDB buffer pool命中率相關(guān)指標(biāo) SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads;這幾個(gè)腳本看著簡(jiǎn)單但信息密度很高。版本和端口就不說(shuō)了Threads_connected和Threads_running這兩個(gè)值一定要配合看前者代表當(dāng)前建立的連接數(shù)后者代表真正在執(zhí)行SQL的線(xiàn)程數(shù)。如果連接數(shù)飆到幾百但Threads_running一直不高說(shuō)明大部分連接是閑置連接問(wèn)題多半出在應(yīng)用側(cè)連接池配置上而不是數(shù)據(jù)庫(kù)本身。InnoDB buffer pool命中率有個(gè)經(jīng)驗(yàn)公式1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)這個(gè)值常年低于99%說(shuō)明內(nèi)存配置或查詢(xún)模式有問(wèn)題光靠加內(nèi)存未必管用可能得從SQL層面找原因。1.2 快速定位大表與膨脹空間剛接手一個(gè)庫(kù)業(yè)務(wù)方問(wèn)我哪些表最大、哪些表膨脹得厲害我從來(lái)不看information_schema因?yàn)槟莻€(gè)統(tǒng)計(jì)信息在數(shù)據(jù)量大的時(shí)候本身就不準(zhǔn)掃描也慢。直接用系統(tǒng)表查快得多-- 查看指定庫(kù)中各表的行數(shù)、數(shù)據(jù)大小、索引大小MySQL 8.0 SELECT table_name, table_rows, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(index_length / 1024 / 1024, 2) AS index_mb, ROUND((data_length index_length) / 1024 / 1024, 2) AS total_mb FROM information_schema.tables WHERE table_schema your_db ORDER BY total_mb DESC LIMIT 20;這里有個(gè)細(xì)節(jié)必須提醒table_rows是估算值InnoDB引擎下誤差可能很大尤其頻繁增刪的表。真要精確行數(shù)還是得SELECT COUNT(*)但大表別在生產(chǎn)上直接跑低峰期再操作。另外我還會(huì)加一個(gè)查詢(xún)看哪些表的數(shù)據(jù)量和索引量比例失調(diào)。曾遇到一張表數(shù)據(jù)才200MB索引卻占了1.5GB查下來(lái)是建了一堆冗余的聯(lián)合索引刪掉三個(gè)索引后空間直接釋放寫(xiě)入性能也提升了。索引不是越多越好這個(gè)后面專(zhuān)門(mén)講。1.3 找出從未被使用的索引索引是有維護(hù)成本的每次寫(xiě)入都要更新索引結(jié)構(gòu)。很多系統(tǒng)上線(xiàn)久了當(dāng)初拍腦袋建的索引根本沒(méi)人用白白拖累寫(xiě)入性能。查索引使用情況MySQL有現(xiàn)成的統(tǒng)計(jì)表-- 查看索引使用統(tǒng)計(jì)需開(kāi)啟performance_schemaMySQL 5.7 SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR, COUNT_READ, COUNT_WRITE FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA your_db AND INDEX_NAME IS NOT NULL ORDER BY COUNT_READ ASC LIMIT 20;COUNT_READ長(zhǎng)期為0的索引基本就是死索引。但刪之前務(wù)必確認(rèn)兩點(diǎn)第一查詢(xún)計(jì)劃里是否真的沒(méi)用到有些統(tǒng)計(jì)值低但偶爾高峰用到的索引不能動(dòng)第二有沒(méi)有唯一性約束依賴(lài)這個(gè)索引。我習(xí)慣把候選索引先標(biāo)記為INVISIBLEMySQL 8.0支持觀察一周確認(rèn)沒(méi)有報(bào)錯(cuò)再真正DROP這個(gè)操作可以說(shuō)非常穩(wěn)妥。2. 備份與恢復(fù)腳本平時(shí)用不上出事保命2.1 邏輯備份腳本的正確姿勢(shì)說(shuō)到備份很多人第一反應(yīng)就是mysqldump一把梭。但我見(jiàn)過(guò)太多因?yàn)閰?shù)沒(méi)加對(duì)導(dǎo)致備份恢復(fù)失敗的案例。我最常用的一套邏輯備份腳本長(zhǎng)這樣#!/bin/bash # 按庫(kù)備份保留最近7天帶時(shí)間戳 BACKUP_DIR/data/backup/mysql DATE$(date %Y%m%d_%H%M%S) DB_USERbackup_user DB_PASSyour_password DB_LISTdb1 db2 db3 for DB in $DB_LIST; do mysqldump \ --single-transaction \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ -u$DB_USER -p$DB_PASS $DB \ | gzip $BACKUP_DIR/${DB}_${DATE}.sql.gz done # 清理7天前的備份 find $BACKUP_DIR -name *.sql.gz -mtime 7 -delete這幾個(gè)參數(shù)每個(gè)都有講究--single-transaction不加這個(gè)InnoDB表備份時(shí)會(huì)對(duì)全表加鎖線(xiàn)上業(yè)務(wù)直接卡住。加上后基于MVCC一致性快照不鎖表。--routines --triggers --events不加這三個(gè)存儲(chǔ)過(guò)程、觸發(fā)器、事件調(diào)度器全部丟失。很多人恢復(fù)后發(fā)現(xiàn)跑批任務(wù)不見(jiàn)了就是栽在這。--set-gtid-purgedOFFGTID模式下的老坑。不關(guān)掉這個(gè)問(wèn)題備份文件里帶著GTID信息恢復(fù)到新實(shí)例時(shí)會(huì)因?yàn)槭聞?wù)ID沖突直接報(bào)錯(cuò)。注意--single-transaction對(duì)MyISAM表無(wú)效MyISAM依舊會(huì)鎖表。如果庫(kù)里還有MyISAM表建議要么遷移到InnoDB要么備份安排在業(yè)務(wù)低谷期執(zhí)行。2.2 更穩(wěn)的物理備份xtrabackupmysqldump在數(shù)據(jù)量超過(guò)幾十GB時(shí)就不太合適了備份慢、恢復(fù)更慢而且邏輯備份恢復(fù)時(shí)重放SQL耗時(shí)很長(zhǎng)。這時(shí)候我直接用物理備份工具做全量增量備份#!/bin/bash # 全量物理備份腳本 BACKUP_BASE/data/backup/xtra DATE$(date %Y%m%d) FULL_DIR$BACKUP_BASE/full_$DATE xtrabackup \ --backup \ --target-dir$FULL_DIR \ --userbackup_user \ --passwordyour_password \ --parallel4 \ --compress # 備份完成后再做一步prepare保證備份集可用于恢復(fù) xtrabackup --prepare --target-dir$FULL_DIR很多人備份完直接拷走恢復(fù)的時(shí)候才發(fā)現(xiàn)xtrabackup --prepare這步?jīng)]做數(shù)據(jù)文件處于不一致?tīng)顟B(tài)根本起不來(lái)。--prepare的原理類(lèi)似InnoDB崩潰恢復(fù)把redo log回放到數(shù)據(jù)文件里這步必須執(zhí)行且prepare后的備份集不能再增量備份。物理備份的恢復(fù)動(dòng)作也比邏輯備份簡(jiǎn)單直接把備份集解壓拷回?cái)?shù)據(jù)目錄改好屬主權(quán)限啟動(dòng)實(shí)例即可。我實(shí)測(cè)過(guò)100GB的數(shù)據(jù)xtrabackup備份加恢復(fù)全程不到半小時(shí)mysqldump可能要折騰兩個(gè)小時(shí)以上。2.3 備份不等于高枕無(wú)憂(yōu)恢復(fù)演練腳本這是我最想強(qiáng)調(diào)的一點(diǎn)不做恢復(fù)演練的備份等于沒(méi)有備份。遇到過(guò)不止一次平時(shí)備份任務(wù)天天跑真到恢復(fù)那天發(fā)現(xiàn)備份文件損壞、參數(shù)不兼容、磁盤(pán)空間不夠全抓瞎。我現(xiàn)在的習(xí)慣是每月至少做一次完整的恢復(fù)演練腳本固定如下#!/bin/bash # 恢復(fù)到臨時(shí)實(shí)例并校驗(yàn)數(shù)據(jù)完整性 RESTORE_DIR/data/restore_test PORT3307 DATA_DIR$RESTORE_DIR/data # 1. 解壓最新全量備份 LATEST_BACKUP$(ls -t /data/backup/mysql/*.sql.gz | head -1) gunzip -c $LATEST_BACKUP $RESTORE_DIR/restore.sql # 2. 直接初始化一個(gè)新實(shí)例 mysqld --no-defaults \ --datadir$DATA_DIR \ --port$PORT \ --socket$RESTORE_DIR/mysql.sock \ --initialize-insecure # 3. 導(dǎo)入數(shù)據(jù) mysql --socket$RESTORE_DIR/mysql.sock -uroot $RESTORE_DIR/restore.sql # 4. 關(guān)鍵表行數(shù)校驗(yàn) mysql --socket$RESTORE_DIR/mysql.sock -uroot -e SELECT COUNT(*) FROM your_db.key_table;恢復(fù)演練不只是驗(yàn)證備份文件可用還能順便驗(yàn)證備份腳本里的參數(shù)變化是否兼容當(dāng)前版本。比如之前從MySQL 5.7升級(jí)到8.0后老的備份恢復(fù)腳本就因?yàn)閟ql_mode差異跑掛過(guò)演練第一個(gè)發(fā)現(xiàn)問(wèn)題。3. 巡檢與監(jiān)控腳本日常保健勝過(guò)臨時(shí)搶救3.1 連接與線(xiàn)程狀態(tài)實(shí)時(shí)巡檢數(shù)據(jù)庫(kù)出問(wèn)題十有八九先反應(yīng)在連接和線(xiàn)程上。我寫(xiě)了一個(gè)一鍵巡檢腳本每分鐘跑一次把異常情況直接打到日志里-- 檢查當(dāng)前連接數(shù)是否超過(guò)閾值這里是200 SHOW STATUS WHERE variable_name Threads_connected; -- 查看當(dāng)前所有非Sleep狀態(tài)的SQL SELECT id, user, host, db, command, time, state, LEFT(info, 120) AS sql_text FROM information_schema.processlist WHERE command ! Sleep ORDER BY time DESC; -- 查看處于鎖等待的會(huì)話(huà) SELECT * FROM information_schema.innodb_trx WHERE trx_state LOCK WAIT;information_schema.processlist這個(gè)表是排查問(wèn)題的第一現(xiàn)場(chǎng)?？吹絫ime特別長(zhǎng)的Query別急著KILL先看state字段如果是Sending data可能真的是大查詢(xún)?nèi)绻荳aiting for table metadata lock多半是DDL和DML打架了如果是Waiting for handler commit可能是磁盤(pán)刷盤(pán)太慢。有一次線(xiàn)上業(yè)務(wù)卡死我用這個(gè)腳本查出來(lái)一個(gè)time2400的ALTER TABLE在等元數(shù)據(jù)鎖后面堵了一串查詢(xún)。找到根源后把那個(gè)一直沒(méi)提交的長(zhǎng)事務(wù)KILL掉業(yè)務(wù)立刻恢復(fù)。沒(méi)有這個(gè)巡檢思路光看連接數(shù)飚高是定位不到根因的。3.2 慢查詢(xún)與錯(cuò)誤日志分析腳本慢查詢(xún)?nèi)罩疽欢ㄒ_(kāi)但開(kāi)了不分析等于白開(kāi)。我習(xí)慣把慢查詢(xún)?nèi)罩竞湾e(cuò)誤日志都統(tǒng)一到一個(gè)目錄然后用腳本做每日分析#!/bin/bash # 每日慢查詢(xún)統(tǒng)計(jì)輸出Top10耗時(shí)SQL SLOW_LOG/var/log/mysql/slow-query.log TODAY$(date %Y%m%d) # 統(tǒng)計(jì)當(dāng)天慢查詢(xún)總數(shù) TOTAL$(grep -c Time: $SLOW_LOG || true) echo $TODAY slow query total: $TOTAL # 提取慢查詢(xún)中的SQL文本統(tǒng)計(jì)出現(xiàn)頻率 grep -A2 Query_time $SLOW_LOG | \ grep ^SELECT\|^UPDATE\|^DELETE\|^INSERT | \ sort | uniq -c | sort -rn | head -20這個(gè)腳本的思路是先看量再看質(zhì)。慢查詢(xún)多不一定是壞事要看是否集中在少數(shù)幾條SQL上。如果每天都有一批SQL穩(wěn)定出現(xiàn)在慢查詢(xún)列表且執(zhí)行計(jì)劃沒(méi)變說(shuō)明SQL本身有問(wèn)題需要從索引和改寫(xiě)兩個(gè)方向處理。有個(gè)經(jīng)驗(yàn)分享慢查詢(xún)?nèi)罩纠锏腞ows_examined遠(yuǎn)比Query_time重要。Rows_examined幾十萬(wàn)但只返回幾行說(shuō)明掃描了大量數(shù)據(jù)索引沒(méi)走對(duì)或者索引選擇性差。優(yōu)化這類(lèi)SQL收益大且確定。3.3 磁盤(pán)與表空間水位監(jiān)控?cái)?shù)據(jù)庫(kù)的磁盤(pán)滿(mǎn)了是真的會(huì)寫(xiě)不進(jìn)去任何數(shù)據(jù)的而且一旦滿(mǎn)盤(pán)恢復(fù)起來(lái)極其痛苦。所以磁盤(pán)監(jiān)控我寧可多寫(xiě)幾個(gè)腳本也不愿意半夜被叫起來(lái)。#!/bin/bash # 磁盤(pán)與關(guān)鍵目錄監(jiān)控超過(guò)80%告警 THRESHOLD80 ALERT_FILE/data/monitor/disk_alert.log df -h | grep -E ^/dev/(sd|vd) | while read line; do USAGE$(echo $line | awk {print $5} | sed s/%//) MOUNT_POINT$(echo $line | awk {print $6}) if [ $USAGE -ge $THRESHOLD ]; then echo $(date %Y-%m-%d %H:%M:%S) WARN: $MOUNT_POINT usage $USAGE% $ALERT_FILE fi done # InnoDB表空間大小分布Top10 mysql -u monitor_user -p$PASSWORD -e SELECT table_schema, SUM(data_length index_length) / 1024 / 1024 / 1024 AS total_gb FROM information_schema.tables GROUP BY table_schema ORDER BY total_gb DESC; $ALERT_FILE告警閾值80%是我個(gè)人的習(xí)慣不一定適合所有人。如果業(yè)務(wù)有大量臨時(shí)排序或日志寫(xiě)入建議把閾值調(diào)到70%給臨時(shí)文件留出緩沖。數(shù)據(jù)目錄和日志目錄最好分開(kāi)監(jiān)控binlog目錄漲速快一天幾個(gè)GB很正常單獨(dú)看才不會(huì)忽略。另外提醒一個(gè)容易忽略的點(diǎn)監(jiān)控腳本本身也要做清理監(jiān)控日志文件無(wú)限增長(zhǎng)會(huì)把磁盤(pán)反過(guò)來(lái)寫(xiě)滿(mǎn)。logrotate配一下或者用find ... -mtime 30 -delete做輪轉(zhuǎn)。4. 性能優(yōu)化輔助腳本找到瓶頸、對(duì)癥下藥4.1 直擊性能瓶頸的SQL定位線(xiàn)上系統(tǒng)變慢最忌諱的就是憑感覺(jué)調(diào)參數(shù)。我的做法是先把壓在庫(kù)上的SQL揪出來(lái)分析執(zhí)行計(jì)劃再?zèng)Q定怎么改。下面這個(gè)腳本是我最常用的SQL分析三板斧-- 1. 開(kāi)啟執(zhí)行計(jì)劃分析 EXPLAIN ANALYZE SELECT order_id, user_id, amount FROM orders WHERE user_id 12345 AND status PAID AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 20;EXPLAIN ANALYZE是MySQL 8.0才有的它不只是給出執(zhí)行計(jì)劃還會(huì)真實(shí)執(zhí)行SQL并返回每一步的耗時(shí)和掃描行數(shù)。對(duì)于一條慢SQL先用它看瓶頸在哪一步是索引掃描行數(shù)太多還是排序耗時(shí)太長(zhǎng)還是回表次數(shù)太多。我遇到過(guò)一條SQL加了個(gè)索引之后從2秒提升到50毫秒以為是索引生效了但用EXPLAIN ANALYZE一查發(fā)現(xiàn)真正的問(wèn)題是ORDER BY create_time DESC觸發(fā)了filesort。后來(lái)把索引改成(user_id, status, create_time)讓索引天然有序徹底消除filesort才真正解決問(wèn)題。沒(méi)有EXPLAIN ANALYZE我只能看到加索引變快了看不到完整的優(yōu)化鏈路。4.2 大表碎片整理腳本InnoDB表在頻繁更新和刪除后會(huì)產(chǎn)生大量碎片。表現(xiàn)就是表的數(shù)據(jù)量沒(méi)怎么漲但物理文件越來(lái)越大全表掃描越來(lái)越慢。整理碎片的方法很直接-- 查看表碎片率 SELECT table_name, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(data_free / 1024 / 1024, 2) AS fragmented_mb, ROUND(data_free / data_length * 100, 2) AS fragment_pct FROM information_schema.tables WHERE table_schema your_db AND data_free 100 * 1024 * 1024 -- 只列出碎片超過(guò)100MB的表 ORDER BY fragment_pct DESC LIMIT 20;對(duì)于碎片率高的表執(zhí)行ALTER TABLE table_name ENGINEInnoDB即可重建表、回收碎片空間。但這里有幾個(gè)必須注意的坑這個(gè)操作會(huì)鎖表盡管MySQL 5.6是Online DDL但數(shù)據(jù)量巨大的表仍可能引發(fā)長(zhǎng)時(shí)間元數(shù)據(jù)鎖務(wù)必安排在維護(hù)窗口。整理碎片前先看磁盤(pán)剩余空間重建表需要約等于原表大小的額外空間。MySQL 8.0之后這個(gè)動(dòng)作可以用ALTER TABLE ... FORCE代替效果更明確。有個(gè)取巧的辦法如果表上有定期歸檔任務(wù)歸檔后碎片自然產(chǎn)生建議把碎片整理加入每月的自動(dòng)任務(wù)比一次性整理大表風(fēng)險(xiǎn)低得多。4.3 關(guān)鍵參數(shù)調(diào)優(yōu)自查腳本網(wǎng)上到處是MySQL優(yōu)化必須改這10個(gè)參數(shù)的文章但盲目調(diào)參翻車(chē)的案例我見(jiàn)得太多了。參數(shù)調(diào)優(yōu)最靠譜的依據(jù)是自己的監(jiān)控?cái)?shù)據(jù)我寫(xiě)了一個(gè)自查腳本輸出當(dāng)前實(shí)際運(yùn)行情況對(duì)應(yīng)的參數(shù)建議-- 查看緩存命中率的真實(shí)數(shù)據(jù) SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read%; SHOW GLOBAL STATUS LIKE Innodb_row_lock_current_waits; SHOW GLOBAL STATUS LIKE Innodb_row_lock_time; -- 查看當(dāng)前實(shí)際生效的參數(shù)值 SHOW VARIABLES LIKE innodb_buffer_pool_size; SHOW VARIABLES LIKE innodb_flush_log_at_trx_commit; SHOW VARIABLES LIKE max_connections; SHOW VARIABLES LIKE transaction_isolation;這里我最想展開(kāi)講的是innodb_flush_log_at_trx_commit。這個(gè)參數(shù)只有三個(gè)取值0、1、2很多優(yōu)化文章建議改成0或2來(lái)提升性能但代價(jià)是事務(wù)提交時(shí)可能丟失最多1秒的日志這在金融、訂單等場(chǎng)景是絕對(duì)不可接受的。我的原則是核心業(yè)務(wù)必須保持1非核心報(bào)表庫(kù)可以考慮20絕不推薦。調(diào)參之前先問(wèn)自己三個(gè)問(wèn)題改這個(gè)參數(shù)要解決的問(wèn)題是什么改完如何驗(yàn)證確實(shí)解決了回滾方案是什么答不上來(lái)任何一個(gè)就不要?jiǎng)印?. 常見(jiàn)問(wèn)題與排查技巧實(shí)錄5.1 連接數(shù)打滿(mǎn)排查現(xiàn)象Threads_connected達(dá)到max_connections上限新連接直接報(bào)Too many connections。排查順序先看Threads_running如果這個(gè)值不高說(shuō)明大量連接是Sleep狀態(tài)應(yīng)用連接池沒(méi)釋放。查information_schema.processlist看Sleep連接來(lái)自哪些host定位是哪個(gè)應(yīng)用?？磻?yīng)用連接池配置maxPoolSize是不是設(shè)置得過(guò)大、idleTimeout是不是沒(méi)生效。如果是突發(fā)流量導(dǎo)致臨時(shí)調(diào)大max_connections能救急但根治要看應(yīng)用側(cè)連接復(fù)用。我以前遇到過(guò)一個(gè)案例某服務(wù)每執(zhí)行一次查詢(xún)就新建連接用完不歸還連接池形同虛設(shè)。排查完發(fā)現(xiàn)是應(yīng)用框架的配置項(xiàng)被誤改了maximumPoolSize寫(xiě)成了1000而數(shù)據(jù)庫(kù)max_connections才500必然打滿(mǎn)。腳本只是定位工具根因分析還得靠對(duì)業(yè)務(wù)側(cè)的理解。5.2 鎖等待問(wèn)題處理現(xiàn)象業(yè)務(wù)報(bào)錯(cuò)里出現(xiàn)Lock wait timeout exceeded或者Deadlock found。處理思路-- 查看當(dāng)前所有事務(wù) SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx; -- 查看鎖等待關(guān)系 SELECT waiting_trx_id, waiting_thread, waiting_query, blocking_trx_id, blocking_thread, blocking_query FROM sys.innodb_lock_waits;sys.innodb_lock_waits是MySQL 5.7及以上版本提供的視圖直接列出誰(shuí)在等鎖、誰(shuí)持有鎖。優(yōu)先處理持有鎖的那個(gè)長(zhǎng)事務(wù)如果業(yè)務(wù)上允許直接把它KILL掉。但KILL是治標(biāo)根源通常在兩條事務(wù)沒(méi)及時(shí)提交應(yīng)用層忘記commit、或者事務(wù)范圍過(guò)大一個(gè)事務(wù)里做了太多查詢(xún)和計(jì)算。死鎖的話(huà)MySQL會(huì)自動(dòng)回滾其中一個(gè)事務(wù)應(yīng)用側(cè)要有重試機(jī)制。這個(gè)事情別依賴(lài)數(shù)據(jù)庫(kù)幫你想辦法應(yīng)用層做重試才是正解。5.3 主從延遲排查主從延遲是另一種常見(jiàn)的數(shù)據(jù)庫(kù)沒(méi)事但業(yè)務(wù)很慢的場(chǎng)景。查主從狀態(tài)用下面的腳本SHOW SLAVE STATUS\G -- 關(guān)鍵字段 -- Seconds_Behind_Master: 從庫(kù)落后主庫(kù)的秒數(shù) -- Slave_IO_Running: IO線(xiàn)程是否正常必須是Yes -- Slave_SQL_Running: SQL線(xiàn)程是否正常必須是Yes -- Last_IO_Error / Last_SQL_Error: 有值必查這是報(bào)錯(cuò)現(xiàn)場(chǎng)Seconds_Behind_Master為0不代表沒(méi)延遲它只是估算值主庫(kù)大事務(wù)執(zhí)行期間這個(gè)值可能顯示0因?yàn)閺膸?kù)根本沒(méi)追上主庫(kù)當(dāng)前的binlog位置。更可靠的判斷標(biāo)準(zhǔn)是對(duì)比主從的Master_Log_File/Read_Master_Log_Pos與從庫(kù)執(zhí)行到的Relay_Master_Log_File/Exec_Master_Log_Pos。主從延遲最常見(jiàn)的原因按優(yōu)先級(jí)排列大事務(wù)一條SQL更新幾百萬(wàn)行、從庫(kù)硬件性能弱、從庫(kù)上跑著分析查詢(xún)搶資源、DDL在從庫(kù)被打斷。解決方向也很明確把大事務(wù)拆小、控制從庫(kù)上的分析負(fù)載、保障主從硬件規(guī)格一致。5.4 快速定位性能拐點(diǎn)最后一個(gè)腳本也是我調(diào)優(yōu)時(shí)候最重要的參考依據(jù)通過(guò)performance_schema拿到真實(shí)的等待事件分布。-- 等待事件Top10MySQL 5.7 SELECT EVENT_NAME, COUNT_STAR, ROUND(SUM_TIMER_WAIT / 1000000000, 2) AS wait_seconds FROM performance_schema.events_waits_summary_global_by_event_name WHERE SUM_TIMER_WAIT 0 ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;看到wait_seconds最大的等待事件基本就鎖定了瓶頸所在。io/sql/file類(lèi)等待大說(shuō)明磁盤(pán)I/O扛不住wait/synch/mutex類(lèi)等待大說(shuō)明并發(fā)爭(zhēng)用嚴(yán)重可能需要拆分熱點(diǎn)或升級(jí)配置idle類(lèi)占比高說(shuō)明數(shù)據(jù)庫(kù)很閑問(wèn)題大概率在應(yīng)用側(cè)。這個(gè)腳本的價(jià)值在于把感覺(jué)慢變成數(shù)據(jù)說(shuō)話(huà)。我優(yōu)化完某個(gè)參數(shù)或SQL之后都會(huì)跑一次這個(gè)查詢(xún)做前后對(duì)比用數(shù)據(jù)確認(rèn)優(yōu)化是否真的有效而不是感覺(jué)似乎快了一點(diǎn)。6. 數(shù)據(jù)訂正與批量操作腳本小心駛得萬(wàn)年船6.1 大批量數(shù)據(jù)更新的安全寫(xiě)法日常工作中總有改數(shù)據(jù)的需求比如訂正一條狀態(tài)、批量覆蓋一個(gè)字段。這類(lèi)操作看著簡(jiǎn)單翻車(chē)概率卻極高尤其是幾百萬(wàn)行的大表直接UPDATE鎖表能把整個(gè)業(yè)務(wù)拖死。我現(xiàn)場(chǎng)更的每條大語(yǔ)句都遵循同一個(gè)模式-- 分批更新每批1000條循環(huán)執(zhí)行 DELIMITER $$ CREATE PROCEDURE batch_update() BEGIN DECLARE v_count INT DEFAULT 1; WHILE v_count 0 DO UPDATE orders SET status ARCHIVED WHERE status OLD AND id IN ( SELECT id FROM ( SELECT id FROM orders WHERE status OLD LIMIT 1000 ) tmp ); SET v_count ROW_COUNT(); -- 每批之間歇一下給其他事務(wù)留機(jī)會(huì) DO SLEEP(0.1); END WHILE; END$$ DELIMITER ; CALL batch_update();外層套一層SELECT id FROM (...)子查詢(xún)是因?yàn)镸ySQL不允許在UPDATE的子查詢(xún)里直接查同一張表。SLEEP(0.1)是為了讓出部分鎖資源避免長(zhǎng)事務(wù)囤積大量行鎖。這種方式雖然慢但安全度完全不同實(shí)測(cè)200萬(wàn)行也就跑十幾分鐘比起鎖死核心表讓整個(gè)業(yè)務(wù)癱瘓這點(diǎn)時(shí)間完全值得。6.2 誤更新后的快速恢復(fù)方案批量更新的另一面是萬(wàn)一條件寫(xiě)錯(cuò)了怎么辦。我給自己留的后手是定期維護(hù)一份關(guān)鍵表的變更前快照-- 大操作前先備份目標(biāo)表到備份表 CREATE TABLE orders_backup_20240101 AS SELECT * FROM orders; -- 如果操作出錯(cuò)用備份表恢復(fù) -- 先刪掉錯(cuò)誤數(shù)據(jù)再按主鍵從備份表恢復(fù) DELETE FROM orders; INSERT INTO orders SELECT * FROM orders_backup_20240101;這個(gè)方案雖然簡(jiǎn)單粗暴但在關(guān)鍵時(shí)刻能救命。注意幾點(diǎn)備份表本身也占空間大表要確認(rèn)磁盤(pán)夠用恢復(fù)是物理復(fù)制業(yè)務(wù)側(cè)如果在這期間有新寫(xiě)入會(huì)丟失這部分?jǐn)?shù)據(jù)所以誤操作發(fā)生后第一時(shí)間要停止業(yè)務(wù)寫(xiě)入再執(zhí)行恢復(fù)。其實(shí)最理想的方案是開(kāi)啟binlog并用binlog2sql這類(lèi)工具做行級(jí)回滾但這類(lèi)工具的部署和學(xué)習(xí)成本較高。如果團(tuán)隊(duì)剛起步備份表方案先頂上,后續(xù)再逐步完善。6.3 主鍵沖突與唯一鍵沖突處理批量導(dǎo)入數(shù)據(jù)時(shí)最容易碰到的就是主鍵或唯一鍵沖突。常見(jiàn)場(chǎng)景從A庫(kù)導(dǎo)入B庫(kù)、重新跑批任務(wù)、數(shù)據(jù)回刷。我通常用INSERT ... ON DUPLICATE KEY UPDATE來(lái)平滑處理INSERT INTO user_profile (user_id, nickname, level, update_time) VALUES (10001, 張三, 5, NOW()) ON DUPLICATE KEY UPDATE nickname VALUES(nickname), level VALUES(level), update_time NOW();這個(gè)寫(xiě)法的含義是有沖突就更新沒(méi)沖突就插入。用起來(lái)要格外小心它會(huì)覆蓋目標(biāo)記錄的全部字段如果只想更新部分字段就只列出那幾列。還有一個(gè)容易被忽視的點(diǎn)這個(gè)語(yǔ)句對(duì)唯一鍵沖突同樣生效不一定非主鍵不可。如果是純導(dǎo)入場(chǎng)景不想覆蓋原有數(shù)據(jù)那就用INSERT IGNORE遇到?jīng)_突直接跳過(guò)。兩種寫(xiě)法差異很大先想清楚業(yè)務(wù)意圖再選我見(jiàn)過(guò)因?yàn)橛缅e(cuò)導(dǎo)致數(shù)據(jù)被覆蓋寫(xiě)壞的教訓(xùn)很深刻。7. 腳本管理的工程化實(shí)踐腳本寫(xiě)得再好沒(méi)有一套管理規(guī)范時(shí)間一長(zhǎng)就變成一團(tuán)亂麻。我自己是從下面幾件事里受益最多的分享出來(lái)。版本管理。數(shù)據(jù)庫(kù)腳本也是代碼必須進(jìn)版本庫(kù)。有些公司把SQL腳本扔在共享文件夾里版本靠文件名標(biāo)注v2_final_最終版這種管理方式遲早出事。我的習(xí)慣是每個(gè)數(shù)據(jù)庫(kù)變更腳本按日期和序號(hào)命名比如20240115_01_add_index_orders.sql提交到統(tǒng)一的代碼倉(cāng)庫(kù)和代碼一起評(píng)審、一起發(fā)布。命名規(guī)范。腳本命名建議包含三個(gè)要素操作對(duì)象、操作類(lèi)型、日期。db_backup_20240115.sh比backup.sh好在哪一年后你翻出來(lái)不用打開(kāi)文件就知道這個(gè)腳本是干什么的、什么時(shí)候?qū)懙?、針?duì)哪個(gè)庫(kù)的。敏感信息處理。腳本里直接寫(xiě)數(shù)據(jù)庫(kù)密碼是大忌。我現(xiàn)在的基本做法是腳本從環(huán)境變量讀取密碼環(huán)境變量由部署平臺(tái)注入代碼庫(kù)里只保留模板。雖然多了一道工序但密碼泄露的風(fēng)險(xiǎn)大幅降低。執(zhí)行前評(píng)審。涉及生產(chǎn)庫(kù)的DDL和不常見(jiàn)DML無(wú)論多緊急都要先走評(píng)審。哪怕是DBA自己執(zhí)行的腳本也先在測(cè)試環(huán)境完整跑一遍確認(rèn)影響行數(shù)在預(yù)期范圍內(nèi)再上生產(chǎn)。這個(gè)習(xí)慣幫我擋掉了至少三次足以釀成大事故的操作。我個(gè)人還有一個(gè)小習(xí)慣每個(gè)核心腳本開(kāi)頭都用注釋寫(xiě)清楚這腳本什么時(shí)候用、怎么用、踩過(guò)哪些坑。這個(gè)習(xí)慣救過(guò)我很多次。半年后你面對(duì)一個(gè)陌生或者陌生的腳本注釋里那三行字比任何文檔都管用。有一次團(tuán)隊(duì)里一位同事半夜調(diào)備份恢復(fù)正是靠著腳本頭部注釋里的注意該腳本依賴(lài)/data/backup/.env文件存在快速定位了問(wèn)題少走了不少?gòu)澛?。做?shù)據(jù)庫(kù)運(yùn)維這些年最大的體會(huì)是所謂常用腳本不是一份到處都能抄的標(biāo)準(zhǔn)答案而是你在一次次故障處理、一次次數(shù)據(jù)恢復(fù)、一次次深夜值班中沉淀下來(lái)的自己的答案。今天的這些腳本是我實(shí)踐中的一部分積累希望能給你的腳本庫(kù)帶來(lái)一些靈感。你在實(shí)際工作里積累了什么好用的腳本或者踩過(guò)什么獨(dú)特的坑歡迎一起聊聊也許你踩過(guò)的那個(gè)坑正好是我下一個(gè)腳本要解決的問(wèn)題。