openEuler 下 Mysql 和 sysbench 测试环境搭建

openEuler 下 Mysql 和 sysbench 测试环境搭建

环境信息

[root@localhost local]# cat /etc/os-releaseNAME="openEuler"VERSION="22.03 (LTS-SP1)"ID="openEuler"VERSION_ID="22.03"PRETTY_NAME="openEuler 22.03 (LTS-SP1)"ANSI_COLOR="0;31"

概述

/etc/my.cnf 是配置文件，安装后运行前需要配置好。

生成测试数据较费时，可以将生成的数据备份起来，以后直接拷贝到 /data/文件夹下

安装流程

Mysql 安装

配置环境

groupadd mysqluseradd -g mysql mysqlpasswd mysqlrm -rf /data/mysqlmkdir -p /data/mysqlcd /data/mysqlmkdir data tmp run log relaylogchown -R mysql:mysql /data[root@localhost mysql]# ll /总用量 68dr-xr-xr-x. 2 root root4096 12月 27 19:45 afslrwxrwxrwx. 1 root root 7 12月 27 19:45 bin -> usr/bindr-xr-xr-x. 7 root root40965月 24 20:06 bootdrwxr-xr-x. 3 mysqlmysql 40966月 20 21:58 data

安装mysql

[root@localhost ~]# yum install mysql[root@localhost ~]# which mysql/usr/bin/mysql或[root@localhost sysbench-0.5-script]# which mysql/usr/local/mysql/bin/mysql[root@localhost /]# rpm -qa | grep mysqlmysql-common-8.0.29-2.oe2203sp1.x86_64mysql-8.0.29-2.oe2203sp1.x86_64

安装mysql-server
在yum install mysql-server 后，才会找到mysqld

[root@localhost local]# yum install mysql-server[root@localhost local]# find / -name mysqld/run/mysqld/usr/libexec/mysqld/usr/sbin/mysqld/etc/logrotate.d/mysqld

运行

如果是安装在/usr/local/mysql下

rm -f /etc/my.cnftouch /etc/my.cnfvim /etc/my.cnf

[mysqld_safe]log-error=/data/mysql/log/mysql.logpid-file=/data/mysql/run/mysqld.pid[mysqldump]quick[mysql]no-auto-rehash[client]default-character-set=utf8[mysqld]basedir=/usr/local/mysqlsocket=/data/mysql/run/mysql.socktmpdir=/data/mysql/tmpdatadir=/data/mysql/datadefault_authentication_plugin=mysql_native_passwordport=3306user=mysql#skip-grant-tables

安装在/usr 下

[mysqld_safe]log-error=/data/mysql/log/mysql.logpid-file=/data/mysql/run/mysqld.pid[mysqldump]quick[mysql]no-auto-rehash[client]default-character-set=utf8[mysqld]#basedir=/usrsocket=/data/mysql/run/mysql.socktmpdir=/data/mysql/tmpdatadir=/data/mysql/datadefault_authentication_plugin=mysql_native_passwordport=3306user=mysql#skip-grant-tables

修改配置文件“/etc/my.cnf”的用户组和用户权限为mysql:mysql

chown mysql:mysql /etc/my.cnfll /etc/my.cnf

注意： 以下3步适用于20.03，而22.03我没有找到mysql.server文件，索性不用这种方式启动。此外2203在yum install之后，就可以找到which mysql，不用配置环境变量
MySQL加入service服务

chmod 777 /usr/local/mysql/support-files/mysql.servercp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqlchkconfig mysql on

修改“/etc/init.d/mysql”的用户组和用户权限为mysql:mysql

chown -R mysql:mysql /etc/init.d/mysqlll /etc/init.d/mysql

配置环境变量,并使配置生效

echo export PATH=$PATH:/usr/local/mysql/bin >> /etc/profilesource /etc/profileenvwhich mysql

切换到mysql用户

su - mysqlwhoami

初始化数据库

