ubuntu6.06下编译MySQL5.0.26（打slow log补丁）

MySQL的slow log可以用来看执行时间超过指定时间的SQL，但是指定的时间最小只能是一秒，有点太大了。通常是要找那些执行次数很多但每次执行又超不过一秒的SQL。打一个补丁，即可记录所有SQL的详细执行时间。原文见：

为slow log打补丁的原文
注意这里用了prefix，是因为机器还有一个mysql5.0.22，我不想引发冲突。

brum@brum-laptop:~/mysql-5.0.26$ ./configure --prefix=/usr/local/mysql
....
....
checking for termcap functions library... configure: error: No curses/termcap library found

需要安装libncurses5-dev包，安装之后，configure就通过了

补丁从这里下载：

打补丁
brum@brum-laptop:~$ pwd
/home/brum
brum@brum-laptop:~$ patch -p0 < patch.slow-micro.5.0.26.diff
patching file mysql-5.0.26/include/my_time.h
patching file mysql-5.0.26/scripts/mysqldumpslow.sh
patching file mysql-5.0.26/sql/log.cc
patching file mysql-5.0.26/sql/mysqld.cc
patching file mysql-5.0.26/sql/set_var.cc
patching file mysql-5.0.26/sql/set_var.h
patching file mysql-5.0.26/sql/sql_class.cc
patching file mysql-5.0.26/sql/sql_class.h
patching file mysql-5.0.26/sql/sql_parse.cc
patching file mysql-5.0.26/sql/sql_show.cc
patching file mysql-5.0.26/sql/structs.h
patching file mysql-5.0.26/sql-common/my_time.c

brum@brum-laptop:~$ cd mysql-5.0.26/
brum@brum-laptop:~/mysql-5.0.26$ make
brum@brum-laptop:~/mysql-5.0.26$ sudo make install
....
....
/usr/bin/ld: cannot find -lz
collect2: ld returned 1 exit status
libtool: install: error: relink `libmysqlclient.la' with the above command before installing it
make[3]: *** [install-pkglibLTLIBRARIES] 错误 1
make[3]: Leaving directory `/home/brum/mysql-5.0.26/libmysql'
make[2]: *** [install-am] 错误 2
make[2]: Leaving directory `/home/brum/mysql-5.0.26/libmysql'
make[1]: *** [install-recursive] 错误 1
make[1]: Leaving directory `/home/brum/mysql-5.0.26'
make: *** [install] 错误 2

brum@brum-laptop:~/mysql-5.0.26$ find -name libz\*
./zlib/libz.la
./zlib/.libs/libz.so.1.2.3
./zlib/.libs/libz.so.1
./zlib/.libs/libz.so
./zlib/.libs/libz.a
./zlib/.libs/libz.lai
./zlib/.libs/libz.la

brum@brum-laptop:~/mysql-5.0.26$ export LD_LIBRARY_PATH=~/mysql-5.0.26/zlib/.libs

