[365 TIP 15 호 ] MySQL 사용하기 (2) 작성일자 : 2009-11-10 작성자 : slowlygo@net-farm.com ( 주 ) 넷팜 http://www. 365managed.com
목차 1. 시작하며...1 2. MySQL storage engines...2 3. 복구유틸...3 1) Mysqlcheck...3 2) myisamchk...5 4. MyISAM...6 1) 테이블생성...6 2).frm 복구...7 3).MYI 복구...11 4).MYD 복구...15 5. InnoDB...17 1) InnoDB 타입테이블생성방법...18 2) 테이블생성...19 3) ibd 복구...20 4).frm 복구...24 6. 마치며...24
1. 시작하며 안녕하세요. 반갑습니다. ^^ 이번호에서는페이지배경을좀넣어보았는데, 어떻습니까? 원체꾸미는건잼병이라... 저번호에서 MySQL 소개및설치, MySQL 로그, 권한시스템, 캐릭터셋을주내용으로하고, 기타내용도좀다루었는데요. 어떠셨는지모르겠네요. 이번호에서도이어서 MySQL 복구를주내용으로기타내용몇가지다루어보도록하겠습니다. 복구도또한제한적으로삭제되었을경우를보도록하겠습니다. 해당내용은 MySQL Server 5.1.37 을기준으로하고있습니다. 혹시잘못된내용이나문의가있으신경우에는 http://community.365managed.net 커뮤니티를 이용하시거나혹은 slowlygo@net-farm.com 으로메일주시면감사하겠습니다. -1-
2. MySQL storage engines 간단히 MySQL storage engines에대해살펴보겠습니다. MySQL은다른테이블타입을가지는여러가지의 storage engines 지원합니다. MySQL 5.1이지원하는 storage engines은다음과같습니다. - MyISAM : 가장널리사용되는디폴트엔진 - InnoDB : 트랜잭션에안전한엔진 - Memory - Merge - Archive - Federated - NDBCLUSTER - CSV - Blackhole - Example 자세한사항은아래 URL을참조하길바랍니다. http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html 참고로항상복구전에백업은필히하셔야합니다. MyISAM과 InnoDB에대해서만다루도록하겠습니다. -2-
3. 복구유틸 mysqlcheck client 혹은 myisamchk 유틸을이용해서체크하거나수선할수있습니다. 위 2가지명령어는유사하지만차이점은 - mysqlcheck는 MySQL이실행중일때사용이가능하고 myisamchk는중지되었을때사용가능하다는점입니다. - 또한 storage engines별로사용가능할수도아닐수도있습니다. - myisamchk는 myisam 방식일때가능합니다. 1) Mysqlcheck 아래와같은 4가지 SQL 구문을이용합니다. - CHECK TABLE : 테이블에러를체크합니다. - REPAIR TABLE : 깨진테이블을복구합니다. 실행전꼭테이타를백업해두셔야합니다. 최악을경우데이타가전부날아갈수있습니다. - ANALYZE TABLE : 테이블의키분포를분석하고저장합니다. - OPTIMIZE TABLE : 테이블에많은부분이삭제되었거나다양한길이를가진 rows에많은변화가있었을경우실행해주는것이좋지만가끔필요할때해주실수있겠네요. 데이타파일의흩어짐과사용되지않는공간을확인하여해당값들을재선언하는역할을합니다. 아래와같이사용이가능합니다. shell> mysqlcheck [options] db_name [tables] shell> mysqlcheck [options] --databases db_name1 [db_name2 db_name3...] shell> mysqlcheck [options] --all-databases 여러가지옵션이있지만일반적으로아래와같은옵션을많이사용합니다. --all-databases, -A --analyze, -a --auto-repair -3-
--fast, -F --force, -f --optimize, -o --repair, -r : 유니크하지않은 unique key를제외한거의모든문제점을고칩니다. 아래는실행한화면입니다. [root@localhost ~]# /usr/local/mysql/bin/mysqlcheck -Aao --auto-repair -u root -p Enter password:... 중략... mysql.general_log note : The storage engine for the table doesn't support optimize mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host Table is already up to date mysql.ndb_binlog_index Table is already up to date mysql.plugin Table is already up to date... 중략... mysql.time_zone_name Table is already up to date mysql.time_zone_transition Table is already up to date mysql.time_zone_transition_type Table is already up to date mysql.user OK 참고로 [ Table is already up to date ] 는테이블을체크할필요가없음을나타냅니다. 어떤문제가있다면문제가있다고나오고자동적으로해당문제를해결할려고합니다. -4-
2) myisamchk myisamchk 은데이타베이스테이블에관한정보를가져오고, 체크, 수선, 최적화하는유틸입니다. shell> myisamchk [options] tbl_name... 여러가지옵션이있지만일반적으로아래와같은옵션을많이사용합니다. --analyze, -a --check, -c, --fast, -F : 적절하게닫히지않은테이블은체크합니다. --force, -f : 에러가발견되면수선합니다. --quick, -q : 데이타파일은수정하지않고빠르게수선합니다. --recover, -r : unique하지않은 unique keys를제외한문제를수선합니다. [root@localhost ~]# ps aux grep mysql root 24427 0.1 0.1 68040 1264 pts/0 S 22:30 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/localhost.pid mysql 24551 2.1 7.5 466180 77316 pts/0 Sl 22:30 0:00 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --logerror=/usr/local/mysql/data/localhost.err --pid-file=/usr/local/mysql/data/localhost.pid --socket=/tmp/mysql.sock --port=3306 [root@localhost ~]# kill -9 24427 24551 # 확인한프로세스강제로죽입니다. [root@localhost ~]# ps aux grep mysql [root@localhost ~]# cp -arpf /usr/local/mysql/data/test/./test # myisamchk를위해임시폴더로체크할테이타베이스를옮깁니다. # 작업전백업은필수입니다. `/usr/local/mysql/data/test/' -> `./test' `/usr/local/mysql/data/test/test.myi' -> `./test/test.myi' `/usr/local/mysql/data/test/test.frm' -> `./test/test.frm' `/usr/local/mysql/data/test/test.myd' -> `./test/test.myd' `/usr/local/mysql/data/test/db.opt' -> `./test/db.opt' -5-
[root@localhost ~]# /usr/local/mysql/bin/myisamchk -ac./test/test Checking MyISAM file:./test/test Data records: 6 Deleted blocks: 0 - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 - check record links [root@localhost ~]# /usr/local/mysql/bin/myisamchk -r./test/test - recovering (with sort) MyISAM-table './test/test' Data records: 6 - Fixing index 1 4. MyISAM MySQL에서디폴트스토리지엔진 ( 생성시 ENGINE옵션을주지않았을경우 ) 이며 MyISAM 테이블은 3개의파일로구성됩니다. 테이블네임 + 파일타입을가르키는확장자형식입니다. -.frm : table 포맷이저장됩니다. -.MYD : 데이타파일이저장됩니다. -.MYI : 인덱스파일이저장됩니다. 1) 테이블생성 mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> create table test(no int(10) not null auto_increment primary key, name varchar(50)); Query OK, 0 rows affected (0.01 sec) mysql> insert into test(name) values('netfarm'); Query OK, 1 row affected (0.00 sec) -6-
# 10번실행 no name 1 netfarm... 중략... 10 netfarm 10 rows in set (0.00 sec) mysql> \! ls -al /usr/local/mysql/data/test/ 합계 32 drwx------ 2 mysql mysql 4096 11월 9 17:36. drwx------ 4 mysql mysql 4096 11월 9 17:31.. -rw-rw---- 1 mysql mysql 61 11월 9 17:31 db.opt -rw-rw---- 1 mysql mysql 220 11월 9 17:38 test.myd -rw-rw---- 1 mysql mysql 2048 11월 9 17:38 test.myi -rw-rw---- 1 mysql mysql 8586 11월 9 17:36 test.frm 2).frm 복구 mysql> \! rm /usr/local/mysql/data/test/test.frm mysql> \! ls -al /usr/local/mysql/data/test/ 합계 20 drwx------ 2 mysql mysql 4096 11월 9 17:42. drwx------ 4 mysql mysql 4096 11월 9 17:31.. -rw-rw---- 1 mysql mysql 61 11월 9 17:31 db.opt -rw-rw---- 1 mysql mysql 220 11월 9 17:38 test.myd -rw-rw---- 1 mysql mysql 2048 11월 9 17:38 test.myi no name 1 netfarm -7-
... 중략... 10 netfarm 10 rows in set (0.00 sec) mysql> insert into test(name) values('netfarm'); Query OK, 1 row affected (0.00 sec) mysql> \! ls -al /usr/local/mysql/data/test/ 합계 20 drwx------ 2 mysql mysql 4096 11월 9 17:42. drwx------ 4 mysql mysql 4096 11월 9 17:31.. -rw-rw---- 1 mysql mysql 61 11월 9 17:31 db.opt -rw-rw---- 1 mysql mysql 220 11월 9 17:43 test.myd -rw-rw---- 1 mysql mysql 2048 11월 9 17:43 test.myi no name 1 netfarm... 중략... 10 netfarm 11 netfarm 11 rows in set (0.00 sec) mysql> delete from test where no=11; Query OK, 1 row affected (0.00 sec) no name 1 netfarm... 중략... 10 netfarm -8-
10 rows in set (0.00 sec) mysql> check table test; +-----------+-------+----------+----------+ Table Op Msg_type Msg_text +-----------+-------+----------+----------+ test.test check status OK +-----------+-------+----------+----------+ 1 row in set (0.00 sec) mysql> insert into test(name) values('netfarm'); Query OK, 1 row affected (0.00 sec) no name 1 netfarm... 중략... 10 netfarm 12 netfarm 11 rows in set (0.00 sec) # 삭제되더라도일단이상은없어보입니다. # query cache 로부터모든 query 결과를제거하기위해아래명령을실행합니다. # (MySQL 서버가리스타트되는경우와동일하다고보셔도무방하겠습니다.) mysql> flush table test; Query OK, 0 rows affected (0.00 sec) ERROR 1146 (42S02): Table 'test.test' doesn't exist mysql> check table test; +-----------+-------+----------+---------------------------------+ Table Op Msg_type Msg_text +-----------+-------+----------+---------------------------------+ -9-
test.test check Error Table 'test.test' doesn't exist test.test check status Operation failed +-----------+-------+----------+---------------------------------+ 2 rows in set (0.00 sec) 백업파일이있을경우백업을이용복구할수있겠지만없을경우는, 테이블을재생성하는방법으로처리할 수있습니다..frm 파일은최초생성시에만수정되는파일이기때문에해당파일만복구하면됩니다. 백업파일이없으므로테이블을다시생성하는방법을취하도록하겠습니다. mysql> \! ls -al /usr/local/mysql/data/test/ 합계 20 drwx------ 2 mysql mysql 4096 11월 9 21:31. drwx------ 4 mysql mysql 4096 11월 9 21:32.. -rw-rw---- 1 mysql mysql 61 11월 9 17:31 db.opt -rw-rw---- 1 mysql mysql 220 11월 9 21:24 test.myd -rw-rw---- 1 mysql mysql 2048 11월 9 21:24 test.myi mysql> \! mv /usr/local/mysql/data/test/test.myd /usr/local/mysql/data/test/test_ori.myd mysql> \! mv /usr/local/mysql/data/test/test.myi /usr/local/mysql/data/test/test_ori.myi # 위 2파일을백업해둡니다. mysql> create table test(no int(10) not null auto_increment primary key, name varchar(50)); Query OK, 0 rows affected (0.00 sec) mysql> \! mv /usr/local/mysql/data/test/test_ori.myi /usr/local/mysql/data/test/test.myi mysql> \! mv /usr/local/mysql/data/test/test_ori.myd /usr/local/mysql/data/test/test.myd # 백업해둔파일을복구합니다. mysql> \! ls -al /usr/local/mysql/data/test 합계 32 drwx------ 2 mysql mysql 4096 11월 9 21:34. drwx------ 4 mysql mysql 4096 11월 9 21:32.. -rw-rw---- 1 mysql mysql 61 11월 9 17:31 db.opt -rw-rw---- 1 mysql mysql 220 11월 9 21:24 test.myd -rw-rw---- 1 mysql mysql 2048 11월 9 21:24 test.myi -rw-rw---- 1 mysql mysql 8586 11월 9 21:34 test.frm no name -10-
1 netfarm... 중략... 10 netfarm 12 netfarm 11 rows in set (0.00 sec) 3).MYI 복구 mysql> show index in test; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+-------- +------+------------+---------+ Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +-------+------------+----------+--------------+-------------+-----------+-------------+----------+-------- +------+------------+---------+ test 0 PRIMARY 1 no A NULL NULL NULL BTREE +-------+------------+----------+--------------+-------------+-----------+-------------+----------+-------- +------+------------+---------+ 1 row in set (0.00 sec) mysql> \! rm /usr/local/mysql/data/test/test.myi # 삭제합니다. mysql> show index in test; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+-------- +------+------------+---------+ Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +-------+------------+----------+--------------+-------------+-----------+-------------+----------+-------- +------+------------+---------+ test 0 PRIMARY 1 no A NULL NULL NULL BTREE -11-
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+-------- +------+------------+---------+ 1 row in set (0.00 sec) mysql> flush table test; Query OK, 0 rows affected (0.00 sec) mysql> show index in test; ERROR 1017 (HY000): Can't find file: 'test' (errno: 2) ERROR 1017 (HY000): Can't find file: 'test' (errno: 2) mysql> repair table test; +-----------+--------+----------+------------------------------------+ Table Op Msg_type Msg_text +-----------+--------+----------+------------------------------------+ test.test repair Error Can't find file: 'test' (errno: 2) test.test repair status Operation failed +-----------+--------+----------+------------------------------------+ 2 rows in set (0.01 sec) mysql> \! cp /usr/local/mysql/data/test/test.myd cp /usr/local/mysql/data/test/test_ori.myd # 복구전데이타파일을백업해둡니다. mysql> truncate table test; Query OK, 0 rows affected (0.00 sec) # 새데이타파일과인덱스파일을생성합니다. # truncate 명령어는데이타를비우는명령어입니다. mysql> \! ls -al /usr/local/mysql/data/test/ 합계 32 drwx------ 2 mysql mysql 4096 11월 9 21:50. drwx------ 4 mysql mysql 4096 11월 9 21:46.. -rw-rw---- 1 mysql mysql 61 11월 9 17:31 db.opt -rw-rw---- 1 mysql mysql 0 11월 9 21:50 test.myd -rw-rw---- 1 mysql mysql 1024 11월 9 21:50 test.myi -rw-rw---- 1 mysql mysql 8586 11월 9 21:34 test.frm -rw-rw---- 1 mysql mysql 240 11월 9 21:37 test_ori.myd mysql> desc test; -12-
+-------+-------------+------+-----+---------+----------------+ Field Type Null Key Default Extra +-------+-------------+------+-----+---------+----------------+ no int(10) NO PRI NULL auto_increment name varchar(50) YES NULL +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> show index from test; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+-------- +------+------------+---------+ Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment +-------+------------+----------+--------------+-------------+-----------+-------------+----------+-------- +------+------------+---------+ test 0 PRIMARY 1 no A 0 NULL NULL BTREE +-------+------------+----------+--------------+-------------+-----------+-------------+----------+-------- +------+------------+---------+ 1 row in set (0.00 sec) mysql> \! cp /usr/local/mysql/data/test/test_ori.myd /usr/local/mysql/data/test/test.myd mysql> flush table test; Query OK, 0 rows affected (0.00 sec) # 백업해둔파일을복구합니다. Empty set (0.00 sec) mysql> check table test; +-----------+-------+----------+----------------------------------------------+ Table Op Msg_type Msg_text +-----------+-------+----------+----------------------------------------------+ test.test check warning Size of datafile is: 240 Should be: 0 test.test check error Record-count is not ok; is 12 Should be: 0 test.test check warning Found 12 key parts. Should be: 0 test.test check error Corrupt -13-
+-----------+-------+----------+----------------------------------------------+ 4 rows in set (0.00 sec) mysql> repair table test; +-----------+--------+----------+-------------------------------------+ Table Op Msg_type Msg_text +-----------+--------+----------+-------------------------------------+ test.test repair warning Number of rows changed from 0 to 12 test.test repair status OK +-----------+--------+----------+-------------------------------------+ 2 rows in set (0.00 sec) # 테이블을수선합니다. no name 1 netfarm... 중략... 10 netfarm 12 netfarm 13 netfarm 12 rows in set (0.00 sec) -14-
4).MYD 복구기본적으로테이타파일이기때문에삭제되어버리고백업이없다면복구는불가능하겠지만깨졌다는가정하에테스트를해보도록하겠습니다. [root@localhost ~]# ls -al /usr/local/mysql/data/test/ 합계 36 drwx------ 2 mysql mysql 4096 11월 9 21:53. drwx------ 4 mysql mysql 4096 11월 9 21:46.. -rw-rw---- 1 mysql mysql 61 11월 9 17:31 db.opt -rw-rw---- 1 mysql mysql 240 11월 9 21:53 test.myd -rw-rw---- 1 mysql mysql 2048 11월 9 21:53 test.myi -rw-rw---- 1 mysql mysql 8586 11월 9 21:34 test.frm -rw-rw---- 1 mysql mysql 240 11월 9 21:37 test_ori.myd [root@localhost ~]# split -b 120 /usr/local/mysql/data/test/test.myd test # 깨진테스트를위해 120bytes로잘랐습니다. [root@localhost ~]# ls testaa testab [root@localhost ~]# mv testaa /usr/local/mysql/data/test/test.myd mv: overwrite `/usr/local/mysql/data/test/test.myd'? y `testaa' -> `/usr/local/mysql/data/test/test.myd' removed `testaa' # 자른파일중하나를덮어씁니다. [root@localhost ~]# mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.40-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test; Database changed mysql> show tables; +----------------+ Tables_in_test +----------------+ -15-
test +----------------+ 1 row in set (0.00 sec) ERROR 1194 (HY000): Table 'test' is marked as crashed and should be repaired mysql> repair table test; +-----------+--------+----------+-------------------------------------+ Table Op Msg_type Msg_text +-----------+--------+----------+-------------------------------------+ test.test repair warning Number of rows changed from 12 to 6 test.test repair status OK +-----------+--------+----------+-------------------------------------+ 2 rows in set (0.00 sec) no name 1 netfarm 2 netfarm 3 netfarm 4 netfarm 5 netfarm 6 netfarm 6 rows in set (0.00 sec) 이후로는바이너리로그를이용복구과정을거쳐야하겠네요. -16-
5. InnoDB InnoDB 는 commit, rollback, crach-recovery capabilities 가진트랜잭션에안전한스토리지엔진입니다. 아래는 --with-plugins=all 옵션을주고컴파일했을때의내용입니다. mysql> show engines; +------------+---------+----------------------------------------------------------------+--------------+------+------------+ Engine Support Comment Transactions XA Savepoints +------------+---------+----------------------------------------------------------------+--------------+------+------------+ ndbcluster NO Clustered, fault-tolerant tables NULL NULL NULL MRG_MYISAM YES Collection of identical MyISAM tables NO NO NO BLACKHOLE YES /dev/null storage engine (anything you write to it disappears) NO NO NO CSV YES CSV storage engine NO NO NO MEMORY YES Hash based, stored in memory, useful for temporary tables NO NO NO FEDERATED NO Federated MySQL storage engine NULL NULL NULL ARCHIVE YES Archive storage engine NO NO NO InnoDB YES Supports transactions, row-level locking, and foreign keys YES YES YES MyISAM DEFAULT Default engine as of MySQL 3.23 with great performance NO NO NO +------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec) mysql> show plugins; +------------+----------+----------------+---------------------+---------+ Name Status Type Library License +------------+----------+----------------+---------------------+---------+ binlog ACTIVE STORAGE ENGINE NULL GPL partition ACTIVE STORAGE ENGINE NULL GPL ARCHIVE ACTIVE STORAGE ENGINE NULL GPL BLACKHOLE ACTIVE STORAGE ENGINE NULL GPL CSV ACTIVE STORAGE ENGINE NULL GPL FEDERATED DISABLED STORAGE ENGINE NULL GPL MEMORY ACTIVE STORAGE ENGINE NULL GPL MyISAM ACTIVE STORAGE ENGINE NULL GPL MRG_MYISAM ACTIVE STORAGE ENGINE NULL GPL ndbcluster DISABLED STORAGE ENGINE NULL GPL InnoDB ACTIVE STORAGE ENGINE ha_innodb_plugin.so GPL +------------+----------+----------------+---------------------+---------+ 11 rows in set (0.03 sec) -17-
디폴트인경우 data 와 indexs 는 ibdata* 에저장되고 bl_name.frm 은보통처럼생성됩니다. multiple tablespaces 를가능하도록설정한경우, data 와 indexs 는.ibd 에함께저장되고 tbl_name.frm 은보 통처럼생성됩니다. 1) InnoDB 타입테이블생성방법 mysql> create table test(no int(10) not null auto_increment primary key, name varchar(50)) ENGINE=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> show table status from test; +------+--------+---------+------------+------+----------------+-------------+----------------- +--------------+------------+----------------+---------------------+-------------+------------ +-----------------+----------+----------------+---------+ Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment +------+--------+---------+------------+------+----------------+-------------+----------------- +--------------+------------+----------------+---------------------+-------------+------------ +-----------------+----------+----------------+---------+ test InnoDB 10 Compact 0 0 16384 0 0 2073034752 1 2009-11-10 12:15:57 NULL NULL utf8_general_ci NULL +------+--------+---------+------------+------+----------------+-------------+----------------- +--------------+------------+----------------+---------------------+-------------+------------ +-----------------+----------+----------------+---------+ 1 row in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into test(name) values('netfarm'); Query OK, 1 row affected (0.00 sec) mysql> commit; -18-
Query OK, 0 rows affected (0.00 sec) mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) # 디폴트는 autocommit=1 값입니다. mysql> insert into test(name) values('netfarm'); Query OK, 1 row affected (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.01 sec) no name 1 netfarm 1 row in set (0.00 sec) 2) 테이블생성 mysql> use test; Database changed mysql> create table test(no int(10) not null auto_increment primary key, name varchar(50)) ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) mysql> insert into test(name) values('netfarm'); Query OK, 1 row affected (0.01 sec) -19-
no name 1 netfarm... 중략... 10 netfarm 10 rows in set (0.01 sec) mysql> \! ls /usr/local/mysql/data/ ib_logfile0 ib_logfile1 ibdata1 ibdata2 localhost.err localhost.pid mysql mysql-bin.000001 mysqlbin.000002 mysql-bin.index test mysql> \! ls /usr/local/mysql/data/test/ test.frm 3) ibd 복구 mysql> \! rm /usr/local/mysql/data/ibdata1 mysql> \! rm /usr/local/mysql/data/ibdata2 # InnoDB 테이타파일을삭제합니다. mysql> \! ls /usr/local/mysql/data/ ib_logfile0 ib_logfile1 localhost.err localhost.pid mysql mysql-bin.000001 mysql-bin.000002 mysqlbin.index test no name 1 netfarm... 중략... 10 netfarm 10 rows in set (0.00 sec) -20-
mysql> insert into test(name) values('netfarm'); Query OK, 1 row affected (0.00 sec) no name 1 netfarm... 중략... 10 netfarm 11 netfarm 11 rows in set (0.00 sec) mysql> \! /etc/init.d/mysqld restart Shutting down MySQL.. [ OK ] Starting MySQL... [ OK ] ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 1 Current database: test ERROR 1286 (42000): Unknown table engine 'InnoDB' mysql> \! tail /usr/local/mysql/data/localhost.err InnoDB: All log files must be created also in database creation. InnoDB: If you want bigger or smaller log files, shut down the InnoDB: database and make sure there were no errors in shutdown. InnoDB: Then delete the existing log files. Edit the.cnf file InnoDB: and start the database again. 091110 14:40:26 [ERROR] Plugin 'InnoDB' init function returned error. 091110 14:40:26 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 091110 14:40:26 [Note] Event Scheduler: Loaded 0 events 091110 14:40:26 [Note] /usr/local/mysql/libexec/mysqld: ready for connections. -21-
Version: '5.1.40-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution # InnoDB 데이타파일과로그파일은동시에생성되어야함을알수있습니다. # 간단히해당로그파일은데이타파일이삭제되었거나이와유사한경우에도움을주지못한다는것을알수있습니다. # 물론이런경우는데이타베이스외적문제로인해발생할가능성이많겠습니다. # 이러한문제가아니라면데이타베이스를다시시작하게되면자동복구됩니다. mysql> \! rm /usr/local/mysql/data/ib* mysql> \! ls /usr/local/mysql/data localhost.err localhost.pid mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysqlbin.000004 mysql-bin.index test mysql> \! /etc/init.d/mysqld restart Shutting down MySQL.. [ OK ] Starting MySQL... [ OK ] mysql> \! tail /usr/local/mysql/data/localhost.err InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 091110 14:46:57 InnoDB Plugin 1.0.4 started; log sequence number 0 091110 14:46:58 [Note] Event Scheduler: Loaded 0 events 091110 14:46:58 [Note] /usr/local/mysql/libexec/mysqld: ready for connections. Version: '5.1.40-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution # 전부초기화되어생성되었습니다. # 백업본이없으므로바이너리로그를통해복구를시도합니다. mysql> select * from test.test; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 1 Current database: test ERROR 1146 (42S02): Table 'test.test' doesn't exist mysql> \! /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.00* grep -n "create -22-
table test" 34:create table test(no int(10) not null auto_increment primary key, name varchar(50)) ENGINE=InnoDB # 위치를찾습니다. mysql> \! /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.00* sed -n 20,36p AAAAAAAAAAAAAAAAAACF+/hKEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC '/*!*/; # at 106 #091110 14:35:19 server id 1 end_log_pos 267 Query thread_id=1 exec_time=0 error_code=0 use test/*!*/; SET TIMESTAMP=1257831319/*!*/; SET @@session.pseudo_thread_id=1/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33 /*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=default/*!*/; create table test(no int(10) not null auto_increment primary key, name varchar(50)) ENGINE=InnoDB /*!*/; # at 267 # 해당위치의시간값을알아냅니다. mysql> \! /usr/local/mysql/bin/mysqlbinlog --start-datetime='2009-11-10 14:35:19' /usr/local/mysql/data/mysql-bin.00* mysql ERROR 1050 (42S01) at line 32: Table 'test' already exists mysql> \! rm /usr/local/mysql/data/test/test.frm mysql> \! /usr/local/mysql/bin/mysqlbinlog --start-datetime='2009-11-10 14:35:19' /usr/local/mysql/data/mysql-bin.00* mysql ERROR 1050 (42S01) at line 32: Table 'test' already exists mysql> \! ls /usr/local/mysql/data/test/ mysql> flush table test; Query OK, 0 rows affected (0.00 sec) -23-
mysql> \! /usr/local/mysql/bin/mysqlbinlog --start-datetime='2009-11-10 14:35:19' /usr/local/mysql/data/mysql-bin.00* mysql # 해당시간이후로전내용을복구합니다. mysql> select * from test.test; no name 1 netfarm... 중략... 10 netfarm 11 netfarm 11 rows in set (0.00 sec) 4).frm 복구 MyISAM 에서와유사한방법으로복구하시면됩니다. 6. 마치며 의문사항이나잘못된내용이있으면메일이나커뮤니티를이용해서문의해주세요.!! 참고문서 : http://dev.mysql.com/doc/refman/5.1/en/ -- 수고많이하셨습니다. -- [ 본문서의수정및재배포를금합니다.] -24-