로그활용8 - 바이너리로그

mysqlbinlog을 사용한 데이터 복구

mysqlbinlog를 사용해서 데이터를 복구하는 데에는 파이프라인을 직접 이용하는 방법과

일단 파일로 출력한 다음에 그것을 사용하는 방법이 있다.

shell$ ./bin/mysqlbinlog ./data/host-bin.000001 | ./bin/mysql

shell$./bin/mysqlbinlog ./data/host-bin.000001 < bin.000001

shell$./bin/mysql > bin.000001

바이너리로그 이벤트 확인

SHOW BINLOG EVENTS를 실행하면 바이너리로그 이벤트를 확인할 수 있다.

LIMIT구문이 없으면 모든 이벤트가 나오므로 주의해야한다.

SHOW BINLOG EVENTS

[IN '로그파일명'] [FROM 위치] [LIMIT [오프셋,] 갯수]

로그활용7 - 바이너리로그

바이너리로그 목록표시

현재 존재하는 바이너리 로그 리스트를 출력하는 경우는 SHOW BINARY LOGS문, 또는 SHOW MASTER LOGS문을 사용합니다.

바이너리로그 리스트를 표시

mysql> SHOW BINARY LOGS;

또는

mysql>SHOW MASTER LOGS;

바이너리로그 삭제

바이너리로그 파일을 삭제하는 방법은 PURGE MASTER LOGS TO문을 사용한다.

바이너리로그 삭제

mysql>PURGE MASTER LOGS TO 'server-bin.000005';

PURGE MASTER LOGS TO문으로 바이너리로그 파일명을 지정한다. 지정된 바이너리로그 보다 작은 숫자 파일이 제거된다. 위 예를 보면 server-bin.000004까지의 번호 파일이 삭제되고 server-bin.000005은 삭제되지 않는다.

바이너리로그 변환

바이너리로그 파일을 에디터나 페이저로 보더라도 의미를 알 수 없다.

확실히 읽기 위해서 텍스트로 변환시켜주는 것이 mysqlbinlog명령어이다.

다음처럼 조작한다.

mysqlbinlog명령어 실행

shell$ ./bin/mysqlbinlog ./data/host-bin.000001

..

..

바이너리로그에는 오퍼레이터가 실행하지 않은 것도 기록된다.

mysqlbinlog를 실행하면 그 정보도 SQL문으로 출력된다.

그것이 주석이나 SET문이다.

주석은 # 이나 /* 로 시작한다.

mysqlbinlog로 생성된 SQL문을 원래대로 데이터를 복원하지 않으면 안되기때문에 SET문에서는 TIMESTAMP 나 AUTO_INCREMENT 값이 복원전과 복원후가 틀리지 않도록 지정한다.

또, SQL문 실행시에는 캐릭터셋도 문제가 되기 때문에 @@session.character_set_client등으로 지정한다.

