简易mysql压测教程-linux ubuntu下

男娘i 2022-12-31 14:24 169阅读 0赞

### 安装mysql ###

#### 环境信息： ####

OS：Ubuntu18.04  
MySQL: 5.7.22

##### 一、安装mysql #####

#命令1
    sudo apt-get update
    #命令2
    sudo apt-get install mysql-server

##### 二、配置mysql #####

sudo mysql_secure_installation

按照以下选项选择：

#1
    VALIDATE PASSWORD PLUGIN can be used to test passwords...
    Press y|Y for Yes, any other key for No: N (我的选项)
    
    #2
    Please set the password for root here...
    New password: (输入密码)
    Re-enter new password: (重复输入)
    
    #3
    By default, a MySQL installation has an anonymous user,
    allowing anyone to log into MySQL without having to have
    a user account created for them...
    Remove anonymous users? (Press y|Y for Yes, any other key for No) : N (我的选项)
    
    #4
    Normally, root should only be allowed to connect from
    'localhost'. This ensures that someone cannot guess at
    the root password from the network...
    Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y (我的选项)
    
    #5
    By default, MySQL comes with a database named 'test' that
    anyone can access...
    Remove test database and access to it? (Press y|Y for Yes, any other key for No) : N (我的选项)
    
    #6
    Reloading the privilege tables will ensure that all changes
    made so far will take effect immediately.
    Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y (我的选项)

##### 三、检查安装结果 #####

systemctl status mysql.service

--------------------

### 通过MySqlSlap压测 ###

#### 命令示例 ####

一、并发数100，循环10次，主动建表和生成数据，指定表引擎为innodb，总的查询次数为100：

mysqlslap -uroot -proot --concurrency=100  --iterations 10 -a  --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=100

> \-uroot -proot：是数据库用户名和密码

##### 注：可能会踩坑报错，需要执行以下语句解决。 #####

sudo mysql
    
    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

*  Running for engine innodb：运行的存储引擎是innodb。
 *  Average number of seconds to run all queries: 0.175 seconds：运行所有查询的平均秒数。
 *  Minimum number of seconds to run all queries: 0.031 seconds：运行所有查询的最小秒数。
 *  Maximum number of seconds to run all queries: 1.078 seconds：运行所有查询的最大秒数。
 *  Number of clients running queries: 100：运行查询的客户端数（并发数）。
 *  Average number of queries per client: 1：平均每个客户的查询数量。

二、分别执行1,50,100,200的并发，循环3次，表有5个字符串字段，5个int型字段，自动创建建数据，新增自动自动增长列，分别测试myisam,innodb两个引擎，指定测试库的名称为enjoytest1，输出内存情况

mysqlslap -uroot -proot  --concurrency=1,50,100,200 --iterations=3 --number-char-cols=5 --number-int-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement  --engine=myisam,innodb  --create-schema='enjoytest1'

#### 相关操作可以参考其他MySqlSlap使用教程文章 ####

### 脚本看这里 ###

[ python编写mysql和redis压测脚本][python_mysql_redis]

[python_mysql_redis]: https://blog.csdn.net/shuryuu/article/details/111991723