8.11. mysqlbinlog — 바이너리 로그 파일을 처리하기 위한 유틸리티

서버가 생성하는 바이너리 로그 파일은 바이너리 포맷으로 작성된다. 이 파일을 텍스트 포맷으로 체크를 하려면, mysqlbinlog 유틸리티를 사용하면 된다. 리플리케이션 셋업에서 슬레이브가 작성한 릴레이 로그 파일을 읽기 위해서도 mysqlbinlog를 사용할 수 있다. 릴레이 로그는 바이너리 로그 파일과 같은 포맷을 가지고 있다.

mysqlbinlog은 다음과 같이 호출한다:

 

shell> mysqlbinlog [option] log_file ...

예를 들면, binlog.000003라는 이름의 바이너리 로그 파일의 내용물을 보여 주기 위해서 다음 명령어를 사용한다:

 

shell> mysqlbinlog binlog.0000003

이 명령어를 실행하면 binlog.000003에 포함되어 있는 모든 이벤트가 나오게 된다.  이벤트 정보는 실행된 명령문, 명령문 실행 시간, 명령문을 입력한 클라이언트의 쓰레드 ID, 실행된 타임 스탬프 등등의 정보가 포함되어 있다.

mysqlbinlog를 통해 얻게 되는 결과는 로그에 있는 명령문을 재 적용하기 위해 다시 실행 시킬 수 있다 (예를 들면, 이것을 mysql의 입력 값으로 사용할 수 있다). 이 값은 서버가 크래시(crash)가 난 후에 복구를 할 때 유용하게 사용할 수 있다.

일반적으로 바이너리 로그 파일을 직접 읽고 이것을 로컬 MySQL 서버에 적용하기 위해 mysqlbinlog를 사용하게 된다. 또한, 이것을 --read-from-remote-server 옵션과 함께 사용해서 원격 서버로부터 바이어리 로그를 읽는 것도 가능하다. 원격 바이너리 로그를 읽을 경우, 접속 파라미터 옵션을 지정해서 서버와의 접속 방법을 지시할 수 있다. 이렇게 할 수 있는 옵션으로는 --host, --password, --port, --protocol, --socket, 그리고 –user가 있다; 이 옵션들은 --read-from-remote-server 옵션을 함께 지정해야만 기능을 발휘한다.

 

바이너리 로그 및 릴레이 로그는 Section 5.11.4, “바이너리 로그”, and Section 6.4.6, “리플리케이션 릴레이 및 상태 파일”을 참조할 것.

 

mysqlbinlog는 다음 옵션을 지원한다:

• --help, -?

도움말 메시지를 출력하고 종료.

• --base64-output

베이스64 인코딩을 이용하여 모든 로그 엔트리를 출력한다. 이것은 디버깅만을 위한 것이다. 이 옵션을 이용해 생성된 로그들은 제작 시스템에 적용하지 않아도 된다. 이 옵션은 MySQL 5.1.5에서 추가되었다.

• --character-sets-dir=path

문자 셋이 설치되어 있는 디렉토리. Section 5.10.1, “데이터 및 정렬을 위해 사용되는 문자 셋”장 참조.

• --database=db_name, -d db_name

지정된 데이터베이스만의 엔트리를 보여준다(로클 로그만 해당됨). 사용자는 이 옵션으로 하나의 데이터베이스만을 지정할 수 있다 - 다중 --database 옵션을 지정한다면, 마지막 하나만 사용된다. 이 옵션으로 This option forces mysqlbinlog는 디폴트 데이터베이스(즉, USE에 의해 선택된 것)가 db_name인 바이너리 로그로부터 엔트리를 출력한다. 다른 데이터베이스를 선택하거나 데이터베이스를 선택하지 않는 동안에, 이 옵션은 UPDATE some_db.some_table SET foo='bar' 와 같은 크로스-데이터베이스 명령문을 복제하지 않는 다는 것을 명심하라.

• --debug[=debug_option], -# [debug_option]

디버깅 로그를 작성한다. 전형적인 debug_options 스트링은 'd:t:o,file_name'.

• --disable-log-bin, -D

