MySQL安装

初始化安装

  1. 安装mysql5.7的rpm仓库
 sudo rpm -ivh https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

修改仓库文件

vim /etc/yum.repos.d/mysql-community.repo

将[mysql57-community]修改为如下所示,也就是将gpgcheck改为0

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
  1. 安装mysql-server
sudo yum install -y mysql-server
  1. 数据库设置utf-8,以及修改数据库最大连接数位1024
vi /etc/my.cnf

#添加

[mysqld]

character_set_server=utf8

init_connect='SET NAMES utf8'

max_connections=1024
  1. 设置开机启动mysql,并启动mysql
systemctl enable mysqld

systemctl start mysqld
  1. 查看初始随机密码
grep 'temporary password' /var/log/mysqld.log

MySQL安装

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

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

新增用户与用户授权

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

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

# 如果你想指定可以登录的主机,可以将上述%换成对应主机ip或者hostname(如果在本地做了hosts映射,那么就用hostname)
  1. 用户授权
# 将任意库以及任意表授权给test用户,并且该用户可以从任意主机登陆
grant all privileges on *.* to 'root'@'%' identified by 'Pass-123-root' with grant option;

使用自带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

上述压测命令表示:进行两次并发读写,第一次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

测试结果说明:

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

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

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

5 1 投票
文章评分

本文为从大数据到人工智能博主「xiaozhch5」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。

原文链接:https://lrting.top/backend/2111/

(1)
上一篇 2021-11-13 02:39
下一篇 2021-11-13 02:44

相关推荐

订阅评论
提醒
guest
1 评论
最旧
最新 最多投票
内联反馈
查看所有评论
trackback
6 月 前

[…] mysql搭建指南可参考:https://lrting.top/backend/2111/ […]

1
0
希望看到您的想法,请您发表评论x