MyInfraBox

MySQL 5.7 부터 read_only종류가 4가지 종류로 늘어나게 되었습니다.
단순히 복제 서버에서 Write를 막기위한 read_only뿐만이 아니라 특수한 read_only 파라미터도 있는데 이것에 대해 알아보겠습니다.
 
■ innodb_read_only
innodb Strage Engine을 사용하는 테이블들에 대해 모두 읽기로만 작동되게 합니다. 기존 5.7 이하 버전에서는 InnoDB Storage엔진을 사용하는 테이블들에 대해서만 삭제나 생성이 불가능했는데 8.0 이상부터는 모든 스토리지 엔진을 대상으로 읽기 상태로 바뀌게 됩니다.
참고로 위의 innodb_read_only가 활성화 되면 다음과 같은 명령어들이 실패하게 됩니다.
ANALYZE TABLE, ALTER TABLE tbl_name ENGINE=engine_name 명령어
CREATE USER 명령어와 GRANT 명령어
INSTALL PLUGIN, UNINSTALL PLUGIN 명령어
CREATE FUNCTION과 DROP FUNCTION 명령어
 
■ read_only
클라이언트 접속시 읽기만 허용되고 쓰기는 금지시키는 설정입니다. 예외적으로  CONNECTION_ADMIN 권한(곧 제거될 SUPER권한과 같음)만 read_only모드가 설정되어 있어도 쓰기가 가능합니다.
대신 아래에서 소개될 super_read_only는 최고 권한을 가지고 있어도 쓰기가 금지됩니다.
 
super_read_only가 ON이면 암묵적으로 read_only도 ON으로 변경됩니다.
read_only가 OFF면 암묵적으로 super_read_only도 OFF가 됩니다.
 
▶︎ read_only가 활성화되고 super_read_only가 활성화되면 서버는 다음 작업을 허용하게 됩니다.
- 서버가 리플리카인 경우 복제 스레드에 의해 업데이트가 수행됩니다. 복제 설정에서 리플리카가 클라이언트가 아닌 원본 서버의 업데이트만 수락하도록 복제 서버에서 읽기 전용을 활성화하는 것이 유용할 수 있습니다.
- 현재 바이너리 로그 파일에 없는 실행된 트랜잭션의 GTID를 저장하는 시스템 테이블 mysql.gtid_executed에 씁니다.
- ANALYZE TABLE 또는 OPTIMIZE TABLE 문을 사용합니다. 읽기 전용 모드의 목적은 테이블 구조나 내용의 변경을 방지하는 것입니다. 분석 및 최적화는 그러한 변경에 해당하지 않습니다. 예를 들어 읽기 전용 복제본에 대한 일관성 검사가 mysqlcheck --all-databases --analyze로 수행될 수 있음을 의미합니다.
- 항상 바이너리 로그에 기록되는 FLUSH STATUS 문 사용합니다.
- TEMPORARY 테이블에 대한 운영이 가능합니다.
- 로그 테이블(mysql.general_log 및 mysql.slow_log)에 인서트가 가능합니다.
- UPDATE 또는 TRUNCATE TABLE 작업과 같은 성능 스키마 테이블 업데이트가 가능합니다.
 
▶︎ 복제 소스 서버에서 read_only 변경 사항은 복제 서버에 복제되지 않습니다. 값은 소스의 설정과 독립적으로 복제본에 설정할 수 있습니다.
 
▶︎ 다음 조건은 read_only 활성화 시도에 적용됩니다(super_read_only 활성화로 인한 암시적 시도 포함).
- 명시적 잠금(LOCK TABLES로 획득)이 있거나 보류 중인 트랜잭션이 있는 경우 명령이 실패하고 오류가 발생합니다.
- 잠금이 해제되고 명령문과 트랜잭션이 종료될 때까지 다른 클라이언트가 진행 중인 명령문, 활성 LOCK TABLES WRITE 또는 진행 중인 커밋을 가지고 있는 동안 모든 명령 수행이 차단됩니다. read_only 활성화 시도가 보류 중인 동안 다른 클라이언트의 테이블 잠금 또는 트랜잭션 시작 요청도 read_only가 설정될 때까지 차단됩니다.
- 메타데이터 잠금을 보유한 활성 트랜잭션이 있는 경우 해당 트랜잭션이 종료될 때까지 명령 시도가 차단됩니다.
- 전역 읽기 잠금(FLUSH TABLES WITH READ LOCK으로 획득)을 유지하는 동안 read_only를 활성화할 수 있습니다. 이는 테이블 잠금을 포함하지 않기 때문입니다.
 
■ super_read_only
read_only는 일반 유저에 대한 서버 업데이트를 못하게 막지만,  CONNECTION_ADMIN 권한을 가진 사용자는 예외적으로 쓰기가 가능했습니다.
하지만 super_read_olny를 설정하게 되면  CONNECTION_ADMIN 혹은 SUPER 권한을 가지더라도 서버에 업데이트가 금지됩니다.
super_read_only가 활성화된 경우 방지되는 클라이언트 업데이트에는 CREATE FUNCTION(로드 가능한 함수 설치), INSTALL PLUGIN 및 INSTALL COMPONENT와 같이 반드시 업데이트로 표시되지 않는 작업이 포함됩니다. 이러한 작업은 mysql 시스템 스키마의 테이블 변경과 관련되기 때문에 금지됩니다.
 
