2012年12月1日 星期六

修復 MySQL InnoDB 資料庫

  前幾天在調整校計中的一個 MySQL 資料庫,主要是讓 Radius 用來做認證和登入紀錄的,每秒鐘就會有上百筆的驗證資料進來,使得其中某些表高達上千萬筆紀錄。

  為了要節省一些空間,準備將舊的備份表刪掉,這個表大約是七千萬筆紀錄,1GB左右的空間,但神奇的事情就從這裡開始,DROP 和 TRUNCATE 都會直接讓 MySQL 崩潰重啟,但是仍然可以做 DELETE,因此就腦袋空空下了 DELETE * FROM xxx WHERE 1,(經過漫長等待),MySQL 開始不斷重開 ........  當 process 一開起來,狀態就轉為 waitsig,然後就重開了。

此時先別慌,先相信資料都還在,只是 InnoDB 的資料表損壞了。在網路找了很久終於找到有幫助的資訊。OK,開始修復

1. 停掉 MySQL,同時停掉所有需要使用此 DB 的程式,避免在備份還原時 DB 過於忙碌

# /usr/local/etc/rc.d/mysql-server stop

2. 備份資料庫目錄,FreeBSD 的 MySQL 放在 /var/db/mysql


# mkdir /var/db/mysql_backup
# cp -R /var/db/mysql/ /var/db/mysql_backup


3. 在 my.conf 內加上 innodb_force_recovery (http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html)


[mysqld]
innodb_force_recovery = 6 
# 會忽略所有的錯誤強制讓服務可以開起來, 但會忽略所有的 INSERT 和 UPDATE


4. 在安全模式下把 MySQL 開起來,此時應該是可以順利開起來


# mysqld_safe &

5. Dump 所有的資料庫


#mysqldump -u root -p --force --compress -- triggers --routines --events --create-options --all-databases > /var/db/alldb.sql

6. 停掉 MySQL

# /usr/local/etc/rc.d/mysql-server stop

7. 刪除資料庫目錄,FreeBSD 的 MySQL 放在 /var/db/mysql


# rm -rf /var/db/mysql

8. 重建資料庫目錄


# mkdir /var/db/mysql
# chown mysql:mysql /var/db/mysql
# mysql_install_db
# chown -R mysql:mysql /var/db/mysql


9. 在安全模式下把 MySQL 開起來


# mysqld_safe &

10. 匯入資料庫


# mysql -u root < /var/db/alldb.sql

11. 更新 MySQL 權限


# mysqladmin -u root flush-privileges

12. 重新以正常模式開啟 MySQL

# /usr/local/etc/rc.d/mysql-server stop
# /usr/local/etc/rc.d/mysql-server start


13. 祈禱成功開啟來囉



參考資料:
1. 修复 MySQL 数据库 (MyISAM/InnoDB) http://www.2cto.com/database/201203/123765.html
    (此為超級好文)
2. How to recover MySQL data from Innodb? http://egil.biz/how-to-recover-mysql-data-from-innodb/
3. 使用 ibdata 和 frm 文件恢复 MySQL 数据库 http://cnzhx.net/blog/restore-mysql-from-ibdata-and-frm/