mysql 常見的幾個錯誤問題

Mysql常見的幾個錯誤問題及解決方法：

1.問題： mysql DNS反解：skip-name-resolve

錯誤日志有類似警告：

點選(此處)折疊或打開

120119 16:26:04 [Warning] IP address '192.168.1.10' could not be resolved: Name or service not known

120119 16:26:04 [Warning] IP address '192.168.1.14' could not be resolved: Name or service not known

120119 16:26:04 [Warning] IP address '192.168.1.17' could not be resolved: Name or service not known

通過show processlist發現大量類似如下的連接配接：

|592|unauthenticated user|192.168.1.10:35320|NULL|Connect| |login|NULL|

|593|unauthenticated user|192.168.1.14:35321|NULL|Connect| |login|NULL|

|594|unauthenticated user|192.168.1.17:35322|NULL|Connect| |login|NULL|

skip-name-resolve 參數的作用：不再進行反解析（ip不反解成域名），這樣可以加快資料庫的反應時間。

修改配置檔案添加并需要重新開機：

[mysqld] 

skip-name-resolve

其實就是在[mysqld]下面一行加入skip-name-resolve重新開機mysql服務就可以了。

下面是更加詳細的解釋：

現象：

程式連接配接mysql時，mysql的error.log裡面提示：

[Warning] IP address '10.0.0.220' could not be resolved: Name or service not known

原因：

Mysql資料庫伺服器沒有配置 /etc/hosts，也沒有DNS服務，導緻mysqld線程解析IP對應的主機名時，解析失敗。

參考資料：

Mysql域名解析：

當一個新的用戶端嘗試跟mysqld建立連接配接時，mysqld産生一個新線程來處理這個請求。新線程會先檢查請求建立連接配接的主機名是否在Mysql的主機名緩沖中，如果不在，線程會嘗試去解析請求連接配接的主機名。

解析的邏輯如下：

a. Mysql線程通過gethostbyaddr()把擷取的IP位址解析成主機名，然後通過gethostbyname()把擷取的主機名解析成IP位址，保障主機名和IP位址對應關系的準确；

b. 如果作業系統支援使用安全程序的gethostbyaddr_r()和gethostbyname_r() 調用，Mysqld線程可以用它倆來優化主機名解析；

c. 如果作業系統不支援安全線程調用，Mysqld程序先做一個互斥鎖，然後調用gethostbyaddr()和gethostbyname()解析主機名。此時，在第一個程序釋放掉主機名緩沖池的主機名之前，其它程序無法再次解析這個主機名; <-------MySQL手冊裡面在此處說的host name ，意思應該是指同一個IP位址和對應的第一個主機名關系。

在啟動mysqld程序是，可以使用 --skip-name-resolve 參數禁用DNS的主機名解析功能，禁用該功能後，在MySQL授權表裡面，你隻能使用IP位址。

如果你所處環境的DNS非常慢 或者 有很多主機， 你可以通過禁用DNS解析功能--skip-name-resolve 或者 提高 HOST_CACHE_SIZE大小 來提升資料庫的響應效率。

禁用主機名緩沖的發方法： 使用--skip-host-cache 參數； 重新整理主機名緩沖區： 執行 flush hosts 或者執行mysqladmin flush-hosts；

禁用TCP/IP連接配接： 使用--skip-networking參數。

實驗：

# grep 192.168.1.1 /etc/hosts 

192.168.1.1 hostname_online

sql> grant usage on *.* to root@'h_tt_%' identified by 'root';

sql> flush hosts;

# mysql -h 192.168.1.1 -uroot -proot

ERROR 1045 (28000): Access denied for user 'root'@'hostname_online' (using password: YES) ### IP解析為hostname_online，不是h_tt_%，通路被拒。

# grep 192.168.1.1 /etc/hosts

192.168.1.1 h_tt_1

ERROR 1045 (28000): Access denied for user 'root'@'hostname_online' (using password: YES)#### mysqld沒有重新整理主機池緩沖池中的IP和主機名資訊,此時IP對應hostname_online

ERROR 1045 (28000): Access denied for user 'root'@'hostname_online' (using password: YES) #### mysqld解析了/etc/hosts裡面同一個IP對應的第一個主機名關系時,就不再解析後面這個IP對應的主機名關系

sql> exit

【實驗：】驗證解析相同IP對應的第一個主機名關系後，就不再解析相同IP：

Sql>grant usage on *.* to root@'h_tt_%' identified by ‘root';

Sql>flush hosts;

#　grep h_tt /etc/hosts # grep h_tt /etc/hosts

192.168.1.1hostname_online 192.168.1.1h_tt_1

192.168.1.1h_tt_1 192,168.1.2h_tt_1

通路mysql被拒絕; 從兩個IP都可以通路mysql.

【結論】

此實驗驗證了，上述mysql手冊中對"How MySQL Uses DNS"的解釋。

即mysqld線程解析/etc/hosts是，是以IP作為唯一辨別的，及時一個IP對應了多個主機名，但是mysqld線程隻解析第一條對應關系，不論後面有幾條這個IP對應的不同主機名的記錄，Mysqld程序都不會去解析，都是無效的。

【适用環境：】

沒有DNS伺服器，主機非常非常多，或者 不想維護/etc/hosts裡面手動配置的IP和主機名對應清單時，可以在mysql授權時執行主機名為"%" 或者禁用IP和主機名解析功能（--skip-name-resolve）

