MySQL 5.6 한글메뉴얼

20.7 Stored Programs의 Binary Logging

바이너리 로그는 데이터베이스의 내용을 변경하는 SQL 문에 대한 정보가 포함되어 있습니다. 이 정보는 변경에 적용 "이벤트"의 형식으로 저장됩니다. 바이너리 로그에는 두 가지 중요한 목적이 있습니다.

복제의 경우, 바이너리 로그는 슬레이브 서버로 전송되는 문 레코드로 마스터 복제 서버에서 사용됩니다. 마스터 서버는 바이너리 로그에 저장되는 이벤트를 슬레이브에 전송하고 슬레이브는 이러한 이벤트를 실행하여 마스터에서 실행 된 것과 동일한 데이터 변경을 실행합니다. 섹션 17.2 "복제 구현" 을 참조하십시오.
특정 데이터 복구 작업에는 바이너리 로그의 사용이 필요합니다. 백업 파일이 복원 된 후 백업 한 이후에 기록 된 바이너리 로그의 이벤트가 다시 실행됩니다. 이 이벤트는 데이터베이스를 백업 포인트에서 최신 상태로 가져갑니다. 섹션 7.3.2 "복구에 대한 백업 사용" 을 참조하십시오.

그러나 기록이 문 수준에서 행해지는 경우, 저장 프로그램 (저장 프로 시저 및 저장 함수, 트리거 이벤트)에 대해 해당 특정 바이너리 로깅 문제가 있습니다.

경우에 따라서는 문이 마스터와 슬레이브로 별도의 행 집합에 영향을 미칠 수 있습니다.
슬레이브에서 실행 된 복제 문은 완전한 권한을 가진 슬레이브 SQL 쓰레드가 처리됩니다. 프로 시저가 마스터 서버와 슬레이브 서버에서 별도의 실행 경로를 따르는 것이 가능하므로, 사용자는 슬레이브에서만 실행 완전한 권한을 가진 슬레이브 쓰레드에서 처리되는 위험한 문을 포함한 루틴을 만들 수 합니다.
데이터를 변경하는 저장 프로그램이 비 결정적이면 재현 가능하지 않습니다. 따라서 마스터와 슬레이브로 데이터가 다른 결과가되거나 복원 된 데이터가 원본 데이터와 일치하지 않거나하는 경우가 있습니다.

이 섹션에서는 MySQL 5.6의 내장 프로그램의 바이너리 로깅의 처리에 대해 설명합니다. 여기에서는 구현이 저장된 프로그램의 사용에 대해 설정된 현재의 조건과 문제를 피하기 위해 실행 가능한 대처 대해 다루고 있습니다. 또한 이러한 조건의 이유에 대한 추가 정보를 제공합니다.

일반적으로 여기에서 설명하는 문제는 SQL 문 수준에서 바이너리 로깅을 할 때 발생합니다. 행 기반의 바이너리 로깅을 사용할 경우 로그는 SQL 문을 실행 한 결과로 개별 행에 변경된 내용이 포함되어 있습니다. 루틴 또는 트리거가 실행되면 행의 변경 사항이 기록되지만, 변경을 행한 진술은 기록되지 않습니다. 저장 프로 시저의 경우, 이것은 CALL 문이 기록되지 않는 것을 의미합니다. 스토어드 함수의 경우 함수에서 열린 행의 변경 사항이 기록 된 함수 호출은 기록되지 않습니다. 트리거의 경우 트리거에 의해 만들어진 행의 변경이 기록됩니다. 슬레이브에서는 행의 변경 만 표시되고 저장 프로그램의 호출은 표시되지 않습니다. 행 기반 로깅에 대한 일반적인 내용은 섹션 17.1.2 "복제 형식" 을 참조하십시오.

별도로 명시되지 않는 한, 여기에서의 설명은 --log-bin 옵션을 사용하여 서버를 시작함으로써 바이너리 로깅을 사용하고 있다고 가정하고 있습니다. ( 섹션 5.2.4 "바이너리 로그" 를 참조하십시오.) 바이너리 로그가 유효하지 않은 경우 복제가 가능 아닌 바이너리 로그를 데이터 복구에 사용할 수 없습니다.

MySQL 5.6에서 스토어드 함수를 사용하는 현재의 조건은 다음과 같이 요약 할 수 있습니다. 이러한 조건은 저장 프로 시저 또는 이벤트 스케줄러 이벤트에는 적용되지 않고, 바이너리 로깅이 사용하지 않으면 적용되지 않습니다.

