MaRui

MMMM

mysql

3848字约13分钟

2024-08-11

MySQL

安装

1、准备安装包

将提前准备好的mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz安装包，将其直接放在root目录下

2、卸载系统自带的 MARIADB

  • 查询已经安装的Mariadb安装包
rpm -qa | grep mariadb
  • 如果有的话都卸载掉
yum -y remove mariadb-server-5.5.56-2.el7.x86_64
yum -y remove mariadb-5.5.56-2.el7.x86_64
yum -y remove mariadb-devel-5.5.56-2.el7.x86_64
yum -y remove mariadb-libs-5.5.56-2.el7.x86_64

3、解压 MySQL 安装包

将MySQL安装包解压到/usr/local目录，并重命名为mysql

tar -zxvf /root/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

mv mysql-5.7.30-linux-glibc2.12-x86_64 mysql

4、创建 MySQL 用户和用户组

groupadd mysql

useradd -g mysql mysql

5、修改 MySQL 目录的归属用户

# /usr/local/mysql 目录下执行
chown -R mysql:mysql ./

6、准备 MySQL 的配置文件

  • 在/etc⽬录下新建my.cnf⽂件
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
socket=/var/lib/mysql/mysql.sock

[mysqld]
skip-name-resolve
#设置3306端⼝
port = 3306
socket=/var/lib/mysql/mysql.sock
# 设置mysql的安装⽬录
basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放⽬录
datadir=/usr/local/mysql/data
# 允许最⼤连接数
max_connections=200
# 服务端使⽤的字符集默认为8⽐特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使⽤的默认存储引擎
default-storage-engine=INNODB
lower_case_table_names=1
max_allowed_packet=16M
  • 创建/usr/local/mysql/data和/var/lib/mysql目录
# 新建`/usr/local/mysql/data`⽬录
mkdir /usr/local/mysql/data

# 新建 /var/lib/mysql 目录，并修改权限
mkdir /var/lib/mysql
chmod 777 /var/lib/mysql

安装 MySQL

记住安装后打印出来的 root 密码，后面首次登录需要使用。没记住也没关系，后面有方式免密登录去修改密码。

cd /usr/local/mysql

./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

复制启动脚本到资源目录

[root@localhost mysql]# cp ./support-files/mysql.server /etc/init.d/mysqld
  • 修改/etc/init.d/mysqld，修改其basedir和datadir为实际对应⽬录
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data

设置MYSQL系统服务并开启⾃启

  • ⾸先增加mysqld服务控制脚本执⾏权限
chmod +x /etc/init.d/mysqld
  • 同时将mysqld服务加⼊到系统服务
chkconfig --add mysqld

-检查mysqld服务是否已经⽣效即可

chkconfig --list mysqld

这样就表明 mysqld 服务已经⽣效了，在2、3、4、5运⾏级别随系统启动⽽⾃动启动，以后可以直接使 ⽤ service 命令控制 mysql 的启停。

启动 mysqld

service mysqld start

将 mysql 的 bin ⽬录加⼊ PATH 环境变量

# 编辑 ~/.bash_profile ⽂件
vi ~/.bash_profile

# 在⽂件末尾处追加如下信息:
export PATH=$PATH:/usr/local/mysql/bin

# 最后执⾏如下命令使环境变量⽣效
source ~/.bash_profile

首次登录 mysql

以 root 账户登录 mysql ，使⽤上⽂安装完成提示的密码进⾏登⼊

mysql -u root -p

修改 root 账户密码

mysql>alter user user() identified by "111111";
mysql>flush privileges;

设置远程主机登录

mysql> use mysql;
mysql> update user set user.Host='%' where user.User='root';
mysql> flush privileges;

重置密码

注意区分半本，这是是 5.7 版本的修改方式

5.7版本下的mysql数据库下已经没有password这个字段了，password字段改成了authentication_string

1、跳过 MySQL 密码认证

# 编辑配置文件
vi /etc/my.cnf

# [mysqld]后面任意一行添加“skip-grant-tables”用来跳过密码验证的过程
skip-grant-tables

2、重启 MySQL

/etc/init.d/mysqld restart

3、重启之后输入 mysql 即可进入 mysql

4、修改密码

# 切换到 mysql 数据库
mysql> use mysql;
# 修改密码
mysql> update mysql.user set authentication_string=password('123456') where user='root';
# 立即生效
mysql> flush privileges;
#  退出
mysql> quit

5、去掉配置文件的免密

数据库压测指标

基于工具模拟一个系统每秒发出 1000 个请求到数据库上，观察他的 CPU负载、磁盘IO负载、网络IO负载、内存负载，然后看数据库能否每秒处理掉这 1000 个请求，还是只能处理 500 个请求，这个过程就是压测

QPS（Query Per Second）：数据库每秒可以处理多少个请求

TPS（Transaction Per Second）：每秒可以处理的事务量，衡量的是一个数据库每秒处理完事务的数量

IO 相关的压测性能指标

  • IOPS：指的是机器的随机 IO 并发处理能力，比如机器可以达到 200 IOPS，意思就是说每秒可以执行 200 个随机 IO 读写请求

