夏天來了，沒想到連 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 文件減肥成功！
生產環境建議
上面的“減肥”計劃對於生產環境可能有點暴力，所以，對於生產環境若是遇到相同場景的，建議採用下面較温和謹慎的方法：
- 申請一台新的服務器，部署從庫。配置好
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