mysqld --defaults-file=/etc/my.cnf --initialize

或

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize

初始化完成后，查看数据目录下数据文件“/data/mysql/data”的用户组和用户权限为mysql:mysql（因为前面/etc/my.cnf文件中配置的操作系统用户是user=mysql）。初始化会根据my.cnf配置一些环境，并生成一个初始密码,登录的时候会用到。假如终端上没有显示，可能是输出到了/data/mysql/log/ 下面的文件中

[mysql@localhost mysql]$ mysqld --defaults-file=/etc/my.cnf --initialize2023-06-21T03:31:50.933036Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.2023-06-21T03:31:50.933046Z 0 [System] [MY-013169] [Server] /usr/libexec/mysqld (mysqld 8.0.29) initializing of server in progress as process 82642023-06-21T03:31:50.940853Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.2023-06-21T03:31:51.248956Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.2023-06-21T03:31:52.592374Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: bM3hna>?ioE:

启动数据库

[mysql@localhost mysql]$ which mysqld/usr/sbin/mysqld

以下三种方式都可以，2203推荐第三个，因为service前面没有注册

/usr/sbin/mysqld --defaults-file=/etc/my.cnf &service mysql startmysqld --defaults-file=/etc/my.cnf &

以下三种方式登录

mysql -uroot -p -S /data/mysql/run/mysql.sock/usr/local/mysql/bin/mysql -uroot -p -S /data/mysql/run/mysql.sock

配置数据库帐号密码
(1)登录数据库以后，修改通过root用户登录数据库的密码

alter user 'root'@'localhost' identified by "123456";

(2)创建全域root用户（允许root从其他服务器访问

create user 'root'@'%' identified by '123456';

(3)进行授权

grant all privileges on *.* to 'root'@'%';flush privileges;

退出数据库

exit

关闭数据库：三种方式

service mysql stopmysqladmin -uroot -p123456 shutdown -S /data/mysql/run/mysql.sock/usr/local/mysql/bin/mysqladmin -uroot -p123456 shutdown -S /data/mysql/run/mysql.sockps -ef | grep mysql

sysbench 安装及测试

yum -y install automake libtool* mariadb-develcd /homewget https://github.com/akopytov/sysbench/archive/0.5.zip --no-check-certificatemv 0.5.zip sysbench-0.5.zipunzip sysbench-0.5.zip

vi /home/sysbench-0.5/sysbench/drivers/mysql/drv_mysql.c

在mysql_drv_real_connect函数末尾找到“return mysql_real_connect(…) == NULL;”。
替换

#if 0return mysql_real_connect(con,db_mysql_con->host,db_mysql_con->user,db_mysql_con->password,db_mysql_con->db,db_mysql_con->port,db_mysql_con->socket,#if MYSQL_VERSION_ID >= 50000CLIENT_MULTI_STATEMENTS#else0#endif) == NULL;#elseunsigned int timeout = 5; if (mysql_options(con, MYSQL_OPT_CONNECT_TIMEOUT, (const char *)&timeout)) {DEBUG("0x%p mysql_options MYSQL_OPT_CONNECT_TIMEOUT failed", con);}int ret = 0;int i = 0;for (; i host,db_mysql_con->user,db_mysql_con->password,db_mysql_con->db,db_mysql_con->port,db_mysql_con->socket,#if MYSQL_VERSION_ID >= 50000CLIENT_MULTI_STATEMENTS#else0#endif) == NULL);if (ret == 0) {DEBUG("0x%p mysql_options succeed", con);break;} DEBUG("0x%p mysql_options MYSQL_OPT_CONNECT_TIMEOUT timeout", con);usleep(1000);} return ret;#endif

编译安装

 ./autogen.sh ./configure make -j128 make -j128 install

上述步骤最好在/home下操作，因为后续调用的测试脚本（比如test_oltp_mix.sh）里面的路径是写死的。
登录数据库后创建sysbench数据库：

