MySQL 删除大体积数据表文件的两种方法

发布时间: 2024-11-27 分类: 系统安全/os_security 热度: 12763

日常业务数据库服务器数据激增导致磁盘空间不足，无法恢复业务，下面将介绍如何删除大的数据表文件，以 telescope_entries 表为例，当可以启动 MySQL 服务时，如何删除，当无法启动 MySQL 服务时又该如何删除。

可以启动 mysql 的情况

直接裁剪 telescope_entries 的表会有外键关联的问题，无法执行，所以需要临时关闭外键检测，使用 SET FOREIGN_KEY_CHECKS = 0; 即可做到。

之后再把外键检测开启：SET FOREIGN_KEY_CHECKS = 1;

整体过程如下：

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE telescope_entries_tags;
TRUNCATE TABLE telescope_monitoring;
TRUNCATE TABLE telescope_entries;
SET FOREIGN_KEY_CHECKS = 1;

truncate 使用该命令阶段表会立即释放数据

无法启动 mysql 的情况

适用于磁盘空间已满，无法快速简单找出有效的大文件进行删除，但又想迫切恢复数据库的方法。

进入 mysql data 目录，再进入数据库目录，直接删除 telescope_entries.ibd、telescope_entries.frm 相关文件。

如：

rm -f telescope_entries.*

给 my.cnf 添加配置：

innodb_force_recovery=0

加到哪里都行，如 docker 的配置一整个文件无配置段的就放一个这个配置也行。

重启 mysql，因为删除了大体积数据表文件，这次应该可以运行。

启动之后这个表就可以自由删除和重建了，你需要提前或者在其他地方获取到该表的结构 SQL 语句。

DROP TABLE telescope_entries_tags;
CREATE TABLE `telescope_entries_tags` (
  `entry_uuid` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tag` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  KEY `telescope_entries_tags_entry_uuid_tag_index` (`entry_uuid`,`tag`),
  KEY `telescope_entries_tags_tag_index` (`tag`),
  CONSTRAINT `telescope_entries_tags_entry_uuid_foreign` FOREIGN KEY (`entry_uuid`) REFERENCES `telescope_entries` (`uuid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


DROP TABLE telescope_monitoring;
CREATE TABLE `telescope_monitoring` (
  `tag` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



DROP TABLE telescope_entries;
CREATE TABLE `telescope_entries` (
  `sequence` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch_id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `family_hash` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `should_display_on_index` tinyint(1) NOT NULL DEFAULT '1',
  `type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`sequence`),
  UNIQUE KEY `telescope_entries_uuid_unique` (`uuid`),
  KEY `telescope_entries_batch_id_index` (`batch_id`),
  KEY `telescope_entries_family_hash_index` (`family_hash`),
  KEY `telescope_entries_created_at_index` (`created_at`),
  KEY `telescope_entries_type_should_display_on_index_index` (`type`,`should_display_on_index`)
) ENGINE=InnoDB   DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

如果你没有添加 my.cnf 而是删除数据表文件后，启动后再执行删除表的命令或者重建数据库的命令，会提示表已经存在，无法删除或者创建，又或是报错：

ERROR 1030 (HY000) at line 1: Got error 168 from storage engine

防范准备

为了防止意外发生，数据库数据激增占满硬盘的情况，提前在服务器创建一个大文件，待到紧急时刻，无脑删除大文件，可以直接恢复服务，然后进而诊断以及删除无效的数据, 以解燃眉之急。

下面通过 dd 命令创建 1G 的文件

dd if=/dev/zero of=/root/empty-file bs=1M count=1000

MySQL 删除大体积数据表文件的两种方法

可以启动 mysql 的情况

无法启动 mysql 的情况

防范准备

在下方留下您的评论.加入TG群.打赏🍗