使用MYISAM存储引擎
当创建
个表时会出现 3个文件:数据文件MYD索引文件MYI,格式文件frm
当索引文件损坏时或者丢失时可以用如下
思路方法进行修复下面是实验过程[mysql@testapp74not
y]$mvmessage_ignore.MYImessage_ignore.MYI.bak [mysql@testapp74not
y]$ [mysql@testapp74not
y]$mysqld_safe& [1]26267
[mysql@testapp74not
y]$08063014:19:03mysqld_safeLoggingto'/usr/mysql_data/log/alert.log'. 08063014:19:03mysqld_safeStartingmysqlddaemonwithdatabasesfrom/usr/mysql_data/data
[mysql@testapp74not
y]$ [mysql@testapp74not
y]$ [mysql@testapp74not
y]$ [mysql@testapp74not
y]$vi/usr/mysql_data/log/alert.log 08063014:18:14[Note]/usr/sbin/mysqld:Normalshutdown
08063014:18:14[Note]EventScheduler:Purgingthequeue.0events
08063014:18:16InnoDB:Startingshutdown...
08063014:18:19InnoDB:Shutdowncompleted;logsequencenumber162783173592
08063014:18:19[Note]/usr/sbin/mysqld:Shutdowncomplete
08063014:18:19mysqld_safemysqldfrompidfile/usr/mysql_data/data/testapp74.pidended
08063014:19:03mysqld_safeStartingmysqlddaemonwithdatabasesfrom/usr/mysql_data/data
08063014:19:03InnoDB:Started;logsequencenumber162783173592
08063014:19:03[Note]EventScheduler:Loaded0events
08063014:19:03[Note]/usr/sbin/mysqld:readyforconnections.
Version:'5.1.23-rc-community'
:'/tmp/mysql.sock'port:3306MySQLCommunityEdition(GPL)
从上面可以看出
当个表出现问题mysql警告日志并没有任何
出现[mysql@testapp74not
y]$mysql-uroot WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis4
Serverversion:5.1.23-rc-communityMySQLCommunityEdition(GPL)
Type'help;'or'\h'forhelp.Type'\c'toclearthebuffer.
mysql>usenot
y Readingtableinformationforcompletionoftableandcolumnnames
Youcanturnoffthisfeaturetogetaquickerstartupwith-A
Databasechanged
mysql>
mysql>
mysql>
mysql>
mysql>select*frommessage_ignorelimit1;--当查询表时
会报错 ERROR1017(HY000):Can'tfindfile:'message_ignore'(errno:2)
mysql>checktablemessage_ignore;
+-----------------------+-------+----------+----------------------------------------------+
|Table|Op|Msg_type|Msg_text|
+-----------------------+-------+----------+----------------------------------------------+
|not
y.message_ignore|check|Error|Can'tfindfile:'message_ignore'(errno:2)| |not
y.message_ignore|check|error|Corrupt| +-----------------------+-------+----------+----------------------------------------------+
2rowsin
(0.03sec) mysql>
mysql>repairtablemessage_ignore;
+-----------------------+--------+----------+----------------------------------------------+
|Table|Op|Msg_type|Msg_text|
+-----------------------+--------+----------+----------------------------------------------+
|not
y.message_ignore|repair|Error|Can'tfindfile:'message_ignore'(errno:2)| |not
y.message_ignore|repair|error|Corrupt| +-----------------------+--------+----------+----------------------------------------------+
2rowsin
(0.00sec)
使用格式化文件frm重新创建索引文件
mysql>repairtablemessage_ignoreUSE_FRM;
+-----------------------+--------+----------+----------+
|Table|Op|Msg_type|Msg_text|
+-----------------------+--------+----------+----------+
|not
y.message_ignore|repair|status|OK| +-----------------------+--------+----------+----------+
1rowin
(0.02sec) mysql>
mysql>checktablemessage_ignore;
+-----------------------+-------+----------+----------+
|Table|Op|Msg_type|Msg_text|
+-----------------------+-------+----------+----------+
|not
y.message_ignore|check|status|OK| +-----------------------+-------+----------+----------+
1rowin
(0.02sec)
修复后
数据可以正常查询mysql>select*frommessage_ignorelimit1;
Empty
(0.00sec)
如果是格式化文件frm损坏
可以从slave拷贝过来即可或者从备份中恢复附录
在哪些情况下myisam存储引擎表容易损坏:14.1.4.1. Corrupted MyISAM Tables
Even though the MyISAM table format is very reliable (all changes to a table made by an SQL statement are written before the statement
s), you can still get corrupted tables any of the following events occur:- The mysqld process is killed in the middle of a write.
- An unexpected computer shutdown occurs (for example, the computer is turned off).
- Hardware failures.
- You are using an external program (such as myisamchk) to mody a table that is being modied by the server at the same time.
- A software bug in the MySQL or MyISAM code.
最新评论