前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >技术分享 | ibdata1 “减肥” 记

技术分享 | ibdata1 “减肥” 记

作者头像
爱可生开源社区
发布2023-08-18 19:51:19
3110
发布2023-08-18 19:51:19
举报

作者:杨彩琳

爱可生华东交付部 DBA,主要负责 MySQL 日常问题处理及 DMP 产品支持。爱好跳舞,追剧。

本文来源:原创投稿

* 爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。


有句话是这么说的:“在 InnoDB 存储引擎中数据是按照表空间来组织存储的”。其实潜台词就是:表空间是表空间文件,是实际存在的物理文件,MySQL 中有很多表空间[1],下面一起来了解一下吧。

1人物介绍

在说“减肥”的故事之前,让我们先了解一下需要“减肥”的文件包含哪些部分,都是什么。

系统表空间

首先要说的是本文的主角,系统表空间。它里面存储的有:

  • 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 的自动扩展数据文件。

2前情提要

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

3制定“减肥”计划

思路:ibdata1 文件中包含了 InnoDB 表的元数据,change buffer,doublewrite buffer,undo logs 等数据,无法自动收缩,必须使用将数据逻辑导出,然后将数据导入的方式来释放 ibdata1 文件。

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

”减肥“前

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

ps:因为是测试‘减肥计划’,所以只模拟了一个‘微胖’的 ibdata1 文件。

代码语言:javascript
复制
[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 文件。

代码语言:javascript
复制
/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

停止数据库服务

代码语言:javascript
复制
systemctl stop mysql_3309

删除原实例

代码语言:javascript
复制
[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

新建实例数据文件如下:

代码语言:javascript
复制
[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

启动数据库服务

代码语言:javascript
复制
[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

导入备份数据

代码语言:javascript
复制
[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。

代码语言:javascript
复制
[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 文件减肥成功!

4生产环境建议

上面的“减肥”计划对于生产环境可能有点暴力,所以,对于生产环境若是遇到相同场景的,建议采用下面较温和谨慎的方法:

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

5UNDO 相关参数设置

注意:MySQL5.7 不支持在线或者离线分离 UNDO 表空间操作,UNDO 表空间的独立必须在数据库初始化时指定。

代码语言:javascript
复制
## 控制 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

参考资料

[1]

Tablespaces: https://dev.mysql.com/doc/refman/5.7/en/innodb-system-tablespace.html

本文关键字:#InnoDB# #表空间# #备份恢复#

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2023-06-15,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 爱可生开源社区 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 1人物介绍
    • 系统表空间
      • 独立表空间
        • 通用表空间
          • UNDO 表空间
            • 临时表空间
            • 2前情提要
            • 3制定“减肥”计划
              • ”减肥“前
                • 全量备份
                  • 停止数据库服务
                    • 删除原实例
                      • 创建新实例
                        • 启动数据库服务
                          • 导入备份数据
                            • 验证结果
                              • 参考资料
                          • 4生产环境建议
                          • 5UNDO 相关参数设置
                          相关产品与服务
                          对象存储
                          对象存储(Cloud Object Storage,COS)是由腾讯云推出的无目录层次结构、无数据格式限制,可容纳海量数据且支持 HTTP/HTTPS 协议访问的分布式存储服务。腾讯云 COS 的存储桶空间无容量上限,无需分区管理,适用于 CDN 数据分发、数据万象处理或大数据计算与分析的数据湖等多种场景。
                          领券
                          问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档