mysql性能压测与mysql安装以及常见启动问题

作者：xiaoshu 来源：xiaoshu168.com LINUX 2024-04-24 12:21:23

2400 0

mysql性能压测与mysql安装以及常见启动问题

1. mysql的安装及配置连接

1.1 Linux下安装mysql

mysql国内镜像地址：http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-8.0/

mysql官网地址：https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz

第一步：下载mysql安装包，也可以通过本地xftp上传到虚拟机

--跳转到下载目录
cd /usr/local
--下载mysql8.0的版本 wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz

第二步：将下载的mysql安装包解压

--解压
tar -xvf mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz

--移动
mv mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz mysql

--添加mysql组和mysql用户
--添加mysql组
groupadd mysql

--添加mysql用户
useradd -r -g mysql mysql

第四步：配置mysql配置文件与初始化mysql

--给mysql组/usr/local/mysql文件权限
chown -R mysql:mysql /usr/local/mysql

初始化mysql服务
./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

chown -R root:root /usr/local/mysql
chown -R mysql:mysql /usr/local/mysql/data
--修改/etc/my.cnf配置文件权限
chmod 755 /etc/my.cnf

以下是配置文件内容：

[mysql]
default-character-set=utf8

[client]
port=3306
default-character-set=utf8
socket=/usr/local/mysql/tmp/mysql.sock

[mysqld]
socket=/usr/local/mysql/tmp/mysql.sock
port=3306
default_authentication_plugin=mysql_native_password
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
character-set-server=utf8
default-storage-engine=InnoDb
max_connections=1000
collation-server=utf8_unicode_ci
init_connect='SET NAMES utf8'
innodb_buffer_pool_size=64M
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=120
innodb_log_buffer_size=4M
innodb_log_file_size=256M
interactive_timeout=120
join_buffer_size=2M
key_buffer_size=32M
log-error=/usr/local/mysql/data/data.err
log_error_verbosity=1
max_allowed_packet=256M
max_heap_table_size=64M
myisam_max_sort_file_size=64G
myisam_sort_buffer_size=32M
read_buffer_size=512kb
read_rnd_buffer_size=4M
log_bin=mysql-bin
server-id=1
skip-external-locking=on
sort_buffer_size=256kb
table_open_cache=256
thread_cache_size=16
tmp_table_size=64M
wait_timeout=2288800
general_log=ON
general_log_file=/usr/local/mysql/data/query_log.pid
slow_query_log=ON
slow_query_log_file=sql-slow.log
long_query_time=1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

创建mysql.sock存储文件tmp

mkdir tmp
chmod 777 tmp

第五步：加入开机自启

--将mysql服务添加到/etc/init.d

cp support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
--添加服务
chkconfig --add mysql
--查看是否添加成功
chkconfig --list mysql

第六步：开启mysql服务并配置环境变量

--启动mysql服务
service mysql start
vi /etc/profile

profile中填写内容：
export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/lib
保存退出之后，运行命令：source /etc/profile

第七步：登录mysql，修改密码

mysql -uroot -p

--修改密码
alter user 'root'@'localhost' identified by 'root';

1.2 Navicat连接mysql

因为安装的mysql是最新的mysql8.0，他对于Navicat，mycat等工具去连接mysql会出现一个异常，这个异常是因为mysql8的密码加密方式是属于全新的加密方式，而目前的Navicat版本尚不支持这个加密方式，错误异常如下：

解决办法

