MySQL安装

从大数据到人工智能

发布于 2022-05-26 09:39:22

5.9K0

初始化安装

安装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

安装mysql-server

sudo yum install -y mysql-server

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

vi /etc/my.cnf

#添加

[mysqld]

character_set_server=utf8

init_connect='SET NAMES utf8'

max_connections=1024

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

systemctl enable mysqld

systemctl start mysqld

查看初始随机密码

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

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

# 登陆
mysql -u root -p

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

新增用户与用户授权

新增用户

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

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

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

用户授权

# 将任意库以及任意表授权给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

测试结果说明：