스토어드 함수를 생성 또는 변경하려면 사용자는 일반적으로 필요한 CREATE ROUTINE 권한 또는 ALTER ROUTINE 권한 이외에 SUPER 권한이 필요합니다. (함수 정의의 DEFINER 값에 따라 바이너리 로깅이 사용되는지 여부에 관계없이 SUPER 가 필요할 수 있습니다. 섹션 13.1.15 "CREATE PROCEDURE 및 CREATE FUNCTION 구문" 을 참조하십시오.)
스토어드 함수를 만들 때 함수가 결정적이라는 것 또는 데이터를 변경하지 않는다는 것을 선언해야합니다. 그렇게하지 않으면 데이터 복구 또한 복제에게는 안전하지 않을 수 있습니다.

기본적으로 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)
```
다음 함수는 결정적인 때문에 (또한 데이터를 변경하지 않습니다) 안전합니다.
```
CREATE FUNCTION f1(i INT)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
  RETURN i;
END;
```
다음 함수는 UUID() 를 사용하지만, 이것은 결정적 않기 때문에 함수도 결정적 아니라 안전하지 않습니다.
```
CREATE FUNCTION f2()
RETURNS CHAR(36) CHARACTER SET utf8
BEGIN
  RETURN UUID();
END;
```
다음 함수는 데이터를 변경하기 때문에 안전하지 않을 수 있습니다.
```
CREATE FUNCTION f3(p_id INT)
RETURNS INT
BEGIN
  UPDATE t SET modtime = NOW() WHERE id = p_id;
  RETURN ROW_COUNT();
END;
```
함수의 성질의 평가는 작성자의 「성실」에 근거하고 있습니다. MySQL은 DETERMINISTIC 으로 선언 된 함수에 비 결정적인 결과를 생성하는 문이 포함되어 있는지 여부를 확인하지 않습니다.
DETERMINISTIC 을 지정하지 않고 단호하다 스토어드 함수를 작성하는 것은 가능하지만, 명령문 기반 바이너리 로깅을 사용하여이 함수를 실행할 수 없습니다. 이러한 함수를 실행하려면 행 기반 또는 혼합 바이너리 로깅을 사용해야합니다. 또는 함수 정의에 DETERMINISTIC 명시 적으로 지정하면 명령문 기반 바이너리 로깅을 포함한 모든 종류의 로깅을 사용할 수 있습니다.
함수 작성에 관한 위의 조건 ( SUPER 권한을 가질 필요가있는 것과 함수가 결정적인지, 데이터를 변경하지 않겠다고 선언 할 필요가 있음)을 완화하려면 log_bin_trust_function_creators 글로벌 시스템 변수를 1로 설정합니다. 기본적으로이 변수는 0의 값이 설정되어 있습니다 만, 다음과 같이 변경할 수 있습니다.
```
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
```
서버 시작시 --log-bin-trust-function-creators=1 옵션을 사용하여이 변수를 설정할 수도 있습니다.

바이너리 로깅을 사용할 수없는 경우 log_bin_trust_function_creators 는 적용되지 않습니다. 전술 한 바와 같이, 함수 정의의 DEFINER 값이 필요하지 않는 한 함수의 작성에 SUPER 는 필요하지 않습니다.
복제에 안전하지 않을 수있는 (따라서 이들을 사용하는 저장 기능도 안전하지 않게됩니다) 내장 함수의 자세한 내용은 섹션 17.4.1 "복제 기능 및 문제" 를 참조하십시오.

트리거는 저장 기능과 유사하기 때문에 함수에 대한 위의 설명이 트리거에도 적용되지만 CREATE TRIGGER 는 옵션 DETERMINISTIC 특성이 없기 때문에 트리거는 항상 결정적이라고 가정된다는 점이 다릅니다. 그러나이 가정은 어떤 경우에 해제 될 수 있습니다. 예를 들어, UUID() 함수는 비 결정적입니다 (또한 복제하지 않습니다). 트리거에서 이러한 함수의 사용에주의해야합니다.

트리거는 테이블을 업데이트 할 수 있기 때문에 필요한 권한이없는 경우에는 CREATE TRIGGER 에서 스토어드 함수의 경우와 같은 오류 메시지가 표시됩니다. 슬레이브는 슬레이브는 DEFINER 트리거 속성을 사용하여 트리거 작성자하다고 생각되는 사용자를 식별합니다.

이 섹션의 나머지 부분에서는 로깅 구현과 그 의미에 대한 추가 정보에 대해 설명합니다. 스토어드 루틴의 사용에 대한 현재 로깅 관련 사항의 이론적 근거에 대한 배경에 관심이있는 경우에는 여기를 읽어 보시기 바랍니다. 이 설명은 명령문 기반 로깅에만 적용 행 기반 로깅에는 해당하지 않지만, CREATE 및 DROP 문은 로깅 모드에 관계없이 문으로 기록된다는 첫 번째 항목은 제외합니다.

서버는 CREATE EVENT , CREATE PROCEDURE , CREATE FUNCTION , ALTER EVENT , ALTER PROCEDURE , ALTER FUNCTION , DROP EVENT , DROP PROCEDURE 및 DROP FUNCTION 문을 바이너리 로그에 기록합니다.
스토어드 함수의 호출이 함수가 데이터를 변경하고 그 이외에서는 기록되지 않도록 명령문에서 행해진 경우 SELECT 문으로 기록됩니다. 그러면 기록되지 않는 문 스토어드 함수를 사용한 결과 발생한 데이터 변경을 복제 할 수 없게되는 사태가 방지됩니다. 예를 들어, SELECT 문은 바이너리 로그에 기록되지 않지만 SELECT 는 변경할 스토어드 함수를 호출하는 경우가 있습니다. 이를 다루기 위해서는 SELECT func_name () 문은 지정된 함수가 변경을 할 때 바이너리 로그에 기록됩니다. 다음 문이 마스터에서 실행된다고합니다.
```
CREATE FUNCTION f1(a INT) RETURNS INT
BEGIN
  IF (a < 3) THEN
    INSERT INTO t2 VALUES (a);
  END IF;
  RETURN 0;
