로그활용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기준으로 되어있고 일시 테이블을 사용하고 있는 경우

로그활용4 - slow query log

slow-query-log(--slow-query-log)

처리에 시간이 걸린 쿼리를 기록하기 위한 옵션이다.

쿼리 실행에 long_query_time에 세팅된 초수(표준 10초)이상 시간이 걸린 경우 기록된다.

기록장소는 mysql.slow_log테이블이던지 slow_query_log_file변수에 지정된 파일(--log-slow-quries옵션에 지정된 파일)이다.

로그의 출력위치를 테이블이나 파일로 할 것인지 하는 것은 log-output옵션에서 지정한다.

MySQL서버 기동중에 slow query log를 얻기위한 지시예

mysql> SET GLOBAL slow_query_log=1;

초수를 3초로 변경하는 예

mysql> SET GLOBAL long_query_time=3;

log-slow-queries(--log-slow-queries)

처리에 시간이 걸린 쿼리를 기록하는 로그파일이다. 이 파일은 FLUSH LOGS로는 로테이트할 수 없다. 또 서버 재기동시에도 로테이트 하지 않는다.

다음 처럼 지정한다.

log-slow-queires[=파일명]

파일명을 생략하면 datadir/호스트명-slow.log로 된다.

또, MySQL서버 기동중에도 slow query log를 기록할건지 말건지 지정하는 것이 가능하다.

MySQL서버 기동중에 slow query log를 유효로 하기

mysql> SET GLOBAL slow_query_log=1;

slow query log의 로그파일을 지정하는 경우는 다음 처럼 조작한다.

mysql> SET GLOBAL slow_query_log_file="/tmp/slow";

또한 SHOW VARIABLES로 봤을 때의 log_slow_queries는 slow query log가 기록되고 있는지에대한 여부를 나타내는 변수로 이것을 SET로 변경하는 것은 불가능하다.

log_queries_not_using_indexes(--log_queries_not_using_indexes)

log_queries_not_using_indexes를 지정하면 인덱스를 사용하지 않은 쿼리도 slow query log에 기록할 수 있다.

log_queries_not_using_indexes

다음처럼 서버기동중에는 SET로 변경하는 것이 가능하다.

MySQL서버기동중에 변경하기

mysql> SET GLOBAL log_queries_not_using_indexes=ON;

long-query-time(--long-query-time)

long-query-time에서는 지정한 초수보다 처리에 시간이 걸린 쿼리를 기록하게 된다.

다음 처럼 지정한다.

long-query-time=초수

또, SET에서 MySQL서버 기동시에도 변경가능하다.

MySQL서버 기동중에 변경하기

mysql> SET long_query_time=5;

로그활용3 - 일반로그

log(--log)

MySQL서버처리를 상세하게 기록하는 로그파일이다. 언제 어떤유저(포함하는 호스트)가 접속해서 어떤 쿼리를 실행했는가가 상세하게 기록된다.

기록은 MySQL서버가 접수한 순서대로 기록된다. 트랜잭션 상태는 고려하는 않는다.

어플리케이션과 연계 디버그나 보안감사에 사용할 수 있다.

이 옵션을 지정하면 general-log옵션이 자동적으로 ON이 된다.

다음처럼 지정한다.

log[=파일명]

파일이름을 생략하면 datadir/호스트명.log파일이 된다. 이 파일은 FLUSH LOGS로는 로테이트되지 않는다. 또 서버 재기동시에도 로테이트하지 않는다.

이 파일을 로테이트하려면 Unix계열에서는 다음처럼 한다.

log를 로테이트한다.

root@shell# mv hostname.log hostname.log.0

root@shell# mysqladmin flush-logs

general-log(--general-log)

general-log는 MySQL5.1이상에서의 기능이다. 이 옵션을 지정하면 mysql.general_log테이블에 General로그가 출력된다.

옵션은 다음처럼 지정한다.

general-log

또, MySQL서버 기동중에 SET문으로 값을 변경하면 유효로 할 수 있다.

MySQL서버기동중에 general-log를 유효로 세팅

mysql> SET GLOBAL general_log=1;

mysql.general_log테이블이 존재하지 않는 경우는 mysql_fix_privilege_tables스크립트를 실행하고 테이블을 작성해야한다. 이 테이블은 CSV스토리지엔진으로 작성되어 있다.

또, 이 옵션이 지정된 경우 보통 --log로 지정된 파일에는 로그를 기록하지 않는다.

주의점은 이 테이블의 캐릭터셋이다.

general_log

CREATE TABLE `general_log`(

...

중략

`command_type` varchar(64) DEFAULT NULL,

`argument` mediumtext

) ENGINE=CSV DEFAULT CHARSET=utf8

이 처럼 실행한 SQL문은 argument헤더에 utf8캐릭터셋으로 기록된다.

만약 UTF-8로 변환불능한 문자가 쿼리에 포함되어 있었을 경우는 이 부분 기록은 옳바르지 않게 된다.

덧붙여 말하면 mysql.general_log테이블에 대해서 DELETE/UPDATE/INSERT문은 실행할 수 없다. FLUSH LOGS를 실행해도 내용은 없어지지 않지만 TRUNCATE TABLE문을 사용해서 내용을 전부 제거하는 것은 가능하다. 또 이 옵션이 무효인 경우(general로그를 출력하지 않을 때)에는 ALTER TABLE문으로 스토리지엔진을 바꾸거나 DROP TABLES문으로 파기하는 것도 가능하다.

log-output(--log-output)

log-output는 general로그와 slow query로그 출력위치를 지정한다.

log-output=값[,값]

값에는 TABLE, FILE, NONE을 지정할 수 있다.

TABLE: mysql.general_log테이블에 쓴다.

FILE: --log옵션으로 지정된 파일에 쓴다.

NONE:로그를 출력하지 않는다. 다른 지정보다 우선도가 높다.

또 , 값을 "," 로 복수 지정하는 것도 가능하다.

log-output=FILE,TABLE

이 경우는 파일과 테이블 양쪽에 출력하게 된다.

log-output는 MySQL서버기동중에 SET문을 사용해서 변경하는 것도 가능하다.

MySQL서버 기동중에 출력위치를 변경

mysql>SET GLOBAL log_output="FILE,TABLE";

로그활용1

로그의 종류

MySQL로그에는 데이터베이스내에서 일어나는 여러가지 사건들이 기록되기때문에 로그를 읽는 것으로 데이터베이스서버 운용에 빠질 수 없는 중요한 정보를 얻는 것이 가능하게 된다.

MySQL 5.1.12-beta로그에는 다음과같은 것이 있다.

• 에러로그(log-error/ log-warnings)
• General로그(log/ general-log)
• slow query로그(log-slow-queries / slow-query-log)
• 바이너리 로그(log-bin)
• ISAM로그(log-isam)

log-isam은 MyISAM 변경을 기록하는 파일이다.

개발자대상 디버그 정보가 기록되기때문에 보통은 사용하지 않는다.

MySQL은 기본적으로는 로그를 기록하지 않기때문에 로그를 기록할 경우는 mysqld 기동시에 기록하고픈 로그를 옵션으로 지정하던지 설정파일 my.cnf(또는 my.ini)의 [mysqld] 그룹에 기술한다.

로그의 활용

로그의 로테이트

로그를 로테이트하기위해서는 SQL문을 실행하던지 mysqladmin명령어를 실행한다.

로그의 로테이트

mysql> FLUSH LOGS;

또는

shell$ mysqladmin flush-logs

또는

shell$ mysqladmin refresh