바이너리 로깅을 비 활성화 시킨다. 이것은 --to-last-log 옵션을 사용하고 그 결과를 동일한 MySQL 서버에 보내는 경우에 무한 루프를 피하기 위해서 유용하게 사용할 수 있다. 또한, 이 옵션은 서버 크래시 이후에 사용자가 로그한 명령문이 중복되는 것을 피하기 위해서도 유용하게 사용된다.

 

이 옵션은 SUPER 권한을 가지고 있어야 한다. 이것은 mysqlbinlog로 하여금 남아있는 결과의 바이너리 로깅을 비 활성화 시키기 위해 자신의 결과에 있는 SET SQL_LOG_BIN=0 명령문을 비 활성화 시키도록 만든다. SET 명령문은 사용자가 SUPER 권한을 가지고 있지 않는 한 효력을 갖지 못한다.

• --force-read, -f

이 옵션을 사용하면, mysqlbinlog가 인식하지 못하는 바이너리 로그 이벤트를 읽는 경우, 서버는 경고를 출력하고, 이벤트를 무시하고 계속 진행한다. 이 옵션을 사용하지 않으면, mysqlbinlog이 이와 같은 이벤트를 읽는 경우 멈추게 된다.

• --hexdump, -H

코멘트에 있는 로그의 헥스(hex)덤프를 출력한다. 이로 인한 결과는 리플리케이션 디버깅에 유용하게 사용될 수 있다. 헥스 덤프는 나중에 설명하기로 한다. 이 옵션은 5.1.2에서 추가 되었다.

• --host=host_name, -h host_name

주어진 호스트 상에 있는 MySQL 서버로부터 바이너리 로그를 가져온다.

• --local-load=path, -l path

지정된 디렉토리에 있는 LOAD DATA INFILE을 위한 로컬 임시 파일을 준비한다.

• --offset=N, -o N

로그에 있는 첫 번째 N 엔트리를 무시 한다(skip).

• --password[=password], -p[password]

서버에 접속을 할 때 사용하는 패스워드. 축약형 옵션 (-p)을 사용할 경우에는, 이 옵션과 패스워드 사이에는 스페이스를 사용하지 않는다. 만일 --password 또는 -p 옵션 다음에 패스워드를 지정하지 않으면, 프롬프트가 나오게 된다.

 

명령문 라인에서 패스워드를 지정하는 것은 불안전한 것으로 간주될 수 있다. Section 5.8.6, “패스워드 보안 유지”참조.  

• --port=port_num, -P port_num

원격 서버에 접속을 하기 위해 사용되는 TCP/IP 포트 번호.

• --position=N, -j N

사용 중지됨. 대신에 --start-position을 사용한다.

• --protocol={TCP|SOCKET|PIPE|MEMORY}

사용할 접속 프로토콜.

• --read-from-remote-server, -R

로컬 로그 파일을 읽는 대신에 MySQL 서버에서 바이너리 로그를 읽는다. 모든 접속 파라미터 옵션은 이 옵션을 같이 지정하지 않는 한 무시된다. 이러한 파라미터 옵션에는 --host, --password, --port, --protocol, --socket, 그리고 –user가 있다.

• --result-file=name, -r name

주어진 파일에 직접 결과를 출력한다.

• --server-id=id

주어진 서버 ID를 가진 서버가 생성하는 이벤트만 받아들인다. 이 옵션은 MySQL 5.1.4부터 사용 가능하다.

• --set-charset=charset_name

로그 파일 처리에 사용된 캐릭터 셋을 지정하기 위해서 아웃풋에 SET NAMES charset_name 명령문을 추가한다. 이 옵션은 MySL 5.1.12에 추가되었다.

• --short-form, -s

로그에 포함되어 있는 명령문만을 다른 정보 없이 표시한다.

• --socket=path, -S path

localhost에 접속하는 경우, 사용하게 될 유닉스 소켓 파일, 또는 윈도우에서의 지명된 파이프 이름.

• --start-datetime=datetime

datetime 인수와 동일하거나 느린 타임 스탬프를 가지고 있는 첫 번째 이벤트에서 바이너리 로그를 읽기 시작한다. datetime 값은 mysqlbinlog를 구동 시키는 시스템의 로컬 타임 존과 관련 있다. 이 값은 DATETIME 또는 TIMESTAMP 데이터 타입에 적합한 포맷 형태를 가져야 한다. 예를 들면:

 

