关于mysql:MySQL-ibdata1-文件减肥记

夏天来了，没想到连 ibdata1 文件也要开始“减肥”了~~~

作者：杨彩琳

爱可生华东交付部 DBA，次要负责 MySQL 日常问题解决及 DMP 产品反对。喜好跳舞，追剧。

本文起源：原创投稿

有句话是这么说的：“在 InnoDB 存储引擎中数据是依照表空间来组织存储的”。其实潜台词就是： 表空间是表空间文件，是理论存在的物理文件 ，MySQL 中有很多表空间，上面一起来理解一下吧。

人物介绍

在说“减肥”的故事之前，让咱们先理解一下须要“减肥”的文件蕴含哪些局部，都是什么。

零碎表空间

首先要说的是本文的配角，零碎表空间。它外面存储的有：

• InnoDB 表元数据
• doublewrite buffer
• change buffer
• undo logs

若在未配置 innodb_file_per_table 参数状况下有新建表的操作，那么零碎表空间也会存储这些表和索引数据信息。后面有说过表空间也是理论存在的表空间文件，同样零碎表空间它能够有一个或多个数据文件，默认状况下，是在数据目录中创立一个名为 ibdata1 文件的零碎表空间数据文件，其文件大小和数量能够由参数 innodb_data_file_path 来定义。

独立表空间

由 innodb_file_per_table 参数定义。启用后，InnoDB 能够在 file-per-table 表空间中创立表，这样新创建的数据库表都独自的表空间文件。该参数在 MySQL 5.6.7 及更高版本曾经默认启用了。

通用表空间

能够通过 CREATE tablespace 语法创立的共享 InnoDB 表空间。与零碎表空间相似，它能存储多个表的数据，也可将数据文件搁置在 MySQL 数据目录之外独自治理。

UNDO 表空间

次要存储 undo logs，默认状况下 undo logs 是存储在零碎表空间中的，可通过参数 innodb_undo_tablespaces 来配置 UNDO 表空间的数量，只能在初始化 MySQL 实例时能力设置该参数，并且在实例的使用寿命内是固定的，MySQL 8.0 可反对动静批改。

长期表空间

非压缩的、用户创立的长期表和磁盘上产生的外部长期表都是存储在共享的长期表空间存储的，能够通过配置参数 innodb_tmp_data_file_path 来定义长期表空间数据文件的门路、名称、大小和属性，如果没有指定，默认是在数据目录下创立一个名为 ibtmp1 的大于 12M 的主动扩大数据文件。

前情提要

客户反馈 MySQL 5.7 的配置文件中没有开启 UNDO 表空间和 UNDO 回收参数，导致 ibdata1 文件过大，并且始终在增长。须要评估下 ibdata1 文件大小如何回收及 UNDO 相干参数配置。

制订“减肥”打算

思路：ibdata1 文件中蕴含了 InnoDB 表的元数据，change buffer，doublewrite buffer，undo logs 等数据，无奈主动膨胀，必须应用将数据逻辑导出，删除 ibdata1 文件，而后将数据导入的形式来开释 ibdata1 文件。

夏天来了，没想到连 ibdata1 文件也要开始“减肥”了~~~

”减肥“前

减肥之前的 ibdata1 分量是 512M。

ps：因为是测试‘减肥打算’，所以只模仿了一个‘微胖’的 ibdata1 文件。

[root@10-186-61-119 data]# ll
total 2109496
-rw-r----- 1 mysql mysql        56 Jun 14 14:26 auto.cnf
-rw-r----- 1 mysql mysql       409 Jun 14 14:26 ib_buffer_pool
-rw-r----- 1 mysql mysql 536870912 Jun 14 14:35 ibdata1
-rw-r----- 1 mysql mysql 536870912 Jun 14 14:35 ib_logfile0
-rw-r----- 1 mysql mysql 536870912 Jun 14 14:35 ib_logfile1
-rw-r----- 1 mysql mysql 536870912 Jun 14 14:32 ib_logfile2
-rw-r----- 1 mysql mysql  12582912 Jun 14 14:26 ibtmp1
drwxr-x--- 2 mysql mysql      4096 Jun 14 14:26 mysql
-rw-r----- 1 mysql mysql         5 Jun 14 14:26 mysqld.pid
srwxrwxrwx 1 mysql mysql         0 Jun 14 14:26 mysqld.sock
-rw------- 1 mysql mysql         5 Jun 14 14:26 mysqld.sock.lock
-rw-r----- 1 mysql mysql      6675 Jun 14 14:32 mysql-error.log
-rw-r----- 1 mysql mysql       967 Jun 14 14:34 mysql-slow.log
drwxr-x--- 2 mysql mysql      8192 Jun 14 14:26 performance_schema
drwxr-x--- 2 mysql mysql      8192 Jun 14 14:26 sys
drwxr-x--- 2 mysql mysql       172 Jun 14 14:30 test

全量备份

对库做全量备份。咱们应用 mysqldump 做全备，因为 Xtrabackup 会备份 ibdata1 文件。

/data/mysql/3309/base/bin/mysqldump -uroot -p \
-S /data/mysql/3309/data/mysqld.sock \
--default-character-set=utf8mb4 \
--single-transaction --hex-blob \
--triggers --routines --events --master-data=2 \
--all-databases > /data/full_$(date +%F).sql

