MySQL ibdata1文件減肥過程解析
引言
夏天來了，沒想到連 ibdata1 文件也要開始“減肥”了~~~
作者：楊彩琳
愛可生華東交付部 DBA，主要負責 MySQL 日常問題處理及 DMP 產(chǎn)品支持。愛好跳舞，追劇。
本文來源：原創(chuàng)投稿
有句話是這么說的：“在 InnoDB 存儲引擎中數(shù)據(jù)是按照表空間來組織存儲的”。其實潛臺詞就是：表空間是表空間文件，是實際存在的物理文件，MySQL 中有很多表空間，下面一起來了解一下吧。
人物介紹
在說“減肥”的故事之前，讓我們先了解一下需要“減肥”的文件包含哪些部分，都是什么。
系統(tǒng)表空間
首先要說的是本文的主角，系統(tǒng)表空間。它里面存儲的有：
- InnoDB 表元數(shù)據(jù)
- doublewrite buffer
- change buffer
- undo logs
若在未配置 innodb_file_per_table 參數(shù)情況下有新建表的操作，那么系統(tǒng)表空間也會存儲這些表和索引數(shù)據(jù)信息。前面有說過表空間也是實際存在的表空間文件，同樣系統(tǒng)表空間它可以有一個或多個數(shù)據(jù)文件，默認情況下，是在數(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)默認啟用了。
通用表空間
可以通過 CREATE tablespace 語法創(chuàng)建的共享 InnoDB 表空間。與系統(tǒng)表空間類似，它能存儲多個表的數(shù)據(jù)，也可將數(shù)據(jù)文件放置在 MySQL 數(shù)據(jù)目錄之外單獨管理。
UNDO 表空間
主要存儲 undo logs，默認情況下 undo logs 是存儲在系統(tǒng)表空間中的，可通過參數(shù) innodb_undo_tablespaces 來配置 UNDO 表空間的數(shù)量，只能在初始化 MySQL 實例時才能設置該參數(shù)，并且在實例的使用壽命內(nèi)是固定的，MySQL 8.0 可支持動態(tài)修改。
臨時表空間
非壓縮的、用戶創(chuàng)建的臨時表和磁盤上產(chǎn)生的內(nèi)部臨時表都是存儲在共享的臨時表空間存儲的，可以通過配置參數(shù) innodb_tmp_data_file_path 來定義臨時表空間數(shù)據(jù)文件的路徑、名稱、大小和屬性，如果沒有指定，默認是在數(shù)據(jù)目錄下創(chuàng)建一個名為 ibtmp1的大于 12M 的自動擴展數(shù)據(jù)文件。
前情提要
客戶反饋 MySQL 5.7 的配置文件中沒有開啟 UNDO 表空間和 UNDO 回收參數(shù)，導致 ibdata1 文件過大，并且一直在增長。需要評估下 ibdata1 文件大小如何回收及 UNDO 相關參數(shù)配置。
制定“減肥”計劃
思路：ibdata1 文件中包含了 InnoDB 表的元數(shù)據(jù)，change buffer，doublewrite buffer，undo logs 等數(shù)據(jù)，無法自動收縮，必須使用將數(shù)據(jù)邏輯導出，刪除 ibdata1 文件，然后將數(shù)據(jù)導入的方式來釋放 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
停止數(shù)據(jù)庫服務
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 = 3
- innodb_max_undo_log_size = 4G
- innodb_undo_log_truncate = 1
- innodb_file_per_table = 1
新建實例數(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
啟動新建的數(shù)據(jù)庫服務
[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
導入備份數(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
驗證結果
減肥前 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)境建議
上面的“減肥”計劃對于生產(chǎn)環(huán)境可能有點暴力，所以，對于生產(chǎn)環(huán)境若是遇到相同場景的，建議采用下面較溫和謹慎的方法：
- 申請一臺新的服務器，部署從庫。配置好
innodb_file_per_table參數(shù)，UNDO 相關參數(shù)； - 主庫進行邏輯全備；
- 將主庫備份數(shù)據(jù)恢復到新從庫，并建立復制關系；
- 主從切換，提升新從庫為主庫。
UNDO 相關參數(shù)設置
注意：MySQL5.7 不支持在線或者離線分離 UNDO 表空間操作，UNDO 表空間的獨立必須在數(shù)據(jù)庫初始化時指定。
## 控制 Innodb 使用的 UNDO 表空間的數(shù)據(jù)量，默認值為 0，即記錄在系統(tǒng)表空間中。 innodb_undo_tablespaces = 3 ## 控制 UNDO 表空間的閾值大小 innodb_max_undo_log_size = 4G ## 控制將超過 innodb_maxundo_log_size 定義的閾值的 UNDO 表空間被標記為 truncation innodb_undo_log_truncate = 1
關于 SQLE
愛可生開源社區(qū)的 SQLE 是一款面向數(shù)據(jù)庫使用者和管理者，支持多場景審核，支持標準化上線流程，原生支持 MySQL 審核且數(shù)據(jù)庫類型可擴展的 SQL 審核工具。
SQLE 獲取
以上就是MySQL ibdata1文件減肥過程解析的詳細內(nèi)容，更多關于MySQL ibdata1文件減肥的資料請關注腳本之家其它相關文章！
相關文章
Mysql通過explain分析定位數(shù)據(jù)庫性能問題
這篇文章主要介紹了Mysql通過explain分析定位數(shù)據(jù)庫性能問題,明確SQL在Mysql中實際的執(zhí)行過程是怎樣的，如果查詢字段沒有索引則增加索引，如果有索引就要分析為什么沒有用到索引，本文詳細講解，需要的朋友可以參考下2023-01-01
逛論壇時無意發(fā)現(xiàn)有個伙計提出這樣的問題,如何用命令行進入mysql，搜集整理了一些特意貼出來與大家分享，感興趣的你可以參考下希望對你有所幫助2013-03-03
這篇文章主要介紹了Mysql事務隔離級別原理實例解析,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2020-03-03
在本文里我們給大家分享了關于mysql開啟binlog的方法和相關知識點，對此有需要的朋友們跟著學習下。2019-03-03
這篇文章主要為大家詳細介紹了MySQL 5.7.18 免安裝版配置教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-05-05
MySQL?到?SelectDB?的實時數(shù)據(jù)同步技術,通過?NineData?的數(shù)據(jù)復制控制臺,僅需輕點鼠標,即可輕松完成?MySQL?到?SelectDB?的同步任務配置,這篇文章主要介紹了MySQL到SelectDB的實時同步策略,需要的朋友可以參考下2023-09-09
這篇文章主要介紹了簡單了解數(shù)據(jù)庫方言dialect，數(shù)據(jù)庫方言也是如此，MySQL 是一種方言，Oracle 也是一種方言，MSSQL 也是一種方言，他們之間在遵循 SQL 規(guī)范的前提下，都有各自的擴展特性,需要的朋友可以參考下2019-07-07
本文詳細介紹了MySQL8.0.27并行DDL的實現(xiàn)方法,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2024-10-10
這篇文章主要介紹了jdbc連接mysq之serverTimezone設定方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-01-01
使用MySQL進行千萬級別數(shù)據(jù)查詢的技巧分享
這篇文章主要介紹了如何使用MySQL進行千萬級別數(shù)據(jù)查詢的技巧,文中通過代碼示例給大家講解的非常詳細,對大家的學習或工作有一定的幫助,需要的朋友可以參考下2024-03-03