19.7mysql 스토어 프로시저 레플리케이션에 안전한가?

mysql 스토어 프로시저 레플리케이션에 안전한가?



예전에 프로시저를 사용할 경우 레플리케이션이 깨지는 버그가 있었다?
   When FOUND_ROWS() or ROW_COUNT() is used. (Bug #12092, Bug #30244)
   해당 펑션을 사용할 경우 안되는 경우가 있었다 하지만 픽스 되었다

스토어 프로시저의 로깅은 어떻게 남는가?
       스토어 프로시저의 로깅은 call 스토어 이름으로 남지 않고 내부의 실행된 sql 로 남거나
       변경된 로우로 남는다.

프로시저 안에서의 commit  rollback 이 문제가 될수 있다?
   프로시저에서 트랜잰션이 지원되는 엔진을 가진 테이블을 업데이트 할 경우 로깅이 되지 않다가
(rollback 될수 있음으로 캐슁만 하고 있다 ) 커밋 바로 직전에 로깅을 남긴다. 
   즉 문제가 발생하지 않는다.

 MIXED 나 ROW 타입의 로깅을 사용하면 레플리케이션 환경에서 스토어 펑션이나 스토어 프로시듀어는 안전하다.

• Procedure calls can occur within a committed or rolled-back transaction. Transactional context is accounted for so that the transactional aspects of procedure execution are replicated correctly. That is, the server logs those statements within the procedure that actually execute and modify data, and also logs BEGIN, COMMIT, andROLLBACK statements as necessary. For example, if a procedure updates only transactional tables and is executed within a transaction that is rolled back, those updates are not logged. If the procedure occurs within a committed transaction, BEGIN and COMMIT statements are logged with the updates. For a procedure that executes within a rolled-back transaction, its statements are logged using the same rules that would apply if the statements were executed in standalone fashion:
  • Updates to transactional tables are not logged.
  • Updates to nontransactional tables are logged because rollback does not cancel them.
  • Updates to a mix of transactional and nontransactional tables are logged surrounded by BEGIN and ROLLBACK so that slaves will make the same changes and rollbacks as on the master.

  Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order.

Within an uncommitted transaction, all updates (UPDATE, DELETE, or INSERT) that change transactional tables such as InnoDB tables are cached until a COMMIT statement is received by the server. At that point, mysqld writes the entire transaction to the binary log before the COMMIT is executed.






참고 :http://dev.mysql.com/doc/refman/5.5/en/stored-programs-logging.htmll
http://dev.mysql.com/doc/refman/5.5/en/binary-log-mixed.html
http://dev.mysql.com/doc/refman/5.5/en/binary-log.html
http://dev.mysql.com/doc/refman/5.5/en/stored-programs-logging.htm

binary log 는 데이터 베이스 일어난 테이블 변경, 또는 데이터 변경에 대한 event 를 기록한다.잠재적으로 가능성이 있는 문장도 기록(effected row 가 0 인 DELETE문)

목적
  1.마스터가 슬레이브에게 변경된 데이터를 전달하기 위해서
  2.백업 후  백업 이후 시점의 바이너리 로그를 리플레이 시켜 최신 데이터 까지 복구

바이너리 로그는 select, show 등 데이터를 변경하지 않는 문장은 기록하지 않는다.

바이너리 로그를 키면 조금 느려지지만 레플리케이션을 사용할수 있음으로 상쇄가능

바이너리 로그에 패스워드가 기록될수 있음으로 로그 자체는 보호 되어야 한다.

바이너리 로그 키기 start the server with the --log-bin[=base_name] option (base_name default is the name of host machine) 

 --binlog-format=type. 

• STATEMENT causes logging to be statement based.
• ROW causes logging to be row based.
• MIXED causes logging to use mixed format.
• 
  
• 스토어 프로시저 안에서 아래의 키워드를 쓰면 레플리케이션이 안되는 버그가 있었음(수정됨)
• 아래의 두개 키워드를 사용하면 로깅 타입이 row-based 로 바뀜

•            When FOUND_ROWS() or ROW_COUNT() is used. (Bug #12092, Bug #30244)

In general, the issues described here result when binary logging occurs at the SQL statement level. If you use row-based binary logging, the log contains changes made to individual rows as a result of executing SQL statements. 

statements 에서만 발생할 수 있는 문제다 만약 row-based 나 mixed 를 쓴다면 상관없다

http://dev.mysql.com/doc/refman/5.5/en/stored-programs-logging.html

* 스토어 프로시저는 call 프로시저 가 로깅 되는게 아니라 내부의 sql이 직접 로깅된다.

*스테이브먼트 타입 로깅에서 스토어 펑션안에서 스토어 프로시져를 쓰면 스토어 프로시저를 호출한  로깅이 남지 않는다!!

A stored procedure call is not written to the binary log at the statement level if the procedure is invoked from within a stored function. In that case