[# at숫자]는 바이너리 로그 파일에 있어서 기술위치(바이트)이다.

[# at숫자]에서부터 [# at숫자]사이를 이벤트라고 부른다.

로그활용6 - 바이너리 로그

sync_binlog(--sync-binlog)

표준으로는 이 값은 0으로 바이너리 로그의 디스크로의 동기를 위한 기록은 실행되지 않는다.

바이너리로그를 동기처리 하기 위해서는 sync_binlog옵션으로 사용한다.

--sync_binlog[=숫자]

또 SET문으로 동적으로 유효로 하는 경우도 가능하다. 다음처럼 조작한다.

바이너리로그 동기를 유효로 하기

mysql> SET GLOBAL sync_binlog=1;

sync_binlog가 유효인 경우 MySQL서버는 바이너리로그 기록에 fdatasync()를 사용한다.

지정한 값이 1이고 트랜잭션인 경우 트랜잭션마다 fdatasync()를 실행하고 트랜잭션이지 않은 경우는 한문장마다 실행한다.

이것으로 트랜잭션 단위로 확실히 바이너리 로그에 기록된다.

값이 1이상인 경우는 지정된 회수의 이벤트가 발생한 후에 flush를 수행한다.

디스크 I/O는 늘어나지만 안전성을 요구하는 경우에는 1을 추천한다.

그외 바이너리로그에 관한 옵션

• binlog-do-db=데이터베이스명 : 지정된 데이터베이스변경만을 바이너리 로그에 기록한다.
• binlog-ignore-db=데이터베이스명 : 지정된 데이터베이스변경만 기록하지 않는다.
• binlog-row-event-max-size=수: 표준값1024, binlog_format=ROW인 경우 1이벤트 최대사이즈(바이트수) 256배수를 지정한다.
• log-bin-trust-function-creators: 표준값0(무효) 1로 지정하면 stored procedure나 트리거 작성은 SUPER권한을 가진 유저만 실행가능하게 되고 바이너리로그를 부시지않는 것만 작성이 허용된다. binlog_format=ROW의 경우는 항상 바이너리로그는 안전

로그활용5 - 바이너리 로그

바이너리 로그는 갱신쿼리를 기록한 것으로 리커버리및 replication에 사용되는 중요한 로그파일이다.

실행된 갱신계 쿼리문이 이 파일에 기록된다.

또, 기록순서는 트랜잭션도 고려하고 있다.

log-bin(--log-bin)

log-bin은 바이너리 로그를 유효로 했을 경우에 사용한다. 다음과 같이 지정한다.

log-bin[=파일명 접두어]

파일명이 생략된 경우는 datadir/호스트명-bin.NNNNNN이 된다.

NNNNNN는 6자리 숫자로 MySQL이 자동으로 부여하는 수치이다.

이것은 000000부터 시작된다.

바이너리로그는 표준으로는 1G바이트 크기에 도달하면 자동으로 로테이트한다.

이 때 파일의 숫자부분에 1이 더해진 파일이 생기고 꼿에 새로운 로그가 기록된다.

현재의 로그는 가장 숫자가 큰 파일에 기록되고 있다는 것이다

로테이트는 FLUSH LOG문으로 강제적으로 실행하는 것도 가능하다.

log-bin-index(--log-bin-index)

log-bin-index는 바이너리 로그 인덱스 파일의 파일명을 변경한다. 바이너리로그 인덱스파일은

바이너리 로그 파일의 목록을 가지고 있는 파일이다. 현재 어떤 바이너리 로그 파일이 있는지를 나타낸다. 표준으로는 datadir/호스트명-bin.index라는 파일에 보존된다.

FLUSH LOGS나 PURGE MASTER LOGS TO를 실행하면 이 파일도 자동으로 변경된다.

바이너리로그 인덱스파일의 파일명을 변경하는 경우는 다음처럼 지정한다.

log-bin-index=파일명

max_binlog_size(--max_binlog_size)

max_binlog_size는 한개의 바이너리로그 파일의 최대 사이즈(바이트)를 지정한다.

여기에서 지정된 사이즈보다 파일이 커지는 경우 자동으로 로테이트한다.

다음처럼 지정한다.

max_binlog_size=숫자

또 SET으로 MySQL서버 기동시에도 변경하는 것이 가능하다.

MySQL서버 기동중에 변경하기

mysql> SET GLOBAL max_binlog_size=104857600;

binlog_cache_size(--binlog-cache-size)

MySQL은 바이너리로그에 써 내리는 내용을 캐쉬하지만 그 캐쉬 사이즈(바이트)를 지정하는 경우는 binlog_cache_size옵션을 사용한다.

binlog_cache_size=1048576

SET문을 사용해서 MySQL기동시에 동적으로 변경하는 것이 가능하다.

캐쉬사이즈를 변경

mysql> SET GLOBAL binlog_cache_size=1048576;

binlog_format(--binlog-format)

MySQL 5.1.5에서 바이너리 로그 포맷에 행 기준 기술방법이 도입되었다.

종래 SQL문을 기록한 바이너리 로그에서는 replication을 실행할 때에 slave 서버도 순서대로 SQL문을 실행하지 않으면 안되었기때문에 시간이 걸리는 쿼리를 실행할 때에는 slave내용은 마스터에 대해서 매우 늦어지는 경우가 있었다.

그러나 행 기준 포맷 바이너리로그를 사용하면 replication할 때에는 행의 변경만이 전달되어지기 때문에 slave처리도 빠르게 된다.

바이너리 로그의 포맷 변경에는 binlog_format 옵션을 사용한다. 기본 포맷은 종래와 마찬가지로 SQL문을 저장한다. 버젼 5.1.8부터는 SQL문 포맷이나 행 기준 포맷, 모두 섞어서 기록할 수 있게도 되었다.

bin_format={ROW|STATEMENT|MIXED}

binlog_format에 주어지는 값은 다음과 같다.

1또는 STATMENT SQL문장을 기록

2또는 ROW 행 기준 바이너리로그를 기록

3또는 MIXED 보통은 STATEMENT와 같은 동작을 하지만 다음과 같은 경우 자동으로 ROW으로 전환된다.

UDF이나 UUID()를 사용했을 경우

Cluster리플리케이션을 사용했을 경우

2또는 ROW를 지정하면 텍스트로 변환한 바이너리로그는 다음과 같이 기재된다.

이것은 다른 SQL문과 마찬가지로 mysql명령어로 처리가능하다.

행기준 바이너리 로그 파일

BINLOG '

3w0rRRMBAAAAJgAAACYAAAAAAA4AAAAAAAABHR1c3QAAWEAAQM=

';

또 SET문으로 동적으로 변경하는 경우는 다음처럼 지정한다.

replication포맷을 SQL문 레벨로 변경

mysql> SET GLOBAL binlog_format="SATATEMENT";

다음과 같은 경우는 SET문으로 동적으로 포맷을 변경하는 것이 불가능하다.

1. stored procedure나 트리거 안

2.NDB가 유효한 경우

3.세션이 ROW기준으로 되어있고 일시 테이블을 사용하고 있는 경우

새로운 스토리지엔진 Falcon

2007년 1월에 MySQL AB는 새로운 스토리지엔진인 Falcon의 알파버전을 릴리스 했다. (5.2.0-alpha)

Falcon은 현재 폭넓게 쓰이고 있는 스토리지엔진 InnoDB의 다른 선택지의 하나로써 기대받고 있다.

Falcon은 관계형 데이터베이스(RDBMS) 계의 권위자인 Jim StarKey씨와 그의 아내 Ann Harrison씨를 중심으로 개발이 진행되고 있다.

Jim StarKey씨는 InterBase의 아버지이자 또 Ann Harrison씨와 함께 Firebird의 메인커밋터로도 활약하고 있다.

그들은 Netfrastructure라 불리우는 회사에서 차세대 RDBMS의 개발을 진행하고 있었지만 2006년 2월에 MySQL AB가 Netfrastructure를 매수하게 됨으로 MySQL AB로 바뀌게 되었다.

Falcon의 가장 기본적인 특징은 트랜잭션을 서포트한다는 것이다.

커밋/롤백에 한하지 않고 row level lock, MVCC(Multi-Version Concurrency Control), lock을 걸지 않고 하는 online backup, crush recovery등은 어느것이던지 트랜잭션 지원을 기반으로 어플리케이션 개발에 있어서 매우 중요한 기능이다.

MySQL5.0에서 트랜잭션을 서포트하고 있는 스토리지엔진은 InnoDB와 NDB(MySQL Cluster)가 있다.

InnoDB는 MyISAM등과 마찬가지로 주로 MySQL 서버 1대 또는 Master/Slave형 replication구성에서 채용된다.

한편 NDB는 클러스터 구성을 전제로 하는 것으로 in-memory형의 고속, 고가용스토리지엔진이고 초당 수만~수십만트랜잭션등의 자릿수가 다른 처리능력을 요하는 환경에 쓰여지고 있다. (유럽과 미국를 중심으로 다수의 실적이 있다.)

Falcon은 이 중 InnoDB와 MyISAM과 같은 영역을 타겟팅하고 있다.

유저 입장에서 보는 기능은 InnoDB와 거의 비슷하므로 InnoDB의 대체적인 존재라고도 말해지고 있다. 그렇지만 Falcon은 InnoDB하고는 내부적으로 아키텍쳐가 전혀 다르고 InnoDB만 커버가능한 특징도 일부 존재한다.

이때문에 InnoDB상위호환(완전한 치환가능한 존재)이 되지는 않으므로 주의해야한다.

Falcon 안정판 릴리스시기는 MySQL 5.1의 다음 버전(5.2)을 예정하고 있다.

다만, 앞으로 개발상황이나 사용자의 요구에 따라서는 5.0이나 5.1에 back-porting될 가능성도 있다.

InnoDB에서만 커버되는 특징

InnoDB가 가지고 있지만 Falcon에는 없는 특징은 다음과 같은 것이 있다.

1. Falcon에서는 cluster index를 채용하고 있지않다.
2. Falcon에서는 분리 레벨 Read Uncommitted을 지원하지 않는다.
3. 바이너리 로그의 기록방식인 「statement 기준(종래형)」,「행 기준」중에서 Falcon에서는 후자만 지원한다.

3의 「행 기준」기록형식은 MySQL 5.1이후에 지원되는 기능으로 바이너리 로그에의 기록형식이 종래의 DDL/DML문 그대로가 아니라 물리적인 값이 된다는 것이다.

InnoDB에서는 2개를 전부 지원하고 있지만 Falcon에서는 기존 방식으로 지원하지 않는다.

바이너리로그의 기록방식의 차이는 replication뿐만아니라 디스크장해가 있을 때의 복구에도 영향을 끼친다.

기존방식에서는 정확히 기록되지 않았던 UUID()등이 이용가능하다라는 merit가 있는 반면 전체적인 사이즈가 증가한다는 결점도 있다.

현 단계에서는 1,2,3 어느것도 지원할 계획은 없지만 요구에 맞게 장래적으로 지원될 가능성도 있을 것이다.

이것 이외에도 InnoDB는 가지고 있고 현시점의 Falcon이 가지고 있지 않는 중요한 기능이 몇개 더 있다.

SELECT FOR UPDATE, foreign key제약, online backup, Two-Phase Commit등은 베타버전에서는 지원될 예정이다.