修復MySQL數據庫(MyISAM/InnoDB)

在網上找瞭篇MySQL的技術文章,感覺不錯,把它翻譯過來共享下。 

 

原文作者:Mike Peters 

 

我整理瞭7條修復MySQL數據庫的方法,當簡單的重啟對數據庫不起作用,或者有表崩潰時。 

 

簡單的MySQL重啟: 

 

/usr/local/mysql/bin/mysqladmin -uUSERNAME -pPASSWORD shutdown 

/usr/local/mysql/bin/mysqld_safe & 

 

1、MyISAM表崩潰 

 

MySQL數據庫允許不同的表使用不同的存儲引擎。它用來存儲與檢索數據。較流行的存儲引擎是MyISAM與InnoDB。 

 

MyISAM表最終“將”崩潰。這是個不爭的事實。 

 

幸運的是,在多數情況下,MyISAM表崩潰很容易修復。 

 

修復單一表,連接你的數據庫執行: 

 

repair TABLENAME 

 

修復所有的表,執行: 

 

/usr/local/mysql/bin/mysqlcheck –all-databases -uUSERNAME -pPASSWORD -r 

 

多數情況,隻有當你瀏覽日志文件時,才知道MyISAM表崩潰瞭。 

我強烈建議在你的/etc/my.cnf配置文件中添加此行。一旦表崩潰它將進行自動修復。 

 

[mysqld] 

myisam-recover=backup,force 

 

如果這個也不管用,還有其他的方法可以試試。 

 

2、多實例MySQL 

 

當你重啟MySQL後,進程馬上死掉,這很常見。 

查看日志文件,它會告訴你,另一個MySQL實例可能正在運行。 

 

停止所有MySQL實例: 

 

/usr/local/mysql/bin/mysqladmin -uUSERNAME -pPASSWORD shutdown 

killall mysql 

killall mysqld 

 

現在重啟數據庫,將隻有一個實例在運行。 

 

3、改變InnoDB日志設置 

一旦MySQL數據庫有在運行InnoDB引擎,你就一定不能修改/etc/my.cnf文件中如下幾行: 

 

datadir = /usr/local/mysql/data 

innodb_data_home_dir = /usr/local/mysql/data 

innodb_data_file_path = ibdata1:10M:autoextend 

innodb_log_group_home_dir = /usr/local/mysql/data 

innodb_log_files_in_group = 2 

innodb_log_file_size = 5242880 

 

InnoDB日志文件大小一旦確定就不能修改。如果改變瞭,數據庫將不能啟動。 

 

4、MySQL host表丟失 

 

有見過幾次這樣的情況。可能是一些異想不到的MyISAM bug。 

 

輕松將其修復如下: 

 

/usr/local/bin/mysql_install_db 

 

5、不正常的MyISAM自動增長(auto_increment) 

 

如果MyISAM表自增計數變得紊亂,你就不能再插入新的紀錄。 

通常你可以告訴自增計數器它現在工作不正常,通過將最後一條紀錄的自增字段設為-1。 

 

解決問題-找到最後一條自增記錄的有效值(執行如下命令) 

 

SELECT max(id) from tablename 

 

然後更新此表的自增計數器,如下: 

 

ALTER TABLE tablename AUTO_INCREMENT = id+1 

 

6、太多連接數 

 

數據庫變得相當繁忙,因為連接數比它能處理的多。而且現在你都不能連接上你的數據庫。 

首先,停止數據庫: 

 

/usr/local/mysql/bin/mysqladmin -uUSERNAME -pPASSWORD shutdown 

 

如果上條命令不管用,可以試試 "killall mysql" 和 "killall mysqld" 

當數據庫停止後,編輯/etc/my.cnf文件,增加連接數。不要癡狂的增加這個數字,否則你會把你的整臺機器搞崩。

 

在一臺專用數據庫機器上,我們通常用: 

 

max_connections = 200 

wait_timeout = 100 

 

試著重啟數據庫看看是否有幫助。 

如果你被查詢弄的措手不及,需要連接數據庫進行表修改操作,那麼在/etc/my.cnf文件中設置一個不同的端口號,開啟數據庫,進行修改操作。然後將端口修改回來(master-port = 3306)再重啟。 

 

