排除MySQL中常見錯(cuò)誤的實(shí)用招術(shù)
譯文【51CTO.com快譯】MySQL是Oracle擁有的一種廣泛使用的開源關(guān)系數(shù)據(jù)庫管理系統(tǒng)(RDMS)。多年來，它是基于Web的應(yīng)用軟件的默認(rèn)選擇，與其他數(shù)據(jù)庫引擎相比仍然大受歡迎。
MySQL是為Web應(yīng)用軟件設(shè)計(jì)和優(yōu)化的，它構(gòu)成了基于Web的各大應(yīng)用(比如Facebook、Twitter、Wikipedia和YouTube等)的必要部分。
你的網(wǎng)站或Web應(yīng)用軟件是否基于MySQL?我們?cè)诒疚闹袑⒔忉屓绾闻懦齅ySQL數(shù)據(jù)庫服務(wù)器中的問題和常見錯(cuò)誤。我們將描述如何查明問題的原因以及如何解決問題。
1. 無法連接到本地MySQL服務(wù)器
MySQL中常見的客戶機(jī)到服務(wù)器連接錯(cuò)誤之一是“ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)”。
圖1. 無法連接到本地MySQL服務(wù)器
該錯(cuò)誤表示主機(jī)系統(tǒng)上沒有MySQL服務(wù)器(mysqld)在運(yùn)行，或者嘗試連接到服務(wù)器時(shí)指定了錯(cuò)誤的Unix套接字文件名或TCP/IP端口。
結(jié)合使用ps命令和grep命令，檢查數(shù)據(jù)庫服務(wù)器主機(jī)上名為mysqld的進(jìn)程，以此確保服務(wù)器在運(yùn)行，如圖所示。
- $ ps xa | grep mysqld | grep -v mysqld
如果上述命令未顯示輸出，則表示數(shù)據(jù)庫服務(wù)器沒在運(yùn)行。因此客戶機(jī)無法連接到它。要啟動(dòng)服務(wù)器，運(yùn)行下列systemctl命令。
- $ sudo systemctl start mysql #Debian/Ubuntu
- $ sudo systemctl start mysqld #RHEL/CentOS/Fedora
要驗(yàn)證MySQL服務(wù)狀態(tài)，請(qǐng)使用下列命令。
- $ sudo systemctl status mysql #Debian/Ubuntu
- $ sudo systemctl status mysqld #RHEL/CentOS/Fedora
圖2. 檢查MySQL狀態(tài)
可以從上述命令的輸出看出，MySQL服務(wù)失敗。這種情況下，你可以嘗試重新啟動(dòng)，再次檢查狀態(tài)。
- $ sudo systemctl restart mysql
- $ sudo systemctl status mysql
圖3. 重新啟動(dòng)MySQL并驗(yàn)證狀態(tài)
此外，如果服務(wù)器在運(yùn)行，如下列命令所示，但你仍看到上述錯(cuò)誤，還應(yīng)驗(yàn)證TCP/IP端口是否被防火墻阻止或任何端口在阻止服務(wù)。
- $ ps xa | grep mysqld | grep -v mysqld
要找到服務(wù)器在偵聽的端口，使用netstat命令，如下所示。
- $ sudo netstat -tlpn | grep "mysql"
2. 無法連接到MySQL服務(wù)器
另一個(gè)常遇到的連接錯(cuò)誤是“(2003) Can’t connect to MySQL server on ‘server’ (10061)”，這意味著網(wǎng)絡(luò)連接被拒絕。
在這里，先檢查MySQL服務(wù)器是否在系統(tǒng)上運(yùn)行，如上所示。還要確保服務(wù)器已啟用網(wǎng)絡(luò)連接，用于連接的網(wǎng)絡(luò)端口是服務(wù)器上配置的那個(gè)端口。
試圖連接到MySQL服務(wù)器時(shí)可能會(huì)遇到的其他常見錯(cuò)誤有：
- ERROR 2003: Can't connect to MySQL server on 'host_name' (111)
- ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)
這些錯(cuò)誤表明服務(wù)器可能在運(yùn)行，但你試圖使用有別于服務(wù)器在偵聽的TCP/IP端口、命名管道或Unix套接字文件來進(jìn)行連接。
3. MySQL中訪問被拒絕錯(cuò)誤
在MySQL中，用戶帳戶是根據(jù)用戶名和用戶連接到服務(wù)器所用的客戶機(jī)主機(jī)或主機(jī)來定義的。此外，帳戶還可能擁有身份驗(yàn)證憑據(jù)，比如密碼。
雖然“訪問被拒絕”錯(cuò)誤有許多不同的原因，但一個(gè)常見原因與服務(wù)器允許客戶機(jī)程序在連接時(shí)使用的MySQL帳戶有關(guān)。它表示連接中指定的用戶名沒有訪問數(shù)據(jù)庫的權(quán)限。
MySQL允許創(chuàng)建使客戶機(jī)用戶能夠連接到服務(wù)器并訪問服務(wù)器管理的數(shù)據(jù)的帳戶。在這方面，如果你遇到訪問被拒絕錯(cuò)誤，檢查是否允許用戶帳戶通過你所使用的客戶機(jī)程序連接到服務(wù)器。
可以通過運(yùn)行SHOW GRANTS命令查看某個(gè)特定帳戶擁有的權(quán)限，如圖所示。
- > SHOW GRANTS FOR 'tecmint'@'localhost';
可以在MySQL shell中使用下列命令，將特定數(shù)據(jù)庫上特定用戶的權(quán)限授予遠(yuǎn)程IP地址。
- > grant all privileges on *.test_db to 'tecmint'@'192.168.0.100';
- > flush privileges;
此外，訪問被拒絕錯(cuò)誤也可能由連接到MySQL時(shí)遇到的問題引起，請(qǐng)參閱前面解釋的錯(cuò)誤。
4. 與MySQL服務(wù)器的連接斷開
由于下列原因，你可能會(huì)遇到該錯(cuò)誤：網(wǎng)絡(luò)連接不早暢、連接超時(shí)或BLOB值出現(xiàn)問題(值大于max_allowed_packet)。如果出現(xiàn)網(wǎng)絡(luò)連接問題，確保你有良好的網(wǎng)絡(luò)連接，訪問遠(yuǎn)程數(shù)據(jù)庫服務(wù)器時(shí)尤為如此。
如果是連接超時(shí)問題，尤其是MySQL試圖與服務(wù)器初始連接時(shí)，加大connect_timeout參數(shù)的值。但是如果BLOB值大于max_allowed_packet，需要在/etc/my.cnf 中的[mysqld]或[client]部分下面為max_allowed_packet設(shè)置更高的值，如圖所示。
- [mysqld]
- connect_timeout=100
- max_allowed_packet=500M
如果你無法訪問MySQL配置文件，可以在MySQL shell中使用下列命令來設(shè)置該值。
- > SET GLOBAL connect_timeout=100;
- > SET GLOBAL max_allowed_packet=524288000;
5. MySQL連接太多
如果MySQL客戶機(jī)遇到“太多連接”錯(cuò)誤，表示所有可用連接被其他客戶機(jī)使用。連接數(shù)量(默認(rèn)為151)由max_connections系統(tǒng)變量控制;可以通過在/etc/my.cnf配置文件中加大值、允許更多連接來解決問題。
- [mysqld]
- max_connections=1000
6. 溢出內(nèi)存MySQL
如果你使用MySQL客戶機(jī)程序運(yùn)行查詢，遇到錯(cuò)誤，意味著MySQL沒有足夠的內(nèi)存來存儲(chǔ)整個(gè)查詢結(jié)果。
***步是確保查詢正確;如果正確，執(zhí)行下列操作：
- 如果你直接使用MySQL客戶機(jī)，用--quick參數(shù)選項(xiàng)符啟動(dòng)它，禁用緩存結(jié)果;或者
- 如果你使用MyODBC驅(qū)動(dòng)程序，配置用戶界面(UI)有標(biāo)志方面的高級(jí)選項(xiàng)卡。選中“Do not cache result”。
另一個(gè)很棒的工具是MySQL Tuner，這個(gè)實(shí)用的腳本會(huì)連接到運(yùn)行中的MySQL服務(wù)器，在如何配置以獲得更高性能方面給出建議。
- $ sudo apt-get install mysqltuner #Debian/Ubuntu
- $ sudo yum install mysqltuner #RHEL/CentOS/Fedora
- $ mysqltuner
想了解MySQL優(yōu)化和性能調(diào)優(yōu)技巧，請(qǐng)閱讀我們的文章：《15個(gè)實(shí)用的MySQL/MariaDB性能調(diào)整和優(yōu)化技巧》(https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/)。
7. MySQL不斷崩潰
如果你遇到該問題，應(yīng)該試著查明問題根源是MySQL服務(wù)器已死還是客戶機(jī)存在問題。請(qǐng)注意，許多服務(wù)器崩潰是由損壞的數(shù)據(jù)文件或索引文件引起的。
你可以檢查服務(wù)器狀態(tài)以確定啟動(dòng)并運(yùn)行了多久。
- $ sudo systemctl status mysql #Debian/Ubuntu
- $ sudo systemctl status mysqld #RHEL/CentOS/Fedora
或者，運(yùn)行下列mysqladmin命令以查找MySQL服務(wù)器的正常運(yùn)行時(shí)間。
- $ sudo mysqladmin version -p
圖4. 查找MySQL服務(wù)器的正常運(yùn)行時(shí)間
其他解決方案包括但不限于停止MySQL服務(wù)器并啟用調(diào)試，然后再次啟動(dòng)服務(wù)。可以嘗試創(chuàng)建一個(gè)測(cè)試用例以便重現(xiàn)問題。此外，打開另一個(gè)終端窗口，并運(yùn)行下列命令，在運(yùn)行其他查詢時(shí)顯示MySQL進(jìn)程的統(tǒng)計(jì)信息：
- $ sudo mysqladmin -i 5 status
或者
- $ sudo mysqladmin -i 5 -r status
結(jié)論：查明導(dǎo)致問題或錯(cuò)誤的原因
雖然我們已查看了一些常見的MySQL問題和錯(cuò)誤，還提供了排除和解決問題的方法，但診斷錯(cuò)誤時(shí)最重要的是明白其含義(就是什么在導(dǎo)致錯(cuò)誤)。
那么如何查明呢?下面幾點(diǎn)將指導(dǎo)你如何確定導(dǎo)致問題的原因：
- ***步也是最重要的步驟是查看存儲(chǔ)在目錄/var/log/mysql/中的MySQL日志。可以使用tail等命令行實(shí)用程序來讀取日志文件。
- 如果MySQL服務(wù)無法啟動(dòng)，使用systemctl檢查其狀態(tài)，或使用systemd下的journetctl(帶-xe標(biāo)志)命令來檢查問題。
- 還可以檢查系統(tǒng)日志文件，比如/var/log/messages或類似文件，查找導(dǎo)致問題的原因。
- 嘗試使用Mytop、glances、top、ps或htop等工具，檢查哪個(gè)程序在占用所有CPU或鎖定機(jī)器，或者檢查是否耗盡了內(nèi)存、磁盤空間、文件描述符或其他一些重要資源。
- 假設(shè)問題出在某個(gè)失控的進(jìn)程，你總是可以嘗試終止它(使用pkill或kill實(shí)用程序)，以便MySQL正常工作。
- 假設(shè)mysqld服務(wù)器導(dǎo)致問題，可以運(yùn)行命令：mysqladmin -u root ping或mysqladmin -u root processlist，獲取來自它的任何響應(yīng)。
- 如果試圖連接到MySQL服務(wù)器時(shí)問題出在客戶機(jī)程序，檢查為什么它未正常工作，嘗試從中獲得任何輸出以用于排除故障。
原文標(biāo)題：Useful Tips to Troubleshoot Common Errors in MySQL，作者：Aaron Kili
【51CTO譯稿，合作站點(diǎn)轉(zhuǎn)載請(qǐng)注明原文譯者和出處為51CTO.com】
