MySQL 5.6 如何給ibdata1瘦身

前不久剛給ibdata1瘦身，發篇文章總結下。

ibdata1是MySQL使用InnoDB引擎時所産生的檔案，其一般存儲資料、索引、結構、緩沖資料、共享資料和重做日志等。因為ibdata1隻增不減，長期操作資料庫，可能會使其越來越大，而浪費空間。

加上使用InnoDB引擎時，沒有添加innodb_file_per_table參數也是導緻ibdata1過大的原因。

但InnoDB隻增不減，也導緻給ibdata1瘦身是件比較麻煩的事。

最大的我見過的是40多G的ibdata1檔案，實際資料庫差不多是20多G，在做了優化後，ibdata1縮小至20多G，是以說減肥還是有必要的。

首先，先略微說下innodb_file_per_table參數，使用該參數可使得InnoDB引擎轉變為獨立表空間模式（預設為共享表空間），也就是每個資料庫的每個表都會生成一個資料空間，就像MyISAM引擎一樣。

獨立表空間優點就是每個表有獨立空間，資料和索引都會存在自已的表空間中，可以實作單表在不同的資料庫中移動。重要的是空間可以回收，而且不管日常怎麼操作，表空間的碎片不會太嚴重的影響性能，優化表的速度也快，表檔案出現問題不會大動幹戈，隻要修複對應表即可。缺點是單表占用的空間比共享表空間方式稍大，共享表空間在Insert操作上有一些優勢。

是以沒增加innodb_file_per_table參數的同學，建議還是加上吧。因為增加innodb_file_per_table參數，與我們後續給ibdata1瘦身并無沖突，而且對以後也隻有好處。

說了這麼多廢話，言歸正傳。談談怎麼給ibdata1瘦身，唯一的方法是就是備份整個資料庫，然後删掉ibdata1和ib_logfile*，再恢複資料庫，以此達到瘦身目的。當然了，操作資料庫肯定是有風險的，而且也需要生産環境允許MySQL暫停寫或通路。

簡單的總結就是以下這幾點：

1.在/etc/my.cnf中添加“innodb_force_recovery=4”使InnoDB成為隻讀表，這其實應該說是第一個坑。另外确定“innodb_data_file_path”參數限定的初始ibdata1大小在合理範圍，一般稍大于現有資料大小。

2.啟動MySQL，使用我給的工具備份除了mysql、information_schema和performance_schema的整個資料庫。為以後順利恢複資料做準備。

3.删除除了mysql、information_schema和performance_schema的整個資料庫，這3個排除在外的其實也删不掉。

4.停止MySQL，删除ibdata1和ib_logfile*檔案。

5.删除資料庫目錄中的mysql目錄的innodb_index_stats.*、innodb_table_stats.*、slave_master_info.*、slave_relay_log_info.*、slave_worker_info.*檔案，這5個是InnoDB的基礎表（狀态表），這是MySQL 5.6的坑，删除ibdata1後不會自動重建這5個表，而且如果不删除這些舊檔案，還不可建立或重建新的。

6.在/etc/my.cnf把“innodb_force_recovery=4”去除。

7.啟動MySQL，将第2步備份的資料庫還原，然後用從MySQL 5.6導出來的InnoDB基礎表備份還原回去重建（第5步删掉的）。

8.搞定，重新開機下MySQL，確定沒有錯誤，沒有異常。

必要的工具，在文末的Github位址下載下傳。

下面詳細說說每個步驟，首先是設定InnoDB為隻讀表，這還是比較必要的。可確定你的資料完整性、安全性。為何是坑呢，因為我遇到過沒這樣設定，導緻後續恢複備份時，資料有異常。

具體操作，在/etc/my.cnf中添加“innodb_force_recovery=4”

...
[mysqld]
...
innodb_force_recovery = 4
...
           

如此便可，innodb_file_per_table也是在[mysqld]下添加。

innodb_force_recovery的值可以設定為1-6，大的數字包含其前面所有數字的影響。

1. (SRV_FORCE_IGNORE_CORRUPT):忽略檢查到的corrupt頁。

