Loading...
MySQL 9.5 Reference Manual 9.5의 27.9 Stored Program Binary Logging의 한국어 번역본입니다.
아래의 경우에 피드백에서 신고해주신다면 반영하겠습니다.
감사합니다 :)
binary log에는 데이터베이스 내용을 변경하는 SQL 문에 대한 정보가 포함됩니다. 이 정보는 변경 내용을 설명하는 “event” 형식으로 저장됩니다. (binary log event는 scheduled event 저장 객체와는 다릅니다.) binary log는 두 가지 중요한 목적을 가집니다:
replication을 위해, binary log는 source replication 서버에서 replica 서버로 전송할 문장의 기록으로 사용됩니다. source는 자신의 binary log에 포함된 event를 replica로 전송하고, replica는 이 event를 실행하여 source에서 이루어진 것과 동일한 데이터 변경을 수행합니다. 자세한 내용은 Section 19.2, “Replication Implementation”을 참고하십시오.
특정 데이터 복구 작업에는 binary log 사용이 필요합니다. 백업 파일을 복원한 후, 백업 생성 이후에 binary log에 기록된 event를 다시 실행합니다. 이 event는 백업 시점부터 데이터베이스를 최신 상태로 만듭니다. 자세한 내용은 Section 9.3.2, “Using Backups for Recovery”를 참고하십시오.
그러나 logging이 문장 레벨에서 발생하는 경우, 저장 프로그램(Stored Procedure 및 function, trigger, event)에 관해 특정 binary logging 이슈가 있습니다:
어떤 경우에는 하나의 문장이 source와 replica에서 서로 다른 row 집합에 영향을 미칠 수 있습니다.
replica에서 실행되는 replicated 문장은 replica의 applier 스레드에 의해 처리됩니다. replication 권한 검사를 구현하지 않는 한(자세한 내용은 Section 19.3.3, “Replication Privilege Checks” 참고), applier 스레드는 모든 권한을 가집니다. 이런 상황에서는 procedure가 source와 replica 서버에서 서로 다른 execution path를 따를 수 있으므로, 사용자가 replica에서만 실행되는 위험한 문장을 포함한 routine을 작성할 수 있습니다.
데이터를 변경하는 저장 프로그램이 비결정적(nondeterministic)인 경우, 그 프로그램은 반복 실행 가능하지 않습니다. 이로 인해 source와 replica의 데이터가 달라지거나, 복원된 데이터가 원래 데이터와 다르게 될 수 있습니다.
이 절에서는 MySQL이 저장 프로그램에 대해 binary logging을 어떻게 처리하는지 설명합니다. 구현이 저장 프로그램 사용에 대해 현재 어떤 제약을 가하는지, 그리고 logging 문제를 피하기 위해 무엇을 할 수 있는지 기술합니다. 또한 이러한 조건이 존재하는 이유에 대한 추가 정보도 제공합니다.
별도의 언급이 없는 한, 여기의 설명은 서버에서 binary logging이 활성화되어 있다고 가정합니다( Section 7.4.4, “The Binary Log” 참고). binary log가 활성화되어 있지 않으면 replication은 불가능하며, 데이터 복구를 위한 binary log도 사용할 수 없습니다. binary logging은 기본적으로 활성화되어 있으며, 서버를 시작할 때 --skip-log-bin 또는 --disable-log-bin으로 시작하는 경우에만 비활성화됩니다.
일반적으로, 여기서 설명하는 이슈는 binary logging이 SQL 문장 레벨에서 발생할 때(statement-based binary logging) 나타납니다. row-based binary logging을 사용하는 경우, log에는 SQL 문 실행 결과로 변경된 개별 row에 대한 변경 내용이 포함됩니다. routine이나 trigger가 실행될 때는 변경을 발생시킨 문장이 아니라 row 변경이 log에 기록됩니다. 저장 프로시저의 경우, 이는 CALL 문은 log에 기록되지 않는다는 의미입니다. 저장 함수의 경우, 함수 호출이 아니라 함수 내에서 수행된 row 변경이 log에 기록됩니다. trigger의 경우, trigger가 수행한 row 변경이 log에 기록됩니다. replica 측에서는 저장 프로그램 호출이 아니라 row 변경만 보이게 됩니다.
Mixed format binary logging( binlog_format=MIXED)은, row-based binary logging만이 올바른 결과를 보장하는 경우를 제외하고, statement-based binary logging을 사용합니다. mixed format에서 저장 함수, 저장 프로시저, trigger, event, prepared statement가 statement-based binary logging에 안전하지 않은 무언가를 포함하는 경우, 전체 문장은 unsafe로 표시되고 row format으로 logging됩니다. procedure, function, trigger, event를 생성하고 삭제하는 데 사용되는 문장은 항상 안전하며, statement format으로 logging됩니다. row-based, mixed, statement-based logging 및 safe/unsafe 문장이 어떻게 결정되는지에 대한 자세한 내용은 Section 19.2.1, “Replication Formats”를 참고하십시오.
MySQL에서 저장 함수 사용에 대한 조건은 다음과 같이 요약할 수 있습니다. 이러한 조건은 저장 프로시저 또는 Event Scheduler event에는 적용되지 않으며, binary logging이 활성화되어 있지 않으면 적용되지 않습니다.
저장 함수를 생성하거나 변경하려면, 일반적으로 필요한 CREATE ROUTINE 또는 ALTER ROUTINE 권한 이외에 SET_ANY_DEFINER 권한이 필요합니다. (함수 정의의 DEFINER 값에 따라, binary logging이 활성화되어 있는지 여부와 상관없이 SET_ANY_DEFINER가 필요할 수 있습니다. 자세한 내용은 Section 15.1.21, “CREATE PROCEDURE and CREATE FUNCTION Statements” 참고.)
저장 함수를 생성할 때, 결정적(deterministic)이거나 데이터를 변경하지 않는다는 점을 선언해야 합니다. 그렇지 않으면 데이터 복구나 replication에 unsafe할 수 있습니다.
기본적으로, CREATE FUNCTION 문이 허용되기 위해서는 DETERMINISTIC, NO SQL, READS SQL DATA 중 하나 이상을 명시적으로 지정해야 합니다. 그렇지 않으면 오류가 발생합니다:
1ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, 2or READS SQL DATA in its declaration and binary logging is enabled 3(you *might* want to use the less safe log_bin_trust_function_creators 4variable)
다음 함수는 결정적이며(그리고 데이터를 변경하지 않으므로), 안전합니다:
1CREATE FUNCTION f1(i INT) 2RETURNS INT 3DETERMINISTIC 4READS SQL DATA 5BEGIN 6 RETURN i; 7END;
다음 함수는 UUID()를 사용하며, 이는 결정적이지 않으므로, 함수 역시 결정적이지 않고 안전하지 않습니다:
1CREATE FUNCTION f2() 2RETURNS CHAR(36) CHARACTER SET utf8mb4 3BEGIN 4 RETURN UUID(); 5END;
다음 함수는 데이터를 변경하므로, 안전하지 않을 수 있습니다:
1CREATE FUNCTION f3(p_id INT) 2RETURNS INT 3BEGIN 4 UPDATE t SET modtime = NOW() WHERE id = p_id; 5 RETURN ROW_COUNT(); 6END;
함수의 특성에 대한 평가는 생성자의 “정직성(honesty)”에 기반합니다. MySQL은 DETERMINISTIC으로 선언된 함수가 비결정적 결과를 생성하는 문장으로부터 자유로운지 검사하지 않습니다.
저장 함수를 실행하려고 시도할 때, binlog_format=STATEMENT이 설정되어 있으면 함수 정의에 DETERMINISTIC 키워드가 지정되어 있어야 합니다. 그렇지 않은 경우 오류가 발생하고, 아래에서 설명하는 대로 log_bin_trust_function_creators=1을 지정하여 이 검사를 무시하지 않는 한 함수는 실행되지 않습니다. 재귀 함수 호출의 경우, 가장 바깥쪽 호출에만 DETERMINISTIC 키워드가 필요합니다. row-based 또는 mixed binary logging을 사용하는 경우, 함수가 DETERMINISTIC 키워드 없이 정의되었더라도 문장은 허용되고 replication됩니다.
MySQL은 생성 시점에 함수가 실제로 결정적인지 검사하지 않기 때문에, DETERMINISTIC 키워드가 사용된 저장 함수 호출이 statement-based logging에 unsafe한 작업을 수행하거나 unsafe한 문장을 포함하는 함수 또는 프로시저를 호출할 수 있습니다. binlog_format=STATEMENT이 설정된 상태에서 이런 일이 발생하면 경고 메시지가 출력됩니다. row-based 또는 mixed binary logging을 사용하는 경우 경고는 출력되지 않고, 해당 문장은 row-based format으로 replication됩니다.
위에서 설명한 함수 생성 조건( SUPER 권한이 필요하고, 함수가 결정적이거나 데이터를 변경하지 않도록 선언되어야 한다는 조건)을 완화하려면, global log_bin_trust_function_creators 시스템 변수를 1로 설정하십시오. 기본적으로 이 변수의 값은 0이지만, 다음과 같이 변경할 수 있습니다:
1mysql> SET GLOBAL log_bin_trust_function_creators = 1;
이 변수는 서버 시작 시에도 설정할 수 있습니다.
binary logging이 활성화되어 있지 않으면, log_bin_trust_function_creators는 적용되지 않습니다. SUPER는, 앞에서 설명한 대로 함수 정의의 DEFINER 값이 이를 요구하지 않는 한, 함수 생성에 필요하지 않습니다.
Trigger는 저장 함수와 유사하므로, 함수에 대한 위의 설명은 다음 예외를 제외하고 trigger에도 적용됩니다: CREATE TRIGGER에는 선택적 DETERMINISTIC 특성이 없으므로, trigger는 항상 결정적인 것으로 간주됩니다. 그러나 이 가정은 일부 경우에 잘못될 수 있습니다. 예를 들어, UUID() 함수는 비결정적이며(replication되지 않습니다), trigger에서 이와 같은 함수를 사용할 때는 주의해야 합니다.
Trigger는 테이블을 업데이트할 수 있으므로, 필요한 권한이 없는 상태에서 CREATE TRIGGER를 사용하면 저장 함수와 유사한 오류 메시지가 발생합니다. replica 측에서는, replica가 trigger의 creator를 판단하기 위해 trigger의 DEFINER 속성을 사용합니다.
이 절의 나머지 부분에서는 logging 구현과 그 영향에 대한 추가 세부 사항을 제공합니다. 저장 routine 사용에 대한 현재 logging 관련 조건의 배경과 근거에 관심이 있는 경우에만 읽으면 됩니다. 이 설명은 첫 번째 항목( CREATE 및 DROP 문은 logging mode와 상관없이 항상 문장으로 logging된다는 점)을 제외하고, row-based logging이 아닌 statement-based logging에만 적용됩니다.
서버는 CREATE EVENT, CREATE PROCEDURE, CREATE FUNCTION, ALTER EVENT, ALTER PROCEDURE, ALTER FUNCTION, DROP EVENT, DROP PROCEDURE, DROP FUNCTION 문을 binary log에 기록합니다.
저장 함수 호출은 함수가 데이터를 변경하고, 그렇지 않으면 logging되지 않을 문장 내에서 발생하는 경우 SELECT 문으로 logging됩니다. 이는 nonlogged 문장 내에서 저장 함수를 사용한 결과 발생한 데이터 변경이 replication되지 않는 것을 방지하기 위한 것입니다. 예를 들어, SELECT 문은 binary log에 기록되지 않지만, SELECT가 변경을 수행하는 저장 함수를 호출할 수 있습니다. 이를 처리하기 위해, 지정된 함수가 변경을 수행하는 경우 SELECT func_name() 문이 binary log에 기록됩니다. source 서버에서 다음 문을 실행한다고 가정해 보겠습니다:
1CREATE FUNCTION f1(a INT) RETURNS INT 2BEGIN 3 IF (a < 3) THEN 4 INSERT INTO t2 VALUES (a); 5 END IF; 6 RETURN 0; 7END; 8 9CREATE TABLE t1 (a INT); 10INSERT INTO t1 VALUES (1),(2),(3); 11 12SELECT f1(a) FROM t1;
SELECT 문이 실행될 때, 함수 f1()은 세 번 호출됩니다. 그 중 두 번의 호출이 row를 insert하며, MySQL은 각 호출에 대해 SELECT 문을 logging합니다. 즉, MySQL은 binary log에 다음 문을 기록합니다:
1SELECT f1(1); 2SELECT f1(2);
서버는 또한 저장 함수 호출이 저장 프로시저를 호출하고, 그 프로시저가 오류를 발생시키는 경우에도 SELECT 문을 logging합니다. 이 경우, 서버는 expected error code와 함께 SELECT 문을 log에 기록합니다. replica에서 동일한 오류가 발생하면, 이는 expected result이며 replication은 계속됩니다. 그렇지 않으면 replication이 중지됩니다.
저장 함수 호출을 함수가 실행한 문장이 아니라 함수 호출 자체로 logging하는 것은 replication에 대해 보안상의 영향을 가질 수 있으며, 이는 두 가지 요인에서 비롯됩니다:
함수가 source와 replica 서버에서 서로 다른 execution path를 따를 수 있습니다.
replica에서 실행되는 문장은 replica의 applier 스레드에 의해 처리됩니다. replication 권한 검사를 구현하지 않는 한(자세한 내용은 Section 19.3.3, “Replication Privilege Checks” 참고), applier 스레드는 모든 권한을 가집니다.
그 결과, 사용자가 함수를 생성하려면 CREATE ROUTINE 권한이 필요하지만, 사용자는 replica에서만 실행되고, 모든 권한을 가진 스레드에 의해 처리되는 위험한 문장을 포함하는 함수를 작성할 수 있습니다. 예를 들어, source와 replica 서버의 server ID가 각각 1과 2인 경우, source 서버의 사용자는 다음과 같이 unsafe한 함수 unsafe_func()를 생성하고 호출할 수 있습니다:
1mysql> delimiter // 2mysql> CREATE FUNCTION unsafe_func () RETURNS INT 3 -> BEGIN 4 -> IF @@server_id=2 THEN dangerous_statement; END IF; 5 -> RETURN 1; 6 -> END; 7 -> // 8mysql> delimiter ; 9mysql> INSERT INTO t VALUES(unsafe_func());
CREATE FUNCTION 및 INSERT 문은 binary log에 기록되므로, replica는 이들을 실행합니다. replica의 applier 스레드는 모든 권한을 가지고 있으므로, dangerous 문장을 실행합니다. 이로 인해 함수 호출이 source와 replica에서 서로 다른 효과를 가지게 되고, replication-safe하지 않게 됩니다.
binary logging이 활성화된 서버에서 이러한 위험을 방지하기 위해, 저장 함수 생성자는 일반적으로 필요한 CREATE ROUTINE 권한 외에 SUPER 권한도 가져야 합니다. 마찬가지로, ALTER FUNCTION을 사용하려면, ALTER ROUTINE 권한 외에 SUPER 권한이 필요합니다. SUPER 권한이 없으면 오류가 발생합니다:
1ERROR 1419 (HY000): You do not have the SUPER privilege and 2binary logging is enabled (you *might* want to use the less safe 3log_bin_trust_function_creators variable)
함수 생성자에게 SUPER 권한을 요구하고 싶지 않은 경우(예를 들어, 시스템에서 CREATE ROUTINE 권한을 가진 사용자가 모두 숙련된 애플리케이션 개발자인 경우), global log_bin_trust_function_creators 시스템 변수를 1로 설정하십시오. 이 변수는 서버 시작 시에도 설정할 수 있습니다. binary logging이 활성화되어 있지 않으면, log_bin_trust_function_creators는 적용되지 않습니다. SUPER는, 앞에서 설명한 대로 함수 정의의 DEFINER 값이 이를 요구하지 않는 한, 함수 생성에 필요하지 않습니다.
함수 생성자에 대한 권한 선택과는 상관없이, replication 권한 검사 사용이 권장됩니다. replication 권한 검사는 replication 채널에 대해 예상되고 관련 있는 작업만 허용되도록 설정할 수 있습니다. 이를 설정하는 방법은 Section 19.3.3, “Replication Privilege Checks”를 참고하십시오.
update를 수행하는 함수가 비결정적이면 반복 실행 가능하지 않습니다. 이는 두 가지 바람직하지 않은 결과를 초래할 수 있습니다:
replica가 source와 다르게 됩니다.
복원된 데이터가 원래 데이터와 일치하지 않습니다.
이 문제를 해결하기 위해, MySQL은 다음 요구 사항을 강제합니다: source 서버에서 함수의 생성 및 변경은 해당 함수가 결정적이거나 데이터를 변경하지 않는다고 선언하지 않는 한 거부됩니다. 여기에는 두 가지 함수 characteristic 집합이 적용됩니다:
DETERMINISTIC 및 NOT DETERMINISTIC characteristic은 함수가 동일한 입력에 대해 항상 동일한 결과를 생성하는지 여부를 나타냅니다. 어느 characteristic도 지정되지 않으면 기본값은 NOT DETERMINISTIC입니다. 함수를 결정적이라고 선언하려면 DETERMINISTIC을 명시적으로 지정해야 합니다.
CONTAINS SQL, NO SQL, READS SQL DATA, MODIFIES SQL DATA characteristic은 함수가 데이터를 읽거나 쓰는지에 대한 정보를 제공합니다. NO SQL 또는 READS SQL DATA는 함수가 데이터를 변경하지 않음을 나타내지만, characteristic이 지정되지 않으면 기본값이 CONTAINS SQL이므로 둘 중 하나를 명시적으로 지정해야 합니다.
기본적으로, CREATE FUNCTION 문이 허용되기 위해서는 DETERMINISTIC, NO SQL, READS SQL DATA 중 하나 이상을 명시적으로 지정해야 합니다. 그렇지 않으면 오류가 발생합니다:
1ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, 2or READS SQL DATA in its declaration and binary logging is enabled 3(you *might* want to use the less safe log_bin_trust_function_creators 4variable)
log_bin_trust_function_creators를 1로 설정하면, 함수가 결정적이거나 데이터를 변경하지 않아야 한다는 요구 사항은 제거됩니다.
CALL 레벨이 아니라 문장 레벨에서 logging됩니다. 즉, 서버는 CALL 문을 log에 기록하지 않고, 실제로 실행되는 프로시저 내부의 문장을 logging합니다. 그 결과, source 서버에서 발생하는 변경과 동일한 변경이 replica에서도 발생합니다. 이는 프로시저가 서로 다른 머신에서 서로 다른 execution path를 갖는 경우에 발생할 수 있는 문제를 방지합니다.일반적으로, 저장 프로시저 내에서 실행되는 문장은 단독으로 실행되는 경우에 적용되는 것과 동일한 규칙을 사용하여 binary log에 기록됩니다. 하지만 프로시저 컨텍스트에서의 문장 실행은 nonprocedure 컨텍스트와 약간 다르므로, 프로시저 문장을 logging할 때는 특별한 주의가 필요합니다:
logging될 문장은 프로시저 로컬 변수에 대한 reference를 포함할 수 있습니다. 이러한 변수는 저장 프로시저 컨텍스트 밖에서는 존재하지 않으므로, 해당 변수를 참조하는 문장은 literal하게 logging될 수 없습니다. 대신, logging 목적을 위해 로컬 변수에 대한 각 reference는 다음과 같은 construct로 대체됩니다:
1NAME_CONST(var_name, var_value)
_var_name_은 로컬 변수 이름이고, _var_value_는 문장이 logging될 때 변수가 가진 값을 나타내는 상수입니다. NAME_CONST()의 값은 _var_value_이고, “이름”은 _var_name_입니다. 따라서 이 함수를 직접 호출하면 다음과 같은 결과를 얻습니다:
1mysql> SELECT NAME_CONST('myname', 14); 2+--------+ 3| myname | 4+--------+ 5| 14 | 6+--------+
NAME_CONST()는 logged standalone 문장이 replica에서 source에서 저장 프로시저 내에서 실행된 original 문장과 동일한 효과를 갖도록 실행될 수 있게 해줍니다.
NAME_CONST()의 사용은 source column expression이 로컬 변수를 참조하는 CREATE TABLE ... SELECT 문에서 문제를 일으킬 수 있습니다. 이러한 reference를 NAME_CONST() 표현식으로 변환하면, source와 replica 서버에서 서로 다른 column name이 생성되거나, legal한 column identifier로 사용하기에는 너무 긴 이름이 생성될 수 있습니다. 이를 해결하는 방법은 로컬 변수를 참조하는 column에 alias를 제공하는 것입니다. 예를 들어, myvar의 값이 1일 때 다음 문을 고려해 보십시오:
1CREATE TABLE t1 SELECT myvar;
이 문은 다음과 같이 rewrite됩니다:
1CREATE TABLE t1 SELECT NAME_CONST(myvar, 1);
source와 replica의 테이블이 동일한 column name을 갖도록 하려면 문을 다음과 같이 작성하십시오:
1CREATE TABLE t1 SELECT myvar AS myvar;
rewrite된 문은 다음과 같습니다:
1CREATE TABLE t1 SELECT NAME_CONST(myvar, 1) AS myvar;
logging될 문장은 사용자 정의 변수에 대한 reference를 포함할 수 있습니다. 이를 처리하기 위해, MySQL은 replica에서 변수가 source와 동일한 값으로 존재하도록 보장하기 위해 binary log에 SET 문을 기록합니다. 예를 들어, 문장이 변수 @my_var를 참조하는 경우, 해당 문장 앞에는 binary log에 다음 문이 기록되며, 여기서 _value_는 source에서 @my_var의 값입니다:
1SET @my_var = value;
프로시저 호출은 commit되거나 rollback된 트랜잭션 내에서 발생할 수 있습니다. 트랜잭션 컨텍스트는 프로시저 실행의 트랜잭션 측면이 올바르게 replication되도록 고려됩니다. 즉, 서버는 프로시저 내에서 실제로 실행되어 데이터를 변경하는 문장을 logging하고, 필요에 따라 BEGIN, COMMIT, ROLLBACK 문도 logging합니다. 예를 들어, 프로시저가 트랜잭션 테이블만 업데이트하고 rollback된 트랜잭션 내에서 실행된 경우, 해당 update는 logging되지 않습니다. 프로시저가 commit된 트랜잭션 내에서 실행된 경우, update와 함께 BEGIN 및 COMMIT 문도 logging됩니다. rollback된 트랜잭션 내에서 실행된 프로시저의 경우, 그 문장은 standalone으로 실행되는 경우에 적용되는 것과 동일한 규칙을 사용하여 logging됩니다:
저장 프로시저 호출은 저장 함수 내부에서 호출되는 경우 문장 레벨에서 binary log에 기록되지 않습니다. 이 경우 logging되는 것은 함수를 호출하는 문장(해당 문장이 logging되는 문장 내에 있는 경우)이나 DO 문(해당 호출이 logging되지 않는 문장 내에 있는 경우)뿐입니다. 이러한 이유로, 프로시저를 호출하는 저장 함수를 사용할 때는, 프로시저 자체가 안전한 경우에도 주의를 기울여야 합니다.
27.8 Stored Object Access Control
27.10 Restrictions on Stored Programs