原因分析

ibdata1是一個用來構建innodb系統表空間的文件

上面是一個數據庫ibdata1文件，達到了780多G，而且還在不斷增長。
這個文件包含了innodb數據字典、修改buffer和雙寫buffer、撤銷日志，還包含在用戶在系統表空間創建的表信息和索引數據
顯然，由于所有表的數據索引和緩存都存在這個文件中，隨著數據庫的不斷增大，這個文件肯定會越來越大的。

解決辦法

和系統表空間（也稱作共享表空間）對應，MySQL提供了另外一種存儲文件的方式：獨立表空間。
獨立表空間模式下，每個innodb表都有自己獨立的表空間文件（.ibd文件），存儲各種表的索引和數據。
通過配置項：innodb_file_per_table指定MySQL使用獨立表空間，MySQL5.6.6以后的版本默認值是ON。MySQL5.6.5以前的版本默認值是OFF。

解決ibdata1文件過大具體操作步驟

如果當前MySQL使用系統表空間的模式，是無法在開啟數據庫的情況下進行切換到共享表空間的。必須關閉MySQL重建數據結構。步驟如下：

備份數據庫

使用mysqldump備份所有InnoDB數據表，包括MySQL的系統表。
使用下面的命令可以參考當前系統表:

SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='mysql' and ENGINE='InnoDB';

MySQL5.6中包含五張系統表：

• innodb_index_stats
• innodb_table_stats
• slave_master_info
• slave_relay_log_info
• slave_worker_info

如果數據庫只用了InnoDB表，可以直接備份所有數據庫。

mysqldump -h localhost -uroot -pxxxxx –all-databases > dump.sql

關閉MySQL服務

使用mysqld_safe關閉MySQL服務。

mysqladmin -uroot -pxxxxx shutdown

刪除現有數據庫文件

首先需要刪除當前存在的所有表空間文件（.idb），包括ibdata1和ib_log文件，以及數據庫自帶的.idb文件。
然后需要刪除所有的表結構描述文件：*.frm。位于數據庫名稱相應的文件夾下。

修改配置文件

在數據庫配置文件my.cnf中的[mysqld]下添加innodb_file_per_table=1配置。

[mysqld]
innodb_file_per_table=1

重新啟動服務器并導入數據

最后使用新的配置文件重新啟動mysql服務。

mysqld_safe --defaults-file=/your/config/path/my.cnf &

然后導入mysaldump備份的數據

# 登錄進入mysql
mysql -hlocalhost -uroot -pxxxxx database_name

# 導入數據
source /your/backup/file/path/dump.sql

其他

獨立表空間的優點

剛開始建立數據庫時，就推薦使用獨立表空間，MySQL5.6.6以后的版本默認是獨立表空間。
使用獨立表空間很顯然能夠提高存儲效率，拆分表和表之間的耦合，將對數據庫的操作粒度降低到表級別。
獨立表空間對于存儲優化，遷移，備份，恢復和監控來說，都更加靈活和強大。下面列舉一些代表性的好處：

