mysqldump意外終止的原因以及解決方法

mysqldump是非常重要的MySQL備份工具。然而在長年累月的使用過程中，TAOBAO多次出現了因mysqldump意外終止而 導致備份失敗的情況。
以下是我們經常遇到的問題：

1、Lost connection to MySQL server at ‘reading initial communication packet’：
這個主要是因為DNS不穩定導致的。如果做了網路隔離，MySQL處於一個相對安全的網路環境，那麼開啟skip-name-resolve選項將會最大 程度避免這個問題。

2、Lost connection to MySQL server at ‘reading authorization packet’：
從MySQL獲取一個可用的連線是多次握手的結果。在多次握手的過程中，網路波動會導致握手失敗。增加connect_timeout可以解決這個問題； 然而增加connect_timeout並不能防止網路故障的發生，反而會引起MySQL執行緒佔用。最好的解決辦法是讓mysqldump重新發起連線請 求。

3、Lost connection to MySQL server during query：
這個問題具備隨機性，而淘寶MySQL的應用場景決定了我們無法多次備份資料以便重現問題。
然而我們注意到這個問題一般會在兩種情況下會發生。一種是mysqldump **** | gzip ****；另外一種是mysqldump **** > /nfs-file
注意，不管是gzip還是nfs都有一種特點，那就是它們影響了mysqldump的速度。從這個角度思考，是不是mysqldump從MySQL接受數 據包的速度不夠快導致Lost connection to MySQL server during query錯誤呢？

為了定位到問題，我搭建了一個測試環境：
test@192.168.0.1：3306
CREATE TABLE `test` (
`id` bigint(20) NOT NULL auto_increment,
`b` varchar(2000) default NULL,
`c` varchar(2000) default NULL,
`d` varchar(2000) default NULL,
`e` varchar(2000) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into test(b,c,d,e) values (lpad(’a',1900,’b'), lpad(’a',1900,’b'), lpad(’a',1900,’b'), lpad(’a',1900,’b'));
多次複製資料使測試環境達到一定資料量。

192.168.0.2：
編寫一個c++程式
#include
#include

using namespace std;

int main()
{
MYSQL conn;
MYSQL_RES *result;
MYSQL_ROW row;
my_bool reconnect = 0;

mysql_init(&conn);
mysql_options(&conn, MYSQL_OPT_RECONNECT, &reconnect);

if(!mysql_real_connect(&conn, “192.168.0.1″, “test”, “test”, “test”, 3306, NULL, 0))
{
fprintf(stderr, “Failed to connect to database: %s\n”, mysql_error(&conn));
exit(0);
}
else
{
fprintf(stdout, “Success to connect\n”);
}

mysql_query(&conn, “show variables like ‘%timeout%’”);
result = mysql_use_result(&conn);
while(row=mysql_fetch_row(result))
{
fprintf(stdout, “%-10s: %s\n”, row[0], row[1]);
}
mysql_free_result(result);
fprintf(stderr, “\n”);

mysql_query(&conn, “select SQL_NO_CACHE * from test.test”);
result = mysql_use_result(&conn);
while((row=mysql_fetch_row(result))!=NULL)
{
fprintf(stderr, “Error %d: %s\n”, mysql_errno(&conn), mysql_error(&conn));
fprintf(stdout, “%s\n”, row[0]);
sleep(100);
}
fprintf(stderr, “Error %d: %s\n”, mysql_errno(&conn), mysql_error(&conn));
mysql_free_result(result);
mysql_close(&conn);
return 1;
}

在這段程式碼裡，sleep函式用來模擬NFS的網路延遲和gzip的運算時間。執行一段時間之後，Lost connection to MySQL server during query出現了，程式意外終止。在資料處理足夠快的情況下，又會是怎樣的結果？

將sleep的時間改為1，重新編譯後發現程式能夠完整跑完。根據《MySQL Timeout解析》上對net_write_timeout的解釋，我們可以發現，mysqldump處理資料過慢（NFS、gzip引起） 會導致MySQL主動斷開連線，此時mysqldump就會報Lost connection to MySQL server during query錯誤。經過多次測試，確定這個錯誤是由於net_write_timeout設定過短引起。

Related posts

• MySQL Timeout解析 (2)

--EOF--