2. (SRV_FORCE_NO_BACKGROUND):阻止主線程的運作，如主線程需要執行full purge操作，會導緻crash。

3. (SRV_FORCE_NO_TRX_UNDO):不執行事務復原操作。

4. (SRV_FORCE_NO_IBUF_MERGE):不執行插入緩沖的合并操作。

5. (SRV_FORCE_NO_UNDO_LOG_SCAN):不檢視重做日志，InnoDB存儲引擎會将未送出的事務視為已送出。

6. (SRV_FORCE_NO_LOG_REDO):不執行前滾的操作。

接着呢，啟動MySQL，備份整個資料庫，一般我們會用

mysqldump --lock-all-tables --all-databases > all-dbs.sql
           

來完整備份資料庫。但這樣的話，就包含了mysql、information_schema和performance_schema這3個資料庫，而在後續還原資料庫，帶着這3個資料庫可能會出錯（我有幾次遇到過）。

是以我提供了shell腳本工具mysql_dump_all_db.sh，因為怕大家不像我是免密碼操作MySQL的，在使用工具前，請先修改腳本中的MySQL帳号和密碼，然後再通過

sh mysql_dump_all_db.sh
           

執行備份操作，備份好的檔案，會存放在腳本運作所在目錄。

這個工具預設排除mysql、information_schema和performance_schema這3個資料庫，如果你有其他想排除的，可以直接修改腳本，增加其他想要排除的資料庫。

完了之後呢，删除所有資料庫，可以通過phpMyadmin或直接在shell操作MySQL删除，在shell下删除，可以在/tmp/DatabasesToDump.txt檢視到所有資料庫，由“mysql_dump_all_db.sh”生成。我一般在phpMyadmin删除，簡單，不怕錯。如你所見，mysql、information_schema和performance_schema這3個資料庫是删不掉的，是以要排除，免得麻煩。

搞定後，就可以停止MySQL了。删除資料庫目錄的ibdata1和ib_logfile*檔案，一般是在/usr/local/mysql/data，看你怎麼配置的了。

接着，在該目錄下的mysql目錄（/usr/local/mysql/data/mysql）中，把innodb_index_stats.*、innodb_table_stats.*、slave_master_info.*、slave_relay_log_info.*、slave_worker_info.*共計10個檔案删除，這些檔案已經無用了，而且占着茅坑不拉屎。MySQL 5.6很煞筆的不會重建這5個表，如果你不删除他們，待會将無法重建或恢複這5個表，接着log一直在報錯，死循環。是以要把這5個表的10個檔案幹掉。是以這個是個坑。

在/etc/my.cnf把“innodb_force_recovery=4”去除後，就可以啟動MySQL了，這時候ibdata1和ib_logfile*檔案會重建。噢，上帝，勝利在望，不要激動，讓我們繼續吧。

把剛才備份的所有資料庫還原，用從MySQL 5.6導出來的InnoDB基礎表備份還原回去重建。

我在後面的Github位址有提供，從全新 MySQL 5.6導出的，名字為“mysql_innodb_basic_tables.sql”的備份檔案，通過它可以重建剛才删掉的5個InnoDB基礎表。

還原資料庫檔案非常簡單，不過我還是略微寫下，照顧下小白，在shell下：

mysql < all-dbs.sql
mysql < mysql_innodb_basic_tables.sql
           

這樣就OK了，如沒有免密碼操作權限，請自行添加-u和-p參數。

好的，做完這些，重新開機下MySQL，確定沒有錯誤即可。這樣就完成了對ibdata1的瘦身。

以上操作環境為：CentOS 6.6 x64、MySQL 5.6.25。

工具存放在Github中（原諒我的渣英文），見： https://github.com/kn007/Reduce-Shrink-Purge-the-ibdata1-file-in-MySQL

寫這篇文章就是為了大家少走一點彎路，也把自己遇到的坑說一下。算是個總結，也是給後人的一些經驗。原則上不提供技術支援，有問題請自行解決。另外畢竟是資料庫，瘦身有風險，操作需謹慎。