腳本之家服務(wù)器常用軟件

快捷導(dǎo)航

MySQL ibdata1文件減肥過程解析

更新時(shí)間：2023年06月19日 10:18:15 作者：愛可生云數(shù)據(jù)庫

這篇文章主要為大家介紹了MySQL ibdata1文件減肥過程解析，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪

引言

夏天來了，沒想到連 ibdata1 文件也要開始“減肥”了~~~

作者：楊彩琳

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

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

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

人物介紹

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

系統(tǒng)表空間

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

InnoDB 表元數(shù)據(jù)
doublewrite buffer
change buffer
undo logs

若在未配置 innodb_file_per_table 參數(shù)情況下有新建表的操作，那么系統(tǒng)表空間也會(huì)存儲(chǔ)這些表和索引數(shù)據(jù)信息。前面有說過表空間也是實(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 來定義。

獨(dú)立表空間

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

通用表空間

可以通過 CREATE tablespace 語法創(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)表空間中的，可通過參數(shù) innodb_undo_tablespaces 來配置 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ǔ)的，可以通過配置參數(shù) innodb_tmp_data_file_path 來定義臨時(shí)表空間數(shù)據(jù)文件的路徑、名稱、大小和屬性，如果沒有指定，默認(rèn)是在數(shù)據(jù)目錄下創(chuàng)建一個(gè)名為 ibtmp1的大于 12M 的自動(dòng)擴(kuò)展數(shù)據(jù)文件。

前情提要

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

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

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

夏天來了，沒想到連 ibdata1 文件也要開始“減肥”了~~~

”減肥“前

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

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

全量備份

對(duì)庫做全量備份。我們使用 mysqldump 做全備，因?yàn)?nbsp;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 &gt; /data/full_$(date +%F).sql

停止數(shù)據(jù)庫服務(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í)例（步驟略過），注意配置文件中需要配置下列參數(shù)：

innodb_undo_tablespaces = 3
innodb_max_undo_log_size = 4G
innodb_undo_log_truncate = 1
innodb_file_per_table = 1

新建實(shí)例數(shù)據(jù)文件如下：

[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

啟動(dòng)新建的數(shù)據(jù)庫服務(wù)

[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

導(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 &lt; full_2023-06-14.sql

驗(yàn)證結(jié)果

減肥前 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 文件減肥成功！

生產(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ù)器，部署從庫。配置好 innodb_file_per_table 參數(shù)，UNDO 相關(guān)參數(shù)；
主庫進(jìn)行邏輯全備；
將主庫備份數(shù)據(jù)恢復(fù)到新從庫，并建立復(fù)制關(guān)系；
主從切換，提升新從庫為主庫。

UNDO 相關(guān)參數(shù)設(shè)置

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

## 控制 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)記為 truncation
innodb_undo_log_truncate = 1

關(guān)于 SQLE

愛可生開源社區(qū)的 SQLE 是一款面向數(shù)據(jù)庫使用者和管理者，支持多場(chǎng)景審核，支持標(biāo)準(zhǔn)化上線流程，原生支持 MySQL 審核且數(shù)據(jù)庫類型可擴(kuò)展的 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文件減肥的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: