centos7中mysql5.7的安装、授权与压测

初始化安装

1. 安装mysql5.7的rpm仓库

 sudo rpm -ivh https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

        

          
        
        
        

          

          AI 代码解读
        
      
      

1. 安装mysql-server

sudo yum install -y mysql-server

        

          
        
        
        

          

          AI 代码解读
        
      
      

1. 数据库设置utf-8，以及修改数据库最大连接数位1024

vi /etc/my.cnf

#添加

[mysqld]

character_set_server=utf8

init_connect='SET NAMES utf8'

max_connections=1024

        

          
        
        
        

          

          AI 代码解读
        
      
      

1. 设置开机启动mysql，并启动mysql

systemctl enable mysqld

systemctl start mysqld

        

          
        
        
        

          

          AI 代码解读
        
      
      

1. 查看初始随机密码

grep 'temporary password' /var/log/mysqld.log

        

          
        
        
        

          

          AI 代码解读
        
      
      

1. 使用刚刚查到的初始随机密码登陆mysql并修改密码（不修改无法操作）。修改的密码有严格要求，必须8位以上，并且至少包含字母、数字、特殊符号三种类型。

# 登陆
mysql -u root -p

# 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Pass-123-root';

        

          
        
        
        

          

          AI 代码解读
        
      
      

新增用户与用户授权

1. 新增用户

# 该语句表示只能从本机登陆此用户
create user 'test'@'localhost' identified by 'Pass-123-test';

# 该语句表示可以从任意主机登陆此用户
create user 'test'@'%' identified by 'Pass-123-test';

# 如果你想指定可以登录的主机，可以将上述%换成对应主机ip或者hostname（如果在本地做了hosts映射，那么就用hostname）

        

          
        
        
        

          

          AI 代码解读
        
      
      

1. 用户授权

# 将任意库以及任意表授权给test用户，并且该用户可以从任意主机登陆
grant all privileges on *.* to 'root'@'%' identified by 'Pass-123-root' with grant option;

        

          
        
        
        

          

          AI 代码解读
        
      
      

使用自带mysql自带工具压测

压测命令：

mysqlslap --defaults-file=/etc/my.cnf --concurrency=100,200 --iterations=1 --number-int-cols=20 --number-char-cols=30 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam,innodb --number-of-queries=2000 -uroot -pPass-123-root --verbose

        

          
        
        
        

          

          AI 代码解读
        
      
      

上述压测命令表示：进行两次并发读写，第一次100，第二次200，自动生成SQL脚本，测试表包含20个init字段，30个char字段，每次执行2000查询请求。测试引擎分别是myisam，innodb。

测试结果示例：

Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.075 seconds
        Minimum number of seconds to run all queries: 0.075 seconds
        Maximum number of seconds to run all queries: 0.075 seconds
        Number of clients running queries: 100
        Average number of queries per client: 20

Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.186 seconds
        Minimum number of seconds to run all queries: 0.186 seconds
        Maximum number of seconds to run all queries: 0.186 seconds
        Number of clients running queries: 200
        Average number of queries per client: 10

Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 1.231 seconds
        Minimum number of seconds to run all queries: 1.231 seconds
        Maximum number of seconds to run all queries: 1.231 seconds
        Number of clients running queries: 100
        Average number of queries per client: 20

Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 1.586 seconds
        Minimum number of seconds to run all queries: 1.586 seconds
        Maximum number of seconds to run all queries: 1.586 seconds
        Number of clients running queries: 200
        Average number of queries per client: 10

        

          
        
        
        

          

          AI 代码解读
        
      
      

测试结果说明：

• Myisam第一次100客户端同时发起增查用0.075/s,第二次200客户端同时发起增查用0.186/s
• Innodb第一次100客户端同时发起增查用1.231/s,第二次200客户端同时发起增查用1.586/s

可以根据实际需求，一点点的加大并发数量进行压力测试。