这个MySQL性能瓶颈问题你遇见过吗？快来看看如何解决

霸王龙的日常

已于 2024-02-05 21:05:25 修改

阅读量913

点赞数 25

文章标签： mysql 数据库

于 2024-02-05 21:04:39 首次发布

本文链接：https://blog.csdn.net/xust_Hankey/article/details/136047893

版权

有一客户生产环境的业务数据的增长速度远远超过了最初的预期。随着用户数量的不断增加以及业务规模的扩大，数据库服务器逐渐面临了性能瓶颈。用户查询的响应时间变得较长，同时数据库处理复杂查询和大量事务时的效率下降，SQL优化后，数据库性能并未达到预期效果。经过诊断最终发现当前使用的磁盘无法提供足够的I/O，成为数据库系统性能的主要瓶颈。为了解决这一问题，客户决定提供一个停机窗口，迁移MySQL的数据文件到一块全新的高性能的SSD磁盘上，这里不得不说长江存储YYDS，相比前几年，国产SSD直接打成了白菜价。

当然，除了上述场景，存储空间不足、合规性要求等需求也可参考该文章方式进行MySQL数据文件的迁移。

在这里插入图片描述

作者：
公众号：霸王龙的日常
墨天伦：https://www.modb.pro/u/368448
CSDN: https://blog.csdn.net/xust_Hankey

环境概要

项目	配置描述
操作系统	CentOS 7.9 x86_64
数据库	MySQL 8 +

操作开始

1 上架新磁盘到服务器

1.1 查看系统是否检测到了新磁盘

fdisk -l

通常，新磁盘设备名称会以 /dev/sdX 的形式显示，其中X是字母。

这里新加的磁盘是/dev/sdc

1.2 分区

fdisk /dev/sdc

输入 n 创建一个新分区。
选择主分区（输入 p）或扩展分区（输入 e）。
提示你选择分区号，如果只有一个分区，通常是 1。
输入起始扇区，如果你不确定，直接按 Enter 接受默认值。
输入结束扇区，也可以直接按 Enter 接受默认值，表示使用整个磁盘空间。

查看新分区：** 再次输入 p 查看新分区是否已经创建。

保存分区表：** 输入 w 保存分区表的更改。

重新读取分区表：

为了确保内核识别到新的分区，可以重新读取分区表：

partprobe /dev/sdc

或者，你也可以重启系统。

1.3 格式化的分区并挂载目录

mkfs.xfs /dev/sdc1

创建一个目录，作为新磁盘的挂载点：

mkdir /newdata

使用 mount 命令将磁盘挂载到指定目录：

mount /dev/sdc1 /newdata

1.4 配置自动挂载

vi /etc/fstab

追加如下一行（假设是xfs文件系统）：

/dev/sdc1   /newdata   xfs   defaults   0   2

设置完成后，开机会自动挂载磁盘

2 查看配置文件

cat /etc/my.cnf

例如，当前环境和路径相关的配置如下

datadir=/mysqldata/data
socket=/mysqldata/mysql.sock
pid-file=/mysqldata/mysqld.pid
log-error=/mysqldata/log/mysqld.log
log_bin = /mysqldata/binlog/mysql-binlog

[client]
socket=/mysqldata/mysql.sock

3 查看数据目录的路径

mysql> show variables like 'datadir';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| datadir       | /mysqldata/data/  |
+---------------+-------------------+
1 row in set (0.04 sec)

4 备份数据库

在执行迁移操作之前，务必进行数据备份以防止意外数据丢失

备份全库

mysqldump -uroot -p -P3306 -A --source-data=2 --single-transaction --flush-logs -R > /bak/db_all.sql

其中/bak/为备份文件存放目录，请根据实际情况进行替换。

你可以根据实际需要选择其他合适的备份方式。

5 停止MySQL服务

在进行任何文件迁移之前，首先停止MySQL服务

systemctl stop mysqld

6 移动数据文件

6.1确定新的磁盘路径

确保您已经挂载了新的磁盘，并记住新磁盘的挂载路径，例如/data

6.2 移动数据文件

使用rsync命令将MySQL数据文件复制到新的磁盘：

rsync -av /mysqldata/ /newdata/

这将在新的磁盘上创建与原始数据相同的目录结构。

6.3 设置权限

查看权限

[root@trexdb1 ~]# ll /newdata/
total 4
drwxr-xr-x 2 mysql mysql   59 Jan 23 15:59 binlog
drwxr-xr-x 8 mysql mysql 4096 Jan 23 16:11 data
drwxr-xr-x 2 mysql mysql   24 Nov 15 21:16 log

如果新磁盘的权限不正确，确保更新权限

chown -R mysql:mysql /newdata

7 更新MySQL配置文件

7.1 打开MySQL配置文件

vi /etc/my.cnf

7.2 更新以下配置项，将数据文件路径指向新磁盘路径

datadir=/newdata/data
socket=/newdata/mysql.sock
pid-file=/newdata/mysqld.pid
log-error=/newdata/log/mysqld.log
log_bin = /newdata/binlog/mysql-binlog

[client]
socket=/newdata/mysql.sock

请根据你的实际环境进行修改，确保路径是正确的，并且 mysql 用户对新路径有适当的权限。

8 启动MySQL服务

然后启动MySQL服务：

systemctl start mysqld

9 验证

9.1 检查MySQL的错误日志

tail -200f /newdata/log/mysqld.log

确保没有启动错误

9.2 通过MySQL客户端连接到数据库

mysql -u root -p

查看数据目录的路径

mysql> show variables like 'datadir';
+---------------+-------------+
| Variable_name | Value       |
+---------------+-------------+
| datadir       | /newdata/data/ |
+---------------+-------------+
1 row in set (0.04 sec)