웹마짱구's 블로그 :: 손상된 테이블 복구하기

손상된 테이블 복구하기

Linux/MySql : 2006/12/26 18:05

============================================================================
데이터베이스 에러가 발생하였습니다.
에러코드 : 1016
사유 : can't open file: 'mydata_1.MYI' . (errno : 130)
============================================================================

MYI 화일를 변조해서 테스트 해봤습니다. MYD, frm 화일 백업자료가 있어야 하고요, 원본 스키마(create table sql) 를 알아야 합니다.

[root@localhost test]# mysqlcheck -p --auto-repair -B test
Enter password:
test.tmp
error : Can't open file: 'tmp.MYI' (errno: 130)

Repairing tables
test.tmp
error : Can't open file: 'tmp.MYI' (errno: 130)

[root@localhost root]# /etc/rc.d/init.d/mysql stop
Killing mysqld with pid 1470
Wait for mysqld to exit. done

[root@localhost test]# rm tmp.MYI
rm: remove 일반 파일 `tmp.MYI'? y
[root@localhost test]# mv tmp.* ..

[root@localhost root]# /etc/rc.d/init.d/mysql start

mysql -p test
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.9-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
mysql> CREATE TABLE `tmp` (
   -> `no` int(11) NOT NULL auto_increment,
   -> `chr` char(3) NOT NULL default '',
   -> PRIMARY KEY (`no`),
   -> KEY `chr` (`chr`)
   -> ) ENGINE=MyISAM DEFAULT CHARSET=euckr;
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye

[root@localhost root]# /etc/rc.d/init.d/mysql stop
Killing mysqld with pid 3190
Wait for mysqld to exit done

[root@localhost test]# mv ../tmp* .
mv: overwrite `./tmp.MYD'? y
mv: overwrite `./tmp.frm'? y

[root@localhost test]# myisamchk -r tmp
- recovering (with sort) MyISAM-table 'tmp'
Data records: 20
- Fixing index 1
- Fixing index 2

[root@localhost root]# /etc/rc.d/init.d/mysql start
[root@localhost root]# mysql -p test
Enter password:
mysql> select * from tmp;
+----+-----+
| no | chr |
+----+-----+
| 1 | 001 |
| 2 | 002 |
| 3 | 003 |
| 4 | 004 |
| 5 | 005 |
| 6 | 006 |
| 7 | 007 |
| 8 | 008 |
| 9 | 009 |
| 10 | 010 |
| 11 | 011 |
| 12 | 012 |
| 13 | 013 |
| 14 | 014 |
| 15 | 015 |
| 16 | 016 |
| 17 | 017 |
| 18 | 018 |
| 19 | 019 |
| 20 | 020 |
+----+-----+
20 rows in set (0.00 sec)