[MySQL] shrink ibdata file

最近幫忙處理某台 MySQL 5.0 server,做了個小實驗,寫個文章把處理過程紀錄下來。

因為該 server 沒有 innodb_file_per_table 這個設定,所有的 InnoDB 資料都放在 ibdata 這個檔案裡,而我們希望能讓該 server 持續運作,盡可能在不停機的狀況下,把 InnoDB 的資料切開。

實驗後,我們透過這些步驟完成切割/轉移:

  1. 確認舊的 server 有開啟 binlog,準備跑 replication。
  2. Percona XtraBackup 備份舊的 MySQL 資料庫。
  3. 準備新機器,把備份檔丟到新的 server。
  4. 在新機器的 my.cnf 加入 innodb_file_per_table 設定,把 MySQL daemon 跑起來。
  5. 在新機器用以下指令把 InnoDB table 挑出來:
    SELECT `TABLE_SCHEMA`, `TABLE_NAME` FROM `TABLES` WHERE `ENGINE`='InnoDB' ORDER BY `TABLE_SCHEMA`,`TABLE_NAME`;
  6. 在新機器用 mysqldump 匯出 table schema & data :
    mysqldump -u root -pMY_PASSWORD --max_allowed_packet=512M -Cx --opt TABLE_SCHEMA TABLE_NAME > TMP_DIR/TABLE_SCHEMA.TABLE_NAME.sql
  7. 在新機器 drop 掉已匯出的 table:
    DROP TABLE `TABLE_SCHEMA`.`TABLE_NAME`;
  8. 停掉新機器的 MySQL daemon,把 datadir 的 ib* 搬走,再啟動 MySQL daemon。
  9. 重新匯入 table:
    mysql -u root -pMY_PASSWORD TABLE_SCHEMA < TMP_DIR/TABLE_SCHEMA.TABLE_NAME.sql
  10. CHANGE MASTER TO … 指令開始讓新機器的 MySQL 成為舊 server 的 SLAVE。
  11. 同步完成後,找時間讓舊的 server 退下,讓新的 server 佔用舊機器的 IP。
  12. 在新機器的 MySQL 執行 RESET SLAVE,停掉 replication。