Chinaunix首页 | 论坛 | 博客
  • 博客访问: 168873
  • 博文数量: 13
  • 博客积分: 2510
  • 博客等级: 少校
  • 技术积分: 185
  • 用 户 组: 普通用户
  • 注册时间: 2008-09-15 14:10
文章分类

全部博文(13)

文章存档

2008年(13)

我的朋友
最近访客

分类: Mysql/postgreSQL

2008-11-06 11:07:24

转载地址: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
1207 是查询命令本身

杀死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 性能如何?
阅读(967) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~