这个指标很关键，你在内存中更新的脏数据，最后都由后台 IO 线程在不确定的时间，刷回磁盘里去，这就是随机 IO 的过程，如果说 IOPS 指标太低了，那么会导致内存里的数据刷会磁盘的效率就会不高

  • 吞吐量：指的是机器的磁盘存储每秒可以读写多少字节的数据量

这个指标也很关键，执行 SQL 语句和提交事务的时候，会大量的写 redo log 之类的日志，这些日志都会直接写入磁盘文件。

一台机器的存储每秒可以读写多少自己的数据量，就决定了他每秒可以把多少 redo log 之类的日志写入到磁盘里去。一般 redo log 之类的日志，都是对磁盘文件顺序写入的，不是随机的读写，那么一般普通磁盘的顺序写入的吞吐量每秒都可以达到 200MB 左右，所以通常而言，机器的磁盘吞吐量都是足够承载高并发请求的

  • latency：指的是往磁盘里写入一条数据的延迟

这个指标同样重要，执行 SQL 语句和提交事务的时候，都需要顺序写 redo log 磁盘文件，所以此时你写一条日志到磁盘文件里，是延迟 1ms 还是延迟 100us，这就对你数据库的 SQL 语句执行性能是有影响的

一般来说，你的磁盘读写延迟越低，那么你的数据库性能就越高，你执行每个 SQL 语句和事务的速度就会越快

压测时要关注的其他性能指标

  • CPU负载

CPU负载是一个很重要的性能指标，假设你数据库压测到了每秒处理 3000 请求了，可能其他性能指标都还正常，但是此时 CPU 负载特别高，那么也说明你的数据库不能继续往下压测更高的 QPS 了，否则 CPU 是吃不消的

  • 网络负载

这个是主要看你的机器宽带情况，再压测到一定的 QPS 和 TPS 的时候，每秒中机器的网卡会输入多少 MB 数据，会输出多少 MB 数据。假设你的网络宽带每秒对多传输 100MB 的数据，可能你的 QPS 到 1000 的时候，网卡就打满了，已经每秒都传输 100MB 的数据库，即使其他指标都还算正常，但是你也不能继续压测了

  • 内存负载

这个是看压测到一定情况下的时候，你的机器内存耗费了多少，如果说机器的内存耗费过高了，说明也不能继续压测下去

如何查看负载情况

CPU、内存 负载情况

最常用的监测linux机器性能的命令，top

#      当前时间 已经运行了70天3:16 当前5个用户  cpu 负载情况 1分钟 5分钟  15分钟
top - 22:36:02 up 70 days,  3:16,  5 users,  load average: 0.53, 3.30, 3.12
Tasks:  92 total,   3 running,  89 sleeping,   0 stopped,   0 zombie
%Cpu(s):  1.3 us,  1.0 sy,  0.0 ni, 97.7 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
#          总共1.79个G          0.06个G空闲     0.75个G使用    0.97个G用作os内核的缓冲区
KiB Mem :  1882052 total,    66176 free,   790576 used,  1025300 buff/cache
KiB Swap:        0 total,        0 free,        0 used.   920856 avail Mem

如果你是 1 核的服务器（1个物理核等于2个虚拟核，云服务器虚拟核），CPU 负载是 1，那就说明 1 核 CPU 已经跑满了，如果负载是 2，CPU 很繁忙，很多进程可能一直在等待 CPU 去执行自己的任务

如果内存使用率在 80 % 以内，基本还能接受，在正常范围内。但是如果你机器的内存使用率到了70%-80%，说明有点危险，就不要继续增加压测的线程数量和 QPS 了

磁盘 IO 情况

# 安装性能检测 dstat
yum install -y dstat
# 使用 dstat -d 命令
dstat -d

-dsk/total-
 read  writ
 103k  16k

存储的 IO 吞吐量是每秒钟 103kb 的数据，每秒写入是 16kb 的数据，普通机械硬盘可以做到每秒钟上百 MB 的读写数据量

# 使用 dstat -r
dstat -r

--io/total-
 read  writ
 0.25   31.9
   0     0

读 IOPS 和写 IOPS 分别是多少，也就是说随机磁盘读取每秒钟多少次，随机磁盘写入每秒钟多少次，随机磁盘读写每秒在两三百次都是可以承受的

如果磁盘 IO 吞吐量太高了，都达到每秒上百 MB 了，或者随机磁盘读写每秒都到极限的两三百次了，此时就不要继续增加线程数量了，否则磁盘 IO 负载太高了

网卡的流量情况

# 使用 dstat -n
dstat -n

-net/total-
 recv send
 16k  17k

每秒钟网卡接收流量有多少 kb，每秒钟通过网卡发送出去的流量有多少 kb。通常来说，机器使用的千兆网卡，那么每秒钟网卡的总流量也就在 100MB 左右，甚至更低一些

如果网卡传输流量已经达到了极限值了，此时你再怎么提高 sysbench 线程数量，数据库的 QPS 也上不去，因为这台机器每秒钟无法通过网卡传输更多的数据了

数据库压测

