2016. 7. 15. 17:50 IT/MySQL
mysqlbinlog query 보기
[MySQL Program] mysqlbinlog
앱으로 보기 본문 기타 기능
mysqlbinlog 프로그램은 어려운 부분은 아니다. 하지만 간과하기 쉬운 몇가지가 있어서 좀 생각하고 넘어가는게 좋을 것 같다.
mysqlbinlog 프로그램을 사용한다는 것은 binary-log format, relay-log format, statement-based format, row-based format, Point-In-Time recovery 와 같은 주제들에 모두 관심을 갖는다는 의미라서 중요하다.
Section 5.2.4, “The Binary Log”
Section 17.2.4, “Replication Relay and Status Logs”
Section 17.2.1, “Replication Formats”
Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”
MySQL Internals: The Binary Log (19.7 Event Structure, 19.6 Event Meanings, 19.9 Event Data for Specific Event Types)
[용도]
mysqlbinlog 프로그램은 보통 두 가지 용도로 사용한다.
1. 추적
데이터에 문제가 발생하거나 마스터 슬레이브간 불일치가 발생하거나 어떤 트랜잭션이 문제가 되었거나 등 어떤 문제가 발생했을 때
write 쿼리에 대해 추적할 수 있는 기능을 제공한다.
복제를 위해 또는 이런 증거를 남기기 위해 binary log를 활성화 시켰다면 mysqlbinlog 프로그램으로 Statement 형태로 변환해서 볼 수 있게 된다.
2. 복구
MySQL의 백업정책은 회사마다 다르겠지만 보통 하루 한번은 full backup을 할 것이다.
이때 full backup만 가지고 복구해서 해결되면 다행이지만 그렇지 않은 경우 binary log를 적용하여 PIT recovery를 수행해야한다.
시점은 datetime일 수도 있고 sequence number 일수도 있다.
[사용법]
shell> mysqlbinlog [options] binlog.0000003
[결과파일]
# at 141 (해당 이벤트의 시작 포지션)
#100309 9:28:36 server id 123 end_log_pos 245 (로그 남은 시간, server id, 끝나는 포지션)
Query thread_id=3350 exec_time=11 error_code=0 (thread id, 수행시간 등)
Statement는 사실 그냥 우리가 알아볼수 있는 쿼리형태라서 따로 설명이 필요 없다.
Row-Based Event 에 대해서만 잠시 살펴보겠다.
이벤트 타입은 3가지로 기록된다. (WRITE_ROWS_EVENT, UPDATE_ROWS_EVENT, DELETE_ROWS_EVENT)
(원본쿼리 예제)
CREATE TABLE t
(
id INT NOT NULL,
name VARCHAR(20) NOT NULL,
date DATE NULL
) ENGINE = InnoDB;
START TRANSACTION;
INSERT INTO t VALUES(1, 'apple', NULL);
UPDATE t SET name = 'pear', date = '2009-01-01' WHERE id = 1;
DELETE FROM t WHERE id = 1;
COMMIT;
(결과1) mysqlbinlog 로 본 결과
Statement-Based와는 달리 알아보기 힘들게 되어 있다.
예제에 따라 Write_rows, Update_rows, Delete_rows 이벤트로 나뉘는 걸 확인 할 수 있다.
shell> mysqlbinlog log_file
...
# at 218
#080828 15:03:08 server id 1 end_log_pos 258 Write_rows: table id 17 flags: STMT_END_F
BINLOG '
fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
'/*!*/;
...
# at 302
#080828 15:03:08 server id 1 end_log_pos 356 Update_rows: table id 17 flags: STMT_END_F
BINLOG '
fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
'/*!*/;
...
# at 400
#080828 15:03:08 server id 1 end_log_pos 442 Delete_rows: table id 17 flags: STMT_END_F
BINLOG '
fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
'/*!*/;
(결과2) --vervose 옵션을 1개 사용
이제는 좀 알아보기 편하게 되었다. 매칭되는 컬럼의 개수와 값을 알 수 있다.
shell> mysqlbinlog -v log_file
or
shell> mysqlbinlog -v --base64-output=DECODE-ROWS log_file
...
# at 218
#080828 15:03:08 server id 1 end_log_pos 258 Write_rows: table id 17 flags: STMT_END_F
BINLOG '
fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
'/*!*/;
### INSERT INTO test.t
### SET
### @1=1
### @2='apple'
### @3=NULL
...
# at 302
#080828 15:03:08 server id 1 end_log_pos 356 Update_rows: table id 17 flags: STMT_END_F
BINLOG '
fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
'/*!*/;
### UPDATE test.t
### WHERE
### @1=1
### @2='apple'
### @3=NULL
### SET
### @1=1
### @2='pear'
### @3='2009:01:01'
...
# at 400
#080828 15:03:08 server id 1 end_log_pos 442 Delete_rows: table id 17 flags: STMT_END_F
BINLOG '
fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
'/*!*/;
### DELETE FROM test.t
### WHERE
### @1=1
### @2='pear'
### @3='2009:01:01'
(결과3) --vervose 옵션을 2개 사용
이제는 컬럼의 데이터 타입과 nullable 도 알 수 있게 되었다.
shell> mysqlbinlog -vv log_file
...
# at 218
#080828 15:03:08 server id 1 end_log_pos 258 Write_rows: table id 17 flags: STMT_END_F
BINLOG '
fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
'/*!*/;
### INSERT INTO test.t
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='apple' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
### @3=NULL /* VARSTRING(20) meta=0 nullable=1 is_null=1 */
...
# at 302
#080828 15:03:08 server id 1 end_log_pos 356 Update_rows: table id 17 flags: STMT_END_F
BINLOG '
fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
'/*!*/;
### UPDATE test.t
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='apple' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
### @3=NULL /* VARSTRING(20) meta=0 nullable=1 is_null=1 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='pear' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
### @3='2009:01:01' /* DATE meta=0 nullable=1 is_null=0 */
...
# at 400
#080828 15:03:08 server id 1 end_log_pos 442 Delete_rows: table id 17 flags: STMT_END_F
BINLOG '
fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
'/*!*/;
### DELETE FROM test.t
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='pear' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
### @3='2009:01:01' /* DATE meta=0 nullable=1 is_null=0 */
(결과4) --base64-output=DECODE-ROWS 와 --verbose 옵션의 조합
binary 형태도 제외하고 보여준다.
shell> mysqlbinlog -v --base64-output=DECODE-ROWS log_file
...
# at 218
#080828 15:03:08 server id 1 end_log_pos 258 Write_rows: table id 17 flags: STMT_END_F
### INSERT INTO test.t
### SET
### @1=1
### @2='apple'
### @3=NULL
...
# at 302
#080828 15:03:08 server id 1 end_log_pos 356 Update_rows: table id 17 flags: STMT_END_F
### UPDATE test.t
### WHERE
### @1=1
### @2='apple'
### @3=NULL
### SET
### @1=1
### @2='pear'
### @3='2009:01:01'
...
# at 400
#080828 15:03:08 server id 1 end_log_pos 442 Delete_rows: table id 17 flags: STMT_END_F
### DELETE FROM test.t
### WHERE
### @1=1
### @2='pear'
### @3='2009:01:01'
4.6.7.2 mysqlbinlog Row Event Display
[옵션들]
--read-from-remote-server
옵션의 경우 리모트서버에서 binlog를 읽을 수 있게 처리해준다. 타겟이 되는 서버에 대한 옵션을 같이 써줘야한다.
--host, --password, --port, --protocol, --socket, and --user
--hexdump
16진수로 덤프를 해준다. 복제에 문제가 생겼을 때 trace하는데 유용하다.
--rewrite-db
DB명을 바꿀 수 있다. 별거 아닌 것 같아도 복구 할때 유용하다.
--database
--binlog-do-db 옵션과 마찬가지로 특정 DB만 내린다. 이건 주의해야한다.
--start-datetime, --stop-datetime
시점(시간)을 명시한다.
--start-position, --stop-position
시점(포지션)을 명시한다.
--verbose
row event를 SQL형태로 변환해준다.
--exclude-gtids, --include-gtids, --skip-gtids
GTID 옵션들
[Row-Based 로 변경되는 경우들]
5.2.4.3 Mixed Binary Logging Format
'IT > MySQL' 카테고리의 다른 글
| Mysql 세자리 콤마 찍기. (0) | 2016.11.16 |
|---|---|
| Mysql 수학 함수 (0) | 2016.11.03 |
| Mysql Binary log(bin log). (0) | 2016.07.15 |
| mysql 여러가지 작업(optimize, Mornitoring 등). (0) | 2016.05.09 |
| mysql table name ibd 형태일 시 파일 의미. (0) | 2016.04.28 |
