바이너리 로그는 데이터 베이스의 컨텐츠를 수정하는 SQL명령문에 대한 정보를 가지고 있다. 이러한 정보는 수정 사항을 설명하는 “이벤트(Event)”형태로 보관되어 진다. 바이너리 로그에는 두 가지 중요한 목적이 담겨 있다:
리 플리케이션의 경우, 마스터 서버는 자신의 바이너리 로그에 포함된 이벤트를 자신의 슬레이브에 전달하는데, 이렇게 함으로서 서버에서 이루어진 데이터의 변경과 동일한 내용을 전달된 이벤트가 실행하도록 하게 한다 . Section 6.2, “리플리케이션 구현 소개” 참조.
특정 데이터 복구 동작은 바이너리 로그의 사용을 요구한다. 백업 파일이 재저장된 후에, 백업이 이루어진 후 기록된 바이너리 로그에 있는 이벤트가 재 실행된다. 이러한 이벤트들은 백업이 이루어지는 시점부터 데이터 베이스를 갱신하게 낸다. Section 5.10.2.2, “복구를 위한 백업 사용하기” 참조.
이 섹션에서는 MySQL 5.0에 있는 스토어드 루틴(프로시저 및 함수)와 트리거에 관련된 바이너리 로깅의 개발을 설명하도록 하겠다. 첫 번째로는 로깅 실행에서 발생하는 변경 사항에 대해 정리를 할 것이고, 그 다음에는 스토어드 루틴의 사용에서 있게 되는 실행의 현재 조건문(current condition)에 대해 언급하기로 한다. 마지막으로, 언제 그리고 어떻게 다앵한 변경이 만들어 지는지에 대한 정보를 제공하는 실행에 대한 자세한 내용을 설명하겠다. 이러한 상세 내용들은 현재의 로깅 행위에 관련된 몇몇 사항이 이전 버전과는 어떻게 달리 실행되는지를 보여 준다.
일반적으로, 여기에서 설명하는 논제들은 바이너리 로깅이 SQL명령문 레벨에서 생긴다는 사실에서부터 출발한다. 향후의 MySQL버전은 로우-레벨(row-level) 바이너리 로깅을 실행할 예정이며, SQL명령문의 실행으로 인해 각 개별 열(row)을 변경시키는 것을 열거할 것이다.
다른 것들은 고려하지 말고, --log-bin옵션으로 서버를 구동해서 바이너리 로깅을 활성화 시켰다고 가정하자. ( Section 5.12.3, “The Binary Log” 참조.) 만일 바이너리 로그가 활성화 되지 않는다면, 리플리케이션은 불가능하게 되거나, 또는 데이터 복구를 위한 바이너리 로그가 불가능하게 된다.
MySQL 5.0 에서 스토어드 루틴 로깅의 개발은 아래와 같이 요약할 수 있다 :
MySQL 5.0.6 이전 버전 : 스토어드 루틴 로깅의 초기 실행에서, 스토어드 루틴과 CALL 명령문을 생성하는 명령문은 로그 되지 않음. 이러한 누락은 리플리케이션과 데이터 복구에 문제를 야기할 수 있다.
MySQL 5.0.6 : 스토어드 루틴과 CALL 명령문을 생성하는 명령문은 로그 되어짐. 스토어드 함수 호출은 데이터를 업데이트 하도록 하는 명령문이 실행될 때 로그 되어 진다 (이러한 명령문들이 로그 되어졌기 때문에). 하지만, 비록 함수 자체에서 데이터의 변경이 이루어 진다 하더라도, 데이터를 변경시키지 않는 SELECT와 같은 명령문이 실행될 때에는 로그 되어지지 않는다; 이것은 문제를 일으키게 된다. 어떤 환경에서는, 서로 다른 시간 또는 서로 다른(서버 및 슬레이브)기계에서 함수 및 프로시저가 실행된다면 서로 다른 영향을 받을 수 있기 때문에 데이터 복구 또는 리플리케이션 자체가 불안정할 수 있다. 이런 문제를 처리하기 위해, 안정적인 루틴의 동일성을 제공하고, 충분한 권한을 갖고 있는 사용자에 의한 행위를 제외한, 일반적으로 불안정한 루틴을 방지하기 위한 조치가 실행된다.
MySQL 5.0.12: 스토어드 함수에 대해서는, 데이터를 변경시키는 함수 호출이 SELECT와 같이 로그 되지 않는(non-logged)명령어 내에서 발생할 때, 서버는 그 함수를 호출하는 DO 명령문을 로그 시킴으로써 데이터가 복구되거나 슬레이브 서버에 리플리케이션되는 동안 함수가 실행되도록 한다. 스토어드 프로시저에 대해서는, 서버는 CALL 명령문을 로그 시키지 못한다. 대신에, 서버는 CALL명령문의 결과로 실행되는 프로시저에 포함되어 있는 개별 명령문은 로그 시킨다. 이것은 프로시저가 마스터 서버가 아닌 슬레이브 서버상에서 서로 다른 실행 경로를 따라 실행될 때 발생할 수 있는 문제들을 제거한다.
MySQL 5.0.16: MySQL 5.0.12에서 제공하는 프로시저 로깅 변경을 통해 불안정한 루틴상의 조건문이 스토어드 프로시저에 대해 안정적으로 동작하도록 해 준다. 따라서, 이러한 조건문을 제어하는 사용자 인터페이스를 함수에 적용 되도록 수정한다. 프로시저 생성자를 더 이상 제한 할 수 없게 되는 것이다.
앞에서 설명한 변경의 결과로, 바이너리 로깅이 활성화될 때에 다음에서 설명하는 조건문을 스토어드 함수에 적용할 수 있게 된다. 이러한 조건문은 스토어드 프로시저 생성에는 적용되지 않는다.
스토어드 함수를 생성 또는 변경하기 위해서는, 일반적으로 CREATE ROUTINE 또는 ALTER ROUTINE 권한을 요구하는 것에 더불어. 반드시 SUPER 권한을 가져야 한다.
스 토어드 함수를 생성할 때에는, 그것이 확정적(deterministic)인지 또는 그것이 데이터를 수정하는 않는다는 것을 선언해야 한다. 그렇지 않으면, 그 함수는 데이터 복구 또는 리플리케이션에 대해 덜 안정한 상태가 되어 버린다. 함수의 특성 중에 두 가지가 여기에 적용된다 :
DETERMINISTIC and NOT DETERMINISTIC 특성은 함수가 주어진 입력 값에 대해 항상 동일한 결과를 만드는지 아닌지를 나타낸다. 어떤 특성도 주어지지 않으면, 디폴트는NOT DETERMINISTIC 이며, 따라서 함수를 확정적인 것으로 선언하기 위해서는 DETERMINISTIC를 확실하게 지정해 주어야 한다.
NOW() 함수(또는 동일 기능 함수) 또는 RAND()의 사용은 함수를 반드시 non-deterministic하게 만들어 주는 것은 아니다. NOW()의 경우, 바이너리 로그는 타임스탬프와 복사본은 올바르게 포함한다. 또한, RAND()도 함수내에서 일단 한번 호출 되어 지면 정확하게 복사본을 만들게 된다. (함수 실행 타임스탬프 및 무작위 수는 마스터 서버 및 슬레이브 상에 있는 동일한 암시적 입력(implicit input)으로 간주할 수 있다.)
CONTAINS SQL, NO SQL, READS SQL DATA, 및 MODIFIES SQL DATA 특성은 함수가 데이터를 읽거나 또는 쓰는 정보를 제공한다. NO SQL 또는 READS SQL DATA 는 함수가 데이터를 변경하지 않는다는 것을 나타내는 것이다. 하지만 어떠한 특성도 주어지지 않으면 디폴트가 CONTAIN SQL이 되기 때문에 반드시 이러한 것 중에 하나를 명확히 지정해 주어야 한다.
CREATE FUNCTION 명령문이 디폴트로 수용되도록 하기 위해서는, DETERMINISTIC 또는 NO SQL 및 READS SQL DATA 중에 한 개는 반드시 확실하게 표현되어야 한다. 그렇지 않으면 에러가 발생한다:
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators
variable)
함수의 특성에 대한 평가는 생성자의 “honesty”를 근거로 한다: MySQL은 DETERMINISTIC으로 선언된 함수가 non-deterministic결과를 만드는 명령문을 갖고 있지 않음을 검사하지 않는다.
함 수 생성(SUPER권한이 있어야 하며 함수가 deterministic으로 선언되거나 또는 데이터를 수정하지 않아야 함)에서 앞에서 언급한 조건을 피하기 위해서는, 글로벌 시스템 변수log_bin_trust_function_creators 를 1로 설정 해야 한다. 디폴트로는, 이 변수의 값은 0이지만, 아래와 같이 변경할 수 있다:
· mysql> SET GLOBAL log_bin_trust_function_creators = 1;
또한, 이 변수는 서버를 구동할 때 --log-bin-trust-function-creators 옵션을 사용해서 설정할 수 있다.
만일 바이너리 로깅이 활성화되지 않으면, log_bin_trust_function_creators 는 적용되지 않으며 루틴 생성을 위한 SUPER는 필요 없게 된다.
트 리거는 스토어드 함수와 유사하고, 따라서 앞에서 언급한 주의 사항 역시 트리거에도 적용된다. 트리거에 대한 예외적인 사항은 다음과 같다: CREATE TRIGGER 는 옵셔널(optional) DETERMINISTIC 특성을 갖지 않기 때문에, 트리거는 항상 deterministic으로 간주된다. 하지만, 이러한 가정은 어떤 경우에서는 틀릴 수도 있다. 예를 들면, UUID() 함수는non-deterministic (그리고 복사되지 않음)이다. 이러한 함수를 트리거에 사용할 경우에는 주의하여야 한다.
트리거는 테이블을 업데이트할 수 있으며(MySQL 5.0.10 버전 현재), 따라서 SUPER권한이 없고 log_bin_trust_function_creators 의 값이 0 이라면, CREATE TRIGGER 과 함께 나타나는 스토어드 함수에 대한 에러 메시지와 비슷한 에러가 발생하게 된다.
이 섹션의 나머지에서는 스토어드 루틴 로깅 개발에 대해서 상세하게 설명하기로 한다. 상세 설명 중에 몇 가지는 현재 스토어드 루틴 사용에서 로깅 관련 조건문에 대한 이론적인 기본 지식을 제공하여 준다.
MySQL 5.0.6 이전 버전에서 루틴 로깅: 스토어드 루틴을 생성하고 사용하는 명령문이 바이너리 로그되는 것이 아니라, 스토어드 루틴내에서 선언된 명령문이 로그되어 진다. 아래의 명령문을 작성 하였다고 가정하자:
CREATE PROCEDURE mysp INSERT INTO t VALUES(1);
CALL mysp;
이 예에서 보면, INSERT 명령문만이 바이너리 로그에서 나타난다. CREATE PROCEDURE 와 CALL 명령문은 나타나지 않는다. 바이너리 로그에서 루틴 관련(routine-related) 명령문이 없다는 것은 스토어드 루틴이 올바르게 복사되지 않았다는 것을 의미한다. 이것은 또한 데이터 복구 동작에 대해, 바이너리 로그에 있는 이벤트의 재실행은 스토어드 루틴를 복구시키지 않는다는 것을 의미하기도 한다.
MySQL 5.0.6에서 루틴 로깅 변경: 스토어드 루틴 생성과 CALL 명령문(그리고 관련된 리플리케이션 및 데이터 복구 문제)에 대한 로깅 부재를 연결(address)하기 위해, 스토어드 루틴에 대한 바이너리 로깅의 특성은 여기에서 설명하였듯이 변경되었다. (아래의 리스트중에 몇 가지 항목은 다음 버전에서 다루어지기 때문에 제외한다.)
서버는CREATE PROCEDURE, CREATE FUNCTION, ALTER PROCEDURE, ALTER FUNCTION, DROP PROCEDURE, 및 DROP FUNCTION 명령문을 바이너리 로그에 쓴다. 또한, 서버는 프로시저내에서 실행되는 명령문이 아닌, CALL 명령문을 로그 한다. 아래의 명령문을 작성하였다고 가정하자:
· CREATE PROCEDURE mysp INSERT INTO t VALUES(1);
· CALL mysp;
이 예문에서 보면, CREATE PROCEDURE 및 CALL 명령문은 바이너리 로그에서 나타나지만, INSERT 명령문은 나타나지 않는다. 이것은 MySQL 5.0.6 이전 버전에서 발생했던 INSERT만 로그되는 문제를 해결해 준다.
CALL 명령문 로깅은 리플리케이션에 대한 보안 문제를 갖게 되는데, 두 가지 요소로 인해 이런 문제가 생긴다:
프로시저가 마스터와 슬레이브 서버상에 있는 서로 다른 실행 경로를 따라갈 수 있게 한다.
슬레이브에서 실행되는 명령문은 전체 권한을 갖고 있는 슬레이브 SQL 쓰레드(Thread)에 의해 실행된다.
비 록 사용자가 루틴을 생성하기 위해서는 반드시 CREATE ROUTINE권한을 가져야 함을 의미 하지만, 전체 권한을 갖는 SQL 쓰레드가 실행하는 명령문이 있는 슬레이브 위에서만 실행될 위험한 명령문을 작성할 수 있다. 예를 들면, 마스터 와 슬레이브 서버가 서버 ID 1과 2를 갖고 있다면, 마스터 서버의 사용자는 아래와 같이 불안정한 프로시저 unsafe_sp() 를 생성해서 호출할 수 있다:
mysql> delimiter //
mysql> CREATE PROCEDURE unsafe_sp ()
-> BEGIN
-> IF @@server_id=2 THEN DROP DATABASE accounting; END IF;
-> END;
-> //
mysql> delimiter ;
mysql> CALL unsafe_sp();
CREATE PROCEDURE 와 CALL 명령문은 바이너리 로그를 작성할 수 있고, 따라서 슬레이브는 이것을 실행할 수 있다. 슬레이브 SQL 쓰레드는 전체 권한이 있기 때문에, accounting 데이터 베이스를 끝내는(drop) DROP DATABASE 명령문을 실행한다. 따라서, CALL 명령문은 마스터와 슬레이브에서 서로 다른 영향을 받게 되고, 이것은 리플리케이션이 안전하게 이루어 지지 않게 된다.
앞선 예문은 스토어드 프로시저를 사용하고 있으나, 바이너리 로그를 작성하는 명령문 내에서 호출되는 스토어드 함수에 대해서도 비슷한 문제가 발생한다: 함수 호출은 마스터와 슬레이브에 서로 다른 효과를 나타낸다.
바 이너리 로깅을 갖는 서버에 대해 이러한 위험을 피하도록 하기 위해, MySQL 5.0.6은 스토어드 프로시저와 함수는 반드시 일반적인 CREATE ROUTINE 권한을 요구하는 것과 아울러 SUPER권한을 갖도록 한다. 비슷하게, ALTER PROCEDURE 또는 ALTER FUNCTION을 사용하기 위해서는, ALTER ROUTINE
리 플리케이션의 경우, 마스터 서버는 자신의 바이너리 로그에 포함된 이벤트를 자신의 슬레이브에 전달하는데, 이렇게 함으로서 서버에서 이루어진 데이터의 변경과 동일한 내용을 전달된 이벤트가 실행하도록 하게 한다 . Section 6.2, “리플리케이션 구현 소개” 참조.
특정 데이터 복구 동작은 바이너리 로그의 사용을 요구한다. 백업 파일이 재저장된 후에, 백업이 이루어진 후 기록된 바이너리 로그에 있는 이벤트가 재 실행된다. 이러한 이벤트들은 백업이 이루어지는 시점부터 데이터 베이스를 갱신하게 낸다. Section 5.10.2.2, “복구를 위한 백업 사용하기” 참조.
이 섹션에서는 MySQL 5.0에 있는 스토어드 루틴(프로시저 및 함수)와 트리거에 관련된 바이너리 로깅의 개발을 설명하도록 하겠다. 첫 번째로는 로깅 실행에서 발생하는 변경 사항에 대해 정리를 할 것이고, 그 다음에는 스토어드 루틴의 사용에서 있게 되는 실행의 현재 조건문(current condition)에 대해 언급하기로 한다. 마지막으로, 언제 그리고 어떻게 다앵한 변경이 만들어 지는지에 대한 정보를 제공하는 실행에 대한 자세한 내용을 설명하겠다. 이러한 상세 내용들은 현재의 로깅 행위에 관련된 몇몇 사항이 이전 버전과는 어떻게 달리 실행되는지를 보여 준다.
일반적으로, 여기에서 설명하는 논제들은 바이너리 로깅이 SQL명령문 레벨에서 생긴다는 사실에서부터 출발한다. 향후의 MySQL버전은 로우-레벨(row-level) 바이너리 로깅을 실행할 예정이며, SQL명령문의 실행으로 인해 각 개별 열(row)을 변경시키는 것을 열거할 것이다.
다른 것들은 고려하지 말고, --log-bin옵션으로 서버를 구동해서 바이너리 로깅을 활성화 시켰다고 가정하자. ( Section 5.12.3, “The Binary Log” 참조.) 만일 바이너리 로그가 활성화 되지 않는다면, 리플리케이션은 불가능하게 되거나, 또는 데이터 복구를 위한 바이너리 로그가 불가능하게 된다.
MySQL 5.0 에서 스토어드 루틴 로깅의 개발은 아래와 같이 요약할 수 있다 :
MySQL 5.0.6 이전 버전 : 스토어드 루틴 로깅의 초기 실행에서, 스토어드 루틴과 CALL 명령문을 생성하는 명령문은 로그 되지 않음. 이러한 누락은 리플리케이션과 데이터 복구에 문제를 야기할 수 있다.
MySQL 5.0.6 : 스토어드 루틴과 CALL 명령문을 생성하는 명령문은 로그 되어짐. 스토어드 함수 호출은 데이터를 업데이트 하도록 하는 명령문이 실행될 때 로그 되어 진다 (이러한 명령문들이 로그 되어졌기 때문에). 하지만, 비록 함수 자체에서 데이터의 변경이 이루어 진다 하더라도, 데이터를 변경시키지 않는 SELECT와 같은 명령문이 실행될 때에는 로그 되어지지 않는다; 이것은 문제를 일으키게 된다. 어떤 환경에서는, 서로 다른 시간 또는 서로 다른(서버 및 슬레이브)기계에서 함수 및 프로시저가 실행된다면 서로 다른 영향을 받을 수 있기 때문에 데이터 복구 또는 리플리케이션 자체가 불안정할 수 있다. 이런 문제를 처리하기 위해, 안정적인 루틴의 동일성을 제공하고, 충분한 권한을 갖고 있는 사용자에 의한 행위를 제외한, 일반적으로 불안정한 루틴을 방지하기 위한 조치가 실행된다.
MySQL 5.0.12: 스토어드 함수에 대해서는, 데이터를 변경시키는 함수 호출이 SELECT와 같이 로그 되지 않는(non-logged)명령어 내에서 발생할 때, 서버는 그 함수를 호출하는 DO 명령문을 로그 시킴으로써 데이터가 복구되거나 슬레이브 서버에 리플리케이션되는 동안 함수가 실행되도록 한다. 스토어드 프로시저에 대해서는, 서버는 CALL 명령문을 로그 시키지 못한다. 대신에, 서버는 CALL명령문의 결과로 실행되는 프로시저에 포함되어 있는 개별 명령문은 로그 시킨다. 이것은 프로시저가 마스터 서버가 아닌 슬레이브 서버상에서 서로 다른 실행 경로를 따라 실행될 때 발생할 수 있는 문제들을 제거한다.
MySQL 5.0.16: MySQL 5.0.12에서 제공하는 프로시저 로깅 변경을 통해 불안정한 루틴상의 조건문이 스토어드 프로시저에 대해 안정적으로 동작하도록 해 준다. 따라서, 이러한 조건문을 제어하는 사용자 인터페이스를 함수에 적용 되도록 수정한다. 프로시저 생성자를 더 이상 제한 할 수 없게 되는 것이다.
앞에서 설명한 변경의 결과로, 바이너리 로깅이 활성화될 때에 다음에서 설명하는 조건문을 스토어드 함수에 적용할 수 있게 된다. 이러한 조건문은 스토어드 프로시저 생성에는 적용되지 않는다.
스토어드 함수를 생성 또는 변경하기 위해서는, 일반적으로 CREATE ROUTINE 또는 ALTER ROUTINE 권한을 요구하는 것에 더불어. 반드시 SUPER 권한을 가져야 한다.
스 토어드 함수를 생성할 때에는, 그것이 확정적(deterministic)인지 또는 그것이 데이터를 수정하는 않는다는 것을 선언해야 한다. 그렇지 않으면, 그 함수는 데이터 복구 또는 리플리케이션에 대해 덜 안정한 상태가 되어 버린다. 함수의 특성 중에 두 가지가 여기에 적용된다 :
DETERMINISTIC and NOT DETERMINISTIC 특성은 함수가 주어진 입력 값에 대해 항상 동일한 결과를 만드는지 아닌지를 나타낸다. 어떤 특성도 주어지지 않으면, 디폴트는NOT DETERMINISTIC 이며, 따라서 함수를 확정적인 것으로 선언하기 위해서는 DETERMINISTIC를 확실하게 지정해 주어야 한다.
NOW() 함수(또는 동일 기능 함수) 또는 RAND()의 사용은 함수를 반드시 non-deterministic하게 만들어 주는 것은 아니다. NOW()의 경우, 바이너리 로그는 타임스탬프와 복사본은 올바르게 포함한다. 또한, RAND()도 함수내에서 일단 한번 호출 되어 지면 정확하게 복사본을 만들게 된다. (함수 실행 타임스탬프 및 무작위 수는 마스터 서버 및 슬레이브 상에 있는 동일한 암시적 입력(implicit input)으로 간주할 수 있다.)
CONTAINS SQL, NO SQL, READS SQL DATA, 및 MODIFIES SQL DATA 특성은 함수가 데이터를 읽거나 또는 쓰는 정보를 제공한다. NO SQL 또는 READS SQL DATA 는 함수가 데이터를 변경하지 않는다는 것을 나타내는 것이다. 하지만 어떠한 특성도 주어지지 않으면 디폴트가 CONTAIN SQL이 되기 때문에 반드시 이러한 것 중에 하나를 명확히 지정해 주어야 한다.
CREATE FUNCTION 명령문이 디폴트로 수용되도록 하기 위해서는, DETERMINISTIC 또는 NO SQL 및 READS SQL DATA 중에 한 개는 반드시 확실하게 표현되어야 한다. 그렇지 않으면 에러가 발생한다:
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators
variable)
함수의 특성에 대한 평가는 생성자의 “honesty”를 근거로 한다: MySQL은 DETERMINISTIC으로 선언된 함수가 non-deterministic결과를 만드는 명령문을 갖고 있지 않음을 검사하지 않는다.
함 수 생성(SUPER권한이 있어야 하며 함수가 deterministic으로 선언되거나 또는 데이터를 수정하지 않아야 함)에서 앞에서 언급한 조건을 피하기 위해서는, 글로벌 시스템 변수log_bin_trust_function_creators 를 1로 설정 해야 한다. 디폴트로는, 이 변수의 값은 0이지만, 아래와 같이 변경할 수 있다:
· mysql> SET GLOBAL log_bin_trust_function_creators = 1;
또한, 이 변수는 서버를 구동할 때 --log-bin-trust-function-creators 옵션을 사용해서 설정할 수 있다.
만일 바이너리 로깅이 활성화되지 않으면, log_bin_trust_function_creators 는 적용되지 않으며 루틴 생성을 위한 SUPER는 필요 없게 된다.
트 리거는 스토어드 함수와 유사하고, 따라서 앞에서 언급한 주의 사항 역시 트리거에도 적용된다. 트리거에 대한 예외적인 사항은 다음과 같다: CREATE TRIGGER 는 옵셔널(optional) DETERMINISTIC 특성을 갖지 않기 때문에, 트리거는 항상 deterministic으로 간주된다. 하지만, 이러한 가정은 어떤 경우에서는 틀릴 수도 있다. 예를 들면, UUID() 함수는non-deterministic (그리고 복사되지 않음)이다. 이러한 함수를 트리거에 사용할 경우에는 주의하여야 한다.
트리거는 테이블을 업데이트할 수 있으며(MySQL 5.0.10 버전 현재), 따라서 SUPER권한이 없고 log_bin_trust_function_creators 의 값이 0 이라면, CREATE TRIGGER 과 함께 나타나는 스토어드 함수에 대한 에러 메시지와 비슷한 에러가 발생하게 된다.
이 섹션의 나머지에서는 스토어드 루틴 로깅 개발에 대해서 상세하게 설명하기로 한다. 상세 설명 중에 몇 가지는 현재 스토어드 루틴 사용에서 로깅 관련 조건문에 대한 이론적인 기본 지식을 제공하여 준다.
MySQL 5.0.6 이전 버전에서 루틴 로깅: 스토어드 루틴을 생성하고 사용하는 명령문이 바이너리 로그되는 것이 아니라, 스토어드 루틴내에서 선언된 명령문이 로그되어 진다. 아래의 명령문을 작성 하였다고 가정하자:
CREATE PROCEDURE mysp INSERT INTO t VALUES(1);
CALL mysp;
이 예에서 보면, INSERT 명령문만이 바이너리 로그에서 나타난다. CREATE PROCEDURE 와 CALL 명령문은 나타나지 않는다. 바이너리 로그에서 루틴 관련(routine-related) 명령문이 없다는 것은 스토어드 루틴이 올바르게 복사되지 않았다는 것을 의미한다. 이것은 또한 데이터 복구 동작에 대해, 바이너리 로그에 있는 이벤트의 재실행은 스토어드 루틴를 복구시키지 않는다는 것을 의미하기도 한다.
MySQL 5.0.6에서 루틴 로깅 변경: 스토어드 루틴 생성과 CALL 명령문(그리고 관련된 리플리케이션 및 데이터 복구 문제)에 대한 로깅 부재를 연결(address)하기 위해, 스토어드 루틴에 대한 바이너리 로깅의 특성은 여기에서 설명하였듯이 변경되었다. (아래의 리스트중에 몇 가지 항목은 다음 버전에서 다루어지기 때문에 제외한다.)
서버는CREATE PROCEDURE, CREATE FUNCTION, ALTER PROCEDURE, ALTER FUNCTION, DROP PROCEDURE, 및 DROP FUNCTION 명령문을 바이너리 로그에 쓴다. 또한, 서버는 프로시저내에서 실행되는 명령문이 아닌, CALL 명령문을 로그 한다. 아래의 명령문을 작성하였다고 가정하자:
· CREATE PROCEDURE mysp INSERT INTO t VALUES(1);
· CALL mysp;
이 예문에서 보면, CREATE PROCEDURE 및 CALL 명령문은 바이너리 로그에서 나타나지만, INSERT 명령문은 나타나지 않는다. 이것은 MySQL 5.0.6 이전 버전에서 발생했던 INSERT만 로그되는 문제를 해결해 준다.
CALL 명령문 로깅은 리플리케이션에 대한 보안 문제를 갖게 되는데, 두 가지 요소로 인해 이런 문제가 생긴다:
프로시저가 마스터와 슬레이브 서버상에 있는 서로 다른 실행 경로를 따라갈 수 있게 한다.
슬레이브에서 실행되는 명령문은 전체 권한을 갖고 있는 슬레이브 SQL 쓰레드(Thread)에 의해 실행된다.
비 록 사용자가 루틴을 생성하기 위해서는 반드시 CREATE ROUTINE권한을 가져야 함을 의미 하지만, 전체 권한을 갖는 SQL 쓰레드가 실행하는 명령문이 있는 슬레이브 위에서만 실행될 위험한 명령문을 작성할 수 있다. 예를 들면, 마스터 와 슬레이브 서버가 서버 ID 1과 2를 갖고 있다면, 마스터 서버의 사용자는 아래와 같이 불안정한 프로시저 unsafe_sp() 를 생성해서 호출할 수 있다:
mysql> delimiter //
mysql> CREATE PROCEDURE unsafe_sp ()
-> BEGIN
-> IF @@server_id=2 THEN DROP DATABASE accounting; END IF;
-> END;
-> //
mysql> delimiter ;
mysql> CALL unsafe_sp();
CREATE PROCEDURE 와 CALL 명령문은 바이너리 로그를 작성할 수 있고, 따라서 슬레이브는 이것을 실행할 수 있다. 슬레이브 SQL 쓰레드는 전체 권한이 있기 때문에, accounting 데이터 베이스를 끝내는(drop) DROP DATABASE 명령문을 실행한다. 따라서, CALL 명령문은 마스터와 슬레이브에서 서로 다른 영향을 받게 되고, 이것은 리플리케이션이 안전하게 이루어 지지 않게 된다.
앞선 예문은 스토어드 프로시저를 사용하고 있으나, 바이너리 로그를 작성하는 명령문 내에서 호출되는 스토어드 함수에 대해서도 비슷한 문제가 발생한다: 함수 호출은 마스터와 슬레이브에 서로 다른 효과를 나타낸다.
바 이너리 로깅을 갖는 서버에 대해 이러한 위험을 피하도록 하기 위해, MySQL 5.0.6은 스토어드 프로시저와 함수는 반드시 일반적인 CREATE ROUTINE 권한을 요구하는 것과 아울러 SUPER권한을 갖도록 한다. 비슷하게, ALTER PROCEDURE 또는 ALTER FUNCTION을 사용하기 위해서는, ALTER ROUTINE
