ANBOB™

Next, let’s create the mysql user and group:

groupadd mysql
useradd -g mysql mysql

create mysql soft and data files store dictroy. and install mysql

mkdir -p /data/mysql

tar zxvf mysql-5.5.36-linux2.6-x86_64.tar.gz
mv mysql-5.5.36-linux2.6-x86_64 /data/mysql/

cd /usr/local
ln -s /data/mysql/mysql-5.5.36-linux2.6-x86_64/ mysql

scripts/mysql_install_db --user=mysql

or (./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data)

Modify File and Directory Permissions

cd /data/mysql/
chown -R root .
chown -R mysql data
chgrp -R mysql .

cp support-files/my-huge.cnf   /etc/my.cnf -- 可以自己选配置大中小

To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system

cp support-files/mysql.server   /etc/init.d/mysql
# chkconfig --add mysql
# chkconfig mysql on
# chkconfig --list|grep mysql
mysql           0:off   1:off   2:on    3:on    4:on    5:on    6:off

# service mysql start
Starting MySQL..                                           [  OK  ]

Once you are happy with the config you can start mysql manually using:

bin/mysqld_safe --user=mysql &

You can stop it manually with:

/usr/local/mysql/bin/mysqladmin shutdown
or
service mysql stop

Security Configuration

mysql> use mysql
mysql> delete from user where user='';
mysql> delete from user where user='root' and host='%';

or perform interactive script file

./bin/mysql_secure_installation
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 Ceit3gxxx

# ./bin/mysqladmin -u root -h mysqlp password 'Ceit3gxxx'

install sysbench

[root@mysqlp mysql]# rpm -ql MySQL-devel
/usr/bin/mysql_config
/usr/include/mysql
/usr/include/mysql/decimal.h
/usr/include/mysql/errmsg.h
/usr/include/mysql/keycache.h
...

记住mysql-devel 的路径/usr/include/mysql 下面安装sysbench要用。确认前安装MySQL-devel 否则make 会提示错误：

drv_mysql.c:35:19: error: mysql.h: No such file or directory
drv_mysql.c:36:26: error: mysqld_error.h: No such file or directory
drv_mysql.c: In function a€?mysql_drv_describea€?:
drv_mysql.c:284: error: a€?MYSQL_RESa€? undeclared (first use in this function)

wget http://sourceforge.net/projects/sysbench/files/sysbench/0.4.12/sysbench-0.4.12.tar.gz/download?use_mirror=nchc&r=http%3A%2F%2Fsourceforge.net%2Fprojects%2Fsysbench%2F&use_mirror=nchc

tar zxvf sysbench-0.4.12.tar.gz

cd sysbench-0.4.12

# ./configure --prefix=/usr/local/sysbench --with-mysql-includes=/usr/include/mysql --with-mysql-libs=/usr/local/mysql/lib

TIP:
with-mysql-includes 指定上面确认的mysql-devel的路径，with-mysql-libs 是安装mysql的路径，我的案例使用的是mysql5.5 的tar版本。是建立软链到/usr/local/mysql 这个常用路径的。

#vi libtool

找到ECHO="echo"行，将ECHO="echo"改成echo="echo",否则编译的时候会提示下面的错误：

Fatal server error:
no screens found
../libtool: line 5172: : command not found
make[2]: Leaving directory `/root/sysbench-0.4.12/sysbench'

#make

#make install

[root@mysqlp sysbench]# ll /usr/local/sysbench/
total 8
drwxr-xr-x 2 root root 4096 Feb 27 14:15 bin
drwxr-xr-x 3 root root 4096 Feb 27 12:54 share

[root@mysqlp bin]# ls
sysbench
[root@mysqlp bin]# pwd
/usr/local/sysbench/bin

[root@mysqlp bin]# ln -s /usr/local/sysbench/bin/sysbench /bin/sysbench

mysql> create database loadtest;
Query OK, 1 row affected (0.00 sec)

mysql> grant all privileges on loadtest.* to weejar@"localhost" identified by "weejar";
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-socket=/tmp/mysql.sock --mysql-db=loadtest --mysql-host=localhost --mysql-user=weejar --mysql-password=weejar --num-threads=128 prepare

问题

[root@mysqlp bin]# ./sysbench
./sysbench: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory

解决方法

[root@mysqlp ~]# export LD_LIBRARY_PATH=/usr/local/mysql/lib

[root@mysqlp ~]# sysbench
Missing required command argument.
Usage:
sysbench [general-options]... --test= [test-options]... command

General options:
--num-threads=N number of threads to use [1]
--max-requests=N limit for total number of requests [10000]
--max-time=N limit for total execution time in seconds [0]
--forced-shutdown=STRING amount of time to wait after --max-time before forcing shutdown [off]
--thread-stack-size=SIZE size of stack per thread [32K]
--init-rng=[on|off] initialize random number generator [off]
--test=STRING test to run
--debug=[on|off] print more debugging info [off]
--validate=[on|off] perform validation checks where possible [off]
--help=[on|off] print help and exit
--version=[on|off] print version and exit

[root@mysqlp ~]# sysbench --test=oltp help
sysbench 0.4.12: multi-threaded system evaluation benchmark

oltp options:
--oltp-test-mode=STRING test type to use {simple,complex,nontrx,sp} [complex]
--oltp-reconnect-mode=STRING reconnect mode {session,transaction,query,random} [session]
--oltp-sp-name=STRING name of store procedure to call in SP test mode []
--oltp-read-only=[on|off] generate only 'read' queries (do not modify database) [off]
--oltp-skip-trx=[on|off] skip BEGIN/COMMIT statements [off]
--oltp-range-size=N range size for range queries [100]
--oltp-point-selects=N number of point selects [10]
--oltp-simple-ranges=N number of simple ranges [1]
--oltp-sum-ranges=N number of sum ranges [1]
--oltp-order-ranges=N number of ordered ranges [1]
--oltp-distinct-ranges=N number of distinct ranges [1]
--oltp-index-updates=N number of index update [1]
--oltp-non-index-updates=N number of non-index updates [1]
--oltp-nontrx-mode=STRING mode for non-transactional test {select, update_key, update_nokey, insert, delete} [select]
--oltp-auto-inc=[on|off] whether AUTO_INCREMENT (or equivalent) should be used on id column [on]
--oltp-connect-delay=N time in microseconds to sleep after connection to database [10000]
--oltp-user-delay-min=N minimum time in microseconds to sleep after each request [0]
--oltp-user-delay-max=N maximum time in microseconds to sleep after each request [0]
--oltp-table-name=STRING name of test table [sbtest]
--oltp-table-size=N number of records in test table [10000

准备数据

  [root@mysqlp ~]# sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-socket=/tmp/mysql.sock --mysql-db=loadtest --mysql-host=localhost --mysql-user=weejar --mysql-password=weejar --num-threads=128 prepare
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Creating table 'sbtest'...
Creating 1000000 records in table 'sbtest'...

测试

sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-socket=/tmp/mysql.sock --mysql-db=loadtest --mysql-host=localhost --mysql-user=weejar --mysql-password=weejar --num-threads=200 run

threads   tps
200		2806
100		2836
50		2938
10		2226

清理

sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-socket=/tmp/mysql.sock --mysql-db=loadtest --mysql-host=localhost --mysql-user=weejar --mysql-password=weejar --num-threads=200 run
drop database loadtest;

打赏

微信扫一扫，打赏作者吧～