转载地址：http://blog.chinaunix.net/u/11408/showart_55593.html
说明：本文档系孤雁寒枝原创，经过辛勤实验，努力编辑而成，转载无妨，请不要修改任何内容。参考不少前辈文章，不一 一列举，谨表感谢！新牛一头，如有错误，敬请好手斧正！
安装mysql5.0.4数据库
freeBSD53#mount /cdrom
freeBSD53#cp /cdrom/mysql-max-5.0.4-beta-unknown-freebsd5.3-i386.tar.gz /tmp
freeBSD53#cd /tmp
freeBSD53# tar –zxvf mysql-max-5.0.4-beta-unknown-freebsd5.3-i386.tar.gz
freeBSD53# mv /tmp/mysql-max-5.0.4-beta-unknown-freebsd5.3-i386 /usr/local/mysql
freeBSD53# cd /usr/local/mysql/
freeBSD53# ./configure --help
NOTE: This is a MySQL binary distribution. It's ready to run, you don't
need to configure it!
To help you a bit, I am now going to create the needed MySQL databases
and start the MySQL server for you. If you run into any trouble, please
consult the MySQL manual, that you can find in the Docs directory.
Installing all prepared tables
Fill help tables
To start mysqld at boot time you have to copy support-files/mysql.server
to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h freeBSD53.chenghe.com password 'new-password'
See the manual for more instructions.
You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &
You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory:
cd sql-bench ; perl run-all-tests
Please report any problems with the ./bin/mysqlbug script!
The latest information about MySQL is available on the web at
Support MySQL by buying support/licenses at
Starting the mysqld server. You can test that it is up and running
with the command:
./bin/mysqladmin version
freeBSD53# chown: mysql: Invalid argument
Starting mysqld daemon with databases from /usr/local/mysql/data
(遇
到过一个提示少libm.so.2的问题，那是我第一次下了个安装
/Downloads/MySQL-5.0/mysql-max-5.0.4-beta-unknown-freebsd4.73-
i386.tar.gz 请注意这个是为freebsd4.73准备的，安装时出现了libm.so.2)
freeBSD53# ps -aux | grep mysql
root 560 0.0 1.4 1656 1224 p0 I 8:10PM 0:00.09 /bin/sh ./bin/mysqld_safe --no-default
root 572 0.0 29.4 56452 26288 p0 S 8:10PM 0:03.66 /usr/local/mysql/bin/mysqld --no-default
root 619 0.0 0.2 348 208 p0 R+ 8:16PM 0:00.00 grep mysql
freeBSD53# ./bin/mysqladmin -u root password "123456"
freeBSD53# ./bin/mysqladmin -u root -h freeBSD53.chenghe.com password "123456"
freeBSD53# ./mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
freeBSD53# ./mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 6 to server version: 5.0.4-beta-max
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql>; create database chenghe
->; ;
Query OK, 1 row affected (0.03 sec)
mysql>; exit
Bye
可以看到mysql已经好用了
这说明在mysql5这新版本软件中已不需要提前作下面的准备工作
／＊
freeBSD53# pw groupadd mysql
freeBSD53# pw useradd mysql -g mysql -s /nonexistent
freeBSD53# chown -R root /usr/local/mysql
freeBSD53# chgrp -R mysql /usr/local/mysql
freeBSD53# chmod 777 /usr/local/mysql/data
freeBSD53# chmod 777 /usr/local/mysql/data/mysql
freeBSD53# chmod 777 /usr/local/mysql/data/mysql/*
chmod 777 /usr/local/mysql/lib/libmysqlclient.a
＊／
查看mysql的进程：
freeBSD53# ps -aux | grep mys | more
root 537 0.0 1.3 1656 1120 p0 I 9:34PM 0:00.09 /bin/sh /usr/local/mysql/bin/mysqld_safe
root
549 0.0 26.9 58568 24040 p0 S 9:34PM 0:05.33
/usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql
--datadir=/usr/local/mysql/data --user=mysql --pid
root 1207 0.0 1.0 1472 872 p0 S+ 10:44PM 0:00.02 grep mys
１２０７ 是查询命令本身
杀死mysql的进程
freeBSD53# kill -9 537
[1] Killed /usr/local/mysql/bin/mysqld_safe
freeBSD53# kill -9 549
freeBSD53# ps -aux | grep mys | more
root 1213 0.0 1.0 1424 860 p0 R+ 10:47PM 0:00.02 grep mys
启动mysql服务
freeBSD53# /usr/local/mysql/bin/mysqld_safe --user=mysql &
[3] 1215
freeBSD53# Starting mysqld daemon with databases from /usr/local/mysql/data
根据./configure –help的输出，我做了mysql 的性能测试
freeBSD53# cd /usr/local/mysql/sql-bench
freeBSD53# perl run-all-tests
Got
error: 'Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (61)' when connecting to
DBI:mysql:database=test;host=localhost with user: '' password: ''
如果您有时候出现以上错误信息，说明mysql服务没有启动
（注意/tmp/mysql.sock这个文件经过实验证明是在启动mysql服务之后产生的，如果您不小心删了，重新启动服务就好了，它又出来了，呵呵）
freeBSD53# /usr/local/mysql/bin/mysqld_safe --user=mysql & 启动mysql服务
[1] 537
freeBSD53# chown: mysql: Invalid argument
Starting mysqld daemon with databases from /usr/local/mysql/data
freeBSD53# perl run-all-tests
Can't
locate DBI.pm in @INC (@INC contains:
/usr/local/lib/perl5/site_perl/5.8.5/mach
/usr/local/lib/perl5/site_perl/5.8.5 /usr/local/lib/perl5/site_perl
/usr/local/lib/perl5/5.8.5/BSDPAN /usr/local/lib/perl5/5.8.5/mach
/usr/local/lib/perl5/5.8.5 .) at run-all-tests line 36.
BEGIN failed--compilation aborted at run-all-tests line 36.
这
说明需要perl5.8.5环境（我自己这样理解，它是一种perl语言编程及运行环境，具体的东西还没研究），可以查看显示的/usr/local
/lib/perl5/site_perl等目录知道是否有perl5.8.5，还需要DBI.pm （我后来猜测它是database
interface perl module 的缩写,不知道对不对）
我开始没注意我的freeBSD5.3里是否安装perl5.8.5环境，但既然能运行perl run-all-tests（只是过程出错）那么我起码有较低的perl版本
下面的信息说明您有perl5.8.5
freeBSD53# ls -G /usr/bin/per*
/usr/bin/perl /usr/bin/perl5.8.5
这也许说明现在我的系统里有两个perl 其中perl5.8.5是我后装的，另一个是装系统时装的（仅供参考）
下面是我安装perl5.8.5的过程
freeBSD53# cd /usr/ports/lang/perl5.8
首先，我想试试ports方法安装：
freeBSD53# make;make install;make clean
===>; Vulnerability check disabled, database not found
>;>; perl-5.8.5.tar.bz2 doesn't seem to exist in /usr/ports/distfiles/.
>;>; Attempting to fetch from
perl-5.8.5.tar.bz2 1% of 9242 kB 1115 Bps 02h18m ^C
fetch: transfer interrupted
进度太慢了才9M大就要两个多小时，于是我^C终止这种浪费时间的行为
编译源代码方法安装：
freeBSD53#cp /cdrom/perl-5.8.5.tar.gz /tmp
freeBSD53#cd /tmp
freeBSD53#tar –zvxf perl-5.8.5.tar.gz
freeBSD53#cd perl-5.8.5
以下为perl5在unix下最基本的安装步骤（参考软件说明，详细的有各种参数还没研究），等好长时间
freeBSD53#rm -f config.sh Policy.sh
freeBSD53#sh Configure -de
freeBSD53#make
freeBSD53#make test
freeBSD53#make install
freeBSD53# ls -G /usr/bin/per*
perl perl5.8.5
安装DBI包:
freeBSD53# cp /cdrom/DBI-1.48.tar.gz /tmp
freeBSD53# cd /tmp
freeBSD53# tar -zvxf DBI-1.48.tar.gz
freeBSD53# cd DBI-1.48
freeBSD53# perl Makefile.PL
freeBSD53# make
freeBSD53# make test
freeBSD53# make install
再进行mysql性能测试
freeBSD53# cd /usr/local/mysql/sql-bench
freeBSD53# perl run-all-tests
install_driver(mysql)
failed: Can't locate DBD/mysql.pm in @INC (@INC contains:
/usr/local/lib/perl5/5.8.5/i386-freebsd /usr/local/lib/perl5/5.8.5
/usr/local/lib/perl5/site_perl/5.8.5/i386-freebsd
/usr/local/lib/perl5/site_perl/5.8.5 /usr/local/lib/perl5/site_perl .)
at (eval 51) line 3.
Perhaps the DBD::mysql perl module hasn't been fully installed,
or perhaps the capitalisation of 'mysql' isn't right.
Available drivers: DBM, ExampleP, File, Proxy, Sponge.
at /usr/local/mysql/sql-bench/server-cfg line 247
说明还需要和mysql相关的DBD包
安装DBD包
freeBSD53# cp /cdrom/DBD-mysql-2.9007.tar.gz /tmp
freeBSD53# cd /tmp
freeBSD53# tar -zxvf DBD-mysql-2.9007.tar.gz
freeBSD53# cd DBD-mysql-2.9007
freeBSD53# perl Makefile.PL
Can't exec "mysql_config": No such file or directory at Makefile.PL line 73.
Cannot find the file 'mysql_config'! Your execution PATH doesn't seem
not contain the path to mysql_config. Resorting to guessed values!
Can't exec "mysql_config": No such file or directory at Makefile.PL line 248.
Can't exec "mysql_config": No such file or directory at Makefile.PL line 248.
Can't exec "mysql_config": No such file or directory at Makefile.PL line 248.
Can't exec "mysql_config": No such file or directory at Makefile.PL line 248.
Can't exec "mysql_config": No such file or directory at Makefile.PL line 248.
Can't exec "mysql_config": No such file or directory at Makefile.PL line 248.
Can't exec "mysql_config": No such file or directory at Makefile.PL line 248.
Can't exec "mysql_config": No such file or directory at Makefile.PL line 248.
Can't exec "mysql_config": No such file or directory at Makefile.PL line 248.
Can't exec "mysql_config": No such file or directory at Makefile.PL line 248.
I will use the following settings for compiling and testing:
cflags (guessed) = -I/usr/local/mysql/include
libs (guessed) = -L/usr/local/mysql/lib -lmysqlclient -lz -lm -lcrypt -lnsl
mysql_config (guessed) = mysql_config
nocatchstderr (default) = 0
nofoundrows (default) = 0
ssl (guessed) = 0
testdb (default) = test
testhost (default) =
testpassword (default) =
testsocket (default) =
testuser (default) =
To change these settings, see 'perl Makefile.PL --help' and
'perldoc INSTALL'.
Note (probably harmless): No library found for -lnsl
Using DBI 1.48 (for perl 5.008005 on i386-freebsd) installed in /usr/local/lib/perl5/site_perl/5.8.5/i386-freebsd/auto/DBI/
Writing Makefile for DBD::mysql
这里明显出错了，先不管它
freeBSD53# make
freeBSD53# make test
freeBSD53# make install
Writing /usr/local/lib/perl5/site_perl/5.8.5/mach/auto/DBD/mysql/.packlist
FreeBSD: Registering installation in the package database
Appending installation info to /usr/local/lib/perl5/5.8.5/mach/perllocal.pod
再进行mysql性能测试
freeBSD53# cd /usr/local/mysql/sql-bench
freeBSD53# perl run-all-tests
Got
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
when connecting to DBI:mysql:database=test;host=localhost with user: ''
password: ''
（假如我们现在执行perl run-all-tests --user='root' --password='123456'会成功，我最终实验时才发现）
于是怀疑DBD安装有问题，重新来一遍
freeBSD53#cd /tmp/DBD-mysql-2.9007
freeBSD53# perl Makefile.PL --help
freeBSD53#perl
Makefile.PL --cflags=-I/usr/local/mysql/bin
--mysql_config=/usr/local/mysql/bin/mysql_config --testdb=test
--testuser=root --testpassword="123456"
I will use the following settings for compiling and testing:
cflags (Users choice) = -I/usr/local/mysql/bin
libs (mysql_config) = -L/usr/local/mysql/lib -lmysqlclient -lz -lcrypt -lm
mysql_config (Users choice) = /usr/local/mysql/bin/mysql_config
nocatchstderr (default ) = 0
nofoundrows (default ) = 0
ssl (guessed ) = 0
testdb (Users choice) = test
testhost (default ) =
testpassword (Users choice) = 123456
testsocket (default ) =
testuser (Users choice) = root
To change these settings, see 'perl Makefile.PL --help' and
'perldoc INSTALL'.
Using DBI 1.48 (for perl 5.008005 on i386-freebsd) installed in /usr/local/lib/perl5/site_perl/5.8.5/i386-freebsd/auto/DBI/
Writing Makefile for DBD::mysql
再进行mysql性能测试
freeBSD53# cd /usr/local/mysql/sql-bench
freeBSD53# perl run-all-tests
Got
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
when connecting to DBI:mysql:database=test;host=localhost with user: ''
password: ''
安装Msql-Mysql-modules-1.2219.tar.gz 包
这个包也许不用打，因为我们照样可以用执行perl run-all-tests --user='root' --password='123456'，但我开始不是这样做的
freeBSD53# cp /cdrom/Msql-Mysql-modules-1.2219.tar.gz /tmp
freeBSD53# cd /tmp
freeBSD53# tar -zvxf Msql-Mysql-modules-1.2219.tar.gz
（freeBSD53# rm -rf Msql-Mysql-modules-1.2219 如果已经存在此目录即您已经编译，而现在想改变原来的一些选项，就把这个目录删了重来）
freeBSD53# cd Msql-Mysql-modules-1.2219
freeBSD53# perl Makefile.PL
Which drivers do you want to install?
1) MySQL only
2) mSQL only (either of mSQL 1 or mSQL 2)
3) MySQL and mSQL (either of mSQL 1 or mSQL 2)
4) mSQL 1 and mSQL 2
5) MySQL, mSQL 1 and mSQL 2
Enter the appropriate number: [3] 1
Do you want to install the MysqlPerl emulation? You might keep your old
Mysql module (to be distinguished from DBD::mysql!) if you are concerned
about compatibility to existing applications! [y]
Where is your MySQL installed? Please tell me the directory that
contains the subdir 'include'. [/usr/local/mysql]
Which database should I use for testing the MySQL drivers? [test]
On which host is database test running (hostname, ip address
or host:port) [localhost]
User name for connecting to database test? [undef] root
Password for connecting to database test? [undef] 123456
Creating files for MySQL ..........................
WARNING: PL_FILES takes a hash reference not a array reference.
Please inform the author.
Checking if your kit is complete...
Looks good
Warning: prerequisite Data::ShowTable 0 not found.
Note (probably harmless): No library found for -lgz
Using DBI 1.48 (for perl 5.008005 on i386-freebsd) installed in /usr/local/lib/perl5/site_perl/5.8.5/i386-freebsd/auto/DBI/
Writing Makefile for DBD::mysql
Writing Makefile for Msql-Mysql-modules
再进行mysql性能测试
freeBSD53# cd /usr/local/mysql/sql-bench
freeBSD53# perl run-all-tests
Got
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
when connecting to DBI:mysql:database=test;host=localhost with user: ''
password: ''
还是不行，这时我才想起用--help参数看看，愚昧！……
freeBSD53#perl run-all-tests --help
freeBSD53# perl run-all-tests --user='root' --password='123456'
Benchmark DBD suite: 2.15
Date of test: 2005-05-05 11:43:41
Running tests on: FreeBSD 5.3-RELEASE i386
Arguments:
Comments:
Limits from:
Server version: MySQL 5.0.4 beta max
Optimization: None
Hardware:
alter-table: Total time: 73 wallclock secs ( 0.13 usr 0.57 sys + 0.00 cusr 0. 00 csys = 0.70 CPU)
ATIS: Total time: 121 wallclock secs ( 4.65 usr 10.03 sys + 0.00 cusr 0.00 csy s = 14.68 CPU)
big-tables: Total time: 59 wallclock secs ( 4.66 usr 19.98 sys + 0.00 cusr 0.0 0 csys = 24.65 CPU)
connect: Total time: 1211 wallclock secs (117.99 usr 350.90 sys + 0.00 cusr 0.00 csys = 468.89 CPU)
create: Total time: 634 wallclock secs (10.48 usr 32.87 sys + 0.00 cusr 0.00 csys = 43.34 CPU)
insert: Estimated total time: 15860.4 wallclock secs (1213.83 usr 2626.05 sys + 0.00 cusr 0.00 csys = 3839.89 CPU)
select: Estimated total time: 2235.26 wallclock secs (99.38 usr 168.48 sys + 0.00 cusr 0.00 csys = 267.86 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time: 105 wallclock secs ( 8.43 usr 20.12 sys + 0.00 cusr 0.00 csys = 28.55 CPU)
All 9 test executed successfully
Tests with estimated time have a + at end of line
Totals per operation:
Operation seconds usr sys cpu tests
alter_table_add 29.00 0.02 0.07 0.09 100
alter_table_drop 30.00 0.01 0.09 0.09 91
connect 80.00 8.45 26.48 34.92 10000
connect+select_1_row 98.00 8.77 30.85 39.62 10000
connect+select_simple 98.00 9.60 31.50 41.10 10000
count 33.00 0.03 0.12 0.16 100
count_distinct 45.00 0.76 1.24 2.00 1000
count_distinct_2 119.00 0.58 1.64 2.22 1000
count_distinct_big 99.00 3.52 2.60 6.12 120
count_distinct_group 30.00 0.87 1.41 2.27 1000
count_distinct_group_on_key 61.00 0.66 1.22 1.88 1000
count_distinct_group_on_key_parts 31.00 0.87 1.46 2.33 1000
count_distinct_key_prefix 39.00 0.65 1.40 2.05 1000
count_group_on_key_parts 49.00 0.87 1.06 1.93 1000
count_on_key 734.26 31.99 55.66 87.65 50100 +
create+drop 152.00 2.78 9.18 11.96 10000
create_MANY_tables 158.00 1.84 5.33 7.16 10000
create_index 6.00 0.00 0.01 0.01 8
create_key+drop 177.00 3.13 9.91 13.04 10000
create_table 1.00 0.02 0.03 0.05 31
delete_all_many_keys 972.00 0.06 0.09 0.16 1
delete_big 2.00 0.00 0.00 0.00 1
delete_big_many_keys 972.00 0.06 0.09 0.16 128
delete_key 38.00 2.52 6.30 8.82 10000
delete_range 81.00 0.00 0.01 0.01 12
drop_index 6.00 0.00 0.01 0.01 8
drop_table 0.00 0.00 0.01 0.01 28
drop_table_when_MANY_tables 54.00 1.30 4.36 5.66 10000
insert 737.00 57.51 151.86 209.38 350768
insert_duplicates 147.00 13.16 36.74 49.91 100000
insert_key 2178.00 28.05 78.03 106.08 100000
insert_many_fields 12.00 0.28 1.10 1.36 2000
insert_select_1_key 11.00 0.01 0.00 0.01 1
insert_select_2_keys 15.00 0.00 0.00 0.00 1
min_max 34.00 0.04 0.10 0.14 60
min_max_on_key 361.00 58.76 110.33 169.09 85000
multiple_value_insert 24.00 0.95 2.61 3.56 100000
once_prepared_select 238.00 22.05 55.33 77.38 100000
order_by_big 34.00 6.59 4.78 11.37 10
order_by_big_key 30.00 7.81 7.07 14.88 10
order_by_big_key2 26.00 8.16 5.52 13.68 10
order_by_big_key_desc 30.00 8.20 6.62 14.81 10
order_by_big_key_diff 30.00 7.98 5.20 13.17 10
order_by_big_key_prefix 27.00 6.70 5.94 12.64 10
order_by_key2_diff 25.00 0.62 0.80 1.42 500
order_by_key_prefix 16.00 0.52 0.88 1.39 500
order_by_range 16.00 0.42 0.61 1.03 500
outer_join 428.00 0.01 0.02 0.02 10
outer_join_found 403.00 0.00 0.01 0.01 10
outer_join_not_found 383.00 0.01 0.01 0.02 500
outer_join_on_key 69.00 0.01 0.01 0.02 10
prepared_select 261.00 27.75 78.52 106.27 100000
select_1_row 143.00 12.89 38.28 51.17 100000
select_1_row_cache 155.00 15.02 39.77 54.78 100000
select_2_rows 170.00 13.99 40.73 54.73 100000
select_big 26.00 6.89 5.28 12.17 80
select_big_str 82.00 9.42 28.31 37.73 10000
select_cache 172.00 5.76 8.95 14.70 10000
select_cache2 173.00 5.70 9.11 14.81 10000
select_column+column 154.00 13.28 39.67 52.95 100000
select_diff_key 1.00 0.17 0.50 0.67 500
select_distinct 25.00 0.57 1.02 1.59 800
select_group 220.00 0.90 2.85 3.75 2911
select_group_when_MANY_tables 93.00 1.43 4.09 5.52 10000
select_join 8.00 0.08 0.30 0.38 100
select_key 815.31 122.25 261.24 383.49 200000 +
select_key2 867.76 128.88 261.07 389.94 200000 +
select_key2_return_key 987.45 147.73 305.30 453.03 200000 +
select_key2_return_prim 999.80 150.79 300.04 450.83 200000 +
select_key_prefix 720.74 102.73 221.00 323.74 200000 +
select_key_prefix_join 10.00 1.41 1.23 2.65 100
select_key_return_key 754.30 116.85 241.90 358.75 200000 +
select_many_fields 47.00 4.39 18.88 23.27 2000
select_range 566.00 5.80 4.40 10.20 410
select_range_key2 153.00 18.92 31.35 50.27 25010
select_range_prefix 94.00 10.42 17.87 28.29 25010
select_simple 120.00 13.80 39.45 53.25 100000
select_simple_cache 111.00 12.77 35.81 48.58 100000
select_simple_join 6.00 0.20 0.59 0.80 500
update_big 415.00 0.01 0.00 0.01 10
update_of_key 310.00 15.11 40.48 55.59 50000
update_of_key_big 205.00 0.13 0.36 0.49 501
update_of_primary_key_many_keys 586.00 0.06 0.19 0.25 256
update_with_key 1758.00 142.48 383.09 525.58 300000
update_with_key_prefix 311.00 36.03 91.09 127.12 100000
wisc_benchmark 13.00 1.41 2.50 3.91 114
TOTALS 21001.62 1453.22 3220.92 4674.11 3425950 +++++++
太好了，终于成功，不过这些测试到底是啥意思，还不太清楚，数据库性能优化也许就能用到啦，这是我的虚拟机，在上面的mysql5 性能如何？
阅读(963) | 评论(0) | 转发(0) |