更新Navicat客户端
设置mysql用户密码加密方式，具体如下：`mysql> select user,host,plugin from mysql.user; +------------------+-----------+-----------------------+ | user | host | plugin | +------------------+-----------+-----------------------+ | starsky | % | caching_sha2_password | | mysql.infoschema | localhost | caching_sha2_password | | mysql.session | localhost | caching_sha2_password | | mysql.sys | localhost | caching_sha2_password | | root | localhost | caching_sha2_password | +------------------+-----------+-----------------------+ 5 rows in set (0.00 sec)

将用户加密方式由原本的caching_sha2_password方式更改为mysql_native_password;

### 2. mysql启动连接异常

>The server quit without updating PID file (/usr/local/mysql/data/localhost.localdomain.pid).问题

![](assets/markdown-img-paste-20200929142525930.png)

问题出现的原因：
1. data目录的权限不够

解决:给予权限，chmod 755 data,然后重启

2. 进程中已经存在mysql进程

解决:kill -9 杀死已经存在的进行

3. 统一解决方案

先删除data目录，使用mysqld进行mysql的初始化操作，在给予data目录权限，用户组。这个的前提是前面两种不可解决的情况下。

>socket文件不存在问题

这个问题出现的环节是在连接mysql时出现，解决他的思路是尝试重新启动mysql，使socket文件根据mysql配置中配置的目录去生成。也有可能是配置文件中socket文件配置的目录权限问题，只需要给予权限即可。

## 2. 使用mysql自带压测工具

mysqlslap是mysql自带的基准测试工具，优点:查询数据,语法简单,灵活容易使用.该工具可以模拟多个客户端同时并发的向服务器发出查询更新,给出了性能测试数据而且提供了多种引擎的性能比较.msqlslap为mysql性能优化前后提供了直观的验证依据,建议系统运维和DBA人员应该掌握一些常见的压力测试工具,才能准确的掌握线上数据库支撑的用户流量上限及其抗压性等问题。

语法

mysqlslap [options]

--auto-generate-sql, -a 自动生成测试表和数据，表示用mysqlslap工具自己生成的SQL脚本来测试并发压力。 --auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，update和mixed(默认)。 --auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持。 --number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列，默认1 --number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列，默认1 --number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数) --query=name,-q 使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者sql语句来执行测试。 --create-schema 代表自定义的测试库名称，测试的schema，MySQL中schema也就是database。 --commint=N 多少条DML后提交一次。 --compress, -C 如果服务器和客户端支持都压缩，则压缩信息传递。 --concurrency=N, -c N 表示并发量，也就是模拟多少个客户端同时执行select。可指定多个值，以逗号或者--delimiter参数指定的值做为分隔符。例如：--concurrency=100,200,500。 --engine=engine_name, -e engine_name 代表要测试的引擎，可以有多个，用分隔符隔开。例如：--engines=myisam,innodb。 --iterations=N, -i N 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次。 --only-print 只打印测试语句而不实际执行。 --detach=N 执行N条语句后断开重连。 --debug-info, -T 打印内存和CPU的相关信息。

测试的过程需要生成测试表，插入测试数据，这个mysqlslap可以自动生成，默认生成一个mysqlslap的schema，如果已经存在则先删除。可以用--only-print来打印实际的测试过程，整个测试完成后不会在数据库中留下痕迹。

测试

mysqlslap --help 单线程测试。测试做了什么。 mysqlslap -a -u root -p 多线程测试。使用–concurrency来模拟并发连接。 mysqlslap -a -c 100 -u root -p 打印参数解释： Benchmark Average number of seconds to run all queries: 0.344 seconds ## 100个客户端（并发）同时运行这些SQL语句平均要花0.351秒 Minimum number of seconds to run all queries: 0.344 seconds Maximum number of seconds to run all queries: 0.344 seconds Number of clients running queries: 100 ## 总共100个客户端（并发）运行这些sql查询 Average number of queries per client: 0 ## 每个客户端（并发）平均运行50次查询（对应--concurrency=100

测试100个并发线程，测试次数1次，自动生成SQL测试脚本，读、写、更新混合测试，自增长字段，测试引擎为innodb，共运行5000次查询 mysqlslap -u root -p -c 100 -i 1 -a --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement -e innodb --number-of-queries=5000 mysqlslap -h 127.0.0.1 -u root -p -c 100 -i 1 --create-schema tipaskx -q "select count(*) from ask_users" -e innodb --number-of-queries=5000

注意这里：在测试-c的时候容易出现

执行 mysqlslap -a -c 1000 -u root -p 出现 mysqlslap: Error when connecting to server: 1040 Too many connections

这个问题的主要是连接的超过了最大的连接次数导致的，解决办法修改MySQL的配置文件my.cnf或my.ini 设置max_connections 的值然后重启；

```sql
-- 查看连接数
show variables like 'max_conn%';