• truncate和drop表時會釋放掉磁盤空間，共享表空間并不會釋放而是在ibdata1中開辟新的空間
• truncate table時速度更快
• 可以將表放在不同的磁盤上（用于I/O優化等），共享表空間必須所有表都反正ibdata1中
• 可以對每個表使用OPTIMIZE TABLE命令進行優化和重建，回收未使用的空間
• 可以移動單個表，或者將單個表從一個實例復制到另外一個實例
• 使用Barracuda文件格式，至此壓縮和動態行等功能
• 使用動態行(dynamic row format）可以使得存儲大型BLOB和TEXT格式數據更高效
• 當文件損壞時，提高成功恢復機會，節省服務器重啟或備份的時間

當然獨立表空間也有一些潛在的缺點

• 由于每個表都存在為使用的空間，這些空間只能同一個表使用，可能會造成空間浪費
• fsync操作必須在每個打開的表上運行
• mysqld必須為每個表保留一個打開的文件句柄，如果表過多，可能會影響性能
• 在刪除表空間的文件時會掃描緩沖池，如果緩沖池達到幾十G，則需要幾秒的時間，而掃描會造成鎖，可能會延遲其他操作
• 如果許多表正在增長，可能會存在更多的碎片，這回妨礙刪除表和掃描表的性能。

MySQL的ibdata1詳解

關于 MySQL 的 ibdata1 文件的這個問題：
當監控服務器發送一個關于 MySQL 服務器存儲的報警時，恐慌就開始了 —— 就是說磁盤快要滿了。
一番調查后你意識到大多數地盤空間被 InnoDB 的共享表空間 ibdata1 使用。而你已經啟用了innodb_file_per_table

ibdata1存了什么

當你啟用了 innodb_file_per_table，表被存儲在他們自己的表空間里，但是共享表空間仍然在存儲其它的 InnoDB 內部數據：

• 數據字典，也就是 InnoDB 表的元數據
• 變更緩沖區
• 雙寫緩沖區
• 撤銷日志

其中的一些在 Percona 服務器上可以被配置來避免增長過大的。例如你可以通過 innodb_ibuf_max_size 設置最大變更緩沖區，或設置 innodb_doublewrite_file 來將雙寫緩沖區存儲到一個分離的文件。
MySQL 5.6 版中你也可以創建外部的撤銷表空間，所以它們可以放到自己的文件來替代存儲到 ibdata1。可以看看這個文檔。

引起 ibdata1 增長迅速的原因

當 MySQL 出現問題通常我們需要執行的第一個命令是：

SHOW ENGINE INNODB STATUS/G

這將展示給我們一些很有價值的信息。我們從** TRANSACTION（事務）**部分開始檢查，然后我們會發現這個：
:::tips

1. —TRANSACTION 36E, ACTIVE 1256288 sec
2. MySQL thread id 42, OS thread handle 0x7f8baaccc700, query id 7900290 localhost root
3. show engine innodb status
4. Trx read view will not see trx with id >= 36F, sees < 36F
   :::
   這是一個最常見的原因，一個14天前創建的相當老的事務。這個狀態是活動的，這意味著 InnoDB 已經創建了一個數據的快照，所以需要在撤銷日志中維護舊頁面，以保障數據庫的一致性視圖，直到事務開始。如果你的數據庫有大量的寫入任務，那就意味著存儲了大量的撤銷頁。
   如果你找不到任何長時間運行的事務，你也可以監控INNODB STATUS 中的其他的變量，“History list length（歷史記錄列表長度）”展示了一些等待清除操作。這種情況下問題經常發生，因為清除線程（或者老版本的主線程）不能像這些記錄進來的速度一樣快地處理撤銷。

我怎么檢查什么被存儲到了 ibdata1 里了

很不幸，MySQL 不提供查看什么被存儲到 ibdata1 共享表空間的信息，但是有兩個工具將會很有幫助。第一個是馬克·卡拉漢制作的一個修改版 innochecksum ，它發布在這個漏洞報告里。
它相當易于使用：
:::tips

1. ./innochecksum /var/lib/mysql/ibdata1

2. 0 bad checksum

3. 13 FIL_PAGE_INDEX

4. 19272 FIL_PAGE_UNDO_LOG

5. 230 FIL_PAGE_INODE

6. 1 FIL_PAGE_IBUF_FREE_LIST

7. 892 FIL_PAGE_TYPE_ALLOCATED

8. 2 FIL_PAGE_IBUF_BITMAP

9. 195 FIL_PAGE_TYPE_SYS

10. 1 FIL_PAGE_TYPE_TRX_SYS

11. 1 FIL_PAGE_TYPE_FSP_HDR

12. 1 FIL_PAGE_TYPE_XDES

13. 0 FIL_PAGE_TYPE_BLOB

14. 0 FIL_PAGE_TYPE_ZBLOB

15. 0 other

16. 3 max index_id

17. 全部的 20608 中有 19272 個撤銷日志頁。這占用了表空間的 93%。
    :::
    第二個檢查表空間內容的方式是杰里米·科爾制作的 InnoDB Ruby 工具。它是個檢查 InnoDB 的內部結構的更先進的工具。例如我們可以使用 space-summary 參數來得到每個頁面及其數據類型的列表。我們可以使用標準的 Unix 工具來統計撤銷日志頁的數量：
    :::tips

18. innodb_space -f /var/lib/mysql/ibdata1 space-summary | grep UNDO_LOG | wc -l

19. 19272
    :::
    盡管這種特殊的情況下，innochedcksum 更快更容易使用，但是我推薦你使用杰里米的工具去了解更多的 InnoDB 內部的數據分布及其內部結構。

解決問題的辦法

這個問題的答案很簡單。如果你還能提交語句，就做吧。如果不能的話，你必須要殺掉線程開始回滾過程。那將停止 ibdata1 的增長，但是很顯然，你的軟件會出現漏洞，有些人會遇到錯誤。現在你知道如何去鑒定問題所在，你需要使用你自己的調試工具或普通的查詢日志來找出誰或者什么引起的問題。
如果問題發生在清除線程，解決方法通常是升級到新版本，新版中使用一個獨立的清除線程替代主線程。更多信息查看該文檔

回收已使用的空間的方法

沒有，目前還沒有一個容易并且快速的方法。InnoDB 表空間從不收縮…參見10 年之久的漏洞報告，最新更新自詹姆斯·戴（謝謝）：
當你刪除一些行，這個頁被標為已刪除稍后重用，但是這個空間從不會被回收。唯一的方法是使用新的 ibdata1 啟動數據庫。要做這個你應該需要使用 mysqldump 做一個邏輯全備份，然后停止 MySQL 并刪除所有數據庫、ib_logfile*、ibdata1* 文件。當你再啟動 MySQL 的時候將會創建一個新的共享表空間。然后恢復邏輯備份。

總結

當 ibdata1 文件增長太快，通常是 MySQL 里長時間運行的被遺忘的事務引起的。嘗試去解決問題越快越好（提交或者殺死事務），因為不經過痛苦緩慢的 mysqldump 過程，你就不能回收浪費的磁盤空間。
也是非常推薦監控數據庫以避免這些問題。我們的 MySQL 監控插件包括一個 Nagios 腳本，如果發現了一個太老的運行事務它可以提醒你。