create database sysbench;show databases;use sysbench;show tables;

登录Sysbench工具客户端，向MySQL数据库服务器导入数据。
执行如下命令向服务器导入测试数据，数据量为100张表*1万行数据（100*10000）

/home/sysbench-0.5/sysbench/sysbench --db-driver=mysql --test=/home/sysbench-0.5/sysbench/tests/db/parallel_prepare.lua --oltp-test-mode=complex --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=sysbench --mysql-user=root --mysql-password=123456 --max-time=7200 --max-requests=0 --mysql-table-engine=innodb --oltp-table-size=10000 --oltp-tables-count=100 --rand-type=special --rand-spec-pct=100 --num-threads=16 prepare

注意根据机器能力和使用场景，生成数据，自己的小虚拟机数据生成小一点。这个脚本末尾时prepare
一些数据库相关的lua测试代码：

[root@localhost sysbench-0.5]# cd sysbench/tests/db/[root@localhost db]# lsbulk_insert.luadelete.luaMakefile Makefile.inoltp_simple.lua select.luaselect_random_ranges.luaupdate_non_index.luacommon.lua insert.luaMakefile.amoltp.lua parallel_prepare.luaselect_random_points.luaupdate_index.lua

下面的命令会生成sbtest1~100

[root@localhost db]# /home/sysbench-0.5/sysbench/sysbench --db-driver=mysql --test=/home/sysbench-0.5/sysbench/tests/db/parallel_prepare.lua --oltp-test-mode=complex --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=sysbench --mysql-user=root --mysql-password=123456 --max-time=7200 --max-requests=0 --mysql-table-engine=innodb --oltp-table-size=10000 --oltp-tables-count=100 --rand-type=special --rand-spec-pct=100 --num-threads=16 preparesysbench 0.5:multi-threaded system evaluation benchmarkCreating table 'sbtest1'...Inserting 10000 records into 'sbtest1'Creating secondary indexes on 'sbtest1'...Creating table 'sbtest2'...Inserting 10000 records into 'sbtest2'Creating secondary indexes on 'sbtest2'...Creating table 'sbtest3'...

生成的数据在my.cnf配置的路径下(sysbench时前面我们创建的数据库)：

[root@localhost sysbench]# pwd/data/mysql/data/sysbench[root@localhost sysbench]# lssbtest100.ibdsbtest16.ibdsbtest22.ibdsbtest29.ibdsbtest35.ibdsbtest41.ibdsbtest48.ibdsbtest54.ibdsbtest60.ibdsbtest67.ibdsbtest73.ibdsbtest7.ibd sbtest86.ibdsbtest92.ibdsbtest99.ibdsbtest10.ibd sbtest17.ibdsbtest23.ibdsbtest2.ibd sbtest36.ibdsbtest42.ibdsbtest49.ibdsbtest55.ibdsbtest61.ibdsbtest68.ibdsbtest74.ibdsbtest80.ibdsbtest87.ibdsbtest93.ibdsbtest9.ibdsbtest11.ibd sbtest18.ibdsbtest24.ibdsbtest30.ibdsbtest37.ibdsbtest43.ibdsbtest4.ibd sbtest56.ibdsbtest62.ibdsbtest69.ibdsbtest75.ibdsbtest81.ibdsbtest88.ibdsbtest94.ibdsbtest12.ibd sbtest19.ibdsbtest25.ibdsbtest31.ibdsbtest38.ibdsbtest44.ibdsbtest50.ibdsbtest57.ibdsbtest63.ibdsbtest6.ibd sbtest76.ibdsbtest82.ibdsbtest89.ibdsbtest95.ibdsbtest13.ibd sbtest1.ibd sbtest26.ibdsbtest32.ibdsbtest39.ibdsbtest45.ibdsbtest51.ibdsbtest58.ibdsbtest64.ibdsbtest70.ibdsbtest77.ibdsbtest83.ibdsbtest8.ibd sbtest96.ibdsbtest14.ibd sbtest20.ibdsbtest27.ibdsbtest33.ibdsbtest3.ibd sbtest46.ibdsbtest52.ibdsbtest59.ibdsbtest65.ibdsbtest71.ibdsbtest78.ibdsbtest84.ibdsbtest90.ibdsbtest97.ibdsbtest15.ibd sbtest21.ibdsbtest28.ibdsbtest34.ibdsbtest40.ibdsbtest47.ibdsbtest53.ibdsbtest5.ibd sbtest66.ibdsbtest72.ibdsbtest79.ibdsbtest85.ibdsbtest91.ibdsbtest98.ibd

