MySQL ibdata1文件減肥過程解析
夏天來了，沒想到連 ibdata1 文件也要開始“減肥”了~~~
作者：楊彩琳
愛可生華東交付部 DBA，主要負(fù)責(zé) MySQL 日常問題處理及 DMP 產(chǎn)品支持。愛好跳舞，追劇。
本文來源：原創(chuàng)投稿
有句話是這么說的：“在 InnoDB 存儲引擎中數(shù)據(jù)是按照表空間來組織存儲的”。其實潛臺詞就是：表空間是表空間文件，是實際存在的物理文件，MySQL 中有很多表空間，下面一起來了解一下吧。
人物介紹在說“減肥”的故事之前，讓我們先了解一下需要“減肥”的文件包含哪些部分，都是什么。
系統(tǒng)表空間首先要說的是本文的主角，系統(tǒng)表空間。它里面存儲的有：
InnoDB 表元數(shù)據(jù)doublewrite bufferchange bufferundo logs若在未配置 innodb_file_per_table 參數(shù)情況下有新建表的操作，那么系統(tǒng)表空間也會存儲這些表和索引數(shù)據(jù)信息。前面有說過表空間也是實際存在的表空間文件，同樣系統(tǒng)表空間它可以有一個或多個數(shù)據(jù)文件，默認(rèn)情況下，是在數(shù)據(jù)目錄中創(chuàng)建一個名為 ibdata1 文件的系統(tǒng)表空間數(shù)據(jù)文件，其文件大小和數(shù)量可以由參數(shù) innodb_data_file_path 來定義。
獨立表空間由 innodb_file_per_table 參數(shù)定義。啟用后，InnoDB 可以在 file-per-table 表空間中創(chuàng)建表，這樣新創(chuàng)建的數(shù)據(jù)庫表都單獨的表空間文件。該參數(shù)在 MySQL 5.6.7 及更高版本已經(jīng)默認(rèn)啟用了。
通用表空間可以通過 CREATE tablespace 語法創(chuàng)建的共享 InnoDB 表空間。與系統(tǒng)表空間類似，它能存儲多個表的數(shù)據(jù)，也可將數(shù)據(jù)文件放置在 MySQL 數(shù)據(jù)目錄之外單獨管理。
UNDO 表空間主要存儲 undo logs，默認(rèn)情況下 undo logs 是存儲在系統(tǒng)表空間中的，可通過參數(shù) innodb_undo_tablespaces 來配置 UNDO 表空間的數(shù)量，只能在初始化 MySQL 實例時才能設(shè)置該參數(shù)，并且在實例的使用壽命內(nèi)是固定的，MySQL 8.0 可支持動態(tài)修改。
臨時表空間非壓縮的、用戶創(chuàng)建的臨時表和磁盤上產(chǎn)生的內(nèi)部臨時表都是存儲在共享的臨時表空間存儲的，可以通過配置參數(shù) innodb_tmp_data_file_path 來定義臨時表空間數(shù)據(jù)文件的路徑、名稱、大小和屬性，如果沒有指定，默認(rèn)是在數(shù)據(jù)目錄下創(chuàng)建一個名為 ibtmp1的大于 12M 的自動擴展數(shù)據(jù)文件。
前情提要客戶反饋 MySQL 5.7 的配置文件中沒有開啟 UNDO 表空間和 UNDO 回收參數(shù)，導(dǎo)致 ibdata1 文件過大，并且一直在增長。需要評估下 ibdata1 文件大小如何回收及 UNDO 相關(guān)參數(shù)配置。
思路：ibdata1 文件中包含了 InnoDB 表的元數(shù)據(jù)，change buffer，doublewrite buffer，undo logs 等數(shù)據(jù)，無法自動收縮，必須使用將數(shù)據(jù)邏輯導(dǎo)出，刪除 ibdata1 文件，然后將數(shù)據(jù)導(dǎo)入的方式來釋放 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停止數(shù)據(jù)庫服務(wù)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新建實例重新創(chuàng)建一個同端口的 MySQL 實例（步驟略過），注意配置文件中需要配置下列參數(shù)：
innodb_undo_tablespaces = 3innodb_max_undo_log_size = 4Ginnodb_undo_log_truncate = 1innodb_file_per_table = 1新建實例數(shù)據(jù)文件如下：
[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啟動新建的數(shù)據(jù)庫服務(wù)[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導(dǎo)入備份數(shù)據(jù)[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驗證結(jié)果減肥前 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 文件減肥成功！
生產(chǎn)環(huán)境建議上面的“減肥”計劃對于生產(chǎn)環(huán)境可能有點暴力，所以，對于生產(chǎn)環(huán)境若是遇到相同場景的，建議采用下面較溫和謹(jǐn)慎的方法：
申請一臺新的服務(wù)器，部署從庫。配置好 innodb_file_per_table 參數(shù)，UNDO 相關(guān)參數(shù)；主庫進行邏輯全備；將主庫備份數(shù)據(jù)恢復(fù)到新從庫，并建立復(fù)制關(guān)系；主從切換，提升新從庫為主庫。UNDO 相關(guān)參數(shù)設(shè)置注意：MySQL5.7 不支持在線或者離線分離 UNDO 表空間操作，UNDO 表空間的獨立必須在數(shù)據(jù)庫初始化時指定。
## 控制 Innodb 使用的 UNDO 表空間的數(shù)據(jù)量，默認(rèn)值為 0，即記錄在系統(tǒng)表空間中。innodb_undo_tablespaces = 3## 控制 UNDO 表空間的閾值大小innodb_max_undo_log_size = 4G## 控制將超過 innodb_maxundo_log_size 定義的閾值的 UNDO 表空間被標(biāo)記為 truncationinnodb_undo_log_truncate = 1關(guān)于 SQLE愛可生開源社區(qū)的 SQLE 是一款面向數(shù)據(jù)庫使用者和管理者，支持多場景審核，支持標(biāo)準(zhǔn)化上線流程，原生支持 MySQL 審核且數(shù)據(jù)庫類型可擴展的 SQL 審核工具。
SQLE 獲取類型地址版本庫https://github.com/actiontech/sqle文檔https://actiontech.github.io/sqle-docs/發(fā)布信息https://github.com/actiontech/sqle/releases數(shù)據(jù)審核插件開發(fā)文檔https://actiontech.github.io/sqle-docs-cn/3.modules/3.7_audit...以上就是MySQL ibdata1文件減肥過程解析的詳細(xì)內(nèi)容，更多關(guān)于MySQL ibdata1文件減肥的資料請關(guān)注好吧啦網(wǎng)其它相關(guān)文章！
網(wǎng)公網(wǎng)安備