마찬가지로 이벤트 스케줄러가 활성화된 경우 super_read_only 시스템 변수를 활성화하면 이벤트 데이터 사전 테이블에서 "마지막으로 실행된" 이벤트 타임스탬프가 업데이트되지 않습니다. 이로 인해 이벤트 스케줄러는 서버 오류 로그에 메시지를 기록한 후 다음에 예약된 이벤트를 실행하려고 할 때 중지됩니다. (이 상황에서 event_scheduler 시스템 변수는 ON에서 OFF로 변경되지 않습니다. 의미는 이 변수가 Event Scheduler를 활성화 또는 비활성화하려는 DBA 의도를 거부한다는 것입니다. 여기서 실제 상태는 시작 또는 중지로 구분될 수 있습니다.) super_read_only가 활성화된 후 이후에 비활성화되면 서버는 MySQL 8.0.26부터 필요에 따라 이벤트 스케줄러를 자동으로 다시 시작합니다. MySQL 8.0.26 이전 버전에서는 Event Scheduler를 다시 활성화하여 수동으로 다시 시작해야 합니다.
 
복제 소스 서버에서 super_read_only에 대한 변경 사항은 복제 서버에 복제되지 않습니다. 값은 소스의 설정과 독립적으로 복제본에 설정할 수 있습니다.
 
 
■ transaction_read_only
트랜잭션 액세스 모드입니다. 값은 OFF(읽기/쓰기, 기본값) 또는 ON(읽기 전용)입니다.
TRANSACTION ACCESS-MODE에는 Global, Session 및 Next Transaction의 세 가지 범위가 있습니다. 이 세 가지 범위 구현은 나중에 설명하는 것처럼 일부 비표준 액세스 모드 할당 의미론으로 이어집니다.
 
런타임 시 액세스 모드는 transaction_read_only 시스템 변수에 값을 할당하는 SET 문을 사용하여 직접 설정하거나 SET TRANSACTION 문을 사용하여 간접적으로 설정할 수 있습니다. 예를 들어 다음 SET 문을 사용하여 GLOBAL 값을 설정합니다.
SET GLOBAL transaction_read_only = ON;
전역 transaction_read_only 값을 설정하면 모든 후속 SESSION에 대한 액세스 모드가 설정됩니다. 기존 세션은 영향을 받지 않습니다.
 
세션 또는 다음 수준 transaction_read_only 값을 설정하려면 SET 문을 사용합니다. 대부분의 SESSION 시스템 변수의 경우 다음 명령문은 값을 설정하는 동일한 방법입니다.
SET @@SESSION.var_name = value;
SET SESSION var_name = value;
SET var_name = value;
SET @@var_name = value;
 
앞에서 언급했듯이 TRANSACTION ACCESS-MODE에는 GLOBAL 및 세션 범위 외에 다음 트랜잭션 범위가 있습니다. 다음 트랜잭션 범위를 설정할 수 있도록 세션 시스템 변수 값을 할당하는 SET 구문에는 transaction_read_only에 대한 비표준 의미가 있습니다.
 
▶︎ 세션 액세스 모드를 설정하려면 다음 구문 중 하나를 사용합니다.
SET @@SESSION.transaction_read_only = value;
SET SESSION transaction_read_only = value;
SET transaction_read_only = value;
 
각 구문에 대해 다음 의미 체계가 적용됩니다.
1. 세션 내에서 수행되는 모든 후속 트랜잭션에 대한 액세스 모드를 설정합니다.
2. 트랜잭션 내에서 허용되지만 현재 진행 중인 트랜잭션에는 영향을 미치지 않습니다.
3. 트랜잭션간에 실행되는 경우 다음 트랜잭션 액세스 모드를 설정하는 이전 문을 재정의합니다.
4. SET SESSION TRANSACTION에 해당 {READ WRITE | READ ONLY}(SESSION 키워드 사용).
 
다음 트랜잭션 액세스 모드를 설정하려면 다음 구문을 사용하십시오.
SET @@transaction_read_only =
 
▶︎ 해당 구문의 경우 다음 의미 체계가 적용됩니다.
1. 세션 내에서 수행되는 다음 단일 트랜잭션에 대해서만 액세스 모드를 설정합니다.
2. 후속 트랜잭션은 세션 액세스 모드로 되돌아갑니다.
3. 거래 내에서는 허용되지 않습니다.
SET TRANSACTION에 해당 {READ WRITE | READ ONLY}(SESSION 키워드 제외).
 
 
참고 자료
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
 
 
 
 
 
 
 
 
transaction_read_only

[MySQL][Parameter] 다양한 read_only 파라미터

댓글