2.問題錯誤日志：Error: Can't create a new thread (errno 12)

資料庫伺服器問題，資料庫操作無法建立新線程。一般是有以下3個方面的原因：

1）、MySQL 線程開得太多。

2）、伺服器系統記憶體溢出。

3）、環境軟體損壞或系統損壞。

【問題解決】

1）進入 phpmyadmin 的 mysql 資料庫中的 user 表，對資料庫的使用者進行編輯，修改 max_connections 的值。适當的改小一點。

2）聯系伺服器管理者檢查伺服器的記憶體和系統是否正常，如果伺服器記憶體緊張，請檢查一下哪些程序消耗了伺服器的記憶體，同時考慮是否增加伺服器的記憶體來提高整個系統的負載能力。

3）mysql版本更改為穩定版本

4）優化網站程式的sql等等

3.  操作報錯：ERROR 1010 (HY000): Error dropping database

mysql> drop database xjtrace;

ERROR 1010 (HY000): Error dropping database (can't rmdir './xjtrace/

在做資料庫删除時出現這種提示，其原因是在database下面含有自己放進去的檔案，譬如*.txt檔案或*.sql檔案等，隻要進去把這個檔案删了在執行。

mysql>drop database xjtrace;

Query OK, 0 rows affected (0.00 sec)

果斷删除即可！！

4.導出資料很快，導入到新庫時卻很慢：

MySQL導出的SQL語句在導入時有可能會非常非常慢，經曆過導入僅400萬條記錄，竟用了近2個小時。在導出時合理使用幾個參數，可以大大加快導入的速度。

-e 使用包括幾個VALUES清單的多行INSERT文法;

--max_allowed_packet=XXX 用戶端/伺服器之間通信的緩存區的最大大小;

--net_buffer_length=XXX TCP/IP和套接字通信緩沖區大小,建立長度達net_buffer_length的行

注意：max_allowed_packet和net_buffer_length不能比目标資料庫的配置數值大，否則可能出錯。

首先确定目标庫的參數值

mysql> show variables like 'max_allowed_packet';

+--------------------+---------+

| Variable_name | Value |

| max_allowed_packet | 1048576 |

1 row in set (0.00 sec)

mysql> show variables like 'net_buffer_length';

+-------------------+-------+

| net_buffer_length | 16384 |

根據參數值書寫mysqldump指令，如：

mysql>mysqldump -uroot -p   資料庫名 -e --max_allowed_packet=1048576 -net_buffer_length=16384 > SQL檔案

例如：

mysql> mysqldump -uroot -p xjtrace -e --max_allowed_packet=1048576 --net_buffer_length=16384 >xjtrace_data_`date +%F`.sql

之前2小時才能導入的sql現在幾十秒就可以完成了。

關于一次導入資料提示的MySQL server has gone away

http://roll.sohu.com/20131217/n391910784.shtml

MySQL根據配置檔案會限制Server接受的資料包大小。有時候大的插入和更新會受 max_allowed_packet 參數限制，導緻寫入或者更新失敗。

檢視目前配置：

複制代碼代碼如下:

show VARIABLES like '%max_allowed_packet%';

顯示的結果為：

| Variable_name      | Value   |

| max_allowed_packet | 1048576 |

+--------------------+---------+ 

以上說明目前的配置是：1M.

 解決方法如下：

1. 編輯 MySQL 安裝目錄下的 my.ini，在最後添加以下内容：

max_allowed_packet=16M

2. 重新開機 MySQL 服務即可

set global max_allowed_packet=1024*1024*16;

可在不重新開機MySQL的情況下立即生效，但是重新開機後就會恢複原樣。

Aborted_clients

mysql > show status like 'abort%';

+------------------+-------+

| Variable_name | Value |

| Aborted_clients | 188 |

| Aborted_connects | 53 |

用戶端被異常中斷的數值，即連接配接到mysql伺服器的用戶端沒有正常地斷開或關閉。對于一些應用程式是沒有影響的，但對于另一些應用程式可能你要跟蹤該值，因為異常中斷連接配接可能表明了一些應用程式有問題。

MySQL server has gone away報錯原因分析

判斷是否屬于這個原因的方法很簡單，執行以下指令，檢視mysql的運作時長

或者檢視MySQL的報錯日志，看看有沒有重新開機的資訊

如果uptime數值很大，表明mysql服務運作了很久了。說明最近服務沒有重新開機過。

如果日志沒有相關資訊，也表名mysql服務最近沒有重新開機過，可以繼續檢查下面幾項内容。

如果程式使用的是長連接配接，則這種情況的可能性會比較大。

即，某個長連接配接很久沒有新的請求發起，達到了server端的timeout，被server強行關閉。

此後再通過這個connection發起查詢的時候，就會報錯server has gone away

這種情況和情況2相似，隻是發起者是DBA或者其他job。發現有長時間的慢查詢執行kill xxx導緻。

當查詢的結果集超過 max_allowed_packet 也會出現這樣的報錯。定位方法是打出相關報錯的語句。

用select * into outfile 的方式導出到檔案，檢視檔案大小是否超過 max_allowed_packet ，如果超過則需要調整參數，或者優化語句。

修改參數：