进行数据库服务

systemctl stop mysql_3309

删除原实例

[root@10-186-61-119 data]# rm -rf /data/mysql/3309
[root@10-186-61-119 data]# rm -rf /etc/systemd/system/mysql_3309.service

新建实例

从新创立一个同端口的 MySQL 实例（步骤略过），留神配置文件中须要配置下列参数：

• innodb_undo_tablespaces = 3
• innodb_max_undo_log_size = 4G
• innodb_undo_log_truncate = 1
• innodb_file_per_table = 1

新建实例数据文件如下：

[root@10-186-61-119 ~]# ll /data/mysql/3309
total 4
drwxr-x--- 2 mysql mysql    6 Jun 14 14:51 backup
drwxr-x--- 9 mysql mysql  129 Jun 14 14:52 base
drwxr-x--- 2 mysql mysql   77 Jun 14 14:52 binlog
drwxr-x--- 5 mysql mysql  331 Jun 14 14:52 data
-rw-r--r-- 1 mysql mysql 3609 Jun 14 14:52 my.cnf.3309
drwxr-x--- 2 mysql mysql    6 Jun 14 14:51 redolog
drwxr-x--- 2 mysql mysql    6 Jun 14 14:51 relaylog
drwxr-x--- 2 mysql mysql    6 Jun 14 14:52 tmp

启动新建的数据库服务

[root@10-186-61-119 ~]# systemctl start mysql_3309
[root@10-186-61-119 ~]# ps -ef | grep 3309
mysql     7341     1  0 14:52 ?        00:00:01 /data/mysql/3309/base/bin/mysqld --defaults-file=/data/mysql/3309/my.cnf.3309 --daemonize

导入备份数据

[root@10-186-61-119 data]# /data/mysql/3309/base/bin/mysql -uroot -p \
-S /data/mysql/3309/data/mysqld.sock < full_2023-06-14.sql

验证后果

减肥前 512M，减肥后 128M。

[root@10-186-61-119 data]# ll
total 1747000
-rw-r----- 1 mysql mysql        56 Jun 14 14:52 auto.cnf
-rw-r----- 1 mysql mysql       422 Jun 14 14:52 ib_buffer_pool
-rw-r----- 1 mysql mysql 134217728 Jun 14 14:57 ibdata1
-rw-r----- 1 mysql mysql 536870912 Jun 14 14:57 ib_logfile0
-rw-r----- 1 mysql mysql 536870912 Jun 14 14:57 ib_logfile1
-rw-r----- 1 mysql mysql 536870912 Jun 14 14:52 ib_logfile2
-rw-r----- 1 mysql mysql  12582912 Jun 14 14:52 ibtmp1
drwxr-x--- 2 mysql mysql      4096 Jun 14 14:55 mysql
-rw-r----- 1 mysql mysql         5 Jun 14 14:52 mysqld.pid
srwxrwxrwx 1 mysql mysql         0 Jun 14 14:52 mysqld.sock
-rw------- 1 mysql mysql         5 Jun 14 14:52 mysqld.sock.lock
-rw-r----- 1 mysql mysql      6841 Jun 14 14:55 mysql-error.log
-rw-r----- 1 mysql mysql       414 Jun 14 14:52 mysql-slow.log
drwxr-x--- 2 mysql mysql      8192 Jun 14 14:52 performance_schema
drwxr-x--- 2 mysql mysql      8192 Jun 14 14:52 sys
drwxr-x--- 2 mysql mysql       172 Jun 14 14:56 test
-rw-r----- 1 mysql mysql  10485760 Jun 14 14:57 undo001
-rw-r----- 1 mysql mysql  10485760 Jun 14 14:57 undo002
-rw-r----- 1 mysql mysql  10485760 Jun 14 14:57 undo003

祝贺 ibdata1 文件减肥胜利！

生产环境倡议

下面的“减肥”打算对于生产环境可能有点暴力，所以，对于生产环境若是遇到雷同场景的，倡议采纳上面较温和审慎的办法：

1. 申请一台新的服务器，部署从库。配置好 innodb_file_per_table 参数，UNDO 相干参数；
2. 主库进行逻辑全备；
3. 将主库备份数据恢复到新从库，并建设复制关系；
4. 主从切换，晋升新从库为主库。

UNDO 相干参数设置

留神：MySQL5.7 不反对在线或者离线拆散 UNDO 表空间操作，UNDO 表空间的独立必须在数据库初始化时指定。

## 管制 Innodb 应用的 UNDO 表空间的数据量，默认值为 0，即记录在零碎表空间中。innodb_undo_tablespaces = 3

## 管制 UNDO 表空间的阈值大小
innodb_max_undo_log_size = 4G

## 管制将超过 innodb_maxundo_log_size 定义的阈值的 UNDO 表空间被标记为 truncation
innodb_undo_log_truncate = 1

对于 SQLE

爱可生开源社区的 SQLE 是一款面向数据库使用者和管理者，反对多场景审核，反对标准化上线流程，原生反对 MySQL 审核且数据库类型可扩大的 SQL 审核工具。

SQLE 获取