高级DBA带你处理MySQL集群爆表导致硬盘爆满系统产品宕机实际生产事故处理实战案例全网唯一

技术很渣

已于 2024-03-13 09:01:47 修改

阅读量737

点赞数 21

分类专栏： DBA运维 MySQL 文章标签： dba mysql 数据库

于 2024-03-12 10:07:52 首次发布

本文链接：https://blog.csdn.net/nasen512/article/details/136641303

版权

DBA运维同时被 2 个专栏收录

34 篇文章 2 订阅

订阅专栏

MySQL

23 篇文章 1 订阅

订阅专栏

高级DBA带你处理MySQL集群爆表导致硬盘爆满系统产品宕机实际生产事故实战

一、事故描述

数据库某个表A其中用到了二进制字段，本来就无比巨大，再加上某个客户端阶段发生程序BUG，无限反复插入重复数据，导致一个大表一下午时间迅速增长到600G！之后可想而知，生产数据库服务器不正常，随之就是客户投诉！服务器硬盘监控还没来得及反应，硬盘瞬间就被沾满了！而且清理掉没用的日志，马上空间又被沾满！并且数据库已经几乎无法操作！迫在眉睫怎么迅速解决问题？这个事故的难点在于，HA双活没有起效，并且硬盘预警的时间没有跟上表空间增长的速度，当你知道服务器硬盘满的时候，其实已经晚了，无法避免事故，我们遇到这种紧急的事故要如何处理呢？

（1）数据库服务器硬盘定位问题

在这里插入图片描述

（2）定位mysql系统的报错日志核心信息定位问题

在 Linux 系统中，MySQL 的启动日志默认通常存储在/var/log/mysql 或 /var/log/mysql/目录下，具体的文件名可能是 mysql.log 或类似的名称。

你可以使用以下步骤来查找 MySQL 的启动日志：

使用终端或命令提示符进入 Linux 系统。
导航到/var/log/mysql 或 /var/log/mysql/目录。你可以使用以下命令：
```
cd /var/log/mysql
```
或者
```
cd /var/log/mysql/
```
在该目录中，查看是否存在名为 mysql.log 或类似的文件。如果存在，这可能就是 MySQL 的启动日志。

请注意，上述路径和文件名可能会因不同的 Linux 发行版和 MySQL 配置而有所不同。如果在默认位置找不到启动日志，你可以尝试在 MySQL 的配置文件（通常是 my.cnf 或 my.cnf）中查找有关日志设置的信息，以确定日志文件的实际位置。

另外，你还可以使用以下命令来查看 MySQL 的日志文件位置：

grep -r "log-error" /etc/mysql/

这将在/etc/mysql/目录及其子目录中搜索包含"log-error"的行，这可能会指示 MySQL 日志文件的位置。

我主要过滤出报错

[ERROR] [MY-013132] [Server] The table '表A' is full!

[ERROR] [MY-010907] [Server] Error writing file '/home/log/mysql-slow.log' (errno: 28 - No space left on device)

[ERROR] [MY-000035] [Server] Disk is full writing './mysql-bin.011677' (OS errno 28 - No space left on device). Waiting for someone to free space... Retry in 60 secs. Message reprinted in 600 secs.

No space left on device 这个信息就告诉你硬盘满了！

（3）用文件大小排序命令来判断出是那个表占满硬盘的

先获取到数据库的表文件的存储路径

SHOW VARIABLES LIKE '%datadir%';

然后进入到数据库名称一样的文件夹

du -sh * | sort -hr          #文件大小从大排列

找出最大表的文件，定位到具体那个表！

二、先介绍当前MySQL集群架构

在这里插入图片描述
数据库A与数据库B互为主从，上层有个nginx代理层，默认指向数据库A，当A宕机自动切换B，实现无缝衔接HA高可用集群！
现在遇到的问题比较特别，是某表再短时间内爆硬盘，硬盘监控还没有来得及预警短信，服务器的硬盘已经被沾满！MYSQL的日常操作已经不能正常！语句已经无法执行！

在这里插入图片描述
如果执行SQL如上图，一般会爆一些FULL的错！

在这里插入图片描述
删除一些没有用的日志文件，空间瞬间又被沾满，头痛至极！

三、解决问题的思路与方法

步骤一关闭入口让硬盘尽量停止增长

关掉2台数据库当前主从同步，其实硬盘已经满了，主从相当于失效，关掉程序客户端程序！避免干扰！

步骤二尝试删除一些没有用的日志

删除2台服务器没有用的日志文件，尝试腾出一些空间，然后在其他存在MYSQL服务器客户端的，执行单表备份！

mysqldump -u用户名 -p密码  --host=IP地址端口 --databases 数据库名称 --tables 表名名称1,表名称2，表名称3 > /root/db_back/all.sql

步骤三然后尝试备份到其他服务器上

然后尝试备份到其他服务器上，确保数据不丢失！
在这里插入图片描述
此步骤如果不能执行，可以执行完步骤四之后再试！

步骤四备份拿到之后，尝试重启数据库服务，释放缓冲池占用硬盘空间

   systemctl restart mysql

   service mysql restart

在这里插入图片描述
重启之后，mysql此时会释放innodb_buffer_pool_size缓冲池在硬盘上占用，此时此刻重启之后的硬盘空间会有所减小，所以此时此刻是操作大表的好机会，趁机备份，然后删除垃圾数据，或者直接truncate 表，数据筛选再还原回去！

