'mysql'에 관한 글 3개

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

덧글을 달아 주세요

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

리눅스서버에 yum 이나 rpm 을 이용해 PHP또는 Apache 등의 서버를 설치하는 것은 매우 쉽다. 현재 CentOS나 페도라에서는 yum install 패키지명 만 하면 의존성 관계가 있는 패키지들까지 자동적으로 설치가 된다. 예전처럼 수동으로 일일이 찾아서 설치하는 시대는 지났다. 레드햇 기반의 배포판들은 페도라, 센토스 등이다. 그런데 공통적으로 같은 명령어인 yum을 이용해 패키지 업데이트를 하기때문에 이 간단한 명령어를 기억한다면 업데이트는 그야말로 식은죽 먹기식이다.?

레드햇 기반에서는 yum 을 이용해 업데이트 하지만 데비안 계열에서는 apt-get 를 이용한다. 둘다 같은 기능을 하므로 간단하게 업데이트 하고픈 패키지를 고르고 각각 yum install 패키지명 / apt-get install 해키지명 해 보면 정말 간단하게 업데이트 되는 것을 알 수 있다.?

RPM은 기본셋팅과 모듈을 탑재한 채 미리 컴파일된 표준 패키지들이다. 그렇지만 어떤 응융프로그램은 RPM컴파일시 포함되지 않은 다른 모듈을 필요로 한다. 이런 경우 php와 Apache 를 수동으로 다시 컴파일해야 한다. 소스로부터 어떤 응용프로그램을 컴파일 하는 것이 더 좋다. 비록때때로 시간을 잡아먹거나 어려워도 우리가 생각하는 각종 모듈들을 지원하게 만들 수 있는데...아래 내용들은 소스로 컴파일하면서 생기는 에러들에 대한 대응 방안들이다.?

1) Configure: error: xml2-config not found. Please check your libxml2 installation.

해결책 :?#yum install libxml2-devel

2) Checking for pkg-config... /usr/bin/pkg-config
configure: error: Cannot find OpenSSL's <evp.h>


해결책?:?#yum install openssl-devel

3) Configure: error: Please reinstall the BZip2 distribution

해결책?:?# yum install bzip2-devel

4) Configure: error: Please reinstall the libcurl distribution -
easy.h should be in <curl-dir>/include/curl/


해결책?:?# yum install curl-devel

5) Configure: error: libjpeg.(also) not found.

해결책?:?# yum install libjpeg-devel

6) Configure: error: libpng.(also) not found.

해결책?:?# yum install libpng-devel

7) Configure: error: freetype.h not found.

해결책?:?#yum install freetype-devel

8) Configure: error: Unable to locate gmp.h

해결책?:?# yum install gmp-devel

9) Configure: error: Cannot find MySQL header files under /usr.
Note that the MySQL client library is not bundled anymore!


해결책?:?# yum install mysql-devel

10) Configure: error: Please reinstall the ncurses distribution

해결책?:?# yum install ncurses-devel

11) Checking for unixODBC support... configure: error: ODBC header file '/usr/include/sqlext.h' not found!

해결책?:?# yum install unixODBC-devel

12) Configure: error: Cannot find pspell

해결책?:?# yum install pspell-devel

13) Configure: error: snmp.h not found. Check your SNMP installation.

해결책?:?# yum install net-snmp-devel

2008/11/18 23:24 2008/11/18 23:24
글 걸기 주소 : http://jis.pe.kr/road/?/trackback/420

