XOOPS 資料庫問題

因硬碟快掛了造成資料庫錯誤
# mysqlcheck -A -o -r -p
Enter password:
xoops.block_instance
error    : Incorrect information in file: './xoops/block_instance.frm'



關閉 MySQL
/etc/init.d/mysqld stop

進入資料庫所在目錄
cd /var/lib/mysql/xoops

複製問題資料庫備份
cp block_instance.* /root/work/backup/.

檢查修復問題資料庫
快速修復
# myisamchk -r -q block_instance.MYI
- check record delete-chain
- recovering (with sort) MyISAM-table 'block_instance.MYI'
Data records: 11
- Fixing index 1
- Fixing index 2

修復
# myisamchk -r block_instance.MYI
- recovering (with sort) MyISAM-table 'block_instance.MYI'
Data records: 11
- Fixing index 1
- Fixing index 2

強制修復
# myisamchk -o block_instance.MYI
- recovering (with keycache) MyISAM-table 'block_instance.MYI'
Data records: 11

強制修復
# myisamchk -f block_instance.MYI
Checking MyISAM file: block_instance.MYI
Data records:      11   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check record links

以上都沒用，因為壞掉的是 block_instance.frm
是結構出問題
# cat block_instance.frm
cat: block_instance.frm: 輸入/輸出錯誤
檔案也有問題

最後想到有上週的備份
刪除發生錯誤的檔案
用測試機器的 phpmyadmin 匯入備份資料庫
再取出資料表語法
用 mysql 匯入

CREATE TABLE IF NOT EXISTS `block_instance` (
  `instanceid` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `bid` int(12) unsigned NOT NULL DEFAULT '0',
  `options` text NOT NULL,
  `title` varchar(255) NOT NULL DEFAULT '',
  `side` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `weight` smallint(5) unsigned NOT NULL DEFAULT '0',
  `visible` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `bcachetime` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`instanceid`),
  KEY `join` (`instanceid`,`visible`,`weight`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;

完成修復

 .frm、.MYD、.MYI   分別是資料表的結構、索引、資料