END;

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);

SELECT f1(a) FROM t1;
```
SELECT 문이 실행되면 함수 f1() 은 3 회 호출됩니다. 이 중 2 회 호출 행을 삽입, MySQL은 각 행에 대해 SELECT 문을 기록합니다. 즉, MySQL은 다음 문을 바이너리 로그에 기록합니다.
```
SELECT f1(1);
SELECT f1(2);
```
서버는 오류를 발생시키는 저장 프로 시저를 저장 함수가 호출 할 때, 그 스토어드 함수 호출에 대한 SELECT 문을 기록합니다. 이 경우 서버는 예상되는 오류 코드와 함께 SELECT 문을 로그에 기록합니다. 슬레이브에서 같은 오류가 발생하면 이는 예상 된 결과이며 복제가 계속됩니다. 그렇지 않으면 복제가 중지됩니다.
함수에 의해 실행되는 명령문이 아닌 스토어드 함수 호출 로깅 복제는 다음 두 가지 요인에서 발생하는 보안의 의미가 있습니다.
- 함수가 마스터 서버와 슬레이브 서버에서 별도의 실행 경로를 따를 수 있습니다.
- 슬레이브에서 실행되는 명령문은 전체 권한을 가진 슬레이브 SQL 쓰레드가 처리됩니다.
즉, 사용자가 함수를 작성하기 위해 CREATE ROUTINE 권한을 가져야하지만, 완전한 권한을 가진 스레드에서 처리되는 슬레이브에서만 실행 위험한 문을 포함한 함수를 만들 수 있습니다. 예를 들어, 마스터 서버와 슬레이브 서버의 서버 ID 값이 각각 1과 2의 경우 마스터 서버의 사용자는 안전하지 않은 함수 unsafe_func() 를 다음과 같이 작성 호출 할 수 있습니다.
```
mysql> delimiter //
mysql> CREATE FUNCTION unsafe_func () RETURNS INT
    -> BEGIN
    ->   IF @@server_id=2 THEN dangerous_statement; END IF;
    ->   RETURN 1;
    -> END;
    -> //
