15.1.2 ALTER DATABASE Statement

15.1.2 ALTER DATABASE Statement

1ALTER {DATABASE | SCHEMA} [db_name]
2    alter_option ...
3
4alter_option: {
5    [DEFAULT] CHARACTER SET [=] charset_name
6  | [DEFAULT] COLLATE [=] collation_name
7  | [DEFAULT] ENCRYPTION [=] {'Y' | 'N'}
8  | READ ONLY [=] {DEFAULT | 0 | 1}
9}

ALTER DATABASE를 사용하면 데이터베이스의 전반적인 특성을 변경할 수 있습니다. 이러한 특성은 데이터 딕셔너리에 저장됩니다. 이 구문을 사용하려면 해당 데이터베이스에 대한 ALTER 권한이 필요합니다. ALTER SCHEMA는 ALTER DATABASE의 동의어입니다.

데이터베이스 이름을 생략하면 이 구문은 기본 데이터베이스에 적용됩니다. 이 경우 기본 데이터베이스가 없으면 오류가 발생합니다.

구문에서 생략된 모든 alter_option 에 대해, 데이터베이스는 현재 옵션 값을 유지합니다. 예외적으로 캐릭터 세트를 변경하면 콜레이션이 변경될 수 있고, 그 반대의 경우도 마찬가지입니다.

• Character Set and Collation Options

• Encryption Option

• Read Only Option

Character Set and Collation Options

CHARACTER SET 옵션은 기본 데이터베이스 캐릭터 세트를 변경합니다. COLLATE 옵션은 기본 데이터베이스 콜레이션을 변경합니다. 캐릭터 세트 및 콜레이션 이름에 대한 정보는 Chapter 12, Character Sets, Collations, Unicode를 참조하십시오.

사용 가능한 캐릭터 세트 및 콜레이션을 확인하려면 SHOW CHARACTER SET 및 SHOW COLLATION 구문을 각각 사용하십시오. Section 15.7.7.4, “SHOW CHARACTER SET Statement” 및 Section 15.7.7.5, “SHOW COLLATION Statement”를 참조하십시오.

루틴이 생성될 때 데이터베이스 기본값을 사용하는 저장 루틴은 해당 기본값을 루틴 정의의 일부로 포함합니다. (저장 루틴에서 문자 데이터 타입을 갖는 변수는 캐릭터 세트 또는 콜레이션이 명시적으로 지정되지 않은 경우 데이터베이스 기본값을 사용합니다. Section 15.1.21, “CREATE PROCEDURE and CREATE FUNCTION Statements”를 참조하십시오.) 데이터베이스의 기본 캐릭터 세트 또는 콜레이션을 변경하는 경우, 새 기본값을 사용해야 하는 저장 루틴은 모두 drop 후 다시 생성해야 합니다.

Encryption Option

ENCRYPTION 옵션은 기본 데이터베이스 암호화를 정의하며, 이는 데이터베이스 내에 생성되는 테이블이 상속합니다. 허용되는 값은 'Y' (암호화 사용) 및 'N' (암호화 사용 안 함)입니다.

mysql 시스템 스키마는 기본 암호화로 설정할 수 없습니다. 그 안에 존재하는 기존 테이블은 일반 mysql 테이블스페이스의 일부이며, 이는 암호화될 수 있습니다. information_schema는 뷰만 포함합니다. 그 안에 어떤 테이블도 생성할 수 없습니다. 디스크에 암호화할 대상이 없습니다. performance_schema의 모든 테이블은 순수 메모리 기반인 PERFORMANCE_SCHEMA 엔진을 사용합니다. 그 안에 다른 테이블을 생성하는 것은 불가능합니다. 디스크에 암호화할 대상이 없습니다.

새로 생성된 테이블만 기본 데이터베이스 암호화를 상속합니다. 데이터베이스와 연관된 기존 테이블의 암호화는 변경되지 않은 상태로 유지됩니다. table_encryption_privilege_check 시스템 변수 사용이 설정되어 있는 경우, TABLE_ENCRYPTION_ADMIN 권한이 필요하며, 이는 default_table_encryption 시스템 변수 값과 다른 기본 암호화 설정을 지정하기 위해 요구됩니다. 자세한 내용은 Defining an Encryption Default for Schemas and General Tablespaces를 참조하십시오.

