維必備：MariaDB命令實(shí)戰(zhàn)指南，快速定位數(shù)據(jù)庫(kù)問(wèn)題)
1. 從“能用”到“會(huì)管”為什么運(yùn)維必須懂MariaDB命令最近在幫一個(gè)朋友排查他們線上服務(wù)間歇性卡頓的問(wèn)題最后定位到數(shù)據(jù)庫(kù)上。登錄服務(wù)器一看mariadb進(jìn)程的CPU占用時(shí)不時(shí)就沖到100%但開發(fā)同學(xué)給的反饋是“SQL都優(yōu)化過(guò)了”。我習(xí)慣性地連上數(shù)據(jù)庫(kù)敲了幾個(gè)最基礎(chǔ)的命令比如SHOW PROCESSLIST;和SHOW GLOBAL STATUS LIKE ‘Threads_connected’;問(wèn)題立刻就清晰了應(yīng)用連接池配置不當(dāng)產(chǎn)生了大量空閑連接把數(shù)據(jù)庫(kù)的連接線程池耗盡了。這件事讓我再次感慨對(duì)于系統(tǒng)運(yùn)維而言掌握數(shù)據(jù)庫(kù)的基本命令絕不是“加分項(xiàng)”而是“保命項(xiàng)”。很多運(yùn)維工程師可能會(huì)覺(jué)得數(shù)據(jù)庫(kù)是DBA的領(lǐng)域我們只要保證服務(wù)端口通、進(jìn)程在、磁盤空間夠就行了。但在實(shí)際的生產(chǎn)環(huán)境中尤其是在中小團(tuán)隊(duì)或DevOps文化盛行的今天運(yùn)維的邊界早已模糊。當(dāng)凌晨三點(diǎn)收到告警“數(shù)據(jù)庫(kù)響應(yīng)超時(shí)”時(shí)你不可能每次都去搖醒DBA。你需要有能力第一時(shí)間登錄服務(wù)器用最直接的方式判斷是連接數(shù)爆了是鎖等待還是某個(gè)慢查詢拖垮了整臺(tái)機(jī)器這些判斷都依賴于對(duì)MariaDB或MySQL一系列基本命令的熟練運(yùn)用。MariaDB作為MySQL最流行的分支其命令體系與MySQL高度兼容是Linux服務(wù)器上最常見的開源關(guān)系型數(shù)據(jù)庫(kù)之一。無(wú)論是部署在CentOS、Ubuntu還是國(guó)產(chǎn)化的麒麟、統(tǒng)信UOS上其管理邏輯都是一致的。這篇文章我就從一個(gè)運(yùn)維的視角拋開復(fù)雜的SQL優(yōu)化和架構(gòu)設(shè)計(jì)聚焦于那些真正能幫你快速定位問(wèn)題、完成日常維護(hù)的MariaDB命令行操作。我們的目標(biāo)不是成為DBA而是成為一個(gè)在數(shù)據(jù)庫(kù)“生病”時(shí)能迅速做出初步診斷的“全科醫(yī)生”。2. 運(yùn)維第一課連接、狀態(tài)查看與基礎(chǔ)信息獲取所有深入的排查都始于一次成功的連接和對(duì)系統(tǒng)狀態(tài)的快速掃描。對(duì)于運(yùn)維來(lái)說(shuō)高效、安全地連接數(shù)據(jù)庫(kù)并獲取全局視圖是后續(xù)所有操作的基礎(chǔ)。2.1 不止于mysql -u root -p安全與靈活的連接姿勢(shì)教科書里教的mysql -u root -p當(dāng)然沒(méi)錯(cuò)但在生產(chǎn)環(huán)境我們需要考慮更多。1. 使用非root用戶與指定主機(jī)連接生產(chǎn)環(huán)境嚴(yán)禁長(zhǎng)期使用root賬戶進(jìn)行日常運(yùn)維。你應(yīng)該創(chuàng)建一個(gè)具有相應(yīng)權(quán)限的運(yùn)維專用賬戶。mysql -u ops_admin -h 127.0.0.1 -p這里-h指定了數(shù)據(jù)庫(kù)服務(wù)器地址。如果是本地Socket連接可以省略或使用-h localhost。使用具體IP而非主機(jī)名有時(shí)可以避免DNS解析帶來(lái)的問(wèn)題。輸入命令后在提示符下輸入密碼。為了不在命令行歷史中留下密碼痕跡不建議使用-pYourPassword的寫法。2. 通過(guò)Socket文件連接常見于本地當(dāng)MySQL/MariaDB服務(wù)與客戶端在同一臺(tái)機(jī)器時(shí)通過(guò)Unix Socket文件連接效率更高也省去了TCP/IP協(xié)議棧的開銷。你需要知道Socket文件的路徑通常在/var/lib/mysql/mysql.sock或/tmp/mysql.sock。mysql -u ops_admin -S /var/lib/mysql/mysql.sock -p3. 在腳本中自動(dòng)化連接對(duì)于監(jiān)控腳本或自動(dòng)化任務(wù)可以使用~/.my.cnf配置文件來(lái)避免在命令行中暴露密碼。 首先創(chuàng)建或編輯該文件權(quán)限必須設(shè)為600vi ~/.my.cnf內(nèi)容如下[client] userops_admin passwordYourSecurePassword host127.0.0.1保存后直接運(yùn)行mysql命令即可無(wú)密碼登錄。這是既安全又方便的做法。注意~/.my.cnf文件的權(quán)限至關(guān)重要。務(wù)必執(zhí)行chmod 600 ~/.my.cnf否則MariaDB會(huì)因安全原因拒絕使用該文件中的密碼。2.2 掌握系統(tǒng)狀態(tài)SHOW 命令家族詳解連接成功后我們來(lái)到了“運(yùn)維診斷室”。SHOW命令就是你的聽診器和血壓計(jì)。1. SHOW STATUS獲取性能指標(biāo)全景圖SHOW GLOBAL STATUS;會(huì)輸出數(shù)百個(gè)系統(tǒng)狀態(tài)變量。全看會(huì)眼花繚亂運(yùn)維需要關(guān)注幾個(gè)關(guān)鍵指標(biāo)連接相關(guān)SHOW GLOBAL STATUS LIKE Threads_%;Threads_connected當(dāng)前打開的連接數(shù)。這是實(shí)時(shí)值。Threads_running正在執(zhí)行查詢的連接數(shù)。如果這個(gè)值持續(xù)很高說(shuō)明數(shù)據(jù)庫(kù)非常繁忙。Threads_created自服務(wù)啟動(dòng)以來(lái)創(chuàng)建的連接總數(shù)。如果這個(gè)數(shù)字增長(zhǎng)過(guò)快說(shuō)明連接池可能配置太小或存在連接泄漏。查詢相關(guān)SHOW GLOBAL STATUS LIKE Com_%; SHOW GLOBAL STATUS LIKE Slow_queries;Com_select,Com_insert,Com_update,Com_delete反映了各類SQL的執(zhí)行頻率。Slow_queries顯示慢查詢的數(shù)量是性能問(wèn)題的重要風(fēng)向標(biāo)。InnoDB存儲(chǔ)引擎相關(guān)如果使用SHOW GLOBAL STATUS LIKE Innodb_buffer_pool%;Innodb_buffer_pool_read_requests邏輯讀和Innodb_buffer_pool_reads物理讀的比值反映了緩沖池的命中率直接影響磁盤I/O壓力。2. SHOW PROCESSLIST實(shí)時(shí)查看誰(shuí)在“干活”這是最常用的實(shí)時(shí)診斷命令相當(dāng)于數(shù)據(jù)庫(kù)的top命令。SHOW FULL PROCESSLIST;FULL關(guān)鍵字可以顯示完整的SQL語(yǔ)句否則過(guò)長(zhǎng)的語(yǔ)句會(huì)被截?cái)?。輸出列中需要重點(diǎn)關(guān)注Id: 連接進(jìn)程ID。User: 連接用戶。Host: 連接來(lái)源。db: 當(dāng)前使用的數(shù)據(jù)庫(kù)。Command: 連接正在執(zhí)行的命令類型Sleep-空閑Query-正在查詢Connect-連接中等。Time: 該狀態(tài)持續(xù)的時(shí)間秒。一個(gè)Sleep連接如果Time很大可能是連接池中的空閑連接一個(gè)Query連接如果Time很大很可能就是慢查詢或阻塞查詢。State: 連接狀態(tài)Sending data,Locked,Creating sort index等有助于判斷查詢卡在哪個(gè)環(huán)節(jié)。Info: 正在執(zhí)行的SQL語(yǔ)句如果存在。當(dāng)你發(fā)現(xiàn)數(shù)據(jù)庫(kù)響應(yīng)變慢時(shí)第一個(gè)動(dòng)作就應(yīng)該是執(zhí)行SHOW FULL PROCESSLIST;查找那些Time值大、State異?；騃nfo是復(fù)雜查詢的進(jìn)程。3. SHOW VARIABLES查看系統(tǒng)配置了解數(shù)據(jù)庫(kù)如何運(yùn)行必須知道它被配置成了什么樣。SHOW GLOBAL VARIABLES LIKE max_connections; -- 查看最大連接數(shù) SHOW GLOBAL VARIABLES LIKE innodb_buffer_pool_size; -- 查看InnoDB緩沖池大小 SHOW GLOBAL VARIABLES LIKE slow_query_log%; -- 查看慢查詢?nèi)罩九渲猛ㄟ^(guò)對(duì)比Threads_connected和max_connections可以判斷連接數(shù)是否接近上限。innodb_buffer_pool_size的設(shè)置是否合理直接決定了數(shù)據(jù)庫(kù)的性能基線。3. 數(shù)據(jù)庫(kù)與表的運(yùn)維管理結(jié)構(gòu)、存儲(chǔ)與數(shù)據(jù)安全日常運(yùn)維中除了監(jiān)控狀態(tài)更頻繁的操作是管理數(shù)據(jù)庫(kù)對(duì)象本身創(chuàng)建、查看、修改、備份。這部分命令構(gòu)成了運(yùn)維工作的“肌肉記憶”。3.1 數(shù)據(jù)庫(kù)生命周期管理1. 創(chuàng)建與選擇數(shù)據(jù)庫(kù)CREATE DATABASE IF NOT EXISTS ops_platform CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;這里有幾個(gè)關(guān)鍵點(diǎn)IF NOT EXISTS避免重復(fù)創(chuàng)建報(bào)錯(cuò)utf8mb4字符集支持完整的UTF-8包括emojiutf8mb4_unicode_ci排序規(guī)則比較通用。創(chuàng)建后使用USE ops_platform;來(lái)切換當(dāng)前數(shù)據(jù)庫(kù)。2. 查看與刪除數(shù)據(jù)庫(kù)SHOW DATABASES; -- 列出所有數(shù)據(jù)庫(kù) SHOW CREATE DATABASE ops_platform; -- 查看某個(gè)數(shù)據(jù)庫(kù)的創(chuàng)建語(yǔ)句含字符集等信息 DROP DATABASE IF EXISTS ops_platform; -- 謹(jǐn)慎操作刪除數(shù)據(jù)庫(kù)。SHOW CREATE DATABASE在需要遷移或重建數(shù)據(jù)庫(kù)時(shí)非常有用它能確保新環(huán)境的結(jié)構(gòu)與原環(huán)境一致。3.2 表結(jié)構(gòu)的探查與維護(hù)運(yùn)維經(jīng)常需要確認(rèn)表是否存在、結(jié)構(gòu)如何、占用了多少空間。1. 查看表信息SHOW TABLES; -- 查看當(dāng)前數(shù)據(jù)庫(kù)所有表 SHOW FULL COLUMNS FROM user; -- 查看user表的列詳情包括注釋 DESCRIBE user; -- DESC是DESCRIBE的簡(jiǎn)寫查看表結(jié)構(gòu) SHOW CREATE TABLE user; -- 查看建表語(yǔ)句包含引擎、字符集、索引等完整信息SHOW CREATE TABLE是神器。當(dāng)開發(fā)同學(xué)問(wèn)你“這張表的某個(gè)字段是否允許NULL索引是什么”時(shí)一條命令就能給出權(quán)威答案。2. 分析表存儲(chǔ)情況SHOW TABLE STATUS LIKE user\G在命令后加\G而不是分號(hào);可以按行垂直顯示結(jié)果在終端里閱讀寬表時(shí)更清晰。這個(gè)命令返回的結(jié)果中有幾個(gè)字段對(duì)運(yùn)維極具價(jià)值Engine: 存儲(chǔ)引擎InnoDB, MyISAM等。Rows: 表行數(shù)的估算值。對(duì)于InnoDB這是一個(gè)近似值不精確。Avg_row_length: 平均行長(zhǎng)度。Data_length: 數(shù)據(jù)部分的大小字節(jié)。Index_length: 索引部分的大小字節(jié)。Data_free: 已分配但未使用的空間碎片空間。 通過(guò)Data_length和Index_length你可以快速判斷哪些表是“空間消耗大戶”。如果Data_free很大說(shuō)明表可能存在碎片可以考慮在業(yè)務(wù)低峰期執(zhí)行OPTIMIZE TABLE user;來(lái)整理碎片注意此操作會(huì)鎖表。3.3 數(shù)據(jù)備份與恢復(fù)運(yùn)維的“后悔藥”沒(méi)有備份的運(yùn)維是在“裸奔”。邏輯備份導(dǎo)出SQL文件是最通用、最常用的方式。1. 使用mysqldump進(jìn)行邏輯備份mysqldump是官方自帶的備份工具它導(dǎo)出的是重建數(shù)據(jù)庫(kù)所需的SQL語(yǔ)句集合。# 備份單個(gè)數(shù)據(jù)庫(kù) mysqldump -u ops_admin -p --single-transaction --routines --triggers --events ops_platform ops_platform_backup_$(date %Y%m%d).sql # 備份所有數(shù)據(jù)庫(kù) mysqldump -u ops_admin -p --single-transaction --routines --triggers --events --all-databases full_backup_$(date %Y%m%d).sql參數(shù)解釋--single-transaction對(duì)于InnoDB表此參數(shù)會(huì)在一個(gè)事務(wù)中導(dǎo)出數(shù)據(jù)確保導(dǎo)出期間數(shù)據(jù)的一致性且不會(huì)鎖表對(duì)MyISAM表無(wú)效。這是生產(chǎn)環(huán)境備份的推薦做法。--routines導(dǎo)出存儲(chǔ)過(guò)程和函數(shù)。--triggers導(dǎo)出觸發(fā)器。--events導(dǎo)出事件調(diào)度器。--all-databases備份所有庫(kù)。2. 備份的進(jìn)階技巧與壓縮為了節(jié)省磁盤空間和傳輸時(shí)間通常直接壓縮備份文件。mysqldump -u ops_admin -p --single-transaction ops_platform | gzip ops_platform_backup_$(date %Y%m%d).sql.gz這條命令利用管道|將mysqldump的輸出直接交給gzip壓縮然后寫入.sql.gz文件一氣呵成。3. 從備份中恢復(fù)恢復(fù)操作相對(duì)簡(jiǎn)單但務(wù)必謹(jǐn)慎最好先在測(cè)試環(huán)境驗(yàn)證。# 解壓并恢復(fù)如果備份是壓縮的 gunzip ops_platform_backup_20231027.sql.gz | mysql -u ops_admin -p ops_platform # 直接恢復(fù)SQL文件 mysql -u ops_admin -p ops_platform ops_platform_backup_20231027.sql重要經(jīng)驗(yàn)恢復(fù)前務(wù)必確認(rèn)當(dāng)前數(shù)據(jù)庫(kù)是否可被覆蓋。對(duì)于重要數(shù)據(jù)的恢復(fù)我個(gè)人的流程是1) 立即對(duì)當(dāng)前生產(chǎn)數(shù)據(jù)再做一次快照備份2) 在測(cè)試環(huán)境完整演練恢復(fù)過(guò)程3) 在計(jì)劃好的維護(hù)窗口進(jìn)行操作。直接在生產(chǎn)環(huán)境執(zhí)行mysql backup.sql是極其危險(xiǎn)的行為。4. 用戶、權(quán)限與連接管理構(gòu)筑安全防線數(shù)據(jù)庫(kù)安全是運(yùn)維的重中之重。管理好用戶和權(quán)限就守住了數(shù)據(jù)的大門。4.1 用戶賬戶的創(chuàng)建與授權(quán)原則MariaDB的權(quán)限系統(tǒng)非常精細(xì)。遵循最小權(quán)限原則是關(guān)鍵。1. 創(chuàng)建用戶并授權(quán)-- 創(chuàng)建一個(gè)只能從內(nèi)網(wǎng)IP段訪問(wèn)對(duì)特定數(shù)據(jù)庫(kù)有讀寫權(quán)限的用戶 CREATE USER app_user192.168.1.% IDENTIFIED BY StrongPassword123!; -- 授予對(duì)ops_platform數(shù)據(jù)庫(kù)所有表的全部權(quán)限謹(jǐn)慎使用 GRANT ALL PRIVILEGES ON ops_platform.* TO app_user192.168.1.%; -- 更常見的做法授予必要的權(quán)限例如SELECT, INSERT, UPDATE, DELETE GRANT SELECT, INSERT, UPDATE, DELETE ON ops_platform.* TO app_user192.168.1.%; -- 授予執(zhí)行存儲(chǔ)過(guò)程的權(quán)限 GRANT EXECUTE ON PROCEDURE ops_platform.some_procedure TO app_user192.168.1.%; -- 使授權(quán)立即生效 FLUSH PRIVILEGES;app_user192.168.1.%用戶名和主機(jī)名共同唯一標(biāo)識(shí)一個(gè)用戶。%是通配符代表任意主機(jī)。生產(chǎn)環(huán)境應(yīng)盡量避免使用user%最好限定為具體的應(yīng)用服務(wù)器IP或網(wǎng)段。IDENTIFIED BY設(shè)置密碼。MariaDB 10.4以后默認(rèn)使用unix_socket或mysql_native_password插件確保密碼強(qiáng)度。GRANT ... ON database.*database.*表示該數(shù)據(jù)庫(kù)下的所有表。也可以精確到database.table。FLUSH PRIVILEGES;大多數(shù)GRANT語(yǔ)句后會(huì)自動(dòng)刷新權(quán)限但顯式執(zhí)行一次是個(gè)好習(xí)慣確保更改立即生效。2. 查看與回收權(quán)限-- 查看某個(gè)用戶的授權(quán)語(yǔ)句 SHOW GRANTS FOR app_user192.168.1.%; -- 查看當(dāng)前登錄用戶的權(quán)限 SHOW GRANTS; -- 回收部分權(quán)限例如收回DELETE權(quán)限 REVOKE DELETE ON ops_platform.* FROM app_user192.168.1.%; -- 刪除用戶會(huì)同時(shí)移除其所有權(quán)限 DROP USER app_user192.168.1.%;SHOW GRANTS的輸出可以直接作為重建用戶權(quán)限的腳本建議定期歸檔。4.2 連接與會(huì)話的管理與干預(yù)當(dāng)數(shù)據(jù)庫(kù)出現(xiàn)異常如慢查詢拖垮性能、死鎖或需要緊急維護(hù)時(shí)運(yùn)維需要有能力干預(yù)會(huì)話。1. 揪出問(wèn)題會(huì)話并終止結(jié)合SHOW PROCESSLIST找到問(wèn)題進(jìn)程的Id然后使用KILL命令。-- 首先找出耗時(shí)長(zhǎng)的查詢 SHOW FULL PROCESSLIST; -- 假設(shè)發(fā)現(xiàn)Id為1234的查詢已經(jīng)執(zhí)行了500秒 -- 溫柔地終止允許查詢完成當(dāng)前語(yǔ)句 KILL 1234; -- 強(qiáng)制立即終止如果上面的命令不生效 KILL QUERY 1234; -- 只終止當(dāng)前執(zhí)行的語(yǔ)句不斷開連接 -- 或者 KILL CONNECTION 1234; -- 終止整個(gè)連接踩坑實(shí)錄不要一看到慢查詢就KILL。首先嘗試用EXPLAIN分析一下它的執(zhí)行計(jì)劃如果還能執(zhí)行的話看是否缺少索引。其次KILL一個(gè)正在更新大量數(shù)據(jù)的事務(wù)可能導(dǎo)致回滾時(shí)間很長(zhǎng)甚至讓數(shù)據(jù)庫(kù)“卡住”更久。務(wù)必先判斷該操作的影響范圍。2. 監(jiān)控連接數(shù)限制連接數(shù)耗盡是常見的故障。你需要知道當(dāng)前連接數(shù)和上限。SHOW VARIABLES LIKE max_connections; SHOW GLOBAL STATUS LIKE Threads_connected; SHOW GLOBAL STATUS LIKE Max_used_connections;如果Threads_connected長(zhǎng)期接近max_connections就需要考慮調(diào)大max_connections參數(shù)在/etc/my.cnf或/etc/my.cnf.d/下的配置文件中修改并重啟服務(wù)但更重要的是排查應(yīng)用是否有連接泄漏。Max_used_connections記錄了服務(wù)啟動(dòng)以來(lái)同時(shí)使用的連接最大數(shù)這對(duì)容量規(guī)劃有參考價(jià)值。5. 進(jìn)階運(yùn)維日志分析、變量調(diào)整與簡(jiǎn)單性能排查掌握了基礎(chǔ)命令就像拿到了工具箱?，F(xiàn)在我們需要學(xué)習(xí)如何用這些工具進(jìn)行更深入的“診斷”和“微調(diào)”。5.1 讀懂日志錯(cuò)誤日志、慢查詢?nèi)罩九c通用日志日志是數(shù)據(jù)庫(kù)的“黑匣子”里面記錄了所有異常和潛在的性能線索。1. 定位并查看錯(cuò)誤日志錯(cuò)誤日志記錄了服務(wù)啟動(dòng)、關(guān)閉、運(yùn)行中的嚴(yán)重錯(cuò)誤信息。首先找到它SHOW GLOBAL VARIABLES LIKE log_error;輸出可能是類似/var/log/mariadb/mariadb.log的路徑。然后你可以用tail,grep等Linux命令查看。# 實(shí)時(shí)查看錯(cuò)誤日志尾部 tail -f /var/log/mariadb/mariadb.log # 查找最近的錯(cuò)誤 grep -i error\|warning /var/log/mariadb/mariadb.log | tail -50常見的錯(cuò)誤包括無(wú)法綁定端口、磁盤空間不足、表?yè)p壞等。遇到數(shù)據(jù)庫(kù)啟動(dòng)失敗第一個(gè)就該查這里。2. 啟用與分析慢查詢?nèi)罩韭樵內(nèi)罩臼切阅軆?yōu)化的金礦。首先確認(rèn)其狀態(tài)和位置SHOW GLOBAL VARIABLES LIKE slow_query_log%; SHOW GLOBAL VARIABLES LIKE long_query_time;slow_query_logON表示已開啟。slow_query_log_file日志文件路徑。long_query_time超過(guò)該時(shí)間秒的查詢會(huì)被記錄。默認(rèn)10秒生產(chǎn)環(huán)境通常設(shè)為1-3秒甚至更低。如果沒(méi)開啟可以在配置文件中設(shè)置需重啟或動(dòng)態(tài)開啟臨時(shí)生效SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2; -- 設(shè)置為2秒 SET GLOBAL slow_query_log_file /var/log/mariadb/slow-query.log;分析慢查詢?nèi)罩究梢允褂肕ariaDB自帶的mysqldumpslow工具如果已安裝進(jìn)行歸類統(tǒng)計(jì)# 統(tǒng)計(jì)最慢的10個(gè)查詢 mysqldumpslow -s t -t 10 /var/log/mariadb/slow-query.log # 統(tǒng)計(jì)包含特定表名的慢查詢 mysqldumpslow -g user_table /var/log/mariadb/slow-query.log更強(qiáng)大的分析可以使用pt-query-digestPercona Toolkit的一部分它能生成非常詳細(xì)的報(bào)告。5.2 動(dòng)態(tài)調(diào)整系統(tǒng)變量一把雙刃劍MariaDB很多參數(shù)可以在運(yùn)行時(shí)動(dòng)態(tài)調(diào)整無(wú)需重啟服務(wù)。這給運(yùn)維帶來(lái)了靈活性但也需格外小心。1. 查看與設(shè)置變量-- 查看當(dāng)前會(huì)話的變量值 SHOW VARIABLES LIKE wait_timeout; -- 查看全局變量值 SHOW GLOBAL VARIABLES LIKE wait_timeout; -- 動(dòng)態(tài)設(shè)置全局變量影響所有新連接 SET GLOBAL wait_timeout 600; -- 設(shè)置當(dāng)前會(huì)話變量?jī)H影響當(dāng)前連接 SET SESSION wait_timeout 300;重要區(qū)別GLOBAL級(jí)修改對(duì)已經(jīng)存在的連接無(wú)效只影響修改后新建的連接。而SESSION級(jí)修改只影響當(dāng)前連接自己。2. 幾個(gè)運(yùn)維常調(diào)的參數(shù)wait_timeout/interactive_timeout非交互/交互式連接的空閑超時(shí)時(shí)間秒。設(shè)置過(guò)小會(huì)導(dǎo)致連接頻繁重建過(guò)大可能導(dǎo)致大量空閑連接占用資源。通常設(shè)為300-600秒。max_allowed_packet客戶端/服務(wù)器通信的最大數(shù)據(jù)包大小。如果應(yīng)用需要插入或查詢很大的BLOB字段可能需要調(diào)大此值例如SET GLOBAL max_allowed_packet1073741824;設(shè)為1GB。innodb_buffer_pool_size這是最重要的性能參數(shù)之一定義了InnoDB緩沖池的大小。理想情況下它應(yīng)能容納你的活躍數(shù)據(jù)集。修改它通常需要重啟服務(wù)但在MariaDB 10.2版本可以通過(guò)SET GLOBAL innodb_buffer_pool_size...動(dòng)態(tài)調(diào)整以chunk為單位。操作心得動(dòng)態(tài)修改GLOBAL變量是臨時(shí)的服務(wù)重啟后會(huì)失效。永久修改必須在配置文件如/etc/my.cnf.d/server.cnf中的[mysqld]段下進(jìn)行例如[mysqld] wait_timeout 600 innodb_buffer_pool_size 2G修改配置文件后需要重啟MariaDB服務(wù)systemctl restart mariadb才能生效。任何重要的參數(shù)調(diào)整尤其是像innodb_buffer_pool_size這種最好先在測(cè)試環(huán)境驗(yàn)證。5.3 簡(jiǎn)單的性能排查流程一個(gè)實(shí)戰(zhàn)案例假設(shè)你收到告警數(shù)據(jù)庫(kù)服務(wù)器CPU使用率持續(xù)超過(guò)90%。你可以遵循以下流程快速排查連接數(shù)據(jù)庫(kù)mysql -u ops_admin -p查看實(shí)時(shí)活動(dòng)SHOW FULL PROCESSLIST;重點(diǎn)觀察Command不是Sleep且Time值很高的進(jìn)程。記錄下它們的Id和InfoSQL語(yǔ)句。分析可疑SQL對(duì)于找到的疑似慢SQL可以嘗試在其連接中執(zhí)行EXPLAIN [SQL語(yǔ)句]查看執(zhí)行計(jì)劃看是否全表掃描、索引使用不當(dāng)。檢查系統(tǒng)狀態(tài)SHOW GLOBAL STATUS LIKE Threads_running; -- 高則說(shuō)明并發(fā)高 SHOW GLOBAL STATUS LIKE Innodb_rows_read%; -- 查看行讀取量 SHOW ENGINE INNODB STATUS\G -- 獲取詳細(xì)的InnoDB狀態(tài)報(bào)告包含鎖信息、信號(hào)量等待等輸出很長(zhǎng)需要分析檢查鎖等待在SHOW ENGINE INNODB STATUS輸出的TRANSACTIONS部分可以查看當(dāng)前運(yùn)行的事務(wù)和鎖等待鏈。如果發(fā)現(xiàn)大量鎖等待可能是事務(wù)設(shè)計(jì)不合理或語(yǔ)句鎖住了過(guò)多資源。結(jié)合操作系統(tǒng)工具不要只看數(shù)據(jù)庫(kù)內(nèi)部。同時(shí)在Linux shell下用top或htop查看是否是mysqld進(jìn)程本身CPU高還是其他進(jìn)程。用iostat或vmstat查看磁盤I/O是否成為瓶頸。通過(guò)這一套組合拳你通常能快速定位問(wèn)題是出在某個(gè)失控的查詢SHOW PROCESSLIST、系統(tǒng)資源不足操作系統(tǒng)工具還是數(shù)據(jù)庫(kù)內(nèi)部競(jìng)爭(zhēng)InnoDB Status上。這就是將基礎(chǔ)命令串聯(lián)起來(lái)解決實(shí)際問(wèn)題的能力。 運(yùn)維必備：MariaDB命令實(shí)戰(zhàn)指南，快速定位數(shù)據(jù)庫(kù)問(wèn)題
1. 從“能用”到“會(huì)管”為什么運(yùn)維必須懂MariaDB命令最近在幫一個(gè)朋友排查他們線上服務(wù)間歇性卡頓的問(wèn)題最后定位到數(shù)據(jù)庫(kù)上。登錄服務(wù)器一看mariadb進(jìn)程的CPU占用時(shí)不時(shí)就沖到100%但開發(fā)同學(xué)給的反饋是“SQL都優(yōu)化過(guò)了”。我習(xí)慣性地連上數(shù)據(jù)庫(kù)敲了幾個(gè)最基礎(chǔ)的命令比如SHOW PROCESSLIST;和SHOW GLOBAL STATUS LIKE ‘Threads_connected’;問(wèn)題立刻就清晰了應(yīng)用連接池配置不當(dāng)產(chǎn)生了大量空閑連接把數(shù)據(jù)庫(kù)的連接線程池耗盡了。這件事讓我再次感慨對(duì)于系統(tǒng)運(yùn)維而言掌握數(shù)據(jù)庫(kù)的基本命令絕不是“加分項(xiàng)”而是“保命項(xiàng)”。很多運(yùn)維工程師可能會(huì)覺(jué)得數(shù)據(jù)庫(kù)是DBA的領(lǐng)域我們只要保證服務(wù)端口通、進(jìn)程在、磁盤空間夠就行了。但在實(shí)際的生產(chǎn)環(huán)境中尤其是在中小團(tuán)隊(duì)或DevOps文化盛行的今天運(yùn)維的邊界早已模糊。當(dāng)凌晨三點(diǎn)收到告警“數(shù)據(jù)庫(kù)響應(yīng)超時(shí)”時(shí)你不可能每次都去搖醒DBA。你需要有能力第一時(shí)間登錄服務(wù)器用最直接的方式判斷是連接數(shù)爆了是鎖等待還是某個(gè)慢查詢拖垮了整臺(tái)機(jī)器這些判斷都依賴于對(duì)MariaDB或MySQL一系列基本命令的熟練運(yùn)用。MariaDB作為MySQL最流行的分支其命令體系與MySQL高度兼容是Linux服務(wù)器上最常見的開源關(guān)系型數(shù)據(jù)庫(kù)之一。無(wú)論是部署在CentOS、Ubuntu還是國(guó)產(chǎn)化的麒麟、統(tǒng)信UOS上其管理邏輯都是一致的。這篇文章我就從一個(gè)運(yùn)維的視角拋開復(fù)雜的SQL優(yōu)化和架構(gòu)設(shè)計(jì)聚焦于那些真正能幫你快速定位問(wèn)題、完成日常維護(hù)的MariaDB命令行操作。我們的目標(biāo)不是成為DBA而是成為一個(gè)在數(shù)據(jù)庫(kù)“生病”時(shí)能迅速做出初步診斷的“全科醫(yī)生”。2. 運(yùn)維第一課連接、狀態(tài)查看與基礎(chǔ)信息獲取所有深入的排查都始于一次成功的連接和對(duì)系統(tǒng)狀態(tài)的快速掃描。對(duì)于運(yùn)維來(lái)說(shuō)高效、安全地連接數(shù)據(jù)庫(kù)并獲取全局視圖是后續(xù)所有操作的基礎(chǔ)。2.1 不止于mysql -u root -p安全與靈活的連接姿勢(shì)教科書里教的mysql -u root -p當(dāng)然沒(méi)錯(cuò)但在生產(chǎn)環(huán)境我們需要考慮更多。1. 使用非root用戶與指定主機(jī)連接生產(chǎn)環(huán)境嚴(yán)禁長(zhǎng)期使用root賬戶進(jìn)行日常運(yùn)維。你應(yīng)該創(chuàng)建一個(gè)具有相應(yīng)權(quán)限的運(yùn)維專用賬戶。mysql -u ops_admin -h 127.0.0.1 -p這里-h指定了數(shù)據(jù)庫(kù)服務(wù)器地址。如果是本地Socket連接可以省略或使用-h localhost。使用具體IP而非主機(jī)名有時(shí)可以避免DNS解析帶來(lái)的問(wèn)題。輸入命令后在提示符下輸入密碼。為了不在命令行歷史中留下密碼痕跡不建議使用-pYourPassword的寫法。2. 通過(guò)Socket文件連接常見于本地當(dāng)MySQL/MariaDB服務(wù)與客戶端在同一臺(tái)機(jī)器時(shí)通過(guò)Unix Socket文件連接效率更高也省去了TCP/IP協(xié)議棧的開銷。你需要知道Socket文件的路徑通常在/var/lib/mysql/mysql.sock或/tmp/mysql.sock。mysql -u ops_admin -S /var/lib/mysql/mysql.sock -p3. 在腳本中自動(dòng)化連接對(duì)于監(jiān)控腳本或自動(dòng)化任務(wù)可以使用~/.my.cnf配置文件來(lái)避免在命令行中暴露密碼。 首先創(chuàng)建或編輯該文件權(quán)限必須設(shè)為600vi ~/.my.cnf內(nèi)容如下[client] userops_admin passwordYourSecurePassword host127.0.0.1保存后直接運(yùn)行mysql命令即可無(wú)密碼登錄。這是既安全又方便的做法。注意~/.my.cnf文件的權(quán)限至關(guān)重要。務(wù)必執(zhí)行chmod 600 ~/.my.cnf否則MariaDB會(huì)因安全原因拒絕使用該文件中的密碼。2.2 掌握系統(tǒng)狀態(tài)SHOW 命令家族詳解連接成功后我們來(lái)到了“運(yùn)維診斷室”。SHOW命令就是你的聽診器和血壓計(jì)。1. SHOW STATUS獲取性能指標(biāo)全景圖SHOW GLOBAL STATUS;會(huì)輸出數(shù)百個(gè)系統(tǒng)狀態(tài)變量。全看會(huì)眼花繚亂運(yùn)維需要關(guān)注幾個(gè)關(guān)鍵指標(biāo)連接相關(guān)SHOW GLOBAL STATUS LIKE Threads_%;Threads_connected當(dāng)前打開的連接數(shù)。這是實(shí)時(shí)值。Threads_running正在執(zhí)行查詢的連接數(shù)。如果這個(gè)值持續(xù)很高說(shuō)明數(shù)據(jù)庫(kù)非常繁忙。Threads_created自服務(wù)啟動(dòng)以來(lái)創(chuàng)建的連接總數(shù)。如果這個(gè)數(shù)字增長(zhǎng)過(guò)快說(shuō)明連接池可能配置太小或存在連接泄漏。查詢相關(guān)SHOW GLOBAL STATUS LIKE Com_%; SHOW GLOBAL STATUS LIKE Slow_queries;Com_select,Com_insert,Com_update,Com_delete反映了各類SQL的執(zhí)行頻率。Slow_queries顯示慢查詢的數(shù)量是性能問(wèn)題的重要風(fēng)向標(biāo)。InnoDB存儲(chǔ)引擎相關(guān)如果使用SHOW GLOBAL STATUS LIKE Innodb_buffer_pool%;Innodb_buffer_pool_read_requests邏輯讀和Innodb_buffer_pool_reads物理讀的比值反映了緩沖池的命中率直接影響磁盤I/O壓力。2. SHOW PROCESSLIST實(shí)時(shí)查看誰(shuí)在“干活”這是最常用的實(shí)時(shí)診斷命令相當(dāng)于數(shù)據(jù)庫(kù)的top命令。SHOW FULL PROCESSLIST;FULL關(guān)鍵字可以顯示完整的SQL語(yǔ)句否則過(guò)長(zhǎng)的語(yǔ)句會(huì)被截?cái)?。輸出列中需要重點(diǎn)關(guān)注Id: 連接進(jìn)程ID。User: 連接用戶。Host: 連接來(lái)源。db: 當(dāng)前使用的數(shù)據(jù)庫(kù)。Command: 連接正在執(zhí)行的命令類型Sleep-空閑Query-正在查詢Connect-連接中等。Time: 該狀態(tài)持續(xù)的時(shí)間秒。一個(gè)Sleep連接如果Time很大可能是連接池中的空閑連接一個(gè)Query連接如果Time很大很可能就是慢查詢或阻塞查詢。State: 連接狀態(tài)Sending data,Locked,Creating sort index等有助于判斷查詢卡在哪個(gè)環(huán)節(jié)。Info: 正在執(zhí)行的SQL語(yǔ)句如果存在。當(dāng)你發(fā)現(xiàn)數(shù)據(jù)庫(kù)響應(yīng)變慢時(shí)第一個(gè)動(dòng)作就應(yīng)該是執(zhí)行SHOW FULL PROCESSLIST;查找那些Time值大、State異?；騃nfo是復(fù)雜查詢的進(jìn)程。3. SHOW VARIABLES查看系統(tǒng)配置了解數(shù)據(jù)庫(kù)如何運(yùn)行必須知道它被配置成了什么樣。SHOW GLOBAL VARIABLES LIKE max_connections; -- 查看最大連接數(shù) SHOW GLOBAL VARIABLES LIKE innodb_buffer_pool_size; -- 查看InnoDB緩沖池大小 SHOW GLOBAL VARIABLES LIKE slow_query_log%; -- 查看慢查詢?nèi)罩九渲猛ㄟ^(guò)對(duì)比Threads_connected和max_connections可以判斷連接數(shù)是否接近上限。innodb_buffer_pool_size的設(shè)置是否合理直接決定了數(shù)據(jù)庫(kù)的性能基線。3. 數(shù)據(jù)庫(kù)與表的運(yùn)維管理結(jié)構(gòu)、存儲(chǔ)與數(shù)據(jù)安全日常運(yùn)維中除了監(jiān)控狀態(tài)更頻繁的操作是管理數(shù)據(jù)庫(kù)對(duì)象本身創(chuàng)建、查看、修改、備份。這部分命令構(gòu)成了運(yùn)維工作的“肌肉記憶”。3.1 數(shù)據(jù)庫(kù)生命周期管理1. 創(chuàng)建與選擇數(shù)據(jù)庫(kù)CREATE DATABASE IF NOT EXISTS ops_platform CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;這里有幾個(gè)關(guān)鍵點(diǎn)IF NOT EXISTS避免重復(fù)創(chuàng)建報(bào)錯(cuò)utf8mb4字符集支持完整的UTF-8包括emojiutf8mb4_unicode_ci排序規(guī)則比較通用。創(chuàng)建后使用USE ops_platform;來(lái)切換當(dāng)前數(shù)據(jù)庫(kù)。2. 查看與刪除數(shù)據(jù)庫(kù)SHOW DATABASES; -- 列出所有數(shù)據(jù)庫(kù) SHOW CREATE DATABASE ops_platform; -- 查看某個(gè)數(shù)據(jù)庫(kù)的創(chuàng)建語(yǔ)句含字符集等信息 DROP DATABASE IF EXISTS ops_platform; -- 謹(jǐn)慎操作刪除數(shù)據(jù)庫(kù)。SHOW CREATE DATABASE在需要遷移或重建數(shù)據(jù)庫(kù)時(shí)非常有用它能確保新環(huán)境的結(jié)構(gòu)與原環(huán)境一致。3.2 表結(jié)構(gòu)的探查與維護(hù)運(yùn)維經(jīng)常需要確認(rèn)表是否存在、結(jié)構(gòu)如何、占用了多少空間。1. 查看表信息SHOW TABLES; -- 查看當(dāng)前數(shù)據(jù)庫(kù)所有表 SHOW FULL COLUMNS FROM user; -- 查看user表的列詳情包括注釋 DESCRIBE user; -- DESC是DESCRIBE的簡(jiǎn)寫查看表結(jié)構(gòu) SHOW CREATE TABLE user; -- 查看建表語(yǔ)句包含引擎、字符集、索引等完整信息SHOW CREATE TABLE是神器。當(dāng)開發(fā)同學(xué)問(wèn)你“這張表的某個(gè)字段是否允許NULL索引是什么”時(shí)一條命令就能給出權(quán)威答案。2. 分析表存儲(chǔ)情況SHOW TABLE STATUS LIKE user\G在命令后加\G而不是分號(hào);可以按行垂直顯示結(jié)果在終端里閱讀寬表時(shí)更清晰。這個(gè)命令返回的結(jié)果中有幾個(gè)字段對(duì)運(yùn)維極具價(jià)值Engine: 存儲(chǔ)引擎InnoDB, MyISAM等。Rows: 表行數(shù)的估算值。對(duì)于InnoDB這是一個(gè)近似值不精確。Avg_row_length: 平均行長(zhǎng)度。Data_length: 數(shù)據(jù)部分的大小字節(jié)。Index_length: 索引部分的大小字節(jié)。Data_free: 已分配但未使用的空間碎片空間。 通過(guò)Data_length和Index_length你可以快速判斷哪些表是“空間消耗大戶”。如果Data_free很大說(shuō)明表可能存在碎片可以考慮在業(yè)務(wù)低峰期執(zhí)行OPTIMIZE TABLE user;來(lái)整理碎片注意此操作會(huì)鎖表。3.3 數(shù)據(jù)備份與恢復(fù)運(yùn)維的“后悔藥”沒(méi)有備份的運(yùn)維是在“裸奔”。邏輯備份導(dǎo)出SQL文件是最通用、最常用的方式。1. 使用mysqldump進(jìn)行邏輯備份mysqldump是官方自帶的備份工具它導(dǎo)出的是重建數(shù)據(jù)庫(kù)所需的SQL語(yǔ)句集合。# 備份單個(gè)數(shù)據(jù)庫(kù) mysqldump -u ops_admin -p --single-transaction --routines --triggers --events ops_platform ops_platform_backup_$(date %Y%m%d).sql # 備份所有數(shù)據(jù)庫(kù) mysqldump -u ops_admin -p --single-transaction --routines --triggers --events --all-databases full_backup_$(date %Y%m%d).sql參數(shù)解釋--single-transaction對(duì)于InnoDB表此參數(shù)會(huì)在一個(gè)事務(wù)中導(dǎo)出數(shù)據(jù)確保導(dǎo)出期間數(shù)據(jù)的一致性且不會(huì)鎖表對(duì)MyISAM表無(wú)效。這是生產(chǎn)環(huán)境備份的推薦做法。--routines導(dǎo)出存儲(chǔ)過(guò)程和函數(shù)。--triggers導(dǎo)出觸發(fā)器。--events導(dǎo)出事件調(diào)度器。--all-databases備份所有庫(kù)。2. 備份的進(jìn)階技巧與壓縮為了節(jié)省磁盤空間和傳輸時(shí)間通常直接壓縮備份文件。mysqldump -u ops_admin -p --single-transaction ops_platform | gzip ops_platform_backup_$(date %Y%m%d).sql.gz這條命令利用管道|將mysqldump的輸出直接交給gzip壓縮然后寫入.sql.gz文件一氣呵成。3. 從備份中恢復(fù)恢復(fù)操作相對(duì)簡(jiǎn)單但務(wù)必謹(jǐn)慎最好先在測(cè)試環(huán)境驗(yàn)證。# 解壓并恢復(fù)如果備份是壓縮的 gunzip ops_platform_backup_20231027.sql.gz | mysql -u ops_admin -p ops_platform # 直接恢復(fù)SQL文件 mysql -u ops_admin -p ops_platform ops_platform_backup_20231027.sql重要經(jīng)驗(yàn)恢復(fù)前務(wù)必確認(rèn)當(dāng)前數(shù)據(jù)庫(kù)是否可被覆蓋。對(duì)于重要數(shù)據(jù)的恢復(fù)我個(gè)人的流程是1) 立即對(duì)當(dāng)前生產(chǎn)數(shù)據(jù)再做一次快照備份2) 在測(cè)試環(huán)境完整演練恢復(fù)過(guò)程3) 在計(jì)劃好的維護(hù)窗口進(jìn)行操作。直接在生產(chǎn)環(huán)境執(zhí)行mysql backup.sql是極其危險(xiǎn)的行為。4. 用戶、權(quán)限與連接管理構(gòu)筑安全防線數(shù)據(jù)庫(kù)安全是運(yùn)維的重中之重。管理好用戶和權(quán)限就守住了數(shù)據(jù)的大門。4.1 用戶賬戶的創(chuàng)建與授權(quán)原則MariaDB的權(quán)限系統(tǒng)非常精細(xì)。遵循最小權(quán)限原則是關(guān)鍵。1. 創(chuàng)建用戶并授權(quán)-- 創(chuàng)建一個(gè)只能從內(nèi)網(wǎng)IP段訪問(wèn)對(duì)特定數(shù)據(jù)庫(kù)有讀寫權(quán)限的用戶 CREATE USER app_user192.168.1.% IDENTIFIED BY StrongPassword123!; -- 授予對(duì)ops_platform數(shù)據(jù)庫(kù)所有表的全部權(quán)限謹(jǐn)慎使用 GRANT ALL PRIVILEGES ON ops_platform.* TO app_user192.168.1.%; -- 更常見的做法授予必要的權(quán)限例如SELECT, INSERT, UPDATE, DELETE GRANT SELECT, INSERT, UPDATE, DELETE ON ops_platform.* TO app_user192.168.1.%; -- 授予執(zhí)行存儲(chǔ)過(guò)程的權(quán)限 GRANT EXECUTE ON PROCEDURE ops_platform.some_procedure TO app_user192.168.1.%; -- 使授權(quán)立即生效 FLUSH PRIVILEGES;app_user192.168.1.%用戶名和主機(jī)名共同唯一標(biāo)識(shí)一個(gè)用戶。%是通配符代表任意主機(jī)。生產(chǎn)環(huán)境應(yīng)盡量避免使用user%最好限定為具體的應(yīng)用服務(wù)器IP或網(wǎng)段。IDENTIFIED BY設(shè)置密碼。MariaDB 10.4以后默認(rèn)使用unix_socket或mysql_native_password插件確保密碼強(qiáng)度。GRANT ... ON database.*database.*表示該數(shù)據(jù)庫(kù)下的所有表。也可以精確到database.table。FLUSH PRIVILEGES;大多數(shù)GRANT語(yǔ)句后會(huì)自動(dòng)刷新權(quán)限但顯式執(zhí)行一次是個(gè)好習(xí)慣確保更改立即生效。2. 查看與回收權(quán)限-- 查看某個(gè)用戶的授權(quán)語(yǔ)句 SHOW GRANTS FOR app_user192.168.1.%; -- 查看當(dāng)前登錄用戶的權(quán)限 SHOW GRANTS; -- 回收部分權(quán)限例如收回DELETE權(quán)限 REVOKE DELETE ON ops_platform.* FROM app_user192.168.1.%; -- 刪除用戶會(huì)同時(shí)移除其所有權(quán)限 DROP USER app_user192.168.1.%;SHOW GRANTS的輸出可以直接作為重建用戶權(quán)限的腳本建議定期歸檔。4.2 連接與會(huì)話的管理與干預(yù)當(dāng)數(shù)據(jù)庫(kù)出現(xiàn)異常如慢查詢拖垮性能、死鎖或需要緊急維護(hù)時(shí)運(yùn)維需要有能力干預(yù)會(huì)話。1. 揪出問(wèn)題會(huì)話并終止結(jié)合SHOW PROCESSLIST找到問(wèn)題進(jìn)程的Id然后使用KILL命令。-- 首先找出耗時(shí)長(zhǎng)的查詢 SHOW FULL PROCESSLIST; -- 假設(shè)發(fā)現(xiàn)Id為1234的查詢已經(jīng)執(zhí)行了500秒 -- 溫柔地終止允許查詢完成當(dāng)前語(yǔ)句 KILL 1234; -- 強(qiáng)制立即終止如果上面的命令不生效 KILL QUERY 1234; -- 只終止當(dāng)前執(zhí)行的語(yǔ)句不斷開連接 -- 或者 KILL CONNECTION 1234; -- 終止整個(gè)連接踩坑實(shí)錄不要一看到慢查詢就KILL。首先嘗試用EXPLAIN分析一下它的執(zhí)行計(jì)劃如果還能執(zhí)行的話看是否缺少索引。其次KILL一個(gè)正在更新大量數(shù)據(jù)的事務(wù)可能導(dǎo)致回滾時(shí)間很長(zhǎng)甚至讓數(shù)據(jù)庫(kù)“卡住”更久。務(wù)必先判斷該操作的影響范圍。2. 監(jiān)控連接數(shù)限制連接數(shù)耗盡是常見的故障。你需要知道當(dāng)前連接數(shù)和上限。SHOW VARIABLES LIKE max_connections; SHOW GLOBAL STATUS LIKE Threads_connected; SHOW GLOBAL STATUS LIKE Max_used_connections;如果Threads_connected長(zhǎng)期接近max_connections就需要考慮調(diào)大max_connections參數(shù)在/etc/my.cnf或/etc/my.cnf.d/下的配置文件中修改并重啟服務(wù)但更重要的是排查應(yīng)用是否有連接泄漏。Max_used_connections記錄了服務(wù)啟動(dòng)以來(lái)同時(shí)使用的連接最大數(shù)這對(duì)容量規(guī)劃有參考價(jià)值。5. 進(jìn)階運(yùn)維日志分析、變量調(diào)整與簡(jiǎn)單性能排查掌握了基礎(chǔ)命令就像拿到了工具箱?，F(xiàn)在我們需要學(xué)習(xí)如何用這些工具進(jìn)行更深入的“診斷”和“微調(diào)”。5.1 讀懂日志錯(cuò)誤日志、慢查詢?nèi)罩九c通用日志日志是數(shù)據(jù)庫(kù)的“黑匣子”里面記錄了所有異常和潛在的性能線索。1. 定位并查看錯(cuò)誤日志錯(cuò)誤日志記錄了服務(wù)啟動(dòng)、關(guān)閉、運(yùn)行中的嚴(yán)重錯(cuò)誤信息。首先找到它SHOW GLOBAL VARIABLES LIKE log_error;輸出可能是類似/var/log/mariadb/mariadb.log的路徑。然后你可以用tail,grep等Linux命令查看。# 實(shí)時(shí)查看錯(cuò)誤日志尾部 tail -f /var/log/mariadb/mariadb.log # 查找最近的錯(cuò)誤 grep -i error\|warning /var/log/mariadb/mariadb.log | tail -50常見的錯(cuò)誤包括無(wú)法綁定端口、磁盤空間不足、表?yè)p壞等。遇到數(shù)據(jù)庫(kù)啟動(dòng)失敗第一個(gè)就該查這里。2. 啟用與分析慢查詢?nèi)罩韭樵內(nèi)罩臼切阅軆?yōu)化的金礦。首先確認(rèn)其狀態(tài)和位置SHOW GLOBAL VARIABLES LIKE slow_query_log%; SHOW GLOBAL VARIABLES LIKE long_query_time;slow_query_logON表示已開啟。slow_query_log_file日志文件路徑。long_query_time超過(guò)該時(shí)間秒的查詢會(huì)被記錄。默認(rèn)10秒生產(chǎn)環(huán)境通常設(shè)為1-3秒甚至更低。如果沒(méi)開啟可以在配置文件中設(shè)置需重啟或動(dòng)態(tài)開啟臨時(shí)生效SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2; -- 設(shè)置為2秒 SET GLOBAL slow_query_log_file /var/log/mariadb/slow-query.log;分析慢查詢?nèi)罩究梢允褂肕ariaDB自帶的mysqldumpslow工具如果已安裝進(jìn)行歸類統(tǒng)計(jì)# 統(tǒng)計(jì)最慢的10個(gè)查詢 mysqldumpslow -s t -t 10 /var/log/mariadb/slow-query.log # 統(tǒng)計(jì)包含特定表名的慢查詢 mysqldumpslow -g user_table /var/log/mariadb/slow-query.log更強(qiáng)大的分析可以使用pt-query-digestPercona Toolkit的一部分它能生成非常詳細(xì)的報(bào)告。5.2 動(dòng)態(tài)調(diào)整系統(tǒng)變量一把雙刃劍MariaDB很多參數(shù)可以在運(yùn)行時(shí)動(dòng)態(tài)調(diào)整無(wú)需重啟服務(wù)。這給運(yùn)維帶來(lái)了靈活性但也需格外小心。1. 查看與設(shè)置變量-- 查看當(dāng)前會(huì)話的變量值 SHOW VARIABLES LIKE wait_timeout; -- 查看全局變量值 SHOW GLOBAL VARIABLES LIKE wait_timeout; -- 動(dòng)態(tài)設(shè)置全局變量影響所有新連接 SET GLOBAL wait_timeout 600; -- 設(shè)置當(dāng)前會(huì)話變量?jī)H影響當(dāng)前連接 SET SESSION wait_timeout 300;重要區(qū)別GLOBAL級(jí)修改對(duì)已經(jīng)存在的連接無(wú)效只影響修改后新建的連接。而SESSION級(jí)修改只影響當(dāng)前連接自己。2. 幾個(gè)運(yùn)維常調(diào)的參數(shù)wait_timeout/interactive_timeout非交互/交互式連接的空閑超時(shí)時(shí)間秒。設(shè)置過(guò)小會(huì)導(dǎo)致連接頻繁重建過(guò)大可能導(dǎo)致大量空閑連接占用資源。通常設(shè)為300-600秒。max_allowed_packet客戶端/服務(wù)器通信的最大數(shù)據(jù)包大小。如果應(yīng)用需要插入或查詢很大的BLOB字段可能需要調(diào)大此值例如SET GLOBAL max_allowed_packet1073741824;設(shè)為1GB。innodb_buffer_pool_size這是最重要的性能參數(shù)之一定義了InnoDB緩沖池的大小。理想情況下它應(yīng)能容納你的活躍數(shù)據(jù)集。修改它通常需要重啟服務(wù)但在MariaDB 10.2版本可以通過(guò)SET GLOBAL innodb_buffer_pool_size...動(dòng)態(tài)調(diào)整以chunk為單位。操作心得動(dòng)態(tài)修改GLOBAL變量是臨時(shí)的服務(wù)重啟后會(huì)失效。永久修改必須在配置文件如/etc/my.cnf.d/server.cnf中的[mysqld]段下進(jìn)行例如[mysqld] wait_timeout 600 innodb_buffer_pool_size 2G修改配置文件后需要重啟MariaDB服務(wù)systemctl restart mariadb才能生效。任何重要的參數(shù)調(diào)整尤其是像innodb_buffer_pool_size這種最好先在測(cè)試環(huán)境驗(yàn)證。5.3 簡(jiǎn)單的性能排查流程一個(gè)實(shí)戰(zhàn)案例假設(shè)你收到告警數(shù)據(jù)庫(kù)服務(wù)器CPU使用率持續(xù)超過(guò)90%。你可以遵循以下流程快速排查連接數(shù)據(jù)庫(kù)mysql -u ops_admin -p查看實(shí)時(shí)活動(dòng)SHOW FULL PROCESSLIST;重點(diǎn)觀察Command不是Sleep且Time值很高的進(jìn)程。記錄下它們的Id和InfoSQL語(yǔ)句。分析可疑SQL對(duì)于找到的疑似慢SQL可以嘗試在其連接中執(zhí)行EXPLAIN [SQL語(yǔ)句]查看執(zhí)行計(jì)劃看是否全表掃描、索引使用不當(dāng)。檢查系統(tǒng)狀態(tài)SHOW GLOBAL STATUS LIKE Threads_running; -- 高則說(shuō)明并發(fā)高 SHOW GLOBAL STATUS LIKE Innodb_rows_read%; -- 查看行讀取量 SHOW ENGINE INNODB STATUS\G -- 獲取詳細(xì)的InnoDB狀態(tài)報(bào)告包含鎖信息、信號(hào)量等待等輸出很長(zhǎng)需要分析檢查鎖等待在SHOW ENGINE INNODB STATUS輸出的TRANSACTIONS部分可以查看當(dāng)前運(yùn)行的事務(wù)和鎖等待鏈。如果發(fā)現(xiàn)大量鎖等待可能是事務(wù)設(shè)計(jì)不合理或語(yǔ)句鎖住了過(guò)多資源。結(jié)合操作系統(tǒng)工具不要只看數(shù)據(jù)庫(kù)內(nèi)部。同時(shí)在Linux shell下用top或htop查看是否是mysqld進(jìn)程本身CPU高還是其他進(jìn)程。用iostat或vmstat查看磁盤I/O是否成為瓶頸。通過(guò)這一套組合拳你通常能快速定位問(wèn)題是出在某個(gè)失控的查詢SHOW PROCESSLIST、系統(tǒng)資源不足操作系統(tǒng)工具還是數(shù)據(jù)庫(kù)內(nèi)部競(jìng)爭(zhēng)InnoDB Status上。這就是將基礎(chǔ)命令串聯(lián)起來(lái)解決實(shí)際問(wèn)題的能力。