戰(zhàn)：從原理到踩坑全解析)
MySQL修改密碼和配置文件這兩個(gè)詞放在一起看起來(lái)是兩個(gè)獨(dú)立的小操作但實(shí)際工作中它們經(jīng)常被綁在一起處理。比如你剛裝完MySQL第一件事往往就是給root換個(gè)密碼又比如某個(gè)環(huán)境突然連接數(shù)爆了你第一反應(yīng)也是翻配置文件調(diào)max_connections。我這些年幫別人排查過(guò)不少數(shù)據(jù)庫(kù)問(wèn)題發(fā)現(xiàn)一大半所謂的數(shù)據(jù)庫(kù)連不上密碼過(guò)期端口被占最后都繞回到這兩件事上要么密碼根本沒改對(duì)要么配置文件壓根沒生效。這篇就基于我自己的實(shí)操經(jīng)驗(yàn)把修改密碼和配置文件這兩塊掰開揉碎講清楚從原理到命令、從踩坑到排查給你一條能直接照著走的完整路線。不管你是剛?cè)胄械暮蠖碎_發(fā)、運(yùn)維新人還是自己折騰服務(wù)器的小白這篇文章都能讓你少走幾趟彎路。我不會(huì)堆一堆無(wú)聊的參數(shù)表而是把那些真正決定成敗的細(xì)節(jié)、那些官方文檔里不會(huì)寫的坑全都給你翻出來(lái)。1. 修改密碼前必須先搞懂的幾個(gè)底層概念1.1 密碼到底存在哪兒改的是哪張表很多新手以為MySQL的密碼是個(gè)獨(dú)立的密鑰文件其實(shí)不是MySQL把用戶信息和密碼哈希都放在一個(gè)叫mysql.user的系統(tǒng)表里。當(dāng)你執(zhí)行改密命令時(shí)本質(zhì)上是更新這張表里對(duì)應(yīng)User和Host字段的authentication_string字段。這個(gè)Host字段特別關(guān)鍵很多人改密碼后死活連不上就是因?yàn)闆]注意到同一個(gè)用戶名可能對(duì)應(yīng)了好幾個(gè)Host記錄。舉個(gè)例子你執(zhí)行ALTER USER rootlocalhost IDENTIFIED BY newpass改的只是rootlocalhost這條記錄但如果你的客戶端是從其他機(jī)器連過(guò)來(lái)的數(shù)據(jù)庫(kù)匹配的可能是root%這條記錄。這時(shí)候你改完密碼自己本地連上去沒問(wèn)題但程序遠(yuǎn)程一連就報(bào)Access denied然后你開始懷疑人生。所以要養(yǎng)成習(xí)慣改密碼之前先查一下SELECT user, host, authentication_string FROM mysql.user WHERE user root;把這條記錄里所有的Host變體都看一遍再?zèng)Q定改哪條。這說(shuō)明一個(gè)道理MySQL的權(quán)限體系是按用戶名來(lái)源主機(jī)這個(gè)組合來(lái)區(qū)分用戶的同一個(gè)用戶名在不同Host下完全是兩個(gè)獨(dú)立賬號(hào)。1.2 密碼哈希的演進(jìn)與兼容性坑MySQL 5.7及之前版本默認(rèn)用mysql_native_password插件密碼的哈希值是40位十六進(jìn)制字符串。MySQL 8.0開始默認(rèn)換成了caching_sha2_password哈希串變成了64位而且認(rèn)證過(guò)程中的處理邏輯完全不同。這個(gè)改動(dòng)帶來(lái)了一個(gè)非常常見的兼容性問(wèn)題你拿著MySQL 8.0建的用戶用老版本的客戶端驅(qū)動(dòng)去連會(huì)報(bào)Authentication plugin caching_sha2_password cannot be loaded。我在實(shí)際項(xiàng)目中遇到過(guò)很多次這種問(wèn)題最典型的是老項(xiàng)目用的jar包連接MySQL 8.0數(shù)據(jù)庫(kù)。解決方案有兩個(gè)要么升級(jí)驅(qū)動(dòng)要么在建用戶或改密碼時(shí)指定用老認(rèn)證插件ALTER USER user% IDENTIFIED WITH mysql_native_password BY password;如果只是臨時(shí)讓某個(gè)賬號(hào)兼容舊客戶端這是個(gè)很快的解法。不過(guò)從長(zhǎng)期來(lái)看還是建議升級(jí)驅(qū)動(dòng)畢竟老插件早晚會(huì)被徹底移除。另外要注意mysql_native_password在MySQL 8.0里默認(rèn)是禁用插件狀態(tài)直接用IDENTIFIED WITH指定它有時(shí)候會(huì)報(bào)錯(cuò)需要先在配置文件里加上default_authentication_pluginmysql_native_password或通過(guò)安裝插件的方式啟用。各版本差異我整理成了下面這個(gè)表格版本默認(rèn)認(rèn)證插件密碼哈希長(zhǎng)度常見坑5.6mysql_native_password40位基本穩(wěn)定5.7mysql_native_password40位無(wú)顯著坑8.0caching_sha2_password64位老客戶端連不上8.4caching_sha2_password64位部分老插件被移除1.3 你執(zhí)行改密命令之后MySQL內(nèi)部發(fā)生了什么了解這一步會(huì)幫你理解為什么有時(shí)候命令都成功了但密碼還是沒變。當(dāng)你執(zhí)行ALTER USER語(yǔ)句時(shí)MySQL服務(wù)端會(huì)校驗(yàn)當(dāng)前登錄用戶是否有CREATE USER權(quán)限或足夠的管理權(quán)限解析你指定的認(rèn)證插件和明文密碼把明文密碼通過(guò)選定插件生成對(duì)應(yīng)的哈希摘要更新mysql.user表對(duì)應(yīng)記錄觸發(fā)內(nèi)存中權(quán)限緩存刷新注意第5步這是個(gè)大坑。如果會(huì)話的權(quán)限表緩存沒有刷新某些場(chǎng)景下可能仍然使用舊密碼完成認(rèn)證。實(shí)際上ALTER USER內(nèi)部會(huì)隱式刷新但如果是直接UPDATE mysql.user改哈希這個(gè)操作我不建議除非你知道自己在干什么就必須手動(dòng)執(zhí)行FLUSH PRIVILEGES。很多老教程會(huì)教人直接UPDATE user SET passwordPASSWORD(...),在8.0里PASSWORD()函數(shù)都沒了這條路徹底走不通。2. 配置文件才是真正的幕后黑手2.1 配置文件的優(yōu)先級(jí)和加載順序MySQL配置文件在Linux上通常叫my.cnf在Windows上叫my.ini。但同一臺(tái)機(jī)器上完全可能存在多個(gè)同名配置文件它們之間有嚴(yán)格的優(yōu)先級(jí)順序。啟動(dòng)時(shí)MySQL會(huì)按照這個(gè)順序讀取配置后讀取的會(huì)覆蓋先讀取的重復(fù)項(xiàng)/etc/my.cnf/etc/mysql/my.cnf$MYSQL_HOME/my.cnf~/.my.cnf搞清楚你當(dāng)前實(shí)例到底用的是哪個(gè)文件是我見過(guò)無(wú)數(shù)人踩坑的第一道坎。你以為改了/etc/my.cnf但實(shí)際MySQL可能因?yàn)槟硞€(gè)deb包安裝路徑的關(guān)系默認(rèn)加載的是/etc/mysql/mysql.conf.d/mysqld.cnf。更折騰的是你在命令行啟動(dòng)時(shí)還能直接指定--defaults-file/path/to/my.cnf如果之前用這個(gè)參數(shù)啟動(dòng)過(guò)實(shí)例那前面所有默認(rèn)路徑就統(tǒng)統(tǒng)失效只認(rèn)你指定那一個(gè)。判斷當(dāng)前實(shí)例加載了哪個(gè)配置文件最簡(jiǎn)單的辦法是登錄MySQL執(zhí)行SHOW VARIABLES LIKE basedir; SHOW VARIABLES LIKE datadir;或者直接看啟動(dòng)進(jìn)程ps aux | grep mysqld用這種方式能看到啟動(dòng)命令里有沒有帶--defaults-file參數(shù)。我建議一上來(lái)就養(yǎng)成習(xí)慣用my_print_defaults mysqld命令查看當(dāng)前環(huán)境下mysqld會(huì)讀取的配置項(xiàng)my_print_defaults mysqld這個(gè)命令會(huì)直接輸出配置項(xiàng)生效后的結(jié)果能幫你快速確認(rèn)哪些參數(shù)是從哪個(gè)文件來(lái)的。2.2 分組和參數(shù)生效規(guī)則配置文件里的內(nèi)容不是隨便平的它分了好幾個(gè)段section比如[client]、[mysql]、[mysqld]、[mysqldump]。不同程序啟動(dòng)時(shí)只讀取對(duì)應(yīng)段下的參數(shù)。這個(gè)設(shè)計(jì)經(jīng)常讓人鬧笑話有人把port3306寫在[mysql]段客戶端連的時(shí)候倒是能讀到端口但mysqld服務(wù)端根本不認(rèn)這個(gè)段的參數(shù)服務(wù)還是跑的默認(rèn)3306。[mysqld]是服務(wù)端核心段改端口、數(shù)據(jù)目錄、字符集、連接數(shù)這些都要放在這里。[client]段主要給命令行客戶端用比如你可以統(tǒng)一寫userroot省得每次敲命令還得指定用戶。[mysql]段針對(duì)交互式命令行客戶端。還有[mysqldump]段可以配置導(dǎo)出的默認(rèn)字符集。搞清楚這些區(qū)別你就能猜到為什么有時(shí)候配置看著對(duì)了實(shí)際卻不生效。另外一個(gè)需要知道的概念是并非所有參數(shù)都能動(dòng)態(tài)修改有些參數(shù)必須在配置文件中設(shè)置并重啟實(shí)例才能生效。MySQL把參數(shù)分成兩類動(dòng)態(tài)參數(shù)可以用SET GLOBAL實(shí)時(shí)改靜態(tài)參數(shù)只能改配置文件重啟。比如port就是靜態(tài)參數(shù)你在數(shù)據(jù)庫(kù)里執(zhí)行SET GLOBAL port3307會(huì)直接報(bào)錯(cuò)。再比如max_connections是動(dòng)態(tài)參數(shù)你可以先在線調(diào)大救急但要永久生效還是得寫進(jìn)配置文件。2.3 常見核心參數(shù)到底該怎么配配置文件里參數(shù)多如牛毛真正日常高頻會(huì)用到的核心參數(shù)其實(shí)就那些。我給出一套基于通用場(chǎng)景的推薦模板注意這是基于常見實(shí)踐的推薦實(shí)際取值要看機(jī)器配置[mysqld] port3306 datadir/var/lib/mysql socket/var/lib/mysql/mysql.sock max_connections500 max_connect_errors1000 character_set_serverutf8mb4 collation_serverutf8mb4_unicode_ci innodb_buffer_pool_size2G innodb_flush_log_at_trx_commit1 slow_query_log1 slow_query_log_file/var/log/mysql/slow.log long_query_time2max_connections這個(gè)值不是越大越好。每建一個(gè)連接MySQL都要給線程棧、臨時(shí)表分配內(nèi)存。如果機(jī)器只有4G內(nèi)存卻把連接數(shù)拉到2000一旦真來(lái)2000個(gè)并發(fā)連接內(nèi)存瞬間被吃掉一大半反而拖垮數(shù)據(jù)庫(kù)性能。通常我按(可用內(nèi)存 - 系統(tǒng)預(yù)留 - InnoDB緩沖池大小) / 單連接內(nèi)存開銷來(lái)估算單個(gè)連接內(nèi)存開銷大約在幾百KB到1MB之間壓力測(cè)試的時(shí)候要專門驗(yàn)證。innodb_buffer_pool_size是InnoDB引擎的緩存池用來(lái)緩存數(shù)據(jù)頁(yè)和索引頁(yè)。原則上在總內(nèi)存里給實(shí)例預(yù)留出操作系統(tǒng)和其他服務(wù)的空間后這個(gè)值可以占到內(nèi)存的70%左右。但如果你的機(jī)器上還跑了別的服務(wù)就要克制一點(diǎn)別把內(nèi)存全吃干凈。character_set_server和collation_server是字符集和排序規(guī)則強(qiáng)烈建議從初始化就固定成utf8mb4不要用utf8。為什么因?yàn)镸ySQL的utf8其實(shí)是utf8mb3它最多只支持3字節(jié)字符像emoji這種4字節(jié)字符會(huì)直接存不進(jìn)去或者變成亂碼。你用utf8mb4不僅完全兼容utf8還能多支持emoji和生僻字沒有理由不用它。3. 完整實(shí)操?gòu)耐況oot密碼到配置調(diào)優(yōu)3.1 模擬項(xiàng)目環(huán)境準(zhǔn)備為了讓你有代入感我拿一個(gè)典型的模擬項(xiàng)目環(huán)境來(lái)說(shuō)事。某公司的運(yùn)營(yíng)后臺(tái)系統(tǒng)用的是MySQL 5.7部署在一臺(tái)Linux服務(wù)器上數(shù)據(jù)庫(kù)除了本地程序訪問(wèn)還允許運(yùn)維網(wǎng)段的某臺(tái)跳板機(jī)遠(yuǎn)程連接。前幾天工作人員告訴我root密碼找不到了而且程序連接數(shù)一直告警。接到這個(gè)任務(wù)我腦子里已經(jīng)有了完整操作路徑。首先要做的不是摸黑干活而是先確認(rèn)版本、配置文件路徑和啟動(dòng)方式mysql --version ps aux | grep mysqld ls -l /etc/my.cnf /etc/mysql/my.cnf確認(rèn)版本是5.7進(jìn)程啟動(dòng)命令是mysqld --defaults-file/etc/my.cnf,這就說(shuō)明生效的配置文件大概率是/etc/my.cnf。確認(rèn)這些之后再開始處理密碼問(wèn)題。3.2 場(chǎng)景A忘記root密碼的應(yīng)急修改忘記root密碼是每個(gè)DBA和全棧工程師都會(huì)遇到的經(jīng)典場(chǎng)景。如果是內(nèi)部開發(fā)環(huán)境最常規(guī)的就是通過(guò)跳過(guò)權(quán)限認(rèn)證的方式啟動(dòng)MySQL進(jìn)去之后重新設(shè)置密碼。詳細(xì)步驟如下第一步先停掉MySQL服務(wù)。不同系統(tǒng)的服務(wù)名不一樣Debian系一般是mysqlCentOS系一般是mysqldsystemctl stop mysqld如果你是在容器里跑的就暫停容器或者直接停掉進(jìn)程。第二步修改配置文件在[mysqld]段下加上這么一行skip-grant-tables這一步的作用是讓MySQL在啟動(dòng)時(shí)跳過(guò)所有權(quán)限驗(yàn)證任何人連上來(lái)都是超級(jí)管理員所以操作要快且僅限于本機(jī)臨時(shí)方案絕對(duì)不能暴露到外網(wǎng)。第三步啟動(dòng)MySQL服務(wù)然后無(wú)密碼登錄systemctl start mysqld mysql -u root如果連不進(jìn)去檢查一下socket文件路徑是否正?；蛘哂胢ysql -u root -h 127.0.0.1配合TCP方式連接。第四步登錄后執(zhí)行改密。在MySQL 5.7里因?yàn)槟J(rèn)還可以用mysql_native_password可以用FLUSH PRIVILEGES; ALTER USER rootlocalhost IDENTIFIED BY NewStrongPass123;注意這里為什么必須先FLUSH PRIVILEGES因?yàn)樵趕kip-grant-tables模式下MySQL不加載權(quán)限表直接ALTER USER可能會(huì)報(bào)錯(cuò)或者不生效先刷新權(quán)限讓MySQL重新讀取權(quán)限表后面操作才穩(wěn)。也有直接先UPDATE mysql.user SET authentication_stringPASSWORD(...)的寫法但PASSWORD()函數(shù)在新版本已經(jīng)廢棄所以要么用ALTER USER要么在5.7里用SET PASSWORD FOR rootlocalhostPASSWORD(...)而在8.0里就統(tǒng)一用ALTER USER。第五步千萬(wàn)記得把配置文件里加的skip-grant-tables注釋掉或刪掉然后重啟MySQLsystemctl restart mysqld mysql -u root -p這一步很多人會(huì)忘導(dǎo)致數(shù)據(jù)庫(kù)一直裸奔在無(wú)認(rèn)證狀態(tài)這是個(gè)高危風(fēng)險(xiǎn)操作。我用一個(gè)表格把整個(gè)流程整理出來(lái)步驟操作目的1關(guān)閉mysqld釋放文件鎖安全變更2配置skip-grant-tables臨時(shí)跳過(guò)權(quán)限認(rèn)證3無(wú)密碼登錄進(jìn)入管理員會(huì)話4FLUSH PRIVILEGES ALTER USER重新加載權(quán)限表并改密碼5還原配置并重啟恢復(fù)正常安全模式3.3 場(chǎng)景B正常修改業(yè)務(wù)賬號(hào)密碼如果沒忘記密碼只是例行輪換或者程序要換賬號(hào)那就完全用不著跳過(guò)權(quán)限表。直接登錄進(jìn)去執(zhí)行ALTER USER就行但里面有幾個(gè)細(xì)節(jié)值得說(shuō)。比如我想把某個(gè)業(yè)務(wù)賬號(hào)的密碼改掉ALTER USER app_user% IDENTIFIED BY App2024Secret;如果要同時(shí)改密碼和認(rèn)證插件可以這么寫ALTER USER app_user% IDENTIFIED WITH mysql_native_password BY App2024Secret;還有一個(gè)容易被忽視的點(diǎn)在MySQL 5.7及以上版本密碼是有過(guò)期策略的。如果你在配置里開啟了default_password_lifetime或者單獨(dú)給某個(gè)賬號(hào)設(shè)置了PASSWORD EXPIRE那么密碼到期后客戶端就會(huì)報(bào)Your password has expired除非你讓賬號(hào)永不過(guò)期ALTER USER app_user% IDENTIFIED BY App2024Secret PASSWORD EXPIRE NEVER;業(yè)務(wù)程序里如果用的是連接池改完密碼之后不能立刻覺得萬(wàn)事大吉。連接池里的舊連接可能在密碼修改前已經(jīng)建立這些連接并不會(huì)因?yàn)槊艽a變了就斷開還能繼續(xù)跑一段時(shí)間。但如果連接池里的連接被回收后新建連接新連接就會(huì)用新密碼去認(rèn)證一旦程序配置里的密碼沒同步改就會(huì)看到間斷性的Access denied錯(cuò)誤。所以正確的操作順序是先改程序里的數(shù)據(jù)庫(kù)配置再改數(shù)據(jù)庫(kù)密碼最后滾動(dòng)重啟應(yīng)用服務(wù)。3.4 順帶把連接數(shù)告警一起解決掉密碼問(wèn)題處理完接下來(lái)看連接數(shù)告警。先登錄數(shù)據(jù)庫(kù)查看當(dāng)前連接狀況SHOW STATUS LIKE Threads_connected; SHOW VARIABLES LIKE max_connections;發(fā)現(xiàn)當(dāng)前連接已經(jīng)480而max_connections只有500確實(shí)快滿了。這種場(chǎng)景先用動(dòng)態(tài)參數(shù)把連接數(shù)臨時(shí)拉高但參數(shù)調(diào)完之后還得確認(rèn)是不是有連接沒有被合理釋放排查有沒有慢查詢堆積。先臨時(shí)調(diào)整SET GLOBAL max_connections 800;這個(gè)命令實(shí)時(shí)生效不用重啟但重啟后就沒了。要永久生效還得在/etc/my.cnf的[mysqld]段里加上max_connections800。注意不能只改配置文件不重啟也不能只動(dòng)態(tài)改不寫文件兩者必須同時(shí)做否則下次重啟又會(huì)回到老配置。除了調(diào)連接數(shù)我還順手看了一下慢查詢?nèi)罩竞蚿rocesslistSHOW FULL PROCESSLIST;發(fā)現(xiàn)有幾條SQL跑了好幾十秒才導(dǎo)致連接堆積。這就靠slow_query_log和long_query_time設(shè)置得合理才能及時(shí)發(fā)現(xiàn)。很多生產(chǎn)事故前期都是連接數(shù)緩慢爬坡本質(zhì)是SQL性能太差光調(diào)大連接數(shù)等于治標(biāo)不治本。3.5 配置文件修改后的啟動(dòng)校驗(yàn)改完配置文件最怕的就是參數(shù)寫錯(cuò)導(dǎo)致MySQL啟動(dòng)失敗。如果你運(yùn)氣好語(yǔ)法錯(cuò)誤會(huì)在啟動(dòng)時(shí)報(bào)出來(lái)并給出具體的錯(cuò)誤行。但有些參數(shù)的值不合規(guī)可能MySQL只是警告一下其中某些參數(shù)可能直接忽略某些參數(shù)導(dǎo)致啟動(dòng)失敗。比如把max_connections設(shè)置成abc啟動(dòng)會(huì)直接報(bào)錯(cuò)把port設(shè)置成一個(gè)已被占用的端口也會(huì)啟動(dòng)失敗。在啟動(dòng)之前強(qiáng)烈建議先用以下方式校驗(yàn)配置mysqld --validate-config --defaults-file/etc/my.cnf這個(gè)命令能幫你檢查配置文件里有沒有語(yǔ)法錯(cuò)誤、有沒有未知參數(shù)、參數(shù)取值是否合法。注意不同版本對(duì)--validate-config的支持略有差異在8.0里通常寫作mysqld --validate-config在5.7里可能需要先確認(rèn)。如果這個(gè)命令沒有輸出基本說(shuō)明配置層面沒問(wèn)題可以放心啟動(dòng)。啟動(dòng)后再次確認(rèn)參數(shù)是否真的加載了mysql -u root -p -e SHOW VARIABLES LIKE max_connections; SHOW VARIABLES LIKE port;如果發(fā)現(xiàn)啟動(dòng)值和配置里寫的不一樣先懷疑是不是加載了別的配置文件用前面說(shuō)的my_print_defaults去查。4. 我踩過(guò)的坑常見問(wèn)題與排查技巧實(shí)錄4.1 改完密碼后程序連續(xù)報(bào)Access denied這是一個(gè)高頻問(wèn)題。最常見的原因是改密碼時(shí)沒有把host匹配規(guī)則考慮進(jìn)去。比如程序連接串里寫的是jdbc:mysql://dbhost:3306/appdb但MySQL里創(chuàng)建的是app_userlocalhost這樣遠(yuǎn)程連接就會(huì)被拒絕。排查步驟如下先確認(rèn)程序是從哪臺(tái)機(jī)器、用什么賬號(hào)連的在數(shù)據(jù)庫(kù)里執(zhí)行SELECT user, host FROM mysql.user;看有沒有匹配記錄如果只有l(wèi)ocalhost記錄就新建一條app_user%或用具體IP創(chuàng)建再檢查賬號(hào)的認(rèn)證插件MySQL 8.0對(duì)齊一下客戶端驅(qū)動(dòng)版本另外一個(gè)隱蔽原因是密碼中包含了特殊字符比如分號(hào)或反斜杠。在命令行里執(zhí)行ALTER USER ... IDENTIFIED BY a;b通常沒問(wèn)題但在shell腳本里如果字符串拼接沒處理好后半段可能會(huì)被當(dāng)成新的SQL語(yǔ)句。所以建議密碼盡量不要包含;、、\這些需要多轉(zhuǎn)義一層的字符或者在腳本里先變量化再執(zhí)行。4.2 配置文件改了但不生效這是最折磨人的問(wèn)題明明文件里改了端口重啟后還是3306。按照我的經(jīng)驗(yàn)優(yōu)先級(jí)依次排查這幾件事啟動(dòng)命令是否帶--defaults-file參數(shù)指定了別的文件當(dāng)前系統(tǒng)加載的配置文件到底是不是你改的那個(gè)用my_print_defaults驗(yàn)證參數(shù)是否放到了正確的段里比如把max_connections放在了[client]段那服務(wù)端根本不讀是否因?yàn)閰?shù)屬于只讀啟動(dòng)參數(shù)且由于某些版本差異動(dòng)態(tài)修改被之后的重啟覆蓋是否改完配置文件之后服務(wù)沒有真正重啟只是停在了那里有個(gè)很典型的案例某次我在一臺(tái)機(jī)器上改了端口從3306改成3307重啟后依然是3306。排查發(fā)現(xiàn)是啟動(dòng)腳本里先寫死了--port3306命令行參數(shù)優(yōu)先于配置文件配置怎么改都沒用。以后遇到配置不生效的現(xiàn)象先看ps aux | grep mysqld的完整參數(shù)列表能省下很多時(shí)間。4.3 使用skip-grant-tables模式后無(wú)法正常登錄有人跳過(guò)權(quán)限驗(yàn)證后執(zhí)行ALTER USER時(shí)報(bào)錯(cuò)Operation requires elevated privileges。這個(gè)原因我前面說(shuō)過(guò)了skip-grant-tables模式下權(quán)限系統(tǒng)未加載需要先FLUSH PRIVILEGES讓權(quán)限表加載起來(lái)。另外在這種模式下MySQL可能拒絕某些賬號(hào)的遠(yuǎn)程連接因?yàn)樗虞d不到權(quán)限數(shù)據(jù)時(shí)行為會(huì)比較保守所以建議全程只在本機(jī)操作socket連接。還有個(gè)小細(xì)節(jié)在skip-grant-tables模式下如果你要重啟服務(wù)千萬(wàn)確認(rèn)配置文件里那一行已經(jīng)被注釋否則服務(wù)啟動(dòng)后又會(huì)跳過(guò)權(quán)限驗(yàn)證。有些老手會(huì)在操作后忘記這個(gè)步驟導(dǎo)致數(shù)據(jù)庫(kù)在公網(wǎng)上裸奔這是不能接受的安全風(fēng)險(xiǎn)。4.4 常見問(wèn)題速查表問(wèn)題現(xiàn)象最可能原因快速處理Access denied using password YES密碼錯(cuò)或host不匹配核對(duì)用戶host組合重設(shè)密碼Authentication plugin cannot be loaded客戶端驅(qū)動(dòng)太老升級(jí)驅(qū)動(dòng)或改用mysql_native_passwordYour password has expired密碼過(guò)期策略觸發(fā)ALTER USER ... PASSWORD EXPIRE NEVER改完配置重啟不生效加載了別的配置文件用ps和my_print_defaults確認(rèn)連接數(shù)到了max新連接等待慢查詢堆積或參數(shù)過(guò)小臨時(shí)SET GLOBAL調(diào)大優(yōu)化SQLUnknown variable xxx參數(shù)名寫錯(cuò)或版本不支持對(duì)照官方文檔檢查參數(shù)名Table mysql.user doesnt exist數(shù)據(jù)目錄損壞或初始化未完成備份數(shù)據(jù)后重新初始化系統(tǒng)庫(kù)5. 關(guān)于改密和配置的最佳實(shí)踐建議5.1 密碼管理和賬號(hào)權(quán)限的最小化無(wú)論你是給root改密碼還是給業(yè)務(wù)賬號(hào)設(shè)密碼都建議遵循最小化授權(quán)原則。日常開發(fā)調(diào)試可以不用root而應(yīng)該創(chuàng)建獨(dú)立的業(yè)務(wù)賬號(hào)只授予它需要的庫(kù)表權(quán)限。比如CREATE USER app_read% IDENTIFIED BY ReadOnly123; GRANT SELECT ON appdb.* TO app_read%; CREATE USER app_write% IDENTIFIED BY WriteOnly123; GRANT INSERT, UPDATE, DELETE ON appdb.* TO app_write%;這樣就算某個(gè)賬號(hào)密碼泄露影響范圍也被控制在較小邊界內(nèi)。同時(shí)密碼本身盡量做到復(fù)雜且唯一不要在多個(gè)環(huán)境復(fù)用同一個(gè)密碼。生產(chǎn)環(huán)境建議用密碼管理器生成隨機(jī)串并把密碼輪換做成定期任務(wù)而不是等到出了問(wèn)題再改。5.2 配置文件管理的版本化和變更流程配置文件是數(shù)據(jù)庫(kù)的生命線改之前強(qiáng)烈建議先備份一份帶時(shí)間戳的副本cp /etc/my.cnf /etc/my.cnf.bak.$(date %F_%H-%M-%S)如果改完出了問(wèn)題可以秒級(jí)回滾。更進(jìn)一步建議用代碼倉(cāng)庫(kù)管理配置模板把開發(fā)、測(cè)試、生產(chǎn)三套配置文件都納入版本管理但每套環(huán)境的密碼和密鑰要單獨(dú)通過(guò)環(huán)境變量或secrets管理不要直接寫死在配置里。變更之前先規(guī)劃好變更窗口確認(rèn)變更影響面。動(dòng)態(tài)參數(shù)可以先在線修改驗(yàn)證靜態(tài)參數(shù)則需要計(jì)劃維護(hù)窗口重啟。一個(gè)穩(wěn)妥的流程是先在測(cè)試環(huán)境完整執(zhí)行一遍變更腳本確認(rèn)無(wú)誤后再上生產(chǎn)執(zhí)行同樣步驟。5.3 提升穩(wěn)定性的幾個(gè)順手配置最后分享幾個(gè)我實(shí)際用下來(lái)能顯著減少問(wèn)題的配置項(xiàng)[mysqld] skip_name_resolveON log_error_verbosity3 max_allowed_packet64M wait_timeout600 interactive_timeout600skip_name_resolve讓MySQL不對(duì)客戶端IP做反向DNS解析可以避免因?yàn)镈NS抖動(dòng)導(dǎo)致連接緩慢但要注意它要求所有授權(quán)記錄里寫的都是IP而不是主機(jī)名。max_allowed_packet調(diào)大一些可以防止導(dǎo)入數(shù)據(jù)或大SQL報(bào)錯(cuò)。wait_timeout和interactive_timeout則控制非交互連接和交互連接的閑置超時(shí)時(shí)間避免大量空閑連接占著連接數(shù)不放。還有一個(gè)容易被忽略的小技巧如果你同時(shí)在維護(hù)多個(gè)MySQL實(shí)例建議每個(gè)實(shí)例都單獨(dú)建一份配置文件用--defaults-file指向各自文件這樣互不干擾。實(shí)例之間可以通過(guò)不同的端口和socket路徑區(qū)分配置隔離避免改了一個(gè)實(shí)例影響了另一個(gè)實(shí)例。我個(gè)人在實(shí)際操作中最深的體會(huì)是改密碼和調(diào)配置看起來(lái)是零碎的小活但它們最考驗(yàn)一個(gè)人對(duì)MySQL運(yùn)行機(jī)制的理解。你理解了權(quán)限表的匹配規(guī)則、理解了配置文件的加載優(yōu)先級(jí)、理解了靜態(tài)參數(shù)和動(dòng)態(tài)參數(shù)的區(qū)別再遇到那些亂七八糟的連不上、不生效問(wèn)題基本都能在幾分鐘內(nèi)定位到根因。希望這篇文章里記下的這些操作過(guò)程和踩坑記錄能幫你少走一些我當(dāng)年走過(guò)的彎路。 MySQL修改密碼與配置文件實(shí)戰(zhàn)：從原理到踩坑全解析
MySQL修改密碼和配置文件這兩個(gè)詞放在一起看起來(lái)是兩個(gè)獨(dú)立的小操作但實(shí)際工作中它們經(jīng)常被綁在一起處理。比如你剛裝完MySQL第一件事往往就是給root換個(gè)密碼又比如某個(gè)環(huán)境突然連接數(shù)爆了你第一反應(yīng)也是翻配置文件調(diào)max_connections。我這些年幫別人排查過(guò)不少數(shù)據(jù)庫(kù)問(wèn)題發(fā)現(xiàn)一大半所謂的數(shù)據(jù)庫(kù)連不上密碼過(guò)期端口被占最后都繞回到這兩件事上要么密碼根本沒改對(duì)要么配置文件壓根沒生效。這篇就基于我自己的實(shí)操經(jīng)驗(yàn)把修改密碼和配置文件這兩塊掰開揉碎講清楚從原理到命令、從踩坑到排查給你一條能直接照著走的完整路線。不管你是剛?cè)胄械暮蠖碎_發(fā)、運(yùn)維新人還是自己折騰服務(wù)器的小白這篇文章都能讓你少走幾趟彎路。我不會(huì)堆一堆無(wú)聊的參數(shù)表而是把那些真正決定成敗的細(xì)節(jié)、那些官方文檔里不會(huì)寫的坑全都給你翻出來(lái)。1. 修改密碼前必須先搞懂的幾個(gè)底層概念1.1 密碼到底存在哪兒改的是哪張表很多新手以為MySQL的密碼是個(gè)獨(dú)立的密鑰文件其實(shí)不是MySQL把用戶信息和密碼哈希都放在一個(gè)叫mysql.user的系統(tǒng)表里。當(dāng)你執(zhí)行改密命令時(shí)本質(zhì)上是更新這張表里對(duì)應(yīng)User和Host字段的authentication_string字段。這個(gè)Host字段特別關(guān)鍵很多人改密碼后死活連不上就是因?yàn)闆]注意到同一個(gè)用戶名可能對(duì)應(yīng)了好幾個(gè)Host記錄。舉個(gè)例子你執(zhí)行ALTER USER rootlocalhost IDENTIFIED BY newpass改的只是rootlocalhost這條記錄但如果你的客戶端是從其他機(jī)器連過(guò)來(lái)的數(shù)據(jù)庫(kù)匹配的可能是root%這條記錄。這時(shí)候你改完密碼自己本地連上去沒問(wèn)題但程序遠(yuǎn)程一連就報(bào)Access denied然后你開始懷疑人生。所以要養(yǎng)成習(xí)慣改密碼之前先查一下SELECT user, host, authentication_string FROM mysql.user WHERE user root;把這條記錄里所有的Host變體都看一遍再?zèng)Q定改哪條。這說(shuō)明一個(gè)道理MySQL的權(quán)限體系是按用戶名來(lái)源主機(jī)這個(gè)組合來(lái)區(qū)分用戶的同一個(gè)用戶名在不同Host下完全是兩個(gè)獨(dú)立賬號(hào)。1.2 密碼哈希的演進(jìn)與兼容性坑MySQL 5.7及之前版本默認(rèn)用mysql_native_password插件密碼的哈希值是40位十六進(jìn)制字符串。MySQL 8.0開始默認(rèn)換成了caching_sha2_password哈希串變成了64位而且認(rèn)證過(guò)程中的處理邏輯完全不同。這個(gè)改動(dòng)帶來(lái)了一個(gè)非常常見的兼容性問(wèn)題你拿著MySQL 8.0建的用戶用老版本的客戶端驅(qū)動(dòng)去連會(huì)報(bào)Authentication plugin caching_sha2_password cannot be loaded。我在實(shí)際項(xiàng)目中遇到過(guò)很多次這種問(wèn)題最典型的是老項(xiàng)目用的jar包連接MySQL 8.0數(shù)據(jù)庫(kù)。解決方案有兩個(gè)要么升級(jí)驅(qū)動(dòng)要么在建用戶或改密碼時(shí)指定用老認(rèn)證插件ALTER USER user% IDENTIFIED WITH mysql_native_password BY password;如果只是臨時(shí)讓某個(gè)賬號(hào)兼容舊客戶端這是個(gè)很快的解法。不過(guò)從長(zhǎng)期來(lái)看還是建議升級(jí)驅(qū)動(dòng)畢竟老插件早晚會(huì)被徹底移除。另外要注意mysql_native_password在MySQL 8.0里默認(rèn)是禁用插件狀態(tài)直接用IDENTIFIED WITH指定它有時(shí)候會(huì)報(bào)錯(cuò)需要先在配置文件里加上default_authentication_pluginmysql_native_password或通過(guò)安裝插件的方式啟用。各版本差異我整理成了下面這個(gè)表格版本默認(rèn)認(rèn)證插件密碼哈希長(zhǎng)度常見坑5.6mysql_native_password40位基本穩(wěn)定5.7mysql_native_password40位無(wú)顯著坑8.0caching_sha2_password64位老客戶端連不上8.4caching_sha2_password64位部分老插件被移除1.3 你執(zhí)行改密命令之后MySQL內(nèi)部發(fā)生了什么了解這一步會(huì)幫你理解為什么有時(shí)候命令都成功了但密碼還是沒變。當(dāng)你執(zhí)行ALTER USER語(yǔ)句時(shí)MySQL服務(wù)端會(huì)校驗(yàn)當(dāng)前登錄用戶是否有CREATE USER權(quán)限或足夠的管理權(quán)限解析你指定的認(rèn)證插件和明文密碼把明文密碼通過(guò)選定插件生成對(duì)應(yīng)的哈希摘要更新mysql.user表對(duì)應(yīng)記錄觸發(fā)內(nèi)存中權(quán)限緩存刷新注意第5步這是個(gè)大坑。如果會(huì)話的權(quán)限表緩存沒有刷新某些場(chǎng)景下可能仍然使用舊密碼完成認(rèn)證。實(shí)際上ALTER USER內(nèi)部會(huì)隱式刷新但如果是直接UPDATE mysql.user改哈希這個(gè)操作我不建議除非你知道自己在干什么就必須手動(dòng)執(zhí)行FLUSH PRIVILEGES。很多老教程會(huì)教人直接UPDATE user SET passwordPASSWORD(...),在8.0里PASSWORD()函數(shù)都沒了這條路徹底走不通。2. 配置文件才是真正的幕后黑手2.1 配置文件的優(yōu)先級(jí)和加載順序MySQL配置文件在Linux上通常叫my.cnf在Windows上叫my.ini。但同一臺(tái)機(jī)器上完全可能存在多個(gè)同名配置文件它們之間有嚴(yán)格的優(yōu)先級(jí)順序。啟動(dòng)時(shí)MySQL會(huì)按照這個(gè)順序讀取配置后讀取的會(huì)覆蓋先讀取的重復(fù)項(xiàng)/etc/my.cnf/etc/mysql/my.cnf$MYSQL_HOME/my.cnf~/.my.cnf搞清楚你當(dāng)前實(shí)例到底用的是哪個(gè)文件是我見過(guò)無(wú)數(shù)人踩坑的第一道坎。你以為改了/etc/my.cnf但實(shí)際MySQL可能因?yàn)槟硞€(gè)deb包安裝路徑的關(guān)系默認(rèn)加載的是/etc/mysql/mysql.conf.d/mysqld.cnf。更折騰的是你在命令行啟動(dòng)時(shí)還能直接指定--defaults-file/path/to/my.cnf如果之前用這個(gè)參數(shù)啟動(dòng)過(guò)實(shí)例那前面所有默認(rèn)路徑就統(tǒng)統(tǒng)失效只認(rèn)你指定那一個(gè)。判斷當(dāng)前實(shí)例加載了哪個(gè)配置文件最簡(jiǎn)單的辦法是登錄MySQL執(zhí)行SHOW VARIABLES LIKE basedir; SHOW VARIABLES LIKE datadir;或者直接看啟動(dòng)進(jìn)程ps aux | grep mysqld用這種方式能看到啟動(dòng)命令里有沒有帶--defaults-file參數(shù)。我建議一上來(lái)就養(yǎng)成習(xí)慣用my_print_defaults mysqld命令查看當(dāng)前環(huán)境下mysqld會(huì)讀取的配置項(xiàng)my_print_defaults mysqld這個(gè)命令會(huì)直接輸出配置項(xiàng)生效后的結(jié)果能幫你快速確認(rèn)哪些參數(shù)是從哪個(gè)文件來(lái)的。2.2 分組和參數(shù)生效規(guī)則配置文件里的內(nèi)容不是隨便平的它分了好幾個(gè)段section比如[client]、[mysql]、[mysqld]、[mysqldump]。不同程序啟動(dòng)時(shí)只讀取對(duì)應(yīng)段下的參數(shù)。這個(gè)設(shè)計(jì)經(jīng)常讓人鬧笑話有人把port3306寫在[mysql]段客戶端連的時(shí)候倒是能讀到端口但mysqld服務(wù)端根本不認(rèn)這個(gè)段的參數(shù)服務(wù)還是跑的默認(rèn)3306。[mysqld]是服務(wù)端核心段改端口、數(shù)據(jù)目錄、字符集、連接數(shù)這些都要放在這里。[client]段主要給命令行客戶端用比如你可以統(tǒng)一寫userroot省得每次敲命令還得指定用戶。[mysql]段針對(duì)交互式命令行客戶端。還有[mysqldump]段可以配置導(dǎo)出的默認(rèn)字符集。搞清楚這些區(qū)別你就能猜到為什么有時(shí)候配置看著對(duì)了實(shí)際卻不生效。另外一個(gè)需要知道的概念是并非所有參數(shù)都能動(dòng)態(tài)修改有些參數(shù)必須在配置文件中設(shè)置并重啟實(shí)例才能生效。MySQL把參數(shù)分成兩類動(dòng)態(tài)參數(shù)可以用SET GLOBAL實(shí)時(shí)改靜態(tài)參數(shù)只能改配置文件重啟。比如port就是靜態(tài)參數(shù)你在數(shù)據(jù)庫(kù)里執(zhí)行SET GLOBAL port3307會(huì)直接報(bào)錯(cuò)。再比如max_connections是動(dòng)態(tài)參數(shù)你可以先在線調(diào)大救急但要永久生效還是得寫進(jìn)配置文件。2.3 常見核心參數(shù)到底該怎么配配置文件里參數(shù)多如牛毛真正日常高頻會(huì)用到的核心參數(shù)其實(shí)就那些。我給出一套基于通用場(chǎng)景的推薦模板注意這是基于常見實(shí)踐的推薦實(shí)際取值要看機(jī)器配置[mysqld] port3306 datadir/var/lib/mysql socket/var/lib/mysql/mysql.sock max_connections500 max_connect_errors1000 character_set_serverutf8mb4 collation_serverutf8mb4_unicode_ci innodb_buffer_pool_size2G innodb_flush_log_at_trx_commit1 slow_query_log1 slow_query_log_file/var/log/mysql/slow.log long_query_time2max_connections這個(gè)值不是越大越好。每建一個(gè)連接MySQL都要給線程棧、臨時(shí)表分配內(nèi)存。如果機(jī)器只有4G內(nèi)存卻把連接數(shù)拉到2000一旦真來(lái)2000個(gè)并發(fā)連接內(nèi)存瞬間被吃掉一大半反而拖垮數(shù)據(jù)庫(kù)性能。通常我按(可用內(nèi)存 - 系統(tǒng)預(yù)留 - InnoDB緩沖池大小) / 單連接內(nèi)存開銷來(lái)估算單個(gè)連接內(nèi)存開銷大約在幾百KB到1MB之間壓力測(cè)試的時(shí)候要專門驗(yàn)證。innodb_buffer_pool_size是InnoDB引擎的緩存池用來(lái)緩存數(shù)據(jù)頁(yè)和索引頁(yè)。原則上在總內(nèi)存里給實(shí)例預(yù)留出操作系統(tǒng)和其他服務(wù)的空間后這個(gè)值可以占到內(nèi)存的70%左右。但如果你的機(jī)器上還跑了別的服務(wù)就要克制一點(diǎn)別把內(nèi)存全吃干凈。character_set_server和collation_server是字符集和排序規(guī)則強(qiáng)烈建議從初始化就固定成utf8mb4不要用utf8。為什么因?yàn)镸ySQL的utf8其實(shí)是utf8mb3它最多只支持3字節(jié)字符像emoji這種4字節(jié)字符會(huì)直接存不進(jìn)去或者變成亂碼。你用utf8mb4不僅完全兼容utf8還能多支持emoji和生僻字沒有理由不用它。3. 完整實(shí)操?gòu)耐況oot密碼到配置調(diào)優(yōu)3.1 模擬項(xiàng)目環(huán)境準(zhǔn)備為了讓你有代入感我拿一個(gè)典型的模擬項(xiàng)目環(huán)境來(lái)說(shuō)事。某公司的運(yùn)營(yíng)后臺(tái)系統(tǒng)用的是MySQL 5.7部署在一臺(tái)Linux服務(wù)器上數(shù)據(jù)庫(kù)除了本地程序訪問(wèn)還允許運(yùn)維網(wǎng)段的某臺(tái)跳板機(jī)遠(yuǎn)程連接。前幾天工作人員告訴我root密碼找不到了而且程序連接數(shù)一直告警。接到這個(gè)任務(wù)我腦子里已經(jīng)有了完整操作路徑。首先要做的不是摸黑干活而是先確認(rèn)版本、配置文件路徑和啟動(dòng)方式mysql --version ps aux | grep mysqld ls -l /etc/my.cnf /etc/mysql/my.cnf確認(rèn)版本是5.7進(jìn)程啟動(dòng)命令是mysqld --defaults-file/etc/my.cnf,這就說(shuō)明生效的配置文件大概率是/etc/my.cnf。確認(rèn)這些之后再開始處理密碼問(wèn)題。3.2 場(chǎng)景A忘記root密碼的應(yīng)急修改忘記root密碼是每個(gè)DBA和全棧工程師都會(huì)遇到的經(jīng)典場(chǎng)景。如果是內(nèi)部開發(fā)環(huán)境最常規(guī)的就是通過(guò)跳過(guò)權(quán)限認(rèn)證的方式啟動(dòng)MySQL進(jìn)去之后重新設(shè)置密碼。詳細(xì)步驟如下第一步先停掉MySQL服務(wù)。不同系統(tǒng)的服務(wù)名不一樣Debian系一般是mysqlCentOS系一般是mysqldsystemctl stop mysqld如果你是在容器里跑的就暫停容器或者直接停掉進(jìn)程。第二步修改配置文件在[mysqld]段下加上這么一行skip-grant-tables這一步的作用是讓MySQL在啟動(dòng)時(shí)跳過(guò)所有權(quán)限驗(yàn)證任何人連上來(lái)都是超級(jí)管理員所以操作要快且僅限于本機(jī)臨時(shí)方案絕對(duì)不能暴露到外網(wǎng)。第三步啟動(dòng)MySQL服務(wù)然后無(wú)密碼登錄systemctl start mysqld mysql -u root如果連不進(jìn)去檢查一下socket文件路徑是否正?；蛘哂胢ysql -u root -h 127.0.0.1配合TCP方式連接。第四步登錄后執(zhí)行改密。在MySQL 5.7里因?yàn)槟J(rèn)還可以用mysql_native_password可以用FLUSH PRIVILEGES; ALTER USER rootlocalhost IDENTIFIED BY NewStrongPass123;注意這里為什么必須先FLUSH PRIVILEGES因?yàn)樵趕kip-grant-tables模式下MySQL不加載權(quán)限表直接ALTER USER可能會(huì)報(bào)錯(cuò)或者不生效先刷新權(quán)限讓MySQL重新讀取權(quán)限表后面操作才穩(wěn)。也有直接先UPDATE mysql.user SET authentication_stringPASSWORD(...)的寫法但PASSWORD()函數(shù)在新版本已經(jīng)廢棄所以要么用ALTER USER要么在5.7里用SET PASSWORD FOR rootlocalhostPASSWORD(...)而在8.0里就統(tǒng)一用ALTER USER。第五步千萬(wàn)記得把配置文件里加的skip-grant-tables注釋掉或刪掉然后重啟MySQLsystemctl restart mysqld mysql -u root -p這一步很多人會(huì)忘導(dǎo)致數(shù)據(jù)庫(kù)一直裸奔在無(wú)認(rèn)證狀態(tài)這是個(gè)高危風(fēng)險(xiǎn)操作。我用一個(gè)表格把整個(gè)流程整理出來(lái)步驟操作目的1關(guān)閉mysqld釋放文件鎖安全變更2配置skip-grant-tables臨時(shí)跳過(guò)權(quán)限認(rèn)證3無(wú)密碼登錄進(jìn)入管理員會(huì)話4FLUSH PRIVILEGES ALTER USER重新加載權(quán)限表并改密碼5還原配置并重啟恢復(fù)正常安全模式3.3 場(chǎng)景B正常修改業(yè)務(wù)賬號(hào)密碼如果沒忘記密碼只是例行輪換或者程序要換賬號(hào)那就完全用不著跳過(guò)權(quán)限表。直接登錄進(jìn)去執(zhí)行ALTER USER就行但里面有幾個(gè)細(xì)節(jié)值得說(shuō)。比如我想把某個(gè)業(yè)務(wù)賬號(hào)的密碼改掉ALTER USER app_user% IDENTIFIED BY App2024Secret;如果要同時(shí)改密碼和認(rèn)證插件可以這么寫ALTER USER app_user% IDENTIFIED WITH mysql_native_password BY App2024Secret;還有一個(gè)容易被忽視的點(diǎn)在MySQL 5.7及以上版本密碼是有過(guò)期策略的。如果你在配置里開啟了default_password_lifetime或者單獨(dú)給某個(gè)賬號(hào)設(shè)置了PASSWORD EXPIRE那么密碼到期后客戶端就會(huì)報(bào)Your password has expired除非你讓賬號(hào)永不過(guò)期ALTER USER app_user% IDENTIFIED BY App2024Secret PASSWORD EXPIRE NEVER;業(yè)務(wù)程序里如果用的是連接池改完密碼之后不能立刻覺得萬(wàn)事大吉。連接池里的舊連接可能在密碼修改前已經(jīng)建立這些連接并不會(huì)因?yàn)槊艽a變了就斷開還能繼續(xù)跑一段時(shí)間。但如果連接池里的連接被回收后新建連接新連接就會(huì)用新密碼去認(rèn)證一旦程序配置里的密碼沒同步改就會(huì)看到間斷性的Access denied錯(cuò)誤。所以正確的操作順序是先改程序里的數(shù)據(jù)庫(kù)配置再改數(shù)據(jù)庫(kù)密碼最后滾動(dòng)重啟應(yīng)用服務(wù)。3.4 順帶把連接數(shù)告警一起解決掉密碼問(wèn)題處理完接下來(lái)看連接數(shù)告警。先登錄數(shù)據(jù)庫(kù)查看當(dāng)前連接狀況SHOW STATUS LIKE Threads_connected; SHOW VARIABLES LIKE max_connections;發(fā)現(xiàn)當(dāng)前連接已經(jīng)480而max_connections只有500確實(shí)快滿了。這種場(chǎng)景先用動(dòng)態(tài)參數(shù)把連接數(shù)臨時(shí)拉高但參數(shù)調(diào)完之后還得確認(rèn)是不是有連接沒有被合理釋放排查有沒有慢查詢堆積。先臨時(shí)調(diào)整SET GLOBAL max_connections 800;這個(gè)命令實(shí)時(shí)生效不用重啟但重啟后就沒了。要永久生效還得在/etc/my.cnf的[mysqld]段里加上max_connections800。注意不能只改配置文件不重啟也不能只動(dòng)態(tài)改不寫文件兩者必須同時(shí)做否則下次重啟又會(huì)回到老配置。除了調(diào)連接數(shù)我還順手看了一下慢查詢?nèi)罩竞蚿rocesslistSHOW FULL PROCESSLIST;發(fā)現(xiàn)有幾條SQL跑了好幾十秒才導(dǎo)致連接堆積。這就靠slow_query_log和long_query_time設(shè)置得合理才能及時(shí)發(fā)現(xiàn)。很多生產(chǎn)事故前期都是連接數(shù)緩慢爬坡本質(zhì)是SQL性能太差光調(diào)大連接數(shù)等于治標(biāo)不治本。3.5 配置文件修改后的啟動(dòng)校驗(yàn)改完配置文件最怕的就是參數(shù)寫錯(cuò)導(dǎo)致MySQL啟動(dòng)失敗。如果你運(yùn)氣好語(yǔ)法錯(cuò)誤會(huì)在啟動(dòng)時(shí)報(bào)出來(lái)并給出具體的錯(cuò)誤行。但有些參數(shù)的值不合規(guī)可能MySQL只是警告一下其中某些參數(shù)可能直接忽略某些參數(shù)導(dǎo)致啟動(dòng)失敗。比如把max_connections設(shè)置成abc啟動(dòng)會(huì)直接報(bào)錯(cuò)把port設(shè)置成一個(gè)已被占用的端口也會(huì)啟動(dòng)失敗。在啟動(dòng)之前強(qiáng)烈建議先用以下方式校驗(yàn)配置mysqld --validate-config --defaults-file/etc/my.cnf這個(gè)命令能幫你檢查配置文件里有沒有語(yǔ)法錯(cuò)誤、有沒有未知參數(shù)、參數(shù)取值是否合法。注意不同版本對(duì)--validate-config的支持略有差異在8.0里通常寫作mysqld --validate-config在5.7里可能需要先確認(rèn)。如果這個(gè)命令沒有輸出基本說(shuō)明配置層面沒問(wèn)題可以放心啟動(dòng)。啟動(dòng)后再次確認(rèn)參數(shù)是否真的加載了mysql -u root -p -e SHOW VARIABLES LIKE max_connections; SHOW VARIABLES LIKE port;如果發(fā)現(xiàn)啟動(dòng)值和配置里寫的不一樣先懷疑是不是加載了別的配置文件用前面說(shuō)的my_print_defaults去查。4. 我踩過(guò)的坑常見問(wèn)題與排查技巧實(shí)錄4.1 改完密碼后程序連續(xù)報(bào)Access denied這是一個(gè)高頻問(wèn)題。最常見的原因是改密碼時(shí)沒有把host匹配規(guī)則考慮進(jìn)去。比如程序連接串里寫的是jdbc:mysql://dbhost:3306/appdb但MySQL里創(chuàng)建的是app_userlocalhost這樣遠(yuǎn)程連接就會(huì)被拒絕。排查步驟如下先確認(rèn)程序是從哪臺(tái)機(jī)器、用什么賬號(hào)連的在數(shù)據(jù)庫(kù)里執(zhí)行SELECT user, host FROM mysql.user;看有沒有匹配記錄如果只有l(wèi)ocalhost記錄就新建一條app_user%或用具體IP創(chuàng)建再檢查賬號(hào)的認(rèn)證插件MySQL 8.0對(duì)齊一下客戶端驅(qū)動(dòng)版本另外一個(gè)隱蔽原因是密碼中包含了特殊字符比如分號(hào)或反斜杠。在命令行里執(zhí)行ALTER USER ... IDENTIFIED BY a;b通常沒問(wèn)題但在shell腳本里如果字符串拼接沒處理好后半段可能會(huì)被當(dāng)成新的SQL語(yǔ)句。所以建議密碼盡量不要包含;、、\這些需要多轉(zhuǎn)義一層的字符或者在腳本里先變量化再執(zhí)行。4.2 配置文件改了但不生效這是最折磨人的問(wèn)題明明文件里改了端口重啟后還是3306。按照我的經(jīng)驗(yàn)優(yōu)先級(jí)依次排查這幾件事啟動(dòng)命令是否帶--defaults-file參數(shù)指定了別的文件當(dāng)前系統(tǒng)加載的配置文件到底是不是你改的那個(gè)用my_print_defaults驗(yàn)證參數(shù)是否放到了正確的段里比如把max_connections放在了[client]段那服務(wù)端根本不讀是否因?yàn)閰?shù)屬于只讀啟動(dòng)參數(shù)且由于某些版本差異動(dòng)態(tài)修改被之后的重啟覆蓋是否改完配置文件之后服務(wù)沒有真正重啟只是停在了那里有個(gè)很典型的案例某次我在一臺(tái)機(jī)器上改了端口從3306改成3307重啟后依然是3306。排查發(fā)現(xiàn)是啟動(dòng)腳本里先寫死了--port3306命令行參數(shù)優(yōu)先于配置文件配置怎么改都沒用。以后遇到配置不生效的現(xiàn)象先看ps aux | grep mysqld的完整參數(shù)列表能省下很多時(shí)間。4.3 使用skip-grant-tables模式后無(wú)法正常登錄有人跳過(guò)權(quán)限驗(yàn)證后執(zhí)行ALTER USER時(shí)報(bào)錯(cuò)Operation requires elevated privileges。這個(gè)原因我前面說(shuō)過(guò)了skip-grant-tables模式下權(quán)限系統(tǒng)未加載需要先FLUSH PRIVILEGES讓權(quán)限表加載起來(lái)。另外在這種模式下MySQL可能拒絕某些賬號(hào)的遠(yuǎn)程連接因?yàn)樗虞d不到權(quán)限數(shù)據(jù)時(shí)行為會(huì)比較保守所以建議全程只在本機(jī)操作socket連接。還有個(gè)小細(xì)節(jié)在skip-grant-tables模式下如果你要重啟服務(wù)千萬(wàn)確認(rèn)配置文件里那一行已經(jīng)被注釋否則服務(wù)啟動(dòng)后又會(huì)跳過(guò)權(quán)限驗(yàn)證。有些老手會(huì)在操作后忘記這個(gè)步驟導(dǎo)致數(shù)據(jù)庫(kù)在公網(wǎng)上裸奔這是不能接受的安全風(fēng)險(xiǎn)。4.4 常見問(wèn)題速查表問(wèn)題現(xiàn)象最可能原因快速處理Access denied using password YES密碼錯(cuò)或host不匹配核對(duì)用戶host組合重設(shè)密碼Authentication plugin cannot be loaded客戶端驅(qū)動(dòng)太老升級(jí)驅(qū)動(dòng)或改用mysql_native_passwordYour password has expired密碼過(guò)期策略觸發(fā)ALTER USER ... PASSWORD EXPIRE NEVER改完配置重啟不生效加載了別的配置文件用ps和my_print_defaults確認(rèn)連接數(shù)到了max新連接等待慢查詢堆積或參數(shù)過(guò)小臨時(shí)SET GLOBAL調(diào)大優(yōu)化SQLUnknown variable xxx參數(shù)名寫錯(cuò)或版本不支持對(duì)照官方文檔檢查參數(shù)名Table mysql.user doesnt exist數(shù)據(jù)目錄損壞或初始化未完成備份數(shù)據(jù)后重新初始化系統(tǒng)庫(kù)5. 關(guān)于改密和配置的最佳實(shí)踐建議5.1 密碼管理和賬號(hào)權(quán)限的最小化無(wú)論你是給root改密碼還是給業(yè)務(wù)賬號(hào)設(shè)密碼都建議遵循最小化授權(quán)原則。日常開發(fā)調(diào)試可以不用root而應(yīng)該創(chuàng)建獨(dú)立的業(yè)務(wù)賬號(hào)只授予它需要的庫(kù)表權(quán)限。比如CREATE USER app_read% IDENTIFIED BY ReadOnly123; GRANT SELECT ON appdb.* TO app_read%; CREATE USER app_write% IDENTIFIED BY WriteOnly123; GRANT INSERT, UPDATE, DELETE ON appdb.* TO app_write%;這樣就算某個(gè)賬號(hào)密碼泄露影響范圍也被控制在較小邊界內(nèi)。同時(shí)密碼本身盡量做到復(fù)雜且唯一不要在多個(gè)環(huán)境復(fù)用同一個(gè)密碼。生產(chǎn)環(huán)境建議用密碼管理器生成隨機(jī)串并把密碼輪換做成定期任務(wù)而不是等到出了問(wèn)題再改。5.2 配置文件管理的版本化和變更流程配置文件是數(shù)據(jù)庫(kù)的生命線改之前強(qiáng)烈建議先備份一份帶時(shí)間戳的副本cp /etc/my.cnf /etc/my.cnf.bak.$(date %F_%H-%M-%S)如果改完出了問(wèn)題可以秒級(jí)回滾。更進(jìn)一步建議用代碼倉(cāng)庫(kù)管理配置模板把開發(fā)、測(cè)試、生產(chǎn)三套配置文件都納入版本管理但每套環(huán)境的密碼和密鑰要單獨(dú)通過(guò)環(huán)境變量或secrets管理不要直接寫死在配置里。變更之前先規(guī)劃好變更窗口確認(rèn)變更影響面。動(dòng)態(tài)參數(shù)可以先在線修改驗(yàn)證靜態(tài)參數(shù)則需要計(jì)劃維護(hù)窗口重啟。一個(gè)穩(wěn)妥的流程是先在測(cè)試環(huán)境完整執(zhí)行一遍變更腳本確認(rèn)無(wú)誤后再上生產(chǎn)執(zhí)行同樣步驟。5.3 提升穩(wěn)定性的幾個(gè)順手配置最后分享幾個(gè)我實(shí)際用下來(lái)能顯著減少問(wèn)題的配置項(xiàng)[mysqld] skip_name_resolveON log_error_verbosity3 max_allowed_packet64M wait_timeout600 interactive_timeout600skip_name_resolve讓MySQL不對(duì)客戶端IP做反向DNS解析可以避免因?yàn)镈NS抖動(dòng)導(dǎo)致連接緩慢但要注意它要求所有授權(quán)記錄里寫的都是IP而不是主機(jī)名。max_allowed_packet調(diào)大一些可以防止導(dǎo)入數(shù)據(jù)或大SQL報(bào)錯(cuò)。wait_timeout和interactive_timeout則控制非交互連接和交互連接的閑置超時(shí)時(shí)間避免大量空閑連接占著連接數(shù)不放。還有一個(gè)容易被忽略的小技巧如果你同時(shí)在維護(hù)多個(gè)MySQL實(shí)例建議每個(gè)實(shí)例都單獨(dú)建一份配置文件用--defaults-file指向各自文件這樣互不干擾。實(shí)例之間可以通過(guò)不同的端口和socket路徑區(qū)分配置隔離避免改了一個(gè)實(shí)例影響了另一個(gè)實(shí)例。我個(gè)人在實(shí)際操作中最深的體會(huì)是改密碼和調(diào)配置看起來(lái)是零碎的小活但它們最考驗(yàn)一個(gè)人對(duì)MySQL運(yùn)行機(jī)制的理解。你理解了權(quán)限表的匹配規(guī)則、理解了配置文件的加載優(yōu)先級(jí)、理解了靜態(tài)參數(shù)和動(dòng)態(tài)參數(shù)的區(qū)別再遇到那些亂七八糟的連不上、不生效問(wèn)題基本都能在幾分鐘內(nèi)定位到根因。希望這篇文章里記下的這些操作過(guò)程和踩坑記錄能幫你少走一些我當(dāng)年走過(guò)的彎路。