分享一个mysqldump源码分析过程，还原拍砖

[zuoxingyu]

过程分析：
今天在分析慢查询日志的时候，发现一些这样的语句：
#=======================================================================================
# Query_time: 2.652960 Lock_time: 0.000000 Rows_sent: 1309590 Rows_examined: 1309590
SET timestamp=1306474204;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `categorylinks`;
#=======================================================================================
查询这个表的所有记录。在程序里面不可能有这样的业务存在。然后看了下产生的时间，是00:30，正是备份数据库的时间。莫非是备份数据库时的查询？

打开备份文件，看下对每个表的备份流程是怎么样的
#========================================================================================
1：LOCK TABLES `myt` WRITE;
2：/*!40000 ALTER TABLE `myt` DISABLE KEYS */;
3：INSERT INTO `myt` VALUES ('table ™ name'),('table ? name'),('table ™ name'),('table ™ name');
4：/*!40000 ALTER TABLE `myt` ENABLE KEYS */;
5：UNLOCK TABLES;
#=========================================================================================
mysqldump程序能够得到values里的值，肯定是查了整个表的，然后再在程序里拼好，形成insert语句。
在每个表的备份里面，都有“LOCK TABLES”，看看源码里是怎么操作的：
#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
if (opt_lock) #是否锁表的参数,就是mysqldump --help 看到的 --add-locks Add locks around INSERT statements.
{
fprintf(md_result_file,"LOCK TABLES %s WRITE;\n", opt_quoted_table); #如果是，打印lock table
check_io(md_result_file); #检查文件写入情况，每个地方都有，如果写入文件失败，就退出错误。
}
/* Moved disable keys to after lock per bug 15977 */
if (opt_disable_keys) #这里就是备份文件的第2行了
{
fprintf(md_result_file, "/*!40000 ALTER TABLE %s DISABLE KEYS */;\n",
opt_quoted_table);
check_io(md_result_file);
}
#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
主要备份的数据是在第三行，看看是怎么处理的：
#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
/*
If write_data is true, then we build up insert statements for
the table's data. Note: in subsequent lines of code, this test
will have to be performed each time we are appending to
insert_pat.
*/
if (write_data) #重点来了，看它怎么样拼SQL
{
if (opt_replace_into)
dynstr_append_checked(&insert_pat, "REPLACE "); #这些根据参数决定用INSERT ,REPLACE等插入方法
else
dynstr_append_checked(&insert_pat, "INSERT ");
dynstr_append_checked(&insert_pat, insert_option);
dynstr_append_checked(&insert_pat, "INTO ");
dynstr_append_checked(&insert_pat, opt_quoted_table);
if (complete_insert)
{
dynstr_append_checked(&insert_pat, " ("); #左边括号出现了
}
else
{
dynstr_append_checked(&insert_pat, " VALUES ");
if (!extended_insert)
dynstr_append_checked(&insert_pat, "(");
}
}

while ((row= mysql_fetch_row(result))) #在这里循环select * from tbname里的记录了
{
if (complete_insert)
{
if (init)
{
dynstr_append_checked(&insert_pat, ", "); #还在拼SQL
}
init=1;
dynstr_append_checked(&insert_pat,
quote_name(row[SHOW_FIELDNAME], name_buff, 0));
}
}
num_fields= mysql_num_rows(result);
mysql_free_result(result);
}
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
if (extended_insert)
{
ulong row_length;
dynstr_append_checked(&extended_row,")"); #反括号在这里出现了

#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
代码写得很复杂，我断章取义挑出其中一部分看看，可能有地方不对，但是可以确定的一点是，mysqldump程序是要select * from tbname去获取数据的，而且如果表很大
这个查询时间很长的话，是会被记录到慢查询日志里的。

连接HOST和DB的函数
#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
/*
db_connect -- connects to the host and selects DB.
*/

static int connect_to_db(char *host, char *user,char *passwd)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
查询表数据的函数
#+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
dynstr_append_checked(&query_string, "SELECT /*!40001 SQL_NO_CACHE */ * FROM ");
dynstr_append_checked(&query_string, result_table);
#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

mysqldump的大概流程整理如下：
1：连接上数据库，根据输入的账户，密码，IP
2：进入INFORMATION_SCHEMA库，获取要备份的数据库的信息，包含存储过程，视图，表
3：进入INFORMATION_SCHEMA库，获取每个表的字段名称，字段类型等信息
4：查询每个表的数据，select SQL_NO_CACHE from tbname
5：拼SQL
6：写入备份文件

[rucypli]

没砖可拍

[ACMAIN_CHM]

楼主很用心。

[小小小小周]

img=https://forum.csdn.net/PointForum/ui/scripts/csdn/Plugin/001/face/79.gif][/img]
学习了，这样也就清楚为什么大数据量的库 不建议用mysqldump来备份了。[

[寒冰2046]

看得我是一愣一愣的

[WWWWA]

学习了，源码太长了，基本原理是这样的

[zuoxingyu]

追到他爸在mysql论坛上的足迹了，立贴存照

Re: Sort aborted & lost data from web
Posted by: david yeung ()
Date: June 15, 2008 08:32PM

This is because you didn't adjust some parameters properly such as sort_buffer_size.

I'm a mysql DBA in china.
To know more details about me can visit:http://yueliangdao0608.cublog.cn

名字起得很骚，david yeung，涛哥，你不知道大卫阳痿吗？

[寒冰2046]

[Quote=引用 6 楼 zuoxingyu 的回复:]

追到他爸在mysql论坛上的足迹了，立贴存照
SQL code

Re: Sort aborted & lost data from web
Posted by: david yeung ()
Date: June 15, 2008 08:32PM

This is because you didn't adjust some param……
[/Quote]嘿嘿。。他爸写的文章很不错啊，博客被我收藏了。。