目錄引言人物介紹系統(tǒng)表空間獨(dú)立表空間通用表空間UNDO 表空間臨時(shí)表空間前情提要制定“減肥”計(jì)劃”減肥“前全量備份停止數(shù)據(jù)庫(kù)服務(wù)刪除原實(shí)例新建實(shí)例啟動(dòng)新建的數(shù)據(jù)庫(kù)服務(wù)導(dǎo)入備份數(shù)據(jù)驗(yàn)證結(jié)果生產(chǎn)環(huán)境建議UNDO 相關(guān)參數(shù)設(shè)置關(guān)于 SQLESQLE 獲取引言

夏天來(lái)了，沒(méi)想到連 ibdata1 文件也要開始“減肥”了~~~

作者：楊彩琳

愛可生華東交付部 DBA，主要負(fù)責(zé) MySQL 日常問(wèn)題處理及 DMP 產(chǎn)品支持。愛好跳舞，追劇。

本文來(lái)源：原創(chuàng)投稿

有句話是這么說(shuō)的：“在 InnoDB 存儲(chǔ)引擎中數(shù)據(jù)是按照表空間來(lái)組織存儲(chǔ)的”。其實(shí)潛臺(tái)詞就是：表空間是表空間文件，是實(shí)際存在的物理文件，MySQL 中有很多表空間，下面一起來(lái)了解一下吧。

人物介紹

在說(shuō)“減肥”的故事之前，讓我們先了解一下需要“減肥”的文件包含哪些部分，都是什么。

系統(tǒng)表空間

首先要說(shuō)的是本文的主角，系統(tǒng)表空間。它里面存儲(chǔ)的有：

InnoDB 表元數(shù)據(jù)doublewrite bufferchange bufferundo logs

若在未配置 innodb_file_per_table 參數(shù)情況下有新建表的操作，那么系統(tǒng)表空間也會(huì)存儲(chǔ)這些表和索引數(shù)據(jù)信息。前面有說(shuō)過(guò)表空間也是實(shí)際存在的表空間文件，同樣系統(tǒng)表空間它可以有一個(gè)或多個(gè)數(shù)據(jù)文件，默認(rèn)情況下，是在數(shù)據(jù)目錄中創(chuàng)建一個(gè)名為 ibdata1 文件的系統(tǒng)表空間數(shù)據(jù)文件，其文件大小和數(shù)量可以由參數(shù) innodb_data_file_path 來(lái)定義。

獨(dú)立表空間

由 innodb_file_per_table 參數(shù)定義。啟用后，InnoDB 可以在 file-per-table 表空間中創(chuàng)建表，這樣新創(chuàng)建的數(shù)據(jù)庫(kù)表都單獨(dú)的表空間文件。該參數(shù)在 MySQL 5.6.7 及更高版本已經(jīng)默認(rèn)啟用了。

通用表空間

可以通過(guò) CREATE tablespace 語(yǔ)法創(chuàng)建的共享 InnoDB 表空間。與系統(tǒng)表空間類似，它能存儲(chǔ)多個(gè)表的數(shù)據(jù)，也可將數(shù)據(jù)文件放置在 MySQL 數(shù)據(jù)目錄之外單獨(dú)管理。

UNDO 表空間

主要存儲(chǔ) undo logs，默認(rèn)情況下 undo logs 是存儲(chǔ)在系統(tǒng)表空間中的，可通過(guò)參數(shù) innodb_undo_tablespaces 來(lái)配置 UNDO 表空間的數(shù)量，只能在初始化 MySQL 實(shí)例時(shí)才能設(shè)置該參數(shù)，并且在實(shí)例的使用壽命內(nèi)是固定的，MySQL 8.0 可支持動(dòng)態(tài)修改。

臨時(shí)表空間

非壓縮的、用戶創(chuàng)建的臨時(shí)表和磁盤上產(chǎn)生的內(nèi)部臨時(shí)表都是存儲(chǔ)在共享的臨時(shí)表空間存儲(chǔ)的，可以通過(guò)配置參數(shù) innodb_tmp_data_file_path 來(lái)定義臨時(shí)表空間數(shù)據(jù)文件的路徑、名稱、大小和屬性，如果沒(méi)有指定，默認(rèn)是在數(shù)據(jù)目錄下創(chuàng)建一個(gè)名為 ibtmp1的大于 12M 的自動(dòng)擴(kuò)展數(shù)據(jù)文件。