如果生成的数据量太大，/目录下放不下：
可以建立一个软连接（假设/home空间大）：

mv /data /home/mysql_dataln -s /home/mysql_data/data /data

删除操作：

[root@localhost sysbench-0.5-script]# /home/sysbench-0.5/sysbench/sysbench --db-driver=mysql --test=/home/sysbench-0.5/sysbench/tests/db/parallel_prepare.lua --oltp-test-mode=complex --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=sysbench --mysql-user=root --mysql-password=123456 --max-time=7200 --max-requests=0 --mysql-table-engine=innodb --oltp-table-size=10000 --oltp-tables-count=100 --rand-type=special --rand-spec-pct=100 --num-threads=16 cleanupsysbench 0.5:multi-threaded system evaluation benchmarkDropping table 'sbtest1'...Dropping table 'sbtest2'...Dropping table 'sbtest3'...Dropping table 'sbtest4'...Dropping table 'sbtest5'...

测试脚本：

wget https://mirrors.huaweicloud.com/kunpeng/archive/kunpeng_solution/database/scripts/sysbench-0.5-script.zip --no-check-certificate[root@localhost sysbench]# unzip sysbench-0.5-script.zip[root@localhost sysbench-0.5-script]# lsdata_filter.shrunall.shtest_oltp_delete.shtest_oltp_index.shtest_oltp_nonindex.shtest_oltp_point.sh test_oltp_sum.shprepare.shrun_sysbench.shtest_oltp_distinct.shtest_oltp_mix.shtest_oltp_order.sh test_oltp_simple.sh

里面包含了8个场景和一个mix场景的测试脚本，以mix场景测试为例（需要登录数据库）：

chmod 777 *./test_oltp_mix.sh -h 127.0.0.1-P 3306-u root -p 123456-D sysbench -C 100 -S 10000 -t 16 -c run

-D sysbench ： 选择sysbench数据库

只要数据库没重启，执行写场景（INDEX、NONINDEX、DELETE）之前，需要执行1次mix场景进行预热。

输出如下：

[root@localhost sysbench-0.5-script]# ./test_oltp_mix.sh -h 127.0.0.1-P 3306-u root -p 123456-D sysbench -C 100 -S 10000 -t 16 -c runCMD=run ENGINE=innodbsysbench 0.5:multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 16Report intermediate results every 1 second(s)Random number generator seed is 0 and will be ignoredForcing shutdown in 121 secondsInitializing worker threads...Threads started![ 1s] threads: 16, tps: 549.88, reads: 7883.31, writes: 2227.52, response time: 46.10ms (95%), errors: 0.00, reconnects:0.00[ 2s] threads: 16, tps: 590.98, reads: 8282.75, writes: 2362.93, response time: 45.58ms (95%), errors: 0.00, reconnects:0.00[ 3s] threads: 16, tps: 619.63, reads: 8649.90, writes: 2479.54, response time: 42.47ms (95%), errors: 0.00, reconnects:0.00[ 4s] threads: 16, tps: 414.05, reads: 5781.72, writes: 1665.21, response time: 111.58ms (95%), errors: 0.00, reconnects:0.00[ 5s] threads: 16, tps: 445.20, reads: 6252.83, writes: 1770.80, response time: 93.83ms (95%), errors: 0.00, reconnects:0.00...[ 119s] threads: 16, tps: 562.10, reads: 7868.40, writes: 2243.40, response time: 49.40ms (95%), errors: 0.00, reconnects:0.00[ 120s] threads: 16, tps: 642.05, reads: 8928.74, writes: 2537.21, response time: 42.46ms (95%), errors: 0.00, reconnects:0.00OLTP test statistics:queries performed:read:988078write: 282308other: 141154total: 1411540transactions:70577(588.03 per sec.)read/write requests: 1270386 (10584.47 per sec.)other operations:141154 (1176.05 per sec.)ignored errors:0(0.00 per sec.)reconnects:0(0.00 per sec.)General statistics:total time:120.0236stotal number of events:70577total time taken by event execution: 1920.0367sresponse time: min:4.85ms avg: 27.20ms max:322.16ms approx.95 percentile:49.79msThreads fairness:events (avg/stddev): 4411.0625/31.27execution time (avg/stddev): 120.0023/0.01