덧글을 달아 주세요

  1. 어드민플레이 2009/01/30 18:44 고유주소 고치기 답하기

    좋은 자료 보고 갑니다..
    고맙습니다..^^

    • 길목 2009/02/01 08:59 고유주소 고치기

      네...도움이 되엇다니 다행이군요. 댓글 남겨주셔서 감사합니다.^^;;

  2. 노스 2009/05/21 19:52 고유주소 고치기 답하기

    좋은 자료 감사합니다. 제 APM설치에 도움이 많이 되네요. 님 글을 읽었더니 제 APM도 RPM으로 갈아타야 하는건가 하는 생각이 드네요.. 특히 yum을 이용한 업뎃이 정말 맘에 듭니다.

    좋은 하루되세요 ^^/

  3. eizt 2009/12/01 11:16 고유주소 고치기 답하기

    좋은 자료 감사합니다.!!!ㅎㅎㅎ

    구글신께서 강림하셔서 길목님의 블로그 까지..ㅎㅎ

    • 길목 2009/12/07 02:55 고유주소 고치기

      도움이 되었다니 다행입니다. 멀리까지 와주셔서 감사합니다.

  4. 바람처럼 2011/07/07 21:11 고유주소 고치기 답하기

    정말 좋은 자료네요~~~^^

  5. tatchi 2011/08/11 14:35 고유주소 고치기 답하기

    감사합니다!
    덕분에 php 설치 잘 진행하고 있습니다.

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

스팸글 지우기

예전에 사용했던 KorWeblog 란 툴을 이용해서 사이트를 구축한 적이 있었다. 그런데 9월 들어 대량의 글을 퍼부은 지라 phpMyAdmin 툴을 이용해 지우려고 했더니 시간이 너무 오래 걸린다.?

그래서 서버에 직접접속하여 해당 레코드들을 지우기 시작한다.?

# mysql -u root -p mysql?

mysql> use Newspaper;

mysql> delete from T_Stories where host='194.8.74.10';
Query OK, 1437 rows affected (3.77 sec)

mysql> delete from T_Stories where host='194.8.75.247';
Query OK, 1085 rows affected (1.20 sec)

mysql> delete from T_Stories where host='194.8.75.103';
Query OK, 138 rows affected (2.29 sec)

mysql> delete from T_Stories where host='194.8.75.245';
Query OK, 567 rows affected (4.21 sec)

mysql> delete from T_Stories where host='194.8.75.214';
Query OK, 404 rows affected (2.65 sec)

mysql> delete from T_Stories where host='89.223.15.68';
Query OK, 72 rows affected (1.34 sec)

mysql> delete from T_Stories where host='194.8.75.239';
Query OK, 325 rows affected (1.72 sec)

mysql> delete from T_Stories where host='194.8.75.212';
Query OK, 203 rows affected (1.34 sec)

mysql> delete from T_Stories where host='91.103.152.241';
Query OK, 47 rows affected (3.22 sec)

위에 해당하는 IP주소의 출처를 찾아보았다.?

사용자 삽입 이미지

organisation:    ORG-DRAG1-RIPE
org-name:        Dragonara Alliance Ltd
org-type:        OTHER
address:         Geneva Place, Waterfront Drive,
                P. O. Box 3469, Road Town, Tortola,
                British Virgin Islands
mnt-ref:         DRAGONARA-MNT
mnt-by:          DRAGONARA-MNT
source:          RIPE # Filtered
국내주소가 아니라 위 whois 서비스를 이용해 보았다. 

89.223.15.68 주소에 대한 결과는 아래와 같았다.
erson:          Filip V. Arhangelov
address:         Russia
address:         Saint-Peterburg
address:         Mayakovskaya 34 - 50
phone:           +7(812)554-35-89
e-mail:          archangel78@mail.ru
nic-hdl:         Fva78-RIPE
remarks:         Pliviledge User
source:          RIPE # Filtered
음 쓸데없이 이렇게 스팸 댓글을 던지는 이유가 무엇일까 궁금해진다. 결국 곧바로 삭제당할 것을 알고 하는 것일까? ?암튼 근 5000여개의 쓸데없는 글이 붙어있는 사이트를 바라보자니 한숨이 나와 위와 같이 처리하니 일단 마음은 상쾌하다.?

mysql 은 쓰면 쓸수록 마음에 드는 DB다.?
2008/09/28 15:34 2008/09/28 15:34
글 걸기 주소 : http://jis.pe.kr/road/?/trackback/393

덧글을 달아 주세요

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