복구를 해야 할 경우가 있어서 테스트를 진행해 봄.
1. .frm 파일 분실
운영하다가 .frm 파일이 없어졌거나, 깨진 경우
동일 테이블을 생성 한 후, frm 파일의 이름을 바꿔 주면 그냥 인식이 됨.
|
#테이블 생성
MariaDB [(none)]> use maria5 ; MariaDB [maria5]> create table recover_test (id int) engine = MyISAM ; MariaDB [maria5]> show create table recover_test ; +--------------+------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------------+------------------------------------------------------------------------------------------------+ | recover_test | CREATE TABLE `recover_test` ( `id` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | +--------------+------------------------------------------------------------------------------------------------+ MariaDB [maria5]> insert into recover_test values (1) ; MariaDB [maria5]> commit ; MariaDB [maria5]> exit
#.frm 파일 삭제
[root@CMaria maria5]# ls -altr
-rw-r----- 1 mysql mysql 61 Dec 1 16:28 db.opt -rw-r----- 1 mysql mysql 8617 Dec 1 16:28 test_enum.frm -rw-rw---- 1 mysql mysql 8556 Dec 1 18:02 recover_test.frm -rw-rw---- 1 mysql mysql 7 Dec 1 18:03 recover_test.MYD -rw-rw---- 1 mysql mysql 1024 Dec 1 18:03 recover_test.MYI drwxr-x--- 2 mysql mysql 188 Dec 1 18:04 . drwxr-xr-x 6 mysql mysql 4096 Dec 1 18:04 ..
[root@CMaria maria5]# rm -rf recover_test.frm [root@CMaria maria5]# ls -al
drwxr-x--- 2 mysql mysql 164 Dec 1 18:05 . drwxr-xr-x 6 mysql mysql 4096 Dec 1 18:04 .. -rw-r----- 1 mysql mysql 61 Dec 1 16:28 db.opt -rw-rw---- 1 mysql mysql 7 Dec 1 18:03 recover_test.MYD -rw-rw---- 1 mysql mysql 1024 Dec 1 18:03 recover_test.MYI -rw-r----- 1 mysql mysql 8617 Dec 1 16:28 test_enum.frm
# 테이블 조회
MariaDB [(none)]> use maria5 MariaDB [maria5]> show tables ; +------------------+ | Tables_in_maria5 | +------------------+ | recover_test | | test_enum | +------------------+
# 동일한 형태의 테이블 생성
MariaDB [maria5]> create table recover_test1 (id int) engine = MyISAM ; MariaDB [maria5]> exit
# frm 파일 복사
[root@CMaria maria5]# ls -altr
-rw-r----- 1 mysql mysql 61 Dec 1 16:28 db.opt -rw-r----- 1 mysql mysql 8617 Dec 1 16:28 test_enum.frm -rw-rw---- 1 mysql mysql 7 Dec 1 18:03 recover_test.MYD -rw-rw---- 1 mysql mysql 1024 Dec 1 18:03 recover_test.MYI -rw-rw---- 1 mysql mysql 8556 Dec 1 18:04 recover_test1.frm -rw-rw---- 1 mysql mysql 1024 Dec 1 18:04 recover_test1.MYI -rw-rw---- 1 mysql mysql 0 Dec 1 18:04 recover_test1.MYD
[root@CMaria maria5]# cp recover_test1.frm recover_test.frm [root@CMaria maria5]# chown mysql:mysql recover_test.frm
# 데이터 확인
MariaDB [(none)]> use maria5 MariaDB [maria5]> show tables ; +------------------+ | Tables_in_maria5 | +------------------+ | recover_test | | recover_test1 | | test_enum | +------------------+
MariaDB [maria5]> select * from recover_test ; +------+ | id | +------+ | 1 | +------+
1 row in set (0.01 sec)
|
2. .MYI 파일 분실 또는 깨진 경우
|
# 인덱스 생성
MariaDB [maria5]> create index test_idx on recover_test(id) ; MariaDB [maria5]> commit ; MariaDB [maria5]> exit
# MYI 파일 삭제
[root@CMaria maria5]# ls -altr
-rw-r----- 1 mysql mysql 61 Dec 1 16:28 db.opt -rw-r----- 1 mysql mysql 8617 Dec 1 16:28 test_enum.frm -rw-rw---- 1 mysql mysql 8556 Dec 1 18:04 recover_test1.frm -rw-rw---- 1 mysql mysql 1024 Dec 1 18:04 recover_test1.MYI -rw-rw---- 1 mysql mysql 0 Dec 1 18:04 recover_test1.MYD
-rw-rw---- 1 mysql mysql 8556 Dec 1 18:14 recover_test.frm -rw-rw---- 1 mysql mysql 2048 Dec 1 18:14 recover_test.MYI -rw-rw---- 1 mysql mysql 7 Dec 1 18:14 recover_test.MYD drwxr-x--- 2 mysql mysql 188 Dec 1 18:14 .
[root@CMaria maria5]# rm -rf recover_test.MYI
# 복구 (frm 파일을 이용하여 인덱스 파일은 새로 생성 함)
MariaDB [(none)]> use maria5 MariaDB [maria5]> show tables ; +------------------+ | Tables_in_maria5 | +------------------+ | recover_test | | recover_test1 | | test_enum | +------------------+
MariaDB [maria5]> select * from recover_test ; ERROR 1017 (HY000): Can't find file: 'recover_test' (errno: 2)
MariaDB [maria5]> repair table recover_test ; +---------------------+--------+----------+--------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------+--------+----------+--------------------------------------------+ | maria5.recover_test | repair | Error | Can't find file: 'recover_test' (errno: 2) | | maria5.recover_test | repair | status | Operation failed | +---------------------+--------+----------+--------------------------------------------+
MariaDB [maria5]> check table recover_test ; +---------------------+-------+----------+--------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------+-------+----------+--------------------------------------------+ | maria5.recover_test | check | Error | Can't find file: 'recover_test' (errno: 2) | | maria5.recover_test | check | status | Operation failed | +---------------------+-------+----------+--------------------------------------------+
MariaDB [maria5]> repair table recover_test use_frm ; +---------------------+--------+----------+------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------+--------+----------+------------------------------------+ | maria5.recover_test | repair | warning | Number of rows changed from 0 to 1 | | maria5.recover_test | repair | status | OK | +---------------------+--------+----------+------------------------------------+
MariaDB [maria5]> select * from recover_test ; +------+ | id | +------+ | 1 | +------+
MariaDB [maria5]> show indexes from recover_test ; +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | recover_test | 1 | test_idx | 1 | id | A | 38 | NULL | NULL | YES | BTREE | | | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
MariaDB [maria5]> insert into recover_test values(2) ; x 20회 Query OK, 1 row affected (0.00 sec)
MariaDB [maria5]> explain select id from recover_test force index (test_idx) ; +------+-------------+--------------+-------+---------------+----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+--------------+-------+---------------+----------+---------+------+------+-------------+ | 1 | SIMPLE | recover_test | index | NULL | test_idx | 5 | NULL | 38 | Using index | +------+-------------+--------------+-------+---------------+----------+---------+------+------+-------------+
|