openEuler 下 Mysql 和 sysbench 测试环境搭建
环境信息
[root@localhost local]# cat /etc/os-release NAME="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 mysql useradd -g mysql mysql passwd mysql rm -rf /data/mysql mkdir -p /data/mysql cd /data/mysql mkdir data tmp run log relaylog chown -R mysql:mysql /data [root@localhost mysql]# ll / 总用量 68 dr-xr-xr-x. 2 root root 4096 12月 27 19:45 afs lrwxrwxrwx. 1 root root 7 12月 27 19:45 bin -> usr/bin dr-xr-xr-x. 7 root root 4096 5月 24 20:06 boot drwxr-xr-x. 3 mysql mysql 4096 6月 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 mysql mysql-common-8.0.29-2.oe2203sp1.x86_64 mysql-8.0.29-2.oe2203sp1.x86_64
安装mysql-server
在
[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.cnf touch /etc/my.cnf vim /etc/my.cnf
[mysqld_safe] log-error=/data/mysql/log/mysql.log pid-file=/data/mysql/run/mysqld.pid [mysqldump] quick [mysql] no-auto-rehash [client] default-character-set=utf8 [mysqld] basedir=/usr/local/mysql socket=/data/mysql/run/mysql.sock tmpdir=/data/mysql/tmp datadir=/data/mysql/data default_authentication_plugin=mysql_native_password port=3306 user=mysql #skip-grant-tables
安装在/usr 下
[mysqld_safe] log-error=/data/mysql/log/mysql.log pid-file=/data/mysql/run/mysqld.pid [mysqldump] quick [mysql] no-auto-rehash [client] default-character-set=utf8 [mysqld] #basedir=/usr socket=/data/mysql/run/mysql.sock tmpdir=/data/mysql/tmp datadir=/data/mysql/data default_authentication_plugin=mysql_native_password port=3306 user=mysql #skip-grant-tables
修改配置文件“/etc/my.cnf”的用户组和用户权限为mysql:mysql
chown mysql:mysql /etc/my.cnf ll /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.server cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql chkconfig mysql on
修改“/etc/init.d/mysql”的用户组和用户权限为mysql:mysql
chown -R mysql:mysql /etc/init.d/mysql ll /etc/init.d/mysql
配置环境变量,并使配置生效
echo export PATH=$PATH:/usr/local/mysql/bin >> /etc/profile source /etc/profile env which mysql
切换到mysql用户
su - mysql whoami
初始化数据库
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 --initialize 2023-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 8264 2023-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 start mysqld --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 stop mysqladmin -uroot -p123456 shutdown -S /data/mysql/run/mysql.sock /usr/local/mysql/bin/mysqladmin -uroot -p123456 shutdown -S /data/mysql/run/mysql.sock ps -ef | grep mysql
sysbench 安装及测试
yum -y install automake libtool* mariadb-devel cd /home wget https://github.com/akopytov/sysbench/archive/0.5.zip --no-check-certificate mv 0.5.zip sysbench-0.5.zip unzip 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 0
return 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 >= 50000
CLIENT_MULTI_STATEMENTS
#else
0
#endif
) == NULL;
#else
unsigned 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 < 5; i++) {
ret = (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 >= 50000
CLIENT_MULTI_STATEMENTS
#else
0
#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]# ls bulk_insert.lua delete.lua Makefile Makefile.in oltp_simple.lua select.lua select_random_ranges.lua update_non_index.lua common.lua insert.lua Makefile.am oltp.lua parallel_prepare.lua select_random_points.lua update_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 prepare sysbench 0.5: multi-threaded system evaluation benchmark Creating 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]# ls sbtest100.ibd sbtest16.ibd sbtest22.ibd sbtest29.ibd sbtest35.ibd sbtest41.ibd sbtest48.ibd sbtest54.ibd sbtest60.ibd sbtest67.ibd sbtest73.ibd sbtest7.ibd sbtest86.ibd sbtest92.ibd sbtest99.ibd sbtest10.ibd sbtest17.ibd sbtest23.ibd sbtest2.ibd sbtest36.ibd sbtest42.ibd sbtest49.ibd sbtest55.ibd sbtest61.ibd sbtest68.ibd sbtest74.ibd sbtest80.ibd sbtest87.ibd sbtest93.ibd sbtest9.ibd sbtest11.ibd sbtest18.ibd sbtest24.ibd sbtest30.ibd sbtest37.ibd sbtest43.ibd sbtest4.ibd sbtest56.ibd sbtest62.ibd sbtest69.ibd sbtest75.ibd sbtest81.ibd sbtest88.ibd sbtest94.ibd sbtest12.ibd sbtest19.ibd sbtest25.ibd sbtest31.ibd sbtest38.ibd sbtest44.ibd sbtest50.ibd sbtest57.ibd sbtest63.ibd sbtest6.ibd sbtest76.ibd sbtest82.ibd sbtest89.ibd sbtest95.ibd sbtest13.ibd sbtest1.ibd sbtest26.ibd sbtest32.ibd sbtest39.ibd sbtest45.ibd sbtest51.ibd sbtest58.ibd sbtest64.ibd sbtest70.ibd sbtest77.ibd sbtest83.ibd sbtest8.ibd sbtest96.ibd sbtest14.ibd sbtest20.ibd sbtest27.ibd sbtest33.ibd sbtest3.ibd sbtest46.ibd sbtest52.ibd sbtest59.ibd sbtest65.ibd sbtest71.ibd sbtest78.ibd sbtest84.ibd sbtest90.ibd sbtest97.ibd sbtest15.ibd sbtest21.ibd sbtest28.ibd sbtest34.ibd sbtest40.ibd sbtest47.ibd sbtest53.ibd sbtest5.ibd sbtest66.ibd sbtest72.ibd sbtest79.ibd sbtest85.ibd sbtest91.ibd sbtest98.ibd
如果生成的数据量太大，/目录下放不下：
可以建立一个软连接（假设/home空间大）：
mv /data /home/mysql_data ln -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 cleanup sysbench 0.5: multi-threaded system evaluation benchmark Dropping 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]# ls data_filter.sh runall.sh test_oltp_delete.sh test_oltp_index.sh test_oltp_nonindex.sh test_oltp_point.sh test_oltp_sum.sh prepare.sh run_sysbench.sh test_oltp_distinct.sh test_oltp_mix.sh test_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
只要数据库没重启，执行写场景（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 run
CMD=run ENGINE=innodb
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 16
Report intermediate results every 1 second(s)
Random number generator seed is 0 and will be ignored
Forcing shutdown in 121 seconds
Initializing 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.00
OLTP test statistics:
queries performed:
read: 988078
write: 282308
other: 141154
total: 1411540
transactions: 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.0236s
total number of events: 70577
total time taken by event execution: 1920.0367s
response time:
min: 4.85ms
avg: 27.20ms
max: 322.16ms
approx. 95 percentile: 49.79ms
Threads fairness:
events (avg/stddev): 4411.0625/31.27
execution time (avg/stddev): 120.0023/0.01
注意
安装mysql，在第一次启动前，要初始化数据库，完成data文件夹初始化，和密码初始化。
启动mysql后，再执行登录操作
一般生成大量数据较慢，频繁测试建议把数据存到/data/路径下，下次拷贝过来直接用
可以参考
常用操作
查看数据库进程
ps -ef | grep mysql
查看数据库版本
[mysql@localhost mysql]$ mysql -V mysql Ver 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]# getenforce Enforcing [root@localhost local]# setenforce 0 [root@localhost local]# getenforce Permissive
查看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 8154 2023-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] Aborting 2023-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 start Redirecting to /bin/systemctl start mysql.service Failed 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.server cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql chkconfig mysql on chown -R mysql:mysql /etc/init.d/mysql ll /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.sock Enter 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
make[2]: 进入目录“/home/sysbench/sysbench-0.5/sysbench/drivers”
Making all in mysql
make[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.c
drv_mysql.c: 在函数‘mysql_drv_bind_param’中:
drv_mysql.c:535:3: 错误：unknown type name ‘my_bool’; did you mean ‘bool’?
535 | my_bool rc;
| ^~~~~~~
| bool
drv_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;
| ^~~~~~~
| bool
drv_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] 错误 1
make[3]: 离开目录“/home/sysbench/sysbench-0.5/sysbench/drivers/mysql”
make[2]: *** [Makefile:415：all-recursive] 错误 1
make[2]: 离开目录“/home/sysbench/sysbench-0.5/sysbench/drivers”
make[1]: *** [Makefile:586：all-recursive] 错误 1
make[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 程序概述