注意

安装mysql，在第一次启动前，要初始化数据库，完成data文件夹初始化，和密码初始化。
启动mysql后，再执行登录操作
一般生成大量数据较慢，频繁测试建议把数据存到/data/路径下，下次拷贝过来直接用
可以参考test_oltp_mix.s 自定义脚本

常用操作

查看数据库进程

ps -ef | grep mysql

查看数据库版本

[mysql@localhost mysql]$ mysql -VmysqlVer 8.0.29 for Linux on x86_64 (Source distribution)

查看数据库监测端口

 yum -y install net-tools netstat -anpt netstat -anpt | grep mysql netstat -anpt | grep 3306

启动，关闭数据库

mysql> show processlist;

可能遇到的问题

不同yum源 install的路径可能不一样，需要在my.cnf中适配

某些操作失败，尝试关闭selinux

mysqld: Can't create/write to file '/data/mysql/tmp/ibV4zkJM

[root@localhost local]# getenforceEnforcing[root@localhost local]# setenforce 0[root@localhost local]# getenforcePermissive

查看SELinux状态及关闭SELinux

初始化/启动数据库失败，DATA路径无效

[root@localhost local]# 2023-06-21T03:27:55.469633Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.2023-06-21T03:27:55.469650Z 0 [System] [MY-010116] [Server] /usr/libexec/mysqld (mysqld 8.0.29) starting as process 81542023-06-21T03:27:55.475745Z 0 [Warning] [MY-010075] [Server] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 9c9f84fb-0fe3-11ee-b5c5-000c29d346e8.2023-06-21T03:27:55.481920Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.2023-06-21T03:27:55.482154Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.2023-06-21T03:27:55.482230Z 0 [ERROR] [MY-010119] [Server] Aborting2023-06-21T03:27:55.482810Z 0 [System] [MY-010910] [Server] /usr/libexec/mysqld: Shutdown complete (mysqld 8.0.29)Source distribution.

解决方案：删除data目录，重建相应文件夹，然后重新初始化

service mysql start 失败

[mysql@localhost mysql]$ service mysql startRedirecting to /bin/systemctl start mysql.serviceFailed to start mysql.service: Interactive authentication required.See system logs and 'systemctl status mysql.service' for details.

原因：MySQL没有加入service服务

chmod 777 /usr/local/mysql/support-files/mysql.servercp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqlchkconfig mysql onchown -R mysql:mysql /etc/init.d/mysqlll /etc/init.d/mysql

22.03 找不到 mysql.server

不用server 方式启动，用mysqld 方式启动

找不到 .sock 文件

[root@localhost ~]# /usr/local/mysql/bin/mysql -uroot -p -S /data/mysql/run/mysql.sockEnter password:ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data/mysql/run/mysql.sock' (2)

启动数据库后才会有这个文件

安装sysbench时 执行./configure报错，缺少mysql-lib