Read Only Option

READ ONLY 옵션은 데이터베이스 및 그 안의 객체 수정 허용 여부를 제어합니다. 허용되는 값은 DEFAULT 또는 0 (읽기 전용 아님) 및 1 (읽기 전용)입니다. 이 옵션은 READ ONLY가 사용 설정된 데이터베이스를 작업 중 변경될 수 있다는 우려 없이 다른 MySQL 인스턴스로 마이그레이션할 수 있으므로 데이터베이스 마이그레이션에 유용합니다.

NDB Cluster에서 하나의 mysqld 서버에서 데이터베이스를 읽기 전용으로 설정하면, 동일 클러스터 내의 다른 mysqld 서버로 동기화되어, 데이터베이스는 모든 mysqld 서버에서 읽기 전용이 됩니다.

READ ONLY 옵션이 사용 설정된 경우, INFORMATION_SCHEMA의 SCHEMATA_EXTENSIONS 테이블에 표시됩니다. Section 28.3.38, “The INFORMATION_SCHEMA SCHEMATA_EXTENSIONS Table”를 참조하십시오.

READ ONLY 옵션은 다음 시스템 스키마에 대해서는 사용 설정할 수 없습니다: mysql, information_schema, performance_schema.

ALTER DATABASE 구문에서, READ ONLY 옵션은 자신과의 다른 인스턴스 및 다른 옵션과 다음과 같이 상호 작용합니다:

• 여러 인스턴스의 READ ONLY가 서로 충돌하는 경우 (예: READ ONLY = 1 READ ONLY = 0), 오류가 발생합니다.

• 읽기 전용 데이터베이스에 대해서도, (충돌하지 않는) READ ONLY 옵션만 포함하는 ALTER DATABASE 구문은 허용됩니다.

• (충돌하지 않는) READ ONLY 옵션과 다른 옵션의 혼합은, 구문 전 또는 후의 데이터베이스 읽기 전용 상태 중 하나가 수정이 가능하도록 허용하는 경우 허용됩니다. 구문 전후의 읽기 전용 상태가 모두 변경을 허용하지 않으면 오류가 발생합니다.

다음 구문은 데이터베이스가 읽기 전용인지 여부와 관계없이 성공합니다:

1ALTER DATABASE mydb READ ONLY = 0 DEFAULT COLLATE utf8mb4_bin;

다음 구문은 데이터베이스가 읽기 전용이 아닌 경우에는 성공하지만, 이미 읽기 전용인 경우에는 실패합니다:

1ALTER DATABASE mydb READ ONLY = 1 DEFAULT COLLATE utf8mb4_bin;

READ ONLY를 사용 설정하면 다음의 읽기 전용 검사(read-only check)가 적용되지 않는 예외를 제외한 모든 데이터베이스 사용자에 영향을 줍니다:

• 서버 초기화, 재시작, 업그레이드 또는 복제의 일부로 서버에서 실행되는 구문.

• 서버 시작 시 init_file 시스템 변수에 의해 지정된 파일 안의 구문.

• TEMPORARY 테이블; 읽기 전용 데이터베이스에서도 TEMPORARY 테이블을 생성, 변경, drop 및 write할 수 있습니다.

• NDB Cluster 비-SQL insert 및 update.

앞에서 나열한 예외 작업을 제외하면, READ ONLY 사용 설정은 데이터베이스 및 그 객체에 대한 쓰기 작업을 금지하며, 여기에는 정의, 데이터 및 메타데이터가 포함됩니다. 다음 목록은 영향을 받는 SQL 구문 및 작업을 자세히 설명합니다:

• 데이터베이스 자체:

  • CREATE DATABASE

  • READ ONLY 옵션을 변경하는 경우를 제외한 ALTER DATABASE

  • DROP DATABASE

• 뷰:

  • CREATE VIEW

  • ALTER VIEW

  • DROP VIEW

  • 부작용(side effect)이 있는 함수를 호출하는 뷰에서의 select.

  • 업데이트 가능한 뷰에 대한 update.

  • 쓰기 가능한 데이터베이스에서 객체를 생성 또는 drop하는 구문은, 읽기 전용 데이터베이스의 뷰 메타데이터에 영향을 미치는 경우 (예: 뷰를 유효 또는 무효하게 만드는 경우) 거부됩니다.