innodb_buffer_pool的介绍

innodb_buffer_pool_size 是 MySQL 中 InnoDB 存储引擎的一个重要参数，它用于设置 InnoDB 缓冲池的大小。

InnoDB 缓冲池是 InnoDB 用于缓存数据和索引的内存区域。当 InnoDB 从磁盘读取数据时，它会将数据缓存在缓冲池中，以便后续的读取操作可以直接从内存中获取数据，而无需再次访问磁盘。这样可以显著提高数据库的性能，减少磁盘 I/O 操作。

innodb_buffer_pool_size 指定了 InnoDB 缓冲池可用的内存大小。较大的缓冲池大小可以提高缓存命中率，减少磁盘 I/O，从而提升数据库的性能。然而，过大的缓冲池大小可能会导致内存占用过高，并且在系统内存有限的情况下可能会影响其他进程的内存使用。

设置合适的 innodb_buffer_pool_size 值需要考虑数据库的工作负载、数据量、内存资源等因素。一般来说，可以根据以下几点来确定合适的大小：

数据量和访问模式：如果数据库中的数据量较大，或者有频繁的读写操作，可能需要较大的缓冲池来提高性能。
内存资源：确保系统有足够的内存来分配给缓冲池，同时也要考虑其他进程的内存需求。
性能测试和监控：可以通过性能测试和监控来评估不同缓冲池大小对性能的影响，找到最佳的设置值。

需要注意的是，修改 innodb_buffer_pool_size 参数需要谨慎操作，并且可能需要重启 MySQL 服务使更改生效。在进行任何配置更改之前，建议先备份数据，并在测试环境中进行试验，以确保对生产环境的影响最小化。

步骤五如果步骤四重启之后，硬盘仍然不足，调整缩小innodb_buffer_pool的配置值

vi /etc/my.cnf

找到参数：

innodb_buffer_pool_size=32000M

在这里插入图片描述
逐步依次减小这个值，然后重启，直到硬盘剩余足够做大表备份，以及删除表操作！！

每次重启MYSQL服务，nnodb_buffer_pool会重载，则每次硬盘会释放，类似于我们的操作系统的虚拟内存的页面文件，原理类似！

此处又关联到linux操作系统虚拟内存的页面文件，当重启mysql服务，也会释放内存，顺便也会释放一些硬盘空间！

MySQL 服务重启后，MySQL 目录占用的硬盘空间变小可能有以下几个原因如下

缓存清除：重启服务时，MySQL 可能会清除一些缓存数据，例如查询缓存、系统缓存等。这些缓存数据通常存储在硬盘上的特定目录中。清除缓存可以释放一些硬盘空间。
临时文件删除：在 MySQL 运行过程中，可能会生成一些临时文件，例如临时查询结果、临时备份文件等。重启服务可能会删除这些临时文件，从而减少硬盘空间的占用。
日志滚动或截断：MySQL 通常会记录日志信息，例如错误日志、事务日志等。在某些情况下，重启服务可能会导致日志文件的滚动或截断，删除旧的日志记录，从而节省硬盘空间。
数据库优化或清理：在重启之前，可能进行了一些数据库优化操作，例如清理无用的索引、删除过期的数据等。这些操作可能会减少数据库占用的硬盘空间。
数据压缩：某些情况下，MySQL 可能会对数据进行压缩，从而减少硬盘空间的使用。重启服务后，这种压缩效果可能会更加明显。
系统清理或修剪：操作系统本身可能在重启时进行一些清理操作，例如删除临时文件、清理回收站等，这也可能导致硬盘空间的减少。
需要注意的是，具体原因可能因系统配置、数据库使用情况和其他因素而异。如果你对硬盘空间的变化感到困惑，可以进一步检查以下内容：
MySQL 配置：确保 MySQL 的配置参数没有导致数据的过度存储或保留。
数据增长：检查数据库中的数据量是否有明显的增长，如果是，可能需要考虑优化数据库结构或进行数据清理。
日志设置：检查日志文件的设置，确保它们不会过度占用硬盘空间，并根据需要调整日志滚动或截断的策略。
监控和分析：使用监控工具来观察硬盘空间的使用情况，以及数据库活动和性能，以便更好地理解和解决潜在的问题。***

按下图的步骤获取保留生产数据！
在这里插入图片描述

四、总结

此案例主要是解决硬盘监控预警跟不上表空间增长速度的特殊情况，我们在实际生产生活中，尤其是高并发的生产项目，也会遇到突然爆数据库表的情况，希望这个案例能帮助到大家！最好是能提前硬盘预警，但是实际有的时候监控跟不上表的增长速度，这个才是难点！

笔者简介
国内某一线知名软件公司企业认证在职员工：任JAVA高级研发工程师,大数据领域专家,数据库领域专家兼任高级DBA!10年软件开发经验！现任国内某大型软件公司大数据研发工程师、MySQL数据库DBA，软件架构师。直接参与设计国家级亿级别大数据项目!并维护真实企业级生产数据库300余个!紧急处理数据库生产事故上百起，挽回数据丢失所造成的灾难损失不计其数!并为某国家级大数据系统的技术方案（国家知识产权局颁布）专利权的第一专利发明人！

在这里插入图片描述