shell> mysqlbinlog --start-datetime="2005-12-25 11:25:56" binlog.000003

 

이 옵션은 포인트-인-타입(point-in-time) 복구 시에 유용하다. Section 5.9.2, “백업 및 복구 전략의 예” 참조할 것.

• --stop-datetime=datetime

datetime 인수와 동일하거나 빠른 타임 스탬프를 가지고 있는 이벤트가 처음으로 발생할 때 바이너리 로그를 읽기를 종료한다. 이 옵션은 포인트-인-타입(point-in-time) 복구 시에 유용하다. datetime 값에 대한 정보는 --start-datetime 옵션에 대한 설명을 참조하기 바란다.

• --start-position=N

N 인수와 동일한 위치를 가지고 있는 이벤트가 처음 발생할 때 바이너리 로그를 읽기 시작한다. 이 옵션은 명령문 라인에 지명된 첫 번째 로그 파일에 적용된다.

• --stop-position=N

N 인수와 동일하거나 보다 큰 위치를 가지고 있는 첫 번째 이벤트 시점에 바이너리 로그를 읽기를 멈춘다. 이 옵션은 명령문 라인에 지명된 마지막 로그 파일에 적용된다.

• --to-last-log, -t

MySQL 서버로부터 요청 받은 바이너리 로그의 끝에서 멈추는 대신에, 마지막 바이너리 로그의 끝이 될 때까지 프린트를 계속한다. 이 결과를 동일한 MySQL 서버에 보낸다면, 무한 루프가 발생할 수도 있다. 이 옵션은 --read-from-remote-server를 필요로 한다.

• --user=user_name, -u user_name

원격 서버에 접속을 할 때 사용하는 MySQL 사용자 이름.

• --version, -V

버전 정보를 출력하고 종료.

• --write-binlog

이 옵션은 디폴트로 작동되기 때문에, mysqlcheck 이 생성한 ANALYZE TABLE, OPTIMIZE TABLE, REPAIR TABLE 명령문들은 바이너리 로그에 쓰여진다. 명령문에 추가시킬 NO_WRITE_TO_BINLOG 을 불러오기 위해 --skip-write-binlog를 사용하면 기록되지 않는다. 이 명령문들을 리플리케이션 슬레이브로 전달하지 않아도 될 때 또는 백업으로부터 복구를 위한 바이너리 로그를 사용할 때 --skip-write-binlog 를 이용하라. 이 옵션은 MySQL 5.1.18에 추가되었다.

또한, --var_name=value 신텍스를 사용해서 아래의 변수를 지정할 수 있다:

• open_files_limit

보관해야 할 오픈 파일 디스크립터의 숫자를 지정한다.

 

--set-variable=var_name=value 또는 -O var_name=value 신텍스를 이용함으로써 변수를 설정할 수 있다. 이 신텍스는 무시된다.

 

바이너리 로그에 포함된 명령문을 실행하기 위해서 mysql 클라이언트 안에 mysqlbinlog 의 아웃풋을 전달할 수 있다. 이것은 이전 백업을 가지고 있을 때 크래쉬로부터 복구를 위해 사용된다. (Section 5.9.1, “데이터베이스 백업”참조). 예를 들어:

 

shell> mysqlbinlog binlog.000001 | mysql

 

또는:

 

shell> mysqlbinlog binlog.[0-9]* | mysql

명령문의 로그를 우선 수정할 필요가 있다면, mysqlbinlog의 결과를 텍스트 파일로 보낼 수가 있다 (예를 들면, 실행되지 않기를 원하는 명령문을 제거하기 위해). 이 파일을 수정한 다음에, 이것을 mysql 프로그램의 입력 값으로 사용해서 그 명령문을 실행 시킨다.

 

mysqlbinlog은 --start-position 옵션을 가지고 있는데, 이것은 바이너리 로그 안에서 주어진 위치와 동일하거나 보다 큰 오프셋을 가지고 있는 명령문만을 출력한다 (주어진 위치는 반드시 하나의 이벤트 시점과 매치가 되어야 한다). 또한, 이것은 주어진 날짜 및 시간을 가지고 있는 이벤트를 발견할 때 종료하고 시작하는 옵션을 가지기도 한다. 이것을 사용하면 --stop-datetime 옵션을 사용해서 포인트-인-타입 복구를 실행할 수 있다.

