MySQL ibdata1文件减肥过程详解
发布时间：2023-09-16 15:03:19 所属栏目：MySql教程 来源：
导读：在说“减肥”的故事之前，让我们先了解一下需要“减肥”的文件包含哪些部分，都是什么。
系统表空间
首先要说的是本文的主角，系统表空间。它里面存储的有：
InnoDB 表元数据
d
系统表空间
首先要说的是本文的主角，系统表空间。它里面存储的有：
InnoDB 表元数据
d
|
在说“减肥”的故事之前，让我们先了解一下需要“减肥”的文件包含哪些部分，都是什么。 系统表空间 首先要说的是本文的主角，系统表空间。它里面存储的有： 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 停止数据库服务 1 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 文件减肥成功！ 生产环境建议 上面的“减肥”计划对于生产环境可能有点暴力，所以，对于生产环境若是遇到相同场景的，建议采用下面较温和谨慎的方法： 申请一台新的服务器，部署从库。配置好 innodb_file_per_table 参数，UNDO 相关参数； 主库进行逻辑全备； 将主库备份数据恢复到新从库，并建立复制关系； 主从切换，提升新从库为主库。 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 （编辑：聊城站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
推荐文章
站长推荐