• 저장 루틴:

  • CREATE PROCEDURE

  • DROP PROCEDURE

  • 부작용이 있는 프로시저의 CALL

  • CREATE FUNCTION

  • DROP FUNCTION

  • 부작용이 있는 함수의 SELECT

  • 프로시저 및 함수에 대해, 읽기 전용 검사는 prelocking 동작을 따릅니다. CALL 구문의 경우 읽기 전용 검사는 구문 단위로 수행되므로, 읽기 전용 데이터베이스에 write하는 어떤 조건부 실행 구문이 실제로 실행되지 않는다면, 호출은 여전히 성공합니다. 반면, SELECT 내에서 호출되는 함수의 경우, 함수 본문의 실행은 prelocked 모드에서 이루어집니다. 함수 내의 어떤 구문이 읽기 전용 데이터베이스에 write하는 한, 그 구문이 실제로 실행되는지와 관계없이 함수 실행은 오류와 함께 실패합니다.

• 트리거:

  • CREATE TRIGGER

  • DROP TRIGGER

  • 트리거 호출.

• 이벤트:

  • CREATE EVENT

  • ALTER EVENT

  • DROP EVENT

  • 이벤트 실행:

    • 데이터베이스에서 이벤트를 실행하면 마지막 실행 타임스탬프가 변경되는데, 이는 데이터 딕셔너리에 저장된 이벤트 메타데이터이므로, 해당 실행은 실패합니다. 이벤트 실행 실패는 이벤트 스케줄러가 중지되는 결과를 가져옵니다.

    • 이벤트가 읽기 전용 데이터베이스의 객체에 write하는 경우, 이벤트 실행은 오류와 함께 실패하지만, 이벤트 스케줄러는 중지되지 않습니다.

• 테이블:

  • CREATE TABLE

  • ALTER TABLE

  • CREATE INDEX

  • DROP INDEX

  • RENAME TABLE

  • TRUNCATE TABLE

  • DROP TABLE

  • DELETE

  • INSERT

  • IMPORT TABLE

  • LOAD DATA

  • LOAD XML

  • REPLACE

  • UPDATE

  • child 테이블이 읽기 전용 데이터베이스에 있는 cascading foreign key의 경우, child 테이블이 직접 영향을 받지 않더라도 parent에 대한 update 및 delete는 거부됩니다.

  • CREATE TABLE s1.t(i int) ENGINE MERGE UNION (s2.t, s3.t), INSERT_METHOD=...와 같은 MERGE 테이블의 경우 다음 동작이 적용됩니다:

    • INSERT into s1.t과 같이 MERGE 테이블에 insert하는 작업은, insert 메서드와 관계없이 s1, s2, s3 중 하나라도 읽기 전용이면 실패합니다. 실제로 쓰기 가능한 테이블에 기록될 것이라 해도 insert는 거부됩니다.

    • DROP TABLE s1.t과 같이 MERGE 테이블을 drop하는 작업은 s1이 읽기 전용이 아닌 한 성공합니다. 읽기 전용 데이터베이스를 참조하는 MERGE 테이블을 drop하는 것은 허용됩니다.

ALTER DATABASE 구문은, 변경 대상 데이터베이스 내의 객체에 이미 접근한 모든 동시 트랜잭션이 커밋될 때까지 블록됩니다. 반대로, 동시에 실행되는 ALTER DATABASE에서 변경 중인 데이터베이스의 객체에 접근하는 쓰기 트랜잭션은, 해당 ALTER DATABASE가 커밋될 때까지 블록됩니다.

Clone 플러그인을 사용하여 로컬 또는 원격 데이터 디렉터리를 클론하는 경우, 클론 내의 데이터베이스는 소스 데이터 디렉터리에서 갖고 있던 읽기 전용 상태를 유지합니다. 읽기 전용 상태는 클로닝 프로세스 자체에는 영향을 미치지 않습니다. 클론에서 동일한 데이터베이스 읽기 전용 상태를 유지하는 것이 바람직하지 않은 경우, 클로닝 프로세스가 완료된 후 클론에 대해 ALTER DATABASE 작업을 사용하여 해당 옵션을 명시적으로 변경해야 합니다.