MySQL 서버에서 하나 이상의 바이너리 로그를 가지고 있다면, 이 모든 것을 한번에 서버에 연결해서 처리하는 것이 안전한 방법이다. 아래에서 보여주는 것은 안전하지 못한 예이다:

 

shell> mysqlbinlog binlog.000001 | mysql # DANGER!!

shell> mysqlbinlog binlog.000002 | mysql # DANGER!!

서버에 서로 다르게 접속을 해서 바이너리 로그를 처리하게 되면, 맨 처음의 로그 파일이 CREATE TEMPORARY TABLE 명령문을 가지고 있고 두 번째 로그가 임시 테이블을 사용하는 명령문을 가지고 있는 경우에는 문제가 발생하게 된다. 맨 처음의 mysql 프로세스가 종료될 때, 서버는 임시 테이블을 제거한다. 두 번째 mysql 프로세스가 이 테이블을 사용하고자 시도하면, 서버는 “unknown table.”이라고 알려주게 된다.

이와 같은 문제를 없애기 위해서는, 처리하고자 하는 모든 바이너리 로그 컨텐츠에 대해서 싱글 접속을 사용하도록 한다. 다음은 그 방법이다:

 

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql

또 다른 방식은 모든 로그를 하나의 파일에 작성을 하고 이 파일을 처리하는 것이다:

 

shell> mysqlbinlog binlog.000001 >  /tmp/statements.sql

shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql

shell> mysql -e "source /tmp/statements.sql"

mysqlbinlog는 원본 데이터 파일을 사용하지 않고 LOAD DATA INFILE 연산을 재 실행하는 결과를 만들어 낸다. mysqlbinlog은 그 데이터를 임시 파일에 복사를 하고 이 파일을 참조하는 LOAD DATA LOCAL INFILE 명령문을 작성한다. 이 파일이 작성되는 디폴트 디렉토리의 위치는 시스템에 따라 달라진다. 이 디렉토리를 명확히 지정하기 위해서는 --local-load 옵션을 사용한다.

mysqlbinlog이 DATA LOCAL INFILE 명령문을 LOAD DATA LOCAL INFILE 명령문으로 변환 시키기 때문에 (즉, LOCAL을 추가 함), 이 명령문을 처리하는 클라이언트 및 서버 모두는 반드시 LOCAL 기능을 처리할 수 있도록 구성되어야 한다. Section 5.7.4, “LOAD DATA LOCAL을 사용한 보안 이슈”를 참조.

Warning: LOAD DATA LOCAL 명령문용으로 생성된 임시 파일은 실제로 이 명령문을 실행할 때까지 필요한 것이기 때문에 자동으로 삭제가 되지 않는다. 더 이상 이 명령문의 로그가 필요하지 않게 되면 그 때에 임시 파일을 삭제 하도록 한다. 이 파일은 임시 파일 디렉토리에서 찾을 수 있으며 original_file_name-#-#와 같은 이름을 가지고 있다.

--hexdump 옵션은 코멘트에서 로그 컨텐츠에 대한 헥스(hex) 덤프를 만든다:

 

shell> mysqlbinlog --hexdump master-bin.000001

위의 명령어를 사용하면, 다음과 같은 결과가 만들어진다:

 

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

# at 4

#051024 17:24:13 server id 1  end_log_pos 98

# Position  Timestamp   Type   Master ID        Size      Master Pos    Flags

# 00000004 9d fc 5c 43   0f   01 00 00 00   5e 00 00 00   62 00 00 00   00 00

# 00000017 04 00 35 2e 30 2e 31 35  2d 64 65 62 75 67 2d 6c |..5.0.15.debug.l|

# 00000027 6f 67 00 00 00 00 00 00  00 00 00 00 00 00 00 00 |og..............|

# 00000037 00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00 |................|

# 00000047 00 00 00 00 9d fc 5c 43  13 38 0d 00 08 00 12 00 |.......C.8......|

# 00000057 04 04 04 04 12 00 00 4b  00 04 1a                |.......K...|

#       Start: binlog v 4, server v 5.0.15-debug-log created 051024 17:24:13