3. ab性能压测工具

ab官网 http://httpd.apache.org/docs/2.0/programs/ab.html

pache的ab命令模拟多线程并发请求，测试服务器负载压力，也可以测试nginx、lighthttp、IIS等其它Web服务器的压力。 ab命令对发出负载的计算机要求很低，既不会占用很多CPU，也不会占用太多的内存，但却会给目标服务器造成巨大的负载，因此是某些DOS攻击之必备良药，老少皆宜。自己使用也须谨慎。否则一次上太多的负载，造成目标服务器直接因内存耗光死机，而不得不硬重启，得不偿失。

操作

cd \xxx\xxx\xxx\Apache\bin
ab -n 800 -c 800 http://127.0.0.1/phpinfo.php （-n发出800个请求，-c模拟800并发，相当800人同时访问，后面是测试url）
ab -t 60 -c 100 http://127.0.0.1/phpinfo.php 在60秒内发请求，一次100个请求。
-n在测试会话中所执行的请求个数。默认时，仅执行一个请求。
-c一次产生的请求个数。默认是一次一个。
-t测试所进行的最大秒数。其内部隐含值是-n 50000，它可以使对服务器的测试限制在一个固定的总时间以内。默认时，没有时间限制。
-p包含了需要POST的数据的文件。
-P对一个中转代理提供BASIC认证信任。用户名和密码由一个:隔开，并以base64编码形式发送。无论服务器是否需要(即, 是否发送了401认证需求代码)，此字符串都会被发送。
-T POST数据所使用的Content-type头信息。
-v设置显示信息的详细程度-4或更大值会显示头信息，3或更大值可以显示响应代码(404,200等),2或更大值可以显示警告和其他信息。
-V显示版本号并退出。
-w以HTML表的格式输出结果。默认时，它是白色背景的两列宽度的一张表。
-i执行HEAD请求，而不是GET。
-x设置<table>属性的字符串。
-X对请求使用代理服务器。
-y设置<tr>属性的字符串。
-z设置<td>属性的字符串。
-C对请求附加一个Cookie:行。其典型形式是name=value的一个参数对，此参数可以重复。
-H对请求附加额外的头信息。此参数的典型形式是一个有效的头信息行，其中包含了以冒号分隔的字段和值的对(如,"Accept-Encoding:zip/zop;8bit")。
-A对服务器提供BASIC认证信任。用户名和密码由一个:隔开，并以base64编码形式发送。无论服务器是否需要(即,是否发送了401认证需求代码)，此字符串都会被发送。
-h显示使用方法。
-d不显示"percentage served within XX [ms] table"的消息(为以前的版本提供支持)。
-e产生一个以逗号分隔的(CSV)文件，其中包含了处理每个相应百分比的请求所需要(从1%到100%)的相应百分比的(以微妙为单位)时间。由于这种格式已经“二进制化”，所以比'gnuplot'格式更有用。
-g把所有测试结果写入一个'gnuplot'或者TSV(以Tab分隔的)文件。此文件可以方便地导入到Gnuplot,IDL,Mathematica,Igor甚至Excel中。其中的第一行为标题。
-i执行HEAD请求，而不是GET。
-k启用HTTP KeepAlive功能，即在一个HTTP会话中执行多个请求。默认时，不启用KeepAlive功能。
-q如果处理的请求数大于150，ab每处理大约10%或者100个请求时，会在stderr输出一个进度计数。此-q标记可以抑制这些信息