donor에서 recipient로 클로닝하는 동안, recipient에 읽기 전용인 사용자 데이터베이스가 있는 경우, 클로닝은 오류 메시지와 함께 실패합니다. 데이터베이스를 쓰기 가능하게 만든 후 클로닝을 다시 시도할 수 있습니다.

READ ONLY는 ALTER DATABASE에는 허용되지만, CREATE DATABASE에는 허용되지 않습니다. 그러나 읽기 전용 데이터베이스에 대해 SHOW CREATE DATABASE에서 생성되는 구문에는 읽기 전용 상태를 나타내기 위해 주석(comment) 내에 READ ONLY=1이 포함됩니다:

1mysql> ALTER DATABASE mydb READ ONLY = 1;
2mysql> SHOW CREATE DATABASE mydb\G
3*************************** 1. row ***************************
4       Database: mydb
5Create Database: CREATE DATABASE `mydb`
6                 /*!40100 DEFAULT CHARACTER SET utf8mb4
7                          COLLATE utf8mb4_0900_ai_ci */
8                 /*!80016 DEFAULT ENCRYPTION='N' */
9                 /* READ ONLY = 1 */

서버가 이러한 주석을 포함하는 CREATE DATABASE 구문을 실행하는 경우, 서버는 해당 주석을 무시하며 READ ONLY 옵션은 처리되지 않습니다. 이는 덤프 출력에 CREATE DATABASE 구문을 생성하기 위해 SHOW CREATE DATABASE를 사용하는 mysqldump에 영향을 줍니다:

• 덤프 파일에서 읽기 전용 데이터베이스에 대한 CREATE DATABASE 구문은 주석 처리된 READ ONLY 옵션을 포함합니다.

• 덤프 파일은 평소와 같이 복원할 수 있지만, 서버는 주석 처리된 READ ONLY 옵션을 무시하므로, 복원된 데이터베이스는 읽기 전용이 아닙니다. 복원 후에도 데이터베이스가 읽기 전용 상태여야 하는 경우, 수동으로 ALTER DATABASE를 실행하여 그렇게 설정해야 합니다.

예를 들어 mydb가 읽기 전용이며 다음과 같이 덤프했다고 가정합니다:

1$> mysqldump --databases mydb > mydb.sql

나중에 복원 작업 후, mydb가 계속 읽기 전용 상태여야 하는 경우 ALTER DATABASE를 이어서 실행해야 합니다:

1$> mysql
2mysql> SOURCE mydb.sql;
3mysql> ALTER DATABASE mydb READ ONLY = 1;

MySQL Enterprise Backup은 이러한 문제의 영향을 받지 않습니다. 이는 읽기 전용 데이터베이스를 다른 데이터베이스와 마찬가지로 백업 및 복원하지만, 백업 시에 사용 설정되어 있었다면 복원 시점에 READ ONLY 옵션을 사용 설정합니다.

ALTER DATABASE는 바이너리 로그에 기록되므로, 복제 소스 서버에서 READ ONLY 옵션 변경은 레플리카에도 영향을 미칩니다. 이를 방지하려면 ALTER DATABASE 구문을 실행하기 전에 바이너리 로깅을 비활성화해야 합니다. 예를 들어 레플리카에 영향을 주지 않고 데이터베이스 마이그레이션을 준비하려면 다음 작업을 수행하십시오:

1. 단일 세션 내에서 바이너리 로깅을 비활성화하고 데이터베이스에 대해 READ ONLY를 사용 설정합니다:

1mysql> SET sql_log_bin = OFF;
2mysql> ALTER DATABASE mydb READ ONLY = 1;

2. 예를 들어 mysqldump로 데이터베이스를 덤프합니다:

1$> mysqldump --databases mydb > mydb.sql

3. 단일 세션 내에서 바이너리 로깅을 비활성화하고 데이터베이스에 대해 READ ONLY를 비활성화합니다:

1mysql> SET sql_log_bin = OFF;
2mysql> ALTER DATABASE mydb READ ONLY = 0;