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 <stdio.h>
#include <mysql.h>
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设置过短引起。
(补充:
在使用mysqldump的时候(尤其是向NFS上备份的时候),很多人都被’mysqldump:Got error:2013: Lost connection to MySQL server during query when dumping table’的问题困扰,在Manual中对这个问题有一些简单的说明。
在向NFS上备份的时候,数据的流向是这样的:MySQL Server端从数据文件中检索出数据,然后分批将数据返回给mysqldump客户端,然后mysqldump将数据写入到NFS上。一般地,向NFS上写入数据的速度较之Server端检索发送数据的速度要慢得多,这就会导致mysqldump无法及时的接受Server端发送过来的数据,Server端的数据就会积压在内存中等待发送,这个等待不是无限期的,当Server的等待时间超过net_write_timeout(默认是60秒)时它就失去了耐心,mysqldump的连接会被断开,同时抛出错误Got error: 2013: Lost connection。
增加net_write_timeout可以解决上述的问题的。在实践中发现,在增大net_write_timeout后,Server端会消耗更多的内存,有时甚至会导致swap的使用(并不确定是不是修改net_write_timeout所至)。建议在mysqldump之前修改net_write_timeout为一个较大的值(如1800),在mysqldump结束后,在将这个值修改到默认的60。
)
以下是我们经常遇到的问题:
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 <stdio.h>
#include <mysql.h>
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设置过短引起。
(补充:
在使用mysqldump的时候(尤其是向NFS上备份的时候),很多人都被’mysqldump:Got error:2013: Lost connection to MySQL server during query when dumping table’的问题困扰,在Manual中对这个问题有一些简单的说明。
在向NFS上备份的时候,数据的流向是这样的:MySQL Server端从数据文件中检索出数据,然后分批将数据返回给mysqldump客户端,然后mysqldump将数据写入到NFS上。一般地,向NFS上写入数据的速度较之Server端检索发送数据的速度要慢得多,这就会导致mysqldump无法及时的接受Server端发送过来的数据,Server端的数据就会积压在内存中等待发送,这个等待不是无限期的,当Server的等待时间超过net_write_timeout(默认是60秒)时它就失去了耐心,mysqldump的连接会被断开,同时抛出错误Got error: 2013: Lost connection。
增加net_write_timeout可以解决上述的问题的。在实践中发现,在增大net_write_timeout后,Server端会消耗更多的内存,有时甚至会导致swap的使用(并不确定是不是修改net_write_timeout所至)。建议在mysqldump之前修改net_write_timeout为一个较大的值(如1800),在mysqldump结束后,在将这个值修改到默认的60。
)