configure: error: mysql_config executable not found********************************************************************************ERROR: cannot find MySQL libraries. If you want to compile with MySQL support, please install the package containing MySQL client libraries and headers. On Debian-based systems the package name is libmysqlclient-dev. On RedHat-based systems, it is mysql-devel. If you have those libraries installed in non-standard locations, you must either specify file locations explicitly using --with-mysql-includes and --with-mysql-libs options, or make sure path to mysql_config is listed in your PATH environment variable. If you want to disable MySQL support, use --without-mysql option.********************************************************************************

解决方法：

yum install mariadb-devel

这里install mysql-devel 也会解决这个问题，但是在后续make的时候会报下面的错误，可能openEuler上的mysql-devel包没适配my_bool，用mariadb-devel代替

make[2]: 进入目录“/home/sysbench/sysbench-0.5/sysbench/drivers”Making all in mysqlmake[3]: 进入目录“/home/sysbench/sysbench-0.5/sysbench/drivers/mysql”gcc -DHAVE_CONFIG_H -I. -I../../../config-I/usr/include/mysql -m64-I../../../sysbench -D_XOPEN_SOURCE=500 -D_GNU_SOURCE-W -Wall -Wextra -Wpointer-arith -Wbad-function-cast -Wstrict-prototypes -Wnested-externs -Winline -funroll-loops-Wundef -Wstrict-prototypes -Wmissing-prototypes -Wmissing-declarations -Wredundant-decls -Wcast-align -O2 -ggdb3-MT libsbmysql_a-drv_mysql.o -MD -MP -MF .deps/libsbmysql_a-drv_mysql.Tpo -c -o libsbmysql_a-drv_mysql.o `test -f 'drv_mysql.c' || echo './'`drv_mysql.cdrv_mysql.c: 在函数‘mysql_drv_bind_param’中:drv_mysql.c:535:3: 错误：unknown type name ‘my_bool’; did you mean ‘bool’?535 | my_bool rc;| ^~~~~~~| booldrv_mysql.c:568:23: 警告：assignment to ‘_Bool *’ from incompatible pointer type ‘char *’ [-Wincompatible-pointer-types]568 | bind[i].is_null = params[i].is_null;| ^drv_mysql.c: 在函数‘mysql_drv_bind_result’中:drv_mysql.c:618:3: 错误：unknown type name ‘my_bool’; did you mean ‘bool’?618 | my_bool rc;| ^~~~~~~| booldrv_mysql.c:633:21: 警告：assignment to ‘_Bool *’ from incompatible pointer type ‘char *’ [-Wincompatible-pointer-types]633 | bind[i].is_null = params[i].is_null;| ^make[3]: *** [Makefile:440：libsbmysql_a-drv_mysql.o] 错误 1make[3]: 离开目录“/home/sysbench/sysbench-0.5/sysbench/drivers/mysql”make[2]: *** [Makefile:415：all-recursive] 错误 1make[2]: 离开目录“/home/sysbench/sysbench-0.5/sysbench/drivers”make[1]: *** [Makefile:586：all-recursive] 错误 1make[1]: 离开目录“/home/sysbench/sysbench-0.5/sysbench”make: *** [Makefile:468：all-recursive] 错误 1

拷贝文件后重新运行数据库，发现数据库自动关闭

cat /data/mysql/log/mysql.log

查看错误日志，发现是找不到bin.index,但这个文件是存在的。
原因：copy文件时没有修改文件owner，

chown -R mysql:mysql /data/mysql/ll /data/mysql/data/

参考链接

文档首页>鲲鹏BoostKit数据库使能套件>开源使能>MySQL>安装指南
文档首页>鲲鹏BoostKit数据库使能套件>测试指导>测试指导>Sysbench 0.5 测试指导

my.cnf 相关
MySQL 配置文件 my.cnf / my.ini 逐行详解
mysql配置my.cnf中!include和!includedir的作用

mysql 程序概述