mysql> delimiter ;
mysql> INSERT INTO t VALUES(unsafe_func());
```
CREATE FUNCTION 문 및 INSERT 문은 바이너리 로그에 기록되기 때문에, 슬레이브 서버는 그들을 실행합니다. 슬레이브 SQL 쓰레드는 전체 권한이 있기 때문에 위험한 문을 실행합니다. 따라서 함수 호출이 마스터와 슬레이브에 미치는 효과는 달리 복제가 안전하지 않습니다.

바이너리 로깅을 활성화하는 서버에 대한이 위험에서 보호하기 위해 스토어드 함수의 작성자는 필요한 일반적인 CREATE ROUTINE 권한 외에도 SUPER 권한도 가질 필요가 있습니다. 마찬가지로, ALTER FUNCTION 을 사용하려면 사용자는 ALTER ROUTINE 권한 외에도 SUPER 권한을 가지고 있어야합니다. SUPER 권한이없는 경우 오류가 발생합니다.
```
ERROR 1419 (HY000): You do not have the SUPER privilege and
binary logging is enabled (you *might* want to use the less safe
log_bin_trust_function_creators variable)
```
함수 작성자가 SUPER 권한을 갖도록 요구하지 않는 경우 (예를 들어, 시스템에서 CREATE ROUTINE 권한을 가진 모든 사용자가 경험이 풍부한 응용 프로그램 개발자 인 경우) log_bin_trust_function_creators 글로벌 시스템 변수를 1로 설정합니다. 서버 시작시 --log-bin-trust-function-creators=1 옵션을 사용하여이 변수를 설정할 수도 있습니다. 바이너리 로깅을 사용할 수없는 경우 log_bin_trust_function_creators 는 적용되지 않습니다. 전술 한 바와 같이, 함수 정의의 DEFINER 값이 필요하지 않는 한 함수의 작성에 SUPER 는 필요하지 않습니다.
업데이트를 수행하는 함수가 비 결정적이면 재현 가능하지 않습니다. 이것은 다음의 두 가지 바람직하지 않은 영향을 미칠 수 있습니다.
- 슬레이브가 마스터와 일치하지 않을 수 있습니다.
- 복원 된 데이터가 원본 데이터와 다릅니다.
이러한 문제를 해결하기 위해 MySQL에서는 함수를 결정적이거나 데이터를 변경하지 않겠다고 선언하지 않는 한 마스터 서버에서는 함수의 작성 및 변경은 거부한다는 요구 사항을 적용하고 있습니다. 여기에서는 다음의 두 함수 특성 세트가 적용됩니다.
- DETERMINISTIC 특성과 NOT DETERMINISTIC 특성은 함수가 일정한 입력에 대해 항상 동일한 결과를 생성할지 여부를 나타냅니다. 이 두 가지 특성이 지정되지 않은 경우 기본값은 NOT DETERMINISTIC 입니다. 함수가 결정적임을 선언에는 명시 적으로 DETERMINISTIC 을 지정해야합니다.
- CONTAINS SQL , NO SQL , READS SQL DATA 및 MODIFIES SQL DATA 특성은 함수가 데이터를 읽거나 기록에 대한 정보를 보여줍니다. NO SQL 또는 READS SQL DATA 는 함수가 데이터를 변경하지 않는 것을 의미하지만 특성이 지정되지 않은 경우 기본값은 CONTAINS 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)
```
log_bin_trust_function_creators 를 1로 설정하면 함수가 결정적인지, 데이터를 변경한다는 조건이 삭제됩니다.
저장 프로 시저 호출은 CALL 수준이 아니라 문 수준으로 기록됩니다. 즉, 서버는 CALL 명령문을 기록하지 않고 실제로 실행되는 프로 시저의 문을 기록합니다. 그 결과, 마스터에서 동일한 변경이 슬레이브 서버에서 확인됩니다. 이렇게하면 별도의 컴퓨터에서 다른 실행 경로를 갖는 프로 시저에서 발생하는 문제를 방지 할 수 있습니다.

일반적으로 저장 프로 시저에서 실행되는 문은 독립적으로 문을 실행 한 경우에 적용되는 것과 동일한 규칙을 사용하여 바이너리 로그에 기록됩니다. 프로 시저 내에서 문 실행은 비 프로 시저의 컨텍스트와 완전히 같게는 안되기 때문에, 프로 시저 문 로깅시에는 충분히주의하십시오.
- 기록 된 문에는 로컬 프로 시저 변수에 대한 참조가 포함될 수 있습니다. 이러한 변수는 저장 프로 시저의 컨텍스트 외부에 존재하지 않기 때문에 이러한 변수를 참조하는 명령문은 말 그대로 기록 할 수 없습니다. 대신 로컬 변수의 각각의 참조는 로깅을 위해 다음과 같은 구조 구문으로 대체합니다.
```
NAME_CONST(var_name, var_value)
```
  var_name 는 로컬 변수 이름이며, var_value 는 문을 기록 할 때 변수에 포함 된 값을 나타내는 정수입니다. NAME_CONST() 는 var_value 값과 var_name 의 "이름"이 포함되어 있습니다. 따라서이 함수를 직접 호출하면 다음과 같은 결과를 얻을 수 있습니다.
