MySQL ibdata1文件減肥過程解析
夏天來了，沒想到連 ibdata1 文件也要開始“減肥”了~~~
作者：楊彩琳
愛可生華東交付部 DBA，主要負責 MySQL 日常問題處理及 DMP 產品支持。愛好跳舞，追劇。
本文來源：原創投稿
有句話是這么說的：“在 InnoDB 存儲引擎中數據是按照表空間來組織存儲的”。其實潛臺詞就是：表空間是表空間文件，是實際存在的物理文件，MySQL 中有很多表空間，下面一起來了解一下吧。
人物介紹在說“減肥”的故事之前，讓我們先了解一下需要“減肥”的文件包含哪些部分，都是什么。
系統表空間首先要說的是本文的主角，系統表空間。它里面存儲的有：
InnoDB 表元數據doublewrite bufferchange bufferundo 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]# lltotal 2109496-rw-r----- 1 mysql mysql56 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 ibtmp1drwxr-x--- 2 mysql mysql 4096 Jun 14 14:26 mysql-rw-r----- 1 mysql mysql 5 Jun 14 14:26 mysqld.pidsrwxrwxrwx 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.logdrwxr-x--- 2 mysql mysql 8192 Jun 14 14:26 performance_schemadrwxr-x--- 2 mysql mysql 8192 Jun 14 14:26 sysdrwxr-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 = 3innodb_max_undo_log_size = 4Ginnodb_undo_log_truncate = 1innodb_file_per_table = 1新建實例數據文件如下：
[root@10-186-61-119 ~]# ll /data/mysql/3309total 4drwxr-x--- 2 mysql mysql 6 Jun 14 14:51 backupdrwxr-x--- 9 mysql mysql 129 Jun 14 14:52 basedrwxr-x--- 2 mysql mysql 77 Jun 14 14:52 binlogdrwxr-x--- 5 mysql mysql 331 Jun 14 14:52 data-rw-r--r-- 1 mysql mysql 3609 Jun 14 14:52 my.cnf.3309drwxr-x--- 2 mysql mysql 6 Jun 14 14:51 redologdrwxr-x--- 2 mysql mysql 6 Jun 14 14:51 relaylogdrwxr-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 3309mysql 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]# lltotal 1747000-rw-r----- 1 mysql mysql56 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 ibtmp1drwxr-x--- 2 mysql mysql 4096 Jun 14 14:55 mysql-rw-r----- 1 mysql mysql 5 Jun 14 14:52 mysqld.pidsrwxrwxrwx 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.logdrwxr-x--- 2 mysql mysql 8192 Jun 14 14:52 performance_schemadrwxr-x--- 2 mysql mysql 8192 Jun 14 14:52 sysdrwxr-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 表空間被標記為 truncationinnodb_undo_log_truncate = 1關于 SQLE愛可生開源社區的 SQLE 是一款面向數據庫使用者和管理者，支持多場景審核，支持標準化上線流程，原生支持 MySQL 審核且數據庫類型可擴展的 SQL 審核工具。
SQLE 獲取類型地址版本庫https://github.com/actiontech/sqle文檔https://actiontech.github.io/sqle-docs/發布信息https://github.com/actiontech/sqle/releases數據審核插件開發文檔https://actiontech.github.io/sqle-docs-cn/3.modules/3.7_audit...以上就是MySQL ibdata1文件減肥過程解析的詳細內容，更多關于MySQL ibdata1文件減肥的資料請關注好吧啦網其它相關文章！
網公網安備