7、InnoDB表崩潰 

 

InnoDB表是我最鐘愛的。事物緩存,可靠,不像MyISAM,InnoDB支持對同一表的並發寫。 

 

InnoDB的內部恢復機制也相當不錯。如果數據庫崩潰,InnoDB將嘗試進行修復,通過從最後一個時間戳開始運行日志文件。大多數情況都會成功,整個過程是透明的。 

 

不過,如果InnoDB自行修復失敗,那麼“整個”數據庫將不能啟動。MySQL將會發出一個錯誤信息並退出,你的整個庫將處於離線狀態。你可以不斷嘗試重啟數據庫,但是如果修復進程失敗,數據庫將拒絕啟動。 

 

這就是為什麼需要運行master/master當使用InnoDB時——當一個master宕掉時,還有一臺冗餘master做後備。 

 

在繼續操作前,先瀏覽下MySQL的日志文件,確定數據庫不是因為InnoDB表的崩潰而崩潰。 

 

有一種方法是更新InnoDB的日志文件計數器以跳過引起崩潰的查詢,但是經驗告訴我們這不是個好方法。這種情況下,將造成數據的不一致性而且會經常使主從復制中斷。 

 

一旦因InnoDB崩潰造成數據庫無法啟動,你就應該按如下五個步驟處理問題: 

 

第一:添加此行到/etc/my.cnf文件中: 

 

[mysqld] 

innodb_force_recovery = 4 

 

第二:重啟MySQL。你的數據庫現在將啟動,但是在innodb_force_recovery參數作用下,所有的插入與更新操作將被忽略。 

 

第三:導出所有的表(Dump all tables) 

 

第四:關閉數據庫,刪除所有的數據文件。運行mysql_install_db 創建默認MySQL表。 

 

第五:從/etc/my.cnf文件中去掉innodb_force_recovery參數,重啟數據庫。(庫現在應該能正常啟動) 

 

第六:從備份文件中恢復所有數據。 

 

續: 

最近遇到瞭個讓人棘手的任務——修復一個失敗的InnoDB數據庫。這個數據庫因崩潰而無法啟動。 

 

第一步將InnoDB在force-recovery模式下開啟,此時InnoDB雖開啟瞭但是將忽略所有更新(UPDATEs)與插入(INSERTs)操作。 

 

在/etc/my.cnf文件中添加此行: 

 

innodb_force_recovery = 2 

 

現在重啟數據庫: 

 

/usr/local/bin/mysqld_safe & 

 

(註意:如果MySQL沒有啟動,繼續增加 innodb_force_recovery 的數值直到將參數值設為8( innodb_force_recovery =) 

 

將所有數據保存到臨時文件alldb.sql(下個命令需要花一定時間): 

 

mysqldump –force –compress –triggers –routines –create-options -uUSERNAME -pPASSWORD –all-databases > /usr/alldb.sql 

 

再次關閉數據庫: 

 

mysqladmin -uUSERNAME -pPASSWORD shutdown 

 

刪除數據庫目錄。(註意:我的數據目錄在/usr/local/var下。你的設置有可能不同,確保刪除的是正確的文件夾。) 

 

rm -fdr /usr/local/var 

 

 

重建數據庫文件夾,安裝MySQL基礎表 

 

mkdir /usr/local/var 

chown -R mysql:mysql /usr/local/var 

/usr/local/bin/mysql_install_db 

chown -R mysql:mysql /usr/local/var 

 

 

從/etc/my.cnf文件中刪除innodb_force_recovery ,重啟數據庫: 

 

/usr/local/bin/mysqld_safe & 

 

 

導入所有備份文件(下一命令需要花一段時間): 

 

mysql -uroot –compress < /usr/alldb.sql 

 

 

最後,刷新MySQL的權限(因為我們也更新瞭MySQL的表) 

 

/usr/local/bin/mysqladmin -uroot flush-privileges 

 

註意:為瞭得到最好的結果,添加port=8819(或任何其他隨機端口)到/etc/my.cnf文件中在重啟MySQL之前,然後將–port=8819添加到mysqldump命令中。這種方法避免瞭MySQL數據庫過於系繁忙當修復進程正在進行時。 

 

 

 

作者 mushme

You May Also Like