brum@brum-laptop:~/mysql-5.0.26$ sudo make install
....
....
mv: cannot stat `libmysqlclient.so.15.0.0': No such file or directory

今天又抽了些时间，看了看configure --help的帮助，重新configure。首先是用make clean把前面做过的编译都清除。然后：
brum@brum-laptop:~/mysql-5.0.26$ ./configure --prefix=/usr/local/mysql --exec-prefix=/usr/local/mysql

make

sudo make install

brum@brum-laptop:~$ cd /usr/local/mysql/
brum@brum-laptop:~$ mkdir etc
brum@brum-laptop:/usr/local/mysql$ sudo cp /home/brum/mysql-5.0.26/support-files/my-small.cnf /usr/local/mysql/etc/my.cnf
这是为了不修改/etc/my.cnf，因为还有另一个mysql服务。

修改my.cnf中[mysql]和[mysqld]
port            = 3307
socket          = /usr/local/mysql/var/mysql.sock
这也是为了不与已经存在的mysql冲突

brum@brum-laptop:/usr/local/mysql$ sudo bin/mysql_install_db --user=brum
brum@brum-laptop:/usr/local/mysql$ sudo chown -R root .
brum@brum-laptop:/usr/local/mysql$ sudo chown -R brum var
brum@brum-laptop:/usr/local/mysql$ sudo chgrp -R brum .
brum@brum-laptop:/usr/local/mysql$ bin/mysqld_safe &
服务启动了
连接一下试试
brum@brum-laptop:/usr/local/mysql$ bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.26

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

好了，成功了。
修改/usr/local/mysql/etc/my.cnf
在[mysqld]中加上
log-slow-queries
long-query-time=0
前者是让mysql记录slow query；后者是利用前面打的补丁，设置为0的含义是把所有的SQL都记录在slow query log中。如果不打补丁，那么long-query-time最小只能设为1，即1秒，即只有超过1秒的SQL才会记录。打了补丁则没有这个限制了。
随便执行了几个SQL，看一下日志：
brum@brum-laptop:~/mysql-5.0.26$ cat /usr/local/mysql/var/brum-laptop-slow.log
/usr/local/mysql/libexec/mysqld, Version: 5.0.26-log. started with:
Tcp port: 3307  Unix socket: /usr/local/mysql/var/mysql.sock
Time                 Id Command    Argument
# Time: 061104 17:47:22 # User@Host: [brum] @ localhost []
# Query_time: 0.014273  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0
use test;
create table test(a int, b varchar(100))engine=innodb;
# Time: 061104 17:47:50 # User@Host: [brum] @ localhost []
# Query_time: 0.000378  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0
alter table test modify a auto_increment primary key;
# Time: 061104 17:48:23 # User@Host: [brum] @ localhost []
# Query_time: 0.007990  Lock_time: 0.000329  Rows_sent: 0  Rows_examined: 0
SET insert_id=1;
insert into test(b) values('aaaaa');
# Time: 061104 17:48:31 # User@Host: [brum] @ localhost []
# Query_time: 0.004855  Lock_time: 0.000116  Rows_sent: 0  Rows_examined: 0
SET insert_id=2;
insert into test(b) values('aaaaa');
# Time: 061104 17:48:32 # User@Host: [brum] @ localhost []
# Query_time: 0.000767  Lock_time: 0.000114  Rows_sent: 0  Rows_examined: 0
SET insert_id=3;
insert into test(b) values('aaaaa');
# Time: 061104 17:48:34 # User@Host: [brum] @ localhost []
# Query_time: 0.031763  Lock_time: 0.000115  Rows_sent: 0  Rows_examined: 0
SET insert_id=4;
insert into test(b) values('aaaaa');
# Time: 061104 17:48:40 # User@Host: [brum] @ localhost []
# Query_time: 0.000846  Lock_time: 0.000116  Rows_sent: 4  Rows_examined: 4
select * from test;
# Time: 061104 17:48:43 # User@Host: [brum] @ localhost []
# Query_time: 0.000042  Lock_time: 0.000000  Rows_sent: 4  Rows_examined: 4
# administrator command: Quit;
精确到微秒级了。然后，还可以下载一个mysql_slow_log_parser，在我上面提供的链接中可以下载。

brum@brum-laptop:~/MySQL5.0.26SlowLogPatch$ ./mysql_slow_log_parser /usr/local/mysql/var/brum-laptop-slow.log

 Starting...
### 1 Query
### Total time: 0.014273, Average time: 0.014273
### Taking 0.014273  seconds to complete
### Rows analyzed 0
use test;
create table test(a int, b varchar(XXX))engine=innodb;

use test;
create table test(a int, b varchar(100))engine=innodb;

### 1 Query
### Total time: 0.000846, Average time: 0.000846
### Taking 0.000846  seconds to complete
### Rows analyzed 4
select * from test;

select * from test;

### 1 Query
### Total time: 0.000378, Average time: 0.000378
### Taking 0.000378  seconds to complete
### Rows analyzed 0
alter table test modify a auto_increment primary key;

alter table test modify a auto_increment primary key;

### 4 Queries
### Total time: 0.045375, Average time: 0.01134375
### Taking 0.000767 , 0.004855 , 0.007990 , 0.031763  seconds to complete
### Rows analyzed 0, 0, 0 and 0
SET insert_id=XXX;
insert into test(b) values('XXX');

SET insert_id=4;
insert into test(b) values('aaaaa');

每个SQL执行了几遍，总共多少时间，非常清楚。打了补丁以后，可以检测到那些每次执行时间小于1秒而执行次数非常多的SQL。很多时候都是这样的SQL在做怪，把这些SQL进行优化，或者修改程序减少这样的SQL的执行次数，都会大大提高应用的效率。

我之前在RHEL4.0上也装过，非常顺利，
./configure --prefix=/usr/local/mysql
之后，make和make install都没有问题。但在ubuntu6.06上，就需要使用
./configure --prefix=/usr/local/mysql --exec-prefix=/usr/local/mysql

没有仔细去研究原因。