MySQL 5.5 Binary Log (binlog_format=mixed) v1.0
|
12.11.15 |
1.0 |
|
|
|
|
|
|
|
|
|
1. Binary log
MySQL 에서 Binary log 는 log-bin 에 값을 셋팅했을 때 (configuration file) 쌓이며 기본적으로 OFF 상태이다.
일반적으로 Replication Master 로 구성할 때 사용하며, 그 이유는 Replication 구성에 필수요소이기 때문이다. Replication Master 의 Binary Log 는 Slave 로 전송되어 Relay log 로 쌓이고 Slave 장비는 이를 읽어들여 실행 (SYNC) 한다.
이 문서에서는 binlog_format = mixed 인 경우에 대해, Function, Procedure, Trigger, Event 가 Bin Log 에 어떤 식으로 정보를 남기는지 확인하려 한다.
확인 대상은 Replication Master / Slave 를 사용했으며 auto commit 을 사용하고 있다. Function, Procedure, Trigger, Event 에 사용한 테이블은 사전에 구성한 상황이다.
2. Extract DML from Binary Log
이후 Binary Log 내용의 확인은 다음의 명령을 이용해 확인하였다.
|
mysqlbinlog -dtest --start-position=90511 --stop-position=90705 -rtmp.txt /u01/mysql/data/mysql-bin.000016 |
옵션을 설명하면 다음과 같다.
-d{database} : 어떤 database 에 대해 DML 을 추출할지 지정한다.
--start-position={start position} : 시작지점을 지정한다.
--stop-position={stop position} : 종료지점을 지정한다.
-r{result file} : 결과를 저장할 파일을 지정한다.
mysqlbinlog {input binary log} : 추출할 Binary log 를 지정한다.
추가적인 설명을 하자면 start/stop position 정보는 Bytes 정보이다.
Bytes 를 확인하는 방법으로는 show binary logs 명령을 통해 현재 사용중인 파일과 쌓인 Bytes 를 확인할 수 있다. 혹은 바이너리 로그 파일이 있는 경로에서 ls –lrt 명령을 통해 확인하는 방법도 가능하다.
테스트에 사용한 MySQL Instance 는 별도의 작업이 없는 IDLE 한 상태로 DML 을 수행하기에 앞서 Bytes 를 사전 조사하여 start / stop 정보로 활용했다.
3. Function
|
delimiter || CREATE FUNCTION ftest (tag int) returns int deterministic begin return tag; end || delimiter ; |
넣은 값을 그대로 리턴하는 간단한 함수이다.
먼저 위 구문을 수행했을 때 Binary Log 에는 어떻게 남는지 확인하였다.
|
CREATE DEFINER=`root`@`localhost` FUNCTION `ftest`(tag int) RETURNS int(11) DETERMINISTIC begin return tag; end /*!*/; DELIMITER ; |
Binay Log 상에서는 위와 같이 생성문을 확인할 수 있었고, 이는 Replication Master, Slave 차이 없었다.
위와 같이 Function 생성후 다음을 확인해 보았다.
- Function 을 이용한 insert
- Function 을 이용한 update
- Function 을 이용한 delete
|
insert into sample values(ftest(1)); insert into sample values(ftest(2));
update sample set id=4 where id = ftest(1); update sample set id=5 where id = ftest(2);
update sample set id=ftest(7) where id = 4; update sample set id=ftest(9) where id = 5;
delete from sample where id = ftest(7); delete from sample where id = ftest(9); |
위와 같이 수행했을 때 Binary log 에서 다음과 같이 확인되었다.
|
use test/*!*/; SET TIMESTAMP=1352873210/*!*/; insert into sample values(ftest(1)) /*!*/;
SET TIMESTAMP=1352873216/*!*/; insert into sample values(ftest(2)) /*!*/;
SET TIMESTAMP=1352873509/*!*/; update sample set id=4 where id = ftest(1) /*!*/;
SET TIMESTAMP=1352873516/*!*/; update sample set id=5 where id = ftest(2) /*!*/;
SET TIMESTAMP=1352873536/*!*/; update sample set id=ftest(7) where id = 4 /*!*/;
SET TIMESTAMP=1352873542/*!*/; update sample set id=ftest(9) where id = 5 /*!*/;
SET TIMESTAMP=1352873568/*!*/; delete from sample where id = ftest(7) /*!*/;
SET TIMESTAMP=1352873573/*!*/; delete from sample where id = ftest(9) /*!*/; |
Binary Log 에 function 의 수행결과가 상수값으로 기록되진 않는다는 것을 알 수 있었다.
추가로 위 결과는 Master, Slave 할 것 없이 동일했다.
거꾸로 function 안에 DML 이 있는 경우는 어떨까? 위에서 확인한 내용은 function 안에 DML 이 없는 경우였다.
|
delimiter || CREATE FUNCTION ftest (tag int) returns int deterministic begin insert into sample values (tag); update sample set id = tag + 1 where id = tag; delete from sample where id = tag + 1; return tag; end || delimiter ; |
위와 같이 function 을 만든 상황에서 select ftest(1); 를 수행한 이후 Binary log 를 확인해 보았다.
|
use test/*!*/; SET TIMESTAMP=1352880215/*!*/; SELECT `test`.`ftest`(1) /*!*/; |
function 안에 DML 을 넣었을지라도 Binary log 상에서는 최초 호출한 구문만 남으며 function 을 구성하는 DML 은 일절 남지 않는다.
4. Procedure
|
delimiter || CREATE PROCEDURE ftest (tag int) begin insert into sample values (tag); update sample set id = tag + 1 where id = tag; delete from sample where id = tag + 1; end || delimiter ; |
call ftest(1); 를 수행한 이후 bin log 를 확인한 결과는 다음과 같았다.
|
use test/*!*/; SET TIMESTAMP=1352881415/*!*/; insert into sample values ( NAME_CONST('tag',1)) /*!*/;
SET TIMESTAMP=1352881415/*!*/; update sample set id = NAME_CONST('tag',1) + 1 where id = NAME_CONST('tag',1) /*!*/;
SET TIMESTAMP=1352881415/*!*/; delete from sample where id = NAME_CONST('tag',1) + 1 /*!*/; |
위 결과는 Master / Slave 동일하다.
Procedure 를 수행한 결과는 Binary Log 에 Procedure 내용으로 남았다.
Procedure 를 구성하고 있는 몸통부분의 DML 이 남아있음을 알수 있고 변수에 실제로 어떤 값이 들어왔는지까지 알 수 있었다.
5. Trigger
|
DELIMITER || CREATE TRIGGER ttest AFTER INSERT ON sample FOR EACH ROW BEGIN INSERT INTO sample1 VALUES(9999); UPDATE sample1 SET id = id + 1 WHERE id = 9999; DELETE FROM sample1 WHERE id = 10000; END || DELIMITER ; |
위와 같이 trigger 를 만든 상황에서 다음의 DML 로 동작시켰다.
insert into sample values(1);
|
use test/*!*/; SET TIMESTAMP=1352883722/*!*/; insert into sample values(1) /*!*/; |
Trigger 와 관련된 일련의 동작들은 Binary Logs 에 기록되지 않았다. 다시 말해 각 장비 (Master, Slave) 에 존재하는 Trigger 의 동작에 맡긴다는 이야기다.
6. Event
|
SET TIMESTAMP=1352945046/*!*/; SET @@session.time_zone='SYSTEM'/*!*/; CREATE DEFINER=`root`@`localhost` EVENT `e_test` ON SCHEDULE EVERY 5 MINUTE STARTS '2012-11-15 10:10:00' ON COMPLETION NOT PRESERVE ENABLE DO begin insert into sample values(1); end /*!*/; |
event 의 생성은 위와같이 바이너리 로그에 내용을 남겼다. 더불어 insert into … 도 event 가 호출되면서 그대로 남겼다. 다만 information_schema 의 events.status 값이 서로 다르다.
Master // STATUS: ENABLED
Slave // STATUS: SLAVESIDE_DISABLED
information_schema 의 events.status 는 ENABLED, DISABLED, SLAVESIDE_DISABLED 값 중에 하나를 가질 수 있다. SLAVESID_DISABLED 는 5.1.18 버전에서 추가 되었으며, Event 의 생성이 다른 MySQL server 에서 일어났으며 slave 로 작동하고 있는 현재의 MySQL 서버로 복제되었음을 말한다. 하지만 event 는 현재 slave 에서 실행되지 않는다.
결과적으로 Slave 도 Master 에서 생성한 e_test 이벤트를 가지고 있지만 실행되지 않는다란 뜻이다.
때문에 Master 에서 이벤트가 발생을 하고 이 이벤트로 인해 발생된 DML 이 Slave 에 전파 될 뿐이다.
7. Summary
위 내용을 표로 정리하면 다음과 같다.
|
|
DDL전파 |
BODY 전파 |
|
FUNCTION |
O |
X |
|
PROCEDURE |
O |
O |
|
TRIGGER |
O |
X |
|
EVENT |
O |
O |
FUNCTION, PROCEDURE, TRIGGER, EVENT 에 대해 DDL 전파가 된다. Master 에 있는게 Slave 에도 존재한다. 다만 Event 의 경우 Slave 에서는 비활성화 되어 있다.
각각을 구성하는 BODY 에 존재하는 DML 의 경우 각기 다른데 FUNCTION 과 TRIGGER 의 경우 전파되지 않는다.
PROCEDURE 와 EVENT 의 경우 BODY 를 구성하는 DML 구문이 Binary Log 에 그대로 남았다.
A. Chapter 20. INFORMATION_SCHEMA Tables | http://docs.oracle.com/cd/E19078-01/mysql/mysql-refman-5.1/information-schema.html#events-table