一台4核8G的机器如果每秒扛 500+ 的请求，那么他的 CPU 负载就已经很高了，基本上最多也就扛下每秒 1000+ 的请求，而且那个时候 CPU 负载基本会打满，机器有挂掉的风险

sysbench

数据库压测工具，这个工具可以自动帮你在数据库里构造出大量的数据，接着可以模拟几千个线程并发的访问你的数据，模拟各种各样 SQL 语句来访问你的数据库，包括模拟出各种事务提交到你的数据库里去，甚至可以模拟出几十万的 TPS 去压测你的数据库。

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash

sudo yum -y install sysbench

sysbench --version

基于 sysbench 构造测试表和测试数据

数据库：test_db，账号：test_user，密码：test_user

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable prepare

  • --db-driver=mysql：基于 mysql 的驱动去连接 mysql 数据库，如果是其他数据库，换成其他数据库的驱动

  • --time=300：连续访问 300 秒

  • --threads=10：用 10 个线程模拟并发访问

  • --report-interval=1：每隔 1 秒输出一下压测情况

  • --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user：数据库的ip、端口和账号、密码

  • --mysql-db=test_db --tables=20 --table_size=1000000：在 test_db 这个库里面，构造 20 个测试表，每个测试表里构造 100 万条测试数据，测试表的名字回事类似于 sbtest1，sbtest2 这样

  • oltp_read_write：执行 oltp 数据库的读写测试

  • --db-ps-mode=disable：禁止 ps 模式

最后的 prepare，意思是参照这个命令的设置去构造出来我们需要的数据库里的数据，他会自动创建 20 个测试表，每个表里创建 100 万条测试数据

对数据库进行全方位测试

注意：命令中最后不是 prepare 而是 run 了，就是运行压测

  • 测试数据库的综合性读写 TPS
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable run
  • 测试数据库的只读性能，使用的是oltp_read_only模式（命令行中的oltp_read_write变成oltp_read_only）
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_only --db-ps-mode=disable run
  • 测试数据库的删除性能，使用的是oltp_delete模式
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_delete --db-ps-mode=disable run
  • 测试数据库的更新索引字段的性能，使用oltp_update_index模式
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_update_index --db-ps-mode=disable run
  • 测试数据库的更新非索引字段的性能，使用oltp_update_non_index模式
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_update_non_index --db-ps-mode=disable run
  • 测试数据库的插入性能，使用oltp_insert模式
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_insert --db-ps-mode=disable run
  • 测试数据库的写入性能，使用oltp_write_only模式
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_write_only --db-ps-mode=disable run

使用上面命令，sysbench 工具会根据你的指令构造出各种各样的 SQL 语句去更新或者查询你的 20 张测试表里的数据，同时检测出你的数据库的压测性能指标，最后完成压测之后，可以执行下面的 cleanup 命令，清理数据

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable cleanup

压测结果分析


按照上面的命令，我们让他每隔 1 秒都会输出一次压测报告的，此时他会每隔 1 秒会输出类似下面的一段东西

[ 300s ] thds: 10 tps: 88.99 qps: 1791.78 (r/w/o: 1263.85/349.96/177.98) lat (ms,95%): 196.89 err/s: 0.00 reconn/s: 0.00

  • thds: 10：表示有 10 个线程在压测

  • tps: 88.99：每秒执行了 88.99 个事务

  • qps: 1791.78：每秒可以执行 1791.78 个请求

  • (r/w/o: 1263.85/349.96/177.98)：在每秒的 1791.78 个请求中，有 1263.85 个是读请求，349.96 个是写请求，177.98 个是其他请求，就是对 QPS 进行了拆解

  • lat (ms,95%): 196.89：95% 的请求的延迟都是在 196.89 毫秒以下

  • err/s: 0.00 reconn/s: 0.00：每秒有 0 个请求失败，发生了 0 次网络重连

完成压测之后，最后会显示一个总的压测报告

SQL statistics:
    queries performed:
        read:                            449288 # 300s 的压测期间执行了 44 万多次读请求
        write:                           128368 # 300s 的压测期间执行了 12 万多次写请求
        other:                           64184 # 300s 的压测期间执行了 6 万多次读其他求
        total:                           641840 # 300s 的压测期间共执行了 64 万多次请求
    transactions:                        32092  (106.94 per sec.) # 一共执行了 3 万多个事务，每秒执行 106.94 个事务
    queries:                             641840 (2138.86 per sec.) # 一共执行了 64 万多次的请求，每秒执行 2138.86 个请求
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
# 一共执行了 300s 的压测，执行了 3W+ 的事务
General statistics:
    total time:                          300.0840s
    total number of events:              32092

Latency (ms):
         min:                                   13.12 # 请求中延迟最小的是 13.12ms
         avg:                                   93.49 # 所有请求平均延迟是 93.49ms
         max:                                  791.95 # 延迟最大的请求是 791.95ms
         95th percentile:                      173.58 # 95% 的请求延迟都在 173.58ms 以内
         sum:                              3000154.01

Threads fairness:
    events (avg/stddev):           3209.2000/8.40
    execution time (avg/stddev):   300.0154/0.03
贡献者: MaRui