'회원디비'에 관한 글 1개

mysql 회원 DB살리기

관련 웹사이트의 회원DB가 사라졌다는 전화를 받고는 밤 늦게 집에 들어갔다.
그리고 혹시나 하여 mysql 로그 파일을 뒤져보았다. 그리고 가까스로 그 해결방법을 적어 놓은 URL을 발견한다.

그리고 미심쩍어 해당 DB를 백업받은 후 아래와 같은 작업을 시도한다. 다행히도 80% 정도의 회원디비가 복구되었다. 순간의 실수가 만들어 놓은 작은 노력들이 이런 저런 일로 해결가능함을 본다. 다행히도 로그화일의 크기가 큼에도 불구하고 남겨놓아서 다행이었다.



먼저 mysqlbinlog 유틸리티를 사용하기 위해서는 Binary Log을 사용하고 있어야 합니다.  서비스를 운영하다 보면 실수로 Delete from Table ~~, Drop Table 구문을 잘못 사용하여 한순간에 자료를 잃어 버리는 경우를 많이 경험하셨을 것입니다. 특히 MySQL과 같이 기본적으로 Transcation 처리가 지원되지 않는 경우에는 더욱이 문제가 됩니다.

본인의 경우에도 Replcation만 믿고 Backup을 소홀히 하였다가 Master에 SQL 구문을 잘못 질의하여 모든 Slave DB까지 한순간에 자료를 잃어 버려 고생을 한 적이 있습니다. 해당 팁은 특히 Replication 환경에서 관리자의 실수로 인하여 발생하는 자료 손실에 대한 복구 방법이 될듯 합니다.

1. Binary Log 사용하기

[root@smson bin]# vi /etc/my.cnf

# Replication Master Server (default)
# binary logging is required for replication
log-bin

또는 Mysql 시작시에 --log-bin[=file_name] 을 사용하시면 됩니다. Binary Log 는 DML 구문에 대하여 아래와 같이 로그파일을 생성합니다.

[root@smson var]# ls -al smson
ls: smson: No such file or directory
[root@smson var]# ls -alh

-rw-rw----    1 mysql    mysql        1.0G Aug 23 09:05 smson-bin.000001
-rw-rw----    1 mysql    mysql        1.0G Sep  8 03:00 smson-bin.000002
-rw-rw----    1 mysql    mysql        1.0G Sep 22 23:20 smson-bin.000003
-rw-rw----    1 mysql    mysql        244M Sep 24 02:02 smson-bin.000004
-rw-rw----    1 mysql    mysql          76 Sep 22 23:20 smson-bin.index
-rw-rw----    1 mysql    root         1.5M Sep 24 00:47 smson.err
-rw-rw----    1 mysql    mysql           6 Jan  6  2006 smson.pid
-rw-rw----    1 mysql    mysql           4 Jan  6  2006 smson-relay-bin.000030
-rw-rw----    1 mysql    mysql          25 Jan  6  2006 smson-relay-bin.index

주기적으로 자료를 백업 후에 Binary Log 파일을 초기화 시켜 주는 것이 디스크 관리상 좋습니다.  Binary Log 파일 초기화는 RESET MASTER 구문을 참고해 주십시요

2. mysqlbinlog

Binary Log 는 binary format 으로 저장되어 있기 때문에 해당 파일을 조회 또는 이용하기 위해서는 mysqlbinlog 유틸리티를 이용하셔야 합니다.

smson-bin.000004  로그파일 조회

[root@smson var]# ../bin/mysqlbinlog -s --database=colleges smson-bin.000004 | more
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
SET LAST_INSERT_ID=1;
SET INSERT_ID=1;
SET LAST_INSERT_ID=439;
SET INSERT_ID=439;
SET LAST_INSERT_ID=1;
SET INSERT_ID=1;

smson-bin.000004 에 대하여 college database 복구

[root@smson var]# ../bin/mysqlbinlog -s --database=colleges smson-bin.000004 | ../bin/mysql

[root@smson var]# ../bin/mysqlbinlog -s --database=colleges smson-bin.0-9]* | ../bin/mysql

3. 복구작업

복구에 필요한 Binary Log 파일들을 결정한다.
mysqlbinlog 을 이용하여 SQL 구문을 작성한다. 물론 직접 실행할 수 있지만 경험상 SQL문을 생성 후에 이를 이용하여 복구작업을 하는 것이 문제 발생시에 도움이 된다.

[root@smson var]# ../bin/mysqlbinlog --disable-log-bin -s --database=collge smson-bin.000003 > b03.sql
[root@smson var]# ../bin/mysqlbinlog --disable-log-bin -s --database=collge smson-bin.000004 > b04.sql
[root@smson var]# tail -f b04.sql
SET INSERT_ID=1;
SET LAST_INSERT_ID=1;
SET INSERT_ID=1;
SET LAST_INSERT_ID=1;
SET INSERT_ID=1;
SET LAST_INSERT_ID=1;
SET INSERT_ID=1;
SET LAST_INSERT_ID=1;
SET INSERT_ID=1;

작성한 SQL 구문을 순차적으로 실행시킨다. 일정부분의 구문만 필요한 경우는 해당 파일 중에서 필요한 SQL 구문을 선택 후 편집한다.

[root@smson var]# cat ./b02.sql | mysql -u root -p --force
ERROR 1050 (42S01) at line 10404: Table 'REGION_T' already exists
ERROR 1062 (23000) at line 14374: Duplicate entry 'US-AK' for key 1
Table   Op      Msg_type        Msg_text
college.US_UNIV_T       repair  status  OK
ERROR 1062 (23000) at line 54657: Duplicate entry 'American University' for key 1
ERROR 1062 (23000) at line 54661: Duplicate entry 'Aquinas College' for key 1
ERROR 1062 (23000) at line 54665: Duplicate entry 'Aquinas College' for key 1
ERROR 1062 (23000) at line 54679: Duplicate entry '83' for key 1
ERROR 1062 (23000) at line 54745: Duplicate entry '2' for key 1
[root@smson var]# cat ./b03.sql | ../bin/mysql college --force
ERROR 1105 (HY000) at line 121593: Unknown error

--force 옵션은 Batch 작업시에 Error 가 발생하더라도 계속 실행하라는 옵션이다.  이에 대하여 정확하게는 잘 모르겠지만 잘못된 SQL 구문에 대하여도 Binary Log에 포함 되어 있어 일괄적으로 복구시에 요긴하게 사용하실 수 있을 것입니다.

4. 해당기간것만 불러서 백업하는 것

[root@smson var] ../bin/mysqlbinlog  --disable-log-bin -s --database=collge --start-datetime="2009-01-01 01:00:00" --stop-datetime='2009-09-28 19:00:00' /usr/local/mysql/var/mysql-bin.000001 > bakcup.sql
2009/09/29 00:53 2009/09/29 00:53
보람말 : , ,
글 걸기 주소 : http://jis.pe.kr/road/?/trackback/469

덧글을 달아 주세요

[로그인][오픈아이디란?]