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 稽核工具。