前情提要

客戶反饋 MySQL 5.7 的配置文件中沒(méi)有開啟 UNDO 表空間和 UNDO 回收參數(shù)，導(dǎo)致 ibdata1 文件過(guò)大，并且一直在增長(zhǎng)。需要評(píng)估下 ibdata1 文件大小如何回收及 UNDO 相關(guān)參數(shù)配置。

制定“減肥”計(jì)劃

思路：ibdata1 文件中包含了 InnoDB 表的元數(shù)據(jù)，change buffer，doublewrite buffer，undo logs 等數(shù)據(jù)，無(wú)法自動(dòng)收縮，必須使用將數(shù)據(jù)邏輯導(dǎo)出，刪除 ibdata1 文件，然后將數(shù)據(jù)導(dǎo)入的方式來(lái)釋放 ibdata1 文件。

夏天來(lái)了，沒(méi)想到連 ibdata1 文件也要開始“減肥”了~~~

”減肥“前

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

ps：因?yàn)槭菧y(cè)試‘減肥計(jì)劃’，所以只模擬了一個(gè)‘微胖’的 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全量備份

對(duì)庫(kù)做全量備份。我們使用 mysqldump 做全備，因?yàn)?Xtrabackup 會(huì)備份 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ù)庫(kù)服務(wù)systemctl stop mysql_3309刪除原實(shí)例[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新建實(shí)例

重新創(chuàng)建一個(gè)同端口的 MySQL 實(shí)例（步驟略過(guò)），注意配置文件中需要配置下列參數(shù)：

innodb_undo_tablespaces = 3innodb_max_undo_log_size = 4Ginnodb_undo_log_truncate = 1innodb_file_per_table = 1

新建實(shí)例數(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啟動(dòng)新建的數(shù)據(jù)庫(kù)服務(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驗(yàn)證結(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)境建議

上面的“減肥”計(jì)劃對(duì)于生產(chǎn)環(huán)境可能有點(diǎn)暴力，所以，對(duì)于生產(chǎn)環(huán)境若是遇到相同場(chǎng)景的，建議采用下面較溫和謹(jǐn)慎的方法：

申請(qǐng)一臺(tái)新的服務(wù)器，部署從庫(kù)。配置好 innodb_file_per_table 參數(shù)，UNDO 相關(guān)參數(shù)；主庫(kù)進(jìn)行邏輯全備；將主庫(kù)備份數(shù)據(jù)恢復(fù)到新從庫(kù)，并建立復(fù)制關(guān)系；主從切換，提升新從庫(kù)為主庫(kù)。UNDO 相關(guān)參數(shù)設(shè)置

注意：MySQL5.7 不支持在線或者離線分離 UNDO 表空間操作，UNDO 表空間的獨(dú)立必須在數(shù)據(jù)庫(kù)初始化時(shí)指定。

## 控制 Innodb 使用的 UNDO 表空間的數(shù)據(jù)量，默認(rèn)值為 0，即記錄在系統(tǒng)表空間中。innodb_undo_tablespaces = 3## 控制 UNDO 表空間的閾值大小innodb_max_undo_log_size = 4G## 控制將超過(guò) innodb_maxundo_log_size 定義的閾值的 UNDO 表空間被標(biāo)記為 truncationinnodb_undo_log_truncate = 1關(guān)于 SQLE

愛可生開源社區(qū)的 SQLE 是一款面向數(shù)據(jù)庫(kù)使用者和管理者，支持多場(chǎng)景審核，支持標(biāo)準(zhǔn)化上線流程，原生支持 MySQL 審核且數(shù)據(jù)庫(kù)類型可擴(kuò)展的 SQL 審核工具。

SQLE 獲取類型地址版本庫(kù)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文件減肥過(guò)程解析的詳細(xì)內(nèi)容，更多關(guān)于MySQL ibdata1文件減肥的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章！