#       at startup

ROLLBACK;

헥스 덤프로 만들어지는 결과에는 다음과 같은 것들이 포함된다. 이 포맷은 향후 변경될 것이다.

• Position: 로그 파일 안에 있는 바이트 위치.
• Timestamp: 이벤트 타임 스탬프. 위의 예문에서 보면, '9d fc 5c 43'은 16진 법(hexadecimal) '051024 17:24:13' 의 다른 표현식이다.
• Type: 로그 이벤트의 타입. 위의 예문에서 보면, '0f'는 예문의 이벤트가 FORMAT_DESCRIPTION_EVENT 라는 것을 의미한다. 아래에서는 가능한 타입을 열거하고 있다.

Type	Name	Meaning
00	UNKNOWN_EVENT	This event should never be present in the log.
01	START_EVENT_V3	This indicates the start of a log file written by MySQL 4 or earlier.
02	QUERY_EVENT	The most common type of events. These contain statements executed on the master.
03	STOP_EVENT	Indicates that master has stopped.
04	ROTATE_EVENT	Written when the master switches to a new log file.
05	INTVAR_EVENT	Used mainly for AUTO_INCREMENT values and when the LAST_INSERT_ID() function is used in the statement.
06	LOAD_EVENT	Used for LOAD DATA INFILE in MySQL 3.23.
07	SLAVE_EVENT	Reserved for future use.
08	CREATE_FILE_EVENT	Used for LOAD DATA INFILE statements. This indicates the start of execution of such a statement. A temporary file is created on the slave. Used in MySQL 4 only.
09	APPEND_BLOCK_EVENT	Contains data for use in a LOAD DATA INFILE statement. The data is stored in the temporary file on the slave.
0a	EXEC_LOAD_EVENT	Used for LOAD DATA INFILE statements. The contents of the temporary file is stored in the table on the slave. Used in MySQL 4 only.
0b	DELETE_FILE_EVENT	Rollback of a LOAD DATA INFILE statement. The temporary file should be deleted on slave.
0c	NEW_LOAD_EVENT	Used for LOAD DATA INFILE in MySQL 4 and earlier.
0d	RAND_EVENT	Used to send information about random values if the RAND() function is used in the statement.
0e	USER_VAR_EVENT	Used to replicate user variables.
0f	FORMAT_DESCRIPTION_EVENT	This indicates the start of a log file written by MySQL 5 or later.
10	XID_EVENT	Event indicating commit of an XA transaction.
11	BEGIN_LOAD_QUERY_EVENT	Used for LOAD DATA INFILE statements in MySQL 5 and later.
12	EXECUTE_LOAD_QUERY_EVENT	Used for LOAD DATA INFILE statements in MySQL 5 and later.
13	TABLE_MAP_EVENT	Information about a table definition. Used in MySQL 5.1 and later.
14	WRITE_ROWS_EVENT	Row data for a single table that should be created. Used in MySQL 5.1 and later.
15	UPDATE_ROWS_EVENT	Row data for a single table that needs to be updated. Used in MySQL 5.1 and later.
16	DELETE_ROWS_EVENT	Row data for a single table that should be deleted. Used in MySQL 5.1 and later.

• Master ID: 이벤트를 생성한 마스터의 서버 ID
• Sizedlqpsxmdml 바이트 단위 크기
• Master Pos: 원본 마스터 로그 파일에 있는 이벤트의 위치
• Flags: 플래그. 현재까지는, 아래의 플래그들이 사용된다. 나머지들은 향후에 사용될 예정이다.

Flag	Name	Meaning
01	LOG_EVENT_BINLOG_IN_USE_F	Log file correctly closed. (Used only in FORMAT_DESCRIPTION_EVENT.) If this flag is set (if the flags are, for example, '01 00') in a FORMAT_DESCRIPTION_EVENT, the log file has not been properly closed. Most probably this is because of a master crash (for example, due to power failure).
02		Reserved for future use.
04	LOG_EVENT_THREAD_SPECIFIC_F	Set if the event is dependent on the connection it was executed in (for example, '04 00'), for example, if the event uses temporary tables.
08	LOG_EVENT_SUPPRESS_USE_F	Set in some circumstances when the event is not dependent on the default database.