```
mysql> SELECT NAME_CONST('myname', 14);
+--------+
| myname |
+--------+
|     14 |
+--------+
```
  NAME_CONST() 는 마스터에 저장 프로 시저에서 실행 된 원래 문과 같은 효과로 기록 된 독립 실행 형 문을 슬레이브에서 실행할 수 있도록합니다.
  
  NAME_CONST() 를 사용한 결과, 소스 컬럼식이 로컬 변수를 참조 할 때 CREATE TABLE ... SELECT 문 문제가 발생할 수 있습니다. 이러한 참조를 NAME_CONST() 식으로 변환 한 결과, 마스터 서버와 슬레이브 서버에서 컬럼 이름이 다르거 나 정당한 컬럼 식별자로는 너무 이름이되거나 할 수 있습니다. 해결 방법은 로컬 변수를 참조하는 컬럼의 별칭을 지정합니다. myvar 의 값이 1이면 다음 문을 검토하십시오.
```
CREATE TABLE t1 SELECT myvar;
```
  이것은 다음과 같이 작성됩니다.
```
CREATE TABLE t1 SELECT NAME_CONST(myvar, 1);
```
  마스터 테이블과 종속 테이블에 동일한 컬럼 이름이 있는지 확인하려면 다음과 같은 명령문을 작성합니다.
```
CREATE TABLE t1 SELECT myvar AS myvar;
```
  재 작성된 문은 다음과 같이됩니다.
```
CREATE TABLE t1 SELECT NAME_CONST(myvar, 1) AS myvar;
```
- 기록되는 명령문은 사용자 정의 변수에 대한 참조가 포함될 수 있습니다. 이를 처리하기 위해 MySQL은 SET 문을 바이너리 로그에 기록 마스터에있는 것과 같은 값의 변수가 슬레이브에다는 것을 확인하십시오. 예를 들어, 문이 변수 @my_var 를 참조하는 경우, 그 문은 바이너리 로그에서 다음 문에 우선합니다. 여기서 value 마스터에서 @my_var 값입니다.
```
SET @my_var = value;
```
- 시저 호출은 커밋 또는 롤백 트랜잭션 내에서 수행 할 수 있습니다. 프로 시저 실행 트랜잭션 측면이 제대로 복제되도록 트랜잭션 컨텍스트가 설명됩니다. 즉, 서버는 실제로 데이터를 실행하고 수정하는 프로 시저 명령문을 기록하고 BEGIN , COMMIT 및 ROLLBACK 문을 필요에 따라 기록합니다. 예를 들어, 프로 시저가 트랜잭션 테이블 만 업데이트 롤백되는 트랜잭션에서 실행되는 경우이 업데이트는 기록되지 않습니다. 프로 시저가 커밋 된 트랜잭션 내에서 수행 된 경우 BEGIN 및 COMMIT 문 업데이트와 함께 기록됩니다. 롤백 트랜잭션 내에서 실행되는 프로 시저의 경우, 그 문은 문이 독립적으로 실행되는 경우에 적용되는 것과 동일한 규칙을 사용하여 기록됩니다.
  - 트랜잭션 테이블에 대한 업데이트는 기록되지 않습니다.
  - 비 트랜잭션 테이블에 대한 업데이트는 롤백 취소되지 않으므로 기록됩니다.
  - 트랜잭션 테이블과 비 트랜잭션 테이블의 혼합에 대한 업데이트는 슬레이브가 마스터와 같은 변경 및 롤백처럼 BEGIN 과 ROLLBACK 으로 둘러싸여 기록됩니다.
저장 프로 시저 호출은 스토어드 함수 내에서 호출되는 경우 문 수준의 바이너리 로그에 기록되지 않습니다. 이 경우 기록 된 유일한 대상은 함수를 호출 문 (기록 된 문에서 행해진 경우) 또는 DO 문 (기록되지 않은 문에서 행해진 경우)입니다. 따라서 그 이외의 경우 프로 시저 자체가 안전에도 시저를 호출 스토어드 함수를 사용할 때주의를 기울여야합니다.