MySQL的InnoDB擴容及ibdata1檔案瘦身方案完全解析_Mysql

mysql的innodb擴容
為了添加一個資料檔案到資料表空間中，首先要關閉 MySQL 資料庫，編輯 my.cnf 檔案，確認innodb ibdata檔案的實際情況和my.cnf的配置是否一致，這裡有兩種情況：
1.my.cnf的配置

innodb_data_file_path=ibdata1:10G;ibdata2:10G:autoextend 

如果當前資料庫正在使用ibdata1，或者使用ibdata2，但ibdata2沒有超過10G，則對my.cnf配置直接改成：

innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G:autoextend 

2.如果設定了最後一個ibdata自動擴充時，有可能最後一個ibdata的佔用空間大於my.cnf的配置空間。例如：

 mysql@test:/data1/mysqldata/innodb/data> ls -lh 

-rw-rw---- 1 mysql mysql 10737418240 2010-01-26 16:34 ibdata1 -rw-rw---- 1 mysql mysql 16106127360 2010-01-26 16:34 ibdata2 

這時，需要精確的計算ibdata2的大小 15360M，修改：

innodb_data_file_path=ibdata1:10G;ibdata2:15360M;ibdata3:10G:autoextend 

重啟mysql。
注意：
1、擴容前注意磁碟空間是否足夠。
2、restart後關注是否產生了新的ibdata。
更多說明：
如果，最後一個檔案以關鍵字 autoextend 來描述，那麼編輯 my.cnf 的過程中，必須檢查最後一個檔案的尺寸，並使它向下接近於 1024 * 1024 bytes (= 1 MB) 的倍數（比方說現在autoextend 的/ibdata/ibdata1為18.5M，而在舊的my.ini中為10M，則需要修改為innodb_data_file_path = /ibdata/ibdata1:19M; 且必須是19M，如果指定20M，就會報錯。），並在 innodb_data_file_path 中明確指定它的尺寸。然後你可以添加另一個資料檔案。記住只有 innodb_data_file_path 中的最後一個檔案可以被指定為 auto-extending。
一個例子：假設起先僅僅只有一個 auto-extending 資料檔案 ibdata1 ，這個檔案接近於 988 MB。下面是添加了另一個 auto-extending 資料檔案後的可能樣本 。

innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend 

ibdata1 瘦身

0. ibdata1裡存了什麼

當你啟用了 innodb_file_per_table，表被儲存在他們自己的資料表空間裡，但是共用資料表空間仍然在儲存其它的 InnoDB 內部資料：
（1）資料字典，也就是 InnoDB 表的中繼資料
（2）變更緩衝區
（3）雙寫緩衝區
（4）撤銷日誌

其中的一些在 Percona 伺服器上可以被配置來避免增長過大的。例如你可以通過 innodb_ibuf_max_size 設定最大變更緩衝區，或設定 innodb_doublewrite_file 來將雙寫緩衝區儲存到一個分離的檔案。

MySQL 5.6 版中你也可以建立外部的撤銷資料表空間，所以它們可以放到自己的檔案來替代儲存到 ibdata1。

1. 什麼引起 ibdata1 增長迅速？

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

SHOW ENGINE INNODB STATUS/G

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

---TRANSACTION 36E, ACTIVE 1256288 secMySQL thread id 42, OS thread handle 0x7f8baaccc700, query id 7900290 localhost rootshow engine innodb statusTrx read view will not see trx with id >= 36F, sees < 36F

這是一個最常見的原因，一個14天前建立的相當老的事務。這個狀態是活動的，這意味著 InnoDB 已經建立了一個資料的快照，所以需要在撤銷日誌中維護舊頁面，以保障資料庫的一致性視圖，直到事務開始。如果你的資料庫有大量的寫入任務，那就意味著儲存了大量的撤銷頁。

如果你找不到任何長時間啟動並執行事務，你也可以監控INNODB STATUS 中的其他的變數，“History list length（歷程清單長度）”展示了一些等待清除操作。這種情況下問題經常發生，因為清除線程（或者老版本的主線程）不能像這些記錄進來的速度一樣快地處理撤銷。

2. 我怎麼檢查什麼被儲存到了 ibdata1 裡了？

很不幸，MySQL 不提供查看什麼被儲存到 ibdata1 共用資料表空間的資訊，但是有兩個工具將會很有協助。第一個是馬克·卡拉漢製作的一個修改版 innochecksum ，它發布在這個漏洞報告裡。

它相當便於使用：

# ./innochecksum /var/lib/mysql/ibdata10 bad checksum13 FIL_PAGE_INDEX19272 FIL_PAGE_UNDO_LOG230 FIL_PAGE_INODE1 FIL_PAGE_IBUF_FREE_LIST892 FIL_PAGE_TYPE_ALLOCATED2 FIL_PAGE_IBUF_BITMAP195 FIL_PAGE_TYPE_SYS1 FIL_PAGE_TYPE_TRX_SYS1 FIL_PAGE_TYPE_FSP_HDR1 FIL_PAGE_TYPE_XDES0 FIL_PAGE_TYPE_BLOB0 FIL_PAGE_TYPE_ZBLOB0 other3 max index_id

全部的 20608 中有 19272 個撤銷日誌頁。這佔用了資料表空間的 93%。

第二個檢查資料表空間內容的方式是傑裡米·科爾製作的 InnoDB Ruby 工具。它是個檢查 InnoDB 的內部結構的更先進的工具。例如我們可以使用 space-summary 參數來得到每個頁面及其資料類型的列表。我們可以使用標準的 Unix 工具來統計撤銷日誌頁的數量：

# innodb_space -f /var/lib/mysql/ibdata1 space-summary | grep UNDO_LOG | wc -l19272

儘管這種特殊的情況下，innochedcksum 更快更容易使用，但是我推薦你使用傑裡米的工具去瞭解更多的 InnoDB 內部的資料分布及其內部結構。

好，現在我們知道問題所在了。

3. ibdata1 瘦身方案
其中的一些在 Percona 伺服器上可以被配置來避免增長過大的。例如你可以通過 innodb_ibuf_max_size 設定最大變更緩衝區，或設定 innodb_doublewrite_file 來將雙寫緩衝區儲存到一個分離的檔案。

MySQL 5.6 版中你也可以建立外部的撤銷資料表空間，所以它們可以放到自己的檔案來替代儲存到 ibdata1。

通常不能移除 InnoDB 的資料檔案。為了減小資料檔案的大小，你必須使用 mysqldump 來轉儲(dump)所有的資料表，再重建立立一個新的資料庫，並將資料匯入新的資料庫中。具體步驟如下：
（1）備份資料庫
mysqldump -uroot -p123456 --default-character-set=utf8 --opt --extended-insert=true --triggers -R --hex-blob --single-transaction --no-autocommit  test > db_name.sql 
（2）停止資料庫

service mysqld stop 

（3）刪除相關檔案

ibdata1 ib_logfile* mysql-bin.index 

（4）手動刪除除Mysql之外所有資料庫檔案夾，然後啟動資料庫 

service mysqld start 

（5）還原資料

 /usr/local/mysql/bin/mysql -uroot -phigkoo < /data/bkup/mysqldump.sql 

主要是使用Mysqldump時的一些參數，建議在使用前看一個說明再操作。另外備份前可以先查看一下當前資料庫裡哪些表佔用空間大，把一些不必要的給truncate table掉。這樣省些空間和時間