Ubuntu系统部署MySQL 8.1.0 并压测

原创 IT邦德 All China Database Union 2023-10-02

500

作者：IT邦德
中国DBA联盟(ACDU)成员，10余年DBA工作经验
擅长主流数据Oracle、MySQL、PG、openGauss运维
备份恢复，安装迁移，性能优化、故障应急处理等

导读

国庆期间正好做华为云云耀云服务器L实例评测，正好免费申请到一台云服务器，
发现是Ubuntu系统，正好将MySQL8.1的部署及压力测试做了一下
达到了预期的效果～～

1 安装包下载

首先要确定自己的操作系统版本，通过以下命令
root@hcss-ecs-7c99:~# lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 22.04.1 LTS
Release:        22.04
Codename:       jammy

通过官网连接，按照下图下载安装包
https://dev.mysql.com/downloads/mysql/

2 解压安装包

1）将下载的mysql安装包上传到/usr/local/mysql目录下
root@hcss-ecs-7c99:~# mkdir -p /usr/local/mysql

2）解压MySQL安装包
root@hcss-ecs-7c99:~# ll /usr/local/mysql
total 428952
drwxr-xr-x  2 root root      4096 Oct  1 10:27 ./
drwxr-xr-x 15 root root      4096 Oct  1 10:25 ../
-rw-rw-rw-  1 root root 439234560 Oct  1 10:34 mysql-server_8.1.0-1ubuntu22.04_amd64.deb-bundle.tar

root@hcss-ecs-7c99:~# cd /usr/local/mysql
root@hcss-ecs-7c99:/usr/local/mysql# sudo tar xvf mysql-server_8.1.0-1ubuntu22.04_amd64.deb-bundle.tar
libmysqlclient22_8.1.0-1ubuntu22.04_amd64.deb
libmysqlclient-dev_8.1.0-1ubuntu22.04_amd64.deb
mysql-client_8.1.0-1ubuntu22.04_amd64.deb
mysql-common_8.1.0-1ubuntu22.04_amd64.deb
mysql-community-client_8.1.0-1ubuntu22.04_amd64.deb
mysql-community-client-core_8.1.0-1ubuntu22.04_amd64.deb
mysql-community-client-plugins_8.1.0-1ubuntu22.04_amd64.deb
mysql-community-server_8.1.0-1ubuntu22.04_amd64.deb
mysql-community-server-core_8.1.0-1ubuntu22.04_amd64.deb
mysql-community-server-debug_8.1.0-1ubuntu22.04_amd64.deb
mysql-community-test_8.1.0-1ubuntu22.04_amd64.deb
mysql-community-test-debug_8.1.0-1ubuntu22.04_amd64.deb
mysql-server_8.1.0-1ubuntu22.04_amd64.deb
mysql-testsuite_8.1.0-1ubuntu22.04_amd64.deb

3 安装

说明：如果提示缺少libaio1、libmecab2包，通过以下方法执行
# sudo apt-get update
# sudo apt-get install libaio1
# sudo apt-get install libmecab2


依次安装包。由于包之间有依赖关系，一定要按照顺序安装依次安装包。
由于包之间有依赖关系，一定要按照顺序安装

$ sudo dpkg -i mysql-common_8.1.0-1ubuntu22.04_amd64.deb
$ sudo dpkg -i mysql-community-client-plugins_8.1.0-1ubuntu22.04_amd64.deb
$ sudo dpkg -i libmysqlclient22_8.1.0-1ubuntu22.04_amd64.deb
$ sudo dpkg -i libmysqlclient-dev_8.1.0-1ubuntu22.04_amd64.deb
$ sudo dpkg -i mysql-community-client-core_8.1.0-1ubuntu22.04_amd64.deb

$ sudo dpkg -i mysql-community-client_8.1.0-1ubuntu22.04_amd64.deb
$ sudo dpkg -i mysql-client_8.1.0-1ubuntu22.04_amd64.deb
$ sudo dpkg -i mysql-community-server_8.1.0-1ubuntu22.04_amd64.deb
$ sudo dpkg -i mysql-community-server-core_8.1.0-1ubuntu22.04_amd64.deb

$ sudo dpkg -i mysql-community-server_8.1.0-1ubuntu22.04_amd64.deb
$ sudo dpkg -i mysql-server_8.1.0-1ubuntu22.04_amd64.deb

4 登录验证

安装完成后验证MySQL是否安装成功
mysql -u root -p  此处密码为beijing@123

root@hcss-ecs-7c99:/etc/mysql# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.1.0 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> status
--------------
mysql  Ver 8.1.0 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          9
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.1.0 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/run/mysqld/mysqld.sock
Binary data as:         Hexadecimal
Uptime:                 1 hour 20 min 33 sec

Threads: 2  Questions: 6  Slow queries: 0  Opens: 119  Flush tables: 3  Open tables: 38  Queries per second avg: 0.001
--------------

mysql> select host,user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)


创建用户，在给用户给权限
mysql> create user root@'%' identified with mysql_native_password by 'root';
mysql> grant all on *.* to root@'%' with grant option;
mysql> flush privileges;

5 ubuntu安装sysbench

要在Ubuntu上安装sysbench，您可以按照以下步骤进行操作：

1.首先，安装编译sysbench所需的依赖。打开终端，并执行以下命令：
sudo apt-get update
sudo apt-get install build-essential libmysqlclient-dev libssl-dev libpq-dev libtool automake

2.安装sysbench。
sudo apt-get install sysbench

3.查询版本
root@hcss-ecs-7c99:~# sysbench --version
sysbench 1.0.20

6 MySQ压力测试

1）准备测试数据
首先创建sysbench所需数据库sbtest(这是sysbench默认使用的库名，必须创建测试库)
root@hcss-ecs-7c99:~# mysqladmin -h127.0.0.1 -uroot -p -P3306 create sbtest;

root@hcss-ecs-7c99:~# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.1.0 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sbtest             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

2)测试过程
sysbench --mysql-host=127.0.0.1 \
         --mysql-port=3306 \
         --mysql-user=root \
         --mysql-password=beijing@123 \
         /usr/share/sysbench/oltp_common.lua \
         --tables=10 \
         --table_size=100000 \
         prepare

sysbench --threads=4 \
         --time=20 \
         --report-interval=5 \
         --mysql-host=127.0.0.1 \
         --mysql-port=3306 \
         --mysql-user=root \
         --mysql-password=beijing@123 \
         /usr/share/sysbench/oltp_read_write.lua \
         --tables=10 \
         --table_size=100000 \
         run

欢迎关注我的公众号【IT邦德】，第一时间一起学习新知识！
————————————————————————————
公众号：IT邦德
CSDN ：https://jeames.blog.csdn.net/
墨天轮：https://www.modb.pro/u/8580
知乎：https://www.zhihu.com/people/wang-ding-ding-71-72
bilibili：https://space.bilibili.com/660863136
————————————————————————————