Amazon RDS for MySQL의 파라미터 구성 모범 사례, 3부: 보안, 운영 관리 용이성 및 연결 제한 시간과 관련된 파라미터

728x90

https://aws.amazon.com/ko/blogs/database/best-practices-for-configuring-parameters-for-amazon-rds-for-mysql-part-3-parameters-related-to-security-operational-manageability-and-connectivity-timeout/

Best practices for configuring parameters for Amazon RDS for MySQL, part 3: Parameters related to security, operational manageab

This blog post was last reviewed or updated May, 2022. In the previous blog post of this series, I discuss MySQL parameters used to optimize replication in Amazon Relational Database Service (Amazon RDS) for MySQL and best practices related to them. In tod

aws.amazon.com

보안 관련 매개변수

다음은 보안과 관련된 매개변수를 나열하고 각각을 구성하기 위한 모범 사례 제안과 함께 나열합니다.

init_connect

이 매개변수는 연결하는 각 클라이언트에 대해 서버가 실행할 문자열을 정의합니다. 이 매개변수에 대한 기본값은 없습니다. 문자열은 세미콜론으로 구분된 하나 이상의 SQL 문으로 구성됩니다.

예를 들어, 이 매개변수를 사용하여 데이터베이스에 성공적으로 연결한 데이터베이스 사용자에 대한 간단한 감사를 작성할 수 있습니다. 그렇게 하려면 먼저 감사 테이블과 트리거를 생성합니다. 그런 다음 init_connect value트리거 이름을 설정합니다. 그렇게 하면 클라이언트가 연결할 때마다 매개변수가 연결 세부 정보를 감사 테이블에 기록하도록 트리거합니다.

다음 예와 같이 이 매개변수를 사용하여 특정 사용자 계정에 대한 자동 커밋을 비활성화할 수도 있습니다.

init_connect = 'set autocommit=case current_user() when 'test@localhost' then 0 else 1 end’.

old_passwords

이 매개변수는 함수에서 사용하는 암호 해싱 방법을 제어합니다 PASSWORD . 또한 절 을 사용하여 암호를 지정하는 명령문에 CREATE USER 의해 수행되는 암호 해싱에도 영향을 줍니다.GRANTIDENTIFIED BY

이 매개변수를 활성화하지 않는 것이 좋습니다. 그렇게 하면 PASSWORD 함수가 안전하지 않은 암호 해시를 사용하게 됩니다. 이 매개변수의 기본값은 MySQL 버전 4.1 기본 해싱을 사용하는 0입니다.

default_password_lifetime

이 매개변수는 MySQL 버전 5.7에서 사용할 수 있습니다. 전역 자동 암호 만료 정책을 정의하는 데 사용됩니다. 값을 N으로 설정하면 허용된 암호 수명이 N일이며 각 암호는 N일마다 변경해야 함을 의미합니다. 기본값은 자동 암호 만료를 비활성화하는 0입니다. 허용되는 값의 범위는 0–65,535입니다.

보안 표준의 요구 사항 및 암호 정책을 충족하도록 이 매개변수를 설정하십시오. 기본 매개변수 설정을 재정의하는 CREATE USER또는 를 사용하여 계정에 대해 명시적으로 값을 설정할 수도 있습니다 . 예를 들면 다음과 같습니다.ALTER USER

mysql> ALTER USER ‘test’@‘localhost' PASSWORD EXPIRE INTERVAL 30 DAY;

max_user_connections

이 매개변수를 사용하여 0이 아닌 값으로 설정하여 MySQL 서버 리소스의 클라이언트 사용을 제한할 수 있습니다. 이 매개변수는 지정된 계정이 만들 수 있는 동시 연결 수를 제한합니다. 연결 수가 이 값에 도달하면 새 연결을 생성하려고 하면 "경고: mysql_connect(): 사용자 xxxxxxxx는 이미 'max_user_connections'보다 많은 활성 연결을 가지고 있습니다"와 같은 오류를 수신합니다. 기본값은 0이며 지정된 계정에 대한 동시 연결을 제한하지 않습니다.

작업 관리와 관련된 매개변수

다음에는 각 구성에 대한 모범 사례 제안과 함께 작업 관리와 관련된 매개 변수를 나열합니다.

log_warnings

중단된 연결 및 새 연결 시도에 대한 액세스 거부 오류를 기록하려면 값을 log_warnings1보다 크게 설정합니다. 일반적으로 연결과 관련된 문제를 디버그하고 MySQL 오류 로그에서 데이터를 수집하는 데 도움이 되도록 2로 설정합니다. 기본값은 1입니다.

이 매개변수는 연결 끊김 및 기타 사소한 문제에 대한 경고를 오류 로그에 입력합니다. 자세한 내용은 AWS 지원 문서 MySQL을 실행하는 Amazon RDS 인스턴스에 실패한 로그인 시도를 어떻게 기록합니까? 를 참조하십시오. 및 MySQL 문서의 서버 시스템 변수 .

성능 스키마

이 매개변수는 성능 스키마를 활성화하거나 비활성화하는 데 사용됩니다. 이 매개변수는 0(성능 스키마 비활성화) 및 1(성능 스키마 활성화)의 두 가지 값만 가질 수 있습니다. MySQL 5.6에서 이 매개변수의 기본값은 성능 스키마를 활성화하는 1입니다. 5.7에서 이 매개변수의 기본값은 성능 스키마를 비활성화하는 0입니다. 성능 스키마를 사용할지 여부에 따라 이 매개변수를 설정하십시오.

Event_scheduler

일반적으로 사용되는 이 매개변수는 이벤트 스케줄러를 켜거나 끕니다. 기본값은 OFF이고 사용 가능한 값은 ON 또는 OFF입니다. OFF 값은 이벤트 스케줄러를 중지하고 ON은 이벤트 스케줄러를 시작합니다.

이 매개변수의 값을 설정하는 방법은 사용 요구 사항에 따라 다릅니다. 그러나 이벤트 스케줄러에는 이벤트의 여러 동시 실행에 대한 보호 기능이 없습니다. 예를 들어, 2분마다 실행되도록 예약된 이벤트가 2분 이상 걸린다고 가정합니다. 예약된 다음 이벤트로 인해 이벤트 실행이 누적될 수 있습니다. 이러한 누적은 교착 상태를 유발할 수 있습니다. 이를 피하는 가장 좋은 방법은 GET_LOCK함수를 사용하여 스레드 간에 통신하는 것입니다.

다음은 이를 수행하는 방법을 보여주는 예입니다.

CREATE EVENT test_event ON SCHEDULE EVERY 10 SECOND DO
BEGIN
 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
 BEGIN
   DO RELEASE_LOCK('test_event');
 END;
 IF GET_LOCK('testlock_event', 0) THEN
   -- add some   logic/DML operation here as per your requirement :
  END IF;
  DO RELEASE_LOCK('testlock_event');
END;

이벤트 정보에 대한 정보 스키마를 쿼리할 수도 있습니다. 예를 들면 다음과 같습니다.

> select * from information_schema.EVENTS\G

이벤트와 관련된 문제를 해결해야 하는 경우 일반적으로 오류 로그에 유용한 정보가 포함되어 있습니다. 현재 이벤트 스레드 상태를 표시하려면 명령을 사용하십시오 show full processlist. 자세한 내용 은 MySQL 설명서의 이벤트 스케줄러 스레드 상태 를 참조하십시오.

복제 시나리오에서 마스터와 복제본 모두에서 이벤트 스케줄러를 활성화하면 잘못된 결과가 발생할 수 있습니다. 이는 마스터의 이벤트가 원하는 레코드를 생성하고 레코드가 복제본에 복제되기 때문에 발생합니다. 복제본에서 이벤트 스케줄러를 활성화하면 이벤트가 다시 실행됩니다. 일반적인 복제 과정에서 프로세스는 이벤트(예: CREATE, ALTER, 등 DROP)에 대한 수정 사항을 복제본에 복제하므로 마스터와 복제본이 동일한 이벤트 구성을 갖습니다. 따라서 이벤트 상태는 SLAVESIDE_DISABLED복제본이 승격될 때까지 복제본의 이벤트가 활성화되지 않도록 해야 합니다.

복제본을 마스터로 승격한 후 새 마스터에서 이벤트 실행을 시작하려면 다음을 수행하십시오.

이벤트 스케줄러를 비활성화합니다(활성화된 경우).
ENABLED다음 나열된 업데이트 문 을 사용하여 원본 마스터에서 복제된 모든 이벤트의 상태를 설정합니다 .
이벤트 스케줄러를 켭니다.

상태가 인 이벤트를 보려면 SLAVESIDE_DISABLED다음 쿼리를 사용하십시오.

SELECT EVENT_SCHEMA, EVENT_NAME
  FROM INFORMATION_SCHEMA.EVENTS
  WHERE STATUS = 'SLAVESIDE_DISABLED';

모든 이벤트의 상태를 활성화됨으로 업데이트하려면 다음 문을 사용합니다.

UPDATE mysql.event SET status='ENABLED';

RDS 복제본에 대해 여러 마스터(예: 외부 마스터)가 있는 경우 다음과 같이 발신자 열을 사용하여 특정 마스터의 이벤트를 활성화할 수 있습니다.

UPDATE mysql.event SET status='ENABLED' WHERE originator=817021841;

여기서 817021841은 원본 마스터의 서버 ID입니다. 를 사용하여 이 ID를 찾을 수 있습니다 SELECT @@server_id.

명시적_defaults_for_timestamp

이 매개변수는 데이터 유형 NULL이 있는 열의 기본값과 값에 대해 MySQL이 작동하는 방식을 결정합니다 . TIMESTAMP기본값은 매개변수를 활성화하는 1이며 허용되는 값은 0과 1입니다.

TIMESTAMPNULL 및 기본값 이 있는 열에 대한 비표준 동작을 방지하려면 이 값을 활성화된 상태로 유지하는 것이 좋습니다 . 자세한 내용은 MySQL 5.6 설명서 및 MySQL 5.7 설명서 를 참조하십시오 . 이 매개변수를 활성화하면 MySQL 5.6 설명서 및 MySQL 5.7 설명서 에 설명된 대로 서버가 비표준 동작을 비활성화합니다 .

그러나 이 매개변수가 활성화되면 다음과 같은 오류가 발생할 수 있습니다.

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'column_name' cannot be null' error when not specifying a value.

이는 RDS MySQL에서 매개변수의 기본값 explicit_defaults_for_timestamp이 1(활성화됨)이기 때문에 나타납니다. 이 설정은 MySQL 기본값인 0(비활성화됨)과 다릅니다. 이러한 불일치로 인해 값이 명시적으로 제공되지 않은 경우 열에 대한 기본 설정이 없습니다. 이 오류를 방지하려면 값 explicit_defaults_for_timestamp을 0으로 설정하도록 파라미터 그룹을 수정하십시오. 이 파라미터는 정적이므로 변경하려면 재부팅해야 합니다.

그러나 explicit_defaults_for_timestamp은(는) 유일한 목적이 MySQL 열의 비표준 동작을 제어하는 것이기 때문에 더 이상 사용되지 않습니다 . 이는 TIMESTAMP향후 MySQL 릴리스에서 제거될 예정입니다. 이러한 비표준 동작이 제거된 후에는 이 매개변수도 사용되지 않으며 MySQL 설명서에 따라 제거됩니다. TIMESTAMP따라서 열의 값을 완전히 지정하여 현재 타임스탬프 값을 설정하도록 코드 변경을 계획하는 것이 좋습니다 . CURRENT_TIMESTAMP또는 와 같은 동의어 를 사용할 수 있습니다 NOW().

문자 집합과 관련된 매개변수

문자 집합 은 일반적으로 데이터 정렬 유형에서 파생되는 기호 및 인코딩 집합입니다. 문자 집합을 사용하여 문자를 비트로 인코딩합니다. 인스턴스 수준, 데이터베이스 수준, 테이블 수준 또는 열 수준에서 데이터 정렬 및 문자 집합을 설정할 수 있습니다. RDS MySQL은 기본적으로 데이터베이스에 Latin1 인코딩을 사용합니다. 사용 중인 문자 집합에 대한 자세한 내용은 MySQL 설명서 의 information_schema.CHARACTER_SETS 테이블을 참조하십시오.

다음은 RDS MySQL의 문자 집합과 관련된 사용 가능한 매개 변수입니다.

매개변수 이름	설명	기본값	사용 가능한 값
character_set_client	클라이언트에서 도착한 명령문에 대한 문자 집합	UTF8	big5, dec8, cp850, hp8, koi8r, latin1, latin2, swe7, ascii, ujis, sjis, 히브리어, tis620, euckr, koi8u, gb2312, 그리스어, cp1250, gbk, key, scut6, armci 매크로맨, cp852, latin7, utf8mb4, cp1251, cp1256, cp1257, 바이너리, geostd8, cp932, eucjpms
character_set_connection	문자 집합 소개자가 없는 리터럴 및 숫자에서 문자열로의 변환에 사용되는 문자 집합	UTF8	big5, dec8, cp850, hp8, koi8r, latin1, latin2, swe7, ascii, ujis, sjis, 히브리어, tis620, euckr, koi8u, gb2312, 그리스어, cp1250, gbk, latin5, scut2, 키 macce, 매크로맨, cp852, latin7, utf8mb4, cp1251, utf16, cp1256, cp1257, utf32, 바이너리, geostd8, cp932, eucjpms
character_set_database	기본 데이터베이스에서 사용하는 문자 집합	라틴어1	big5, dec8, cp850, hp8, koi8r, latin1, latin2, swe7, ascii, ujis, sjis, 히브리어, tis620, euckr, koi8u, gb2312, 그리스어, cp1250, gbk, latin5, scut2, 키 macce, 매크로맨, cp852, latin7, utf8mb4, cp1251, utf16, cp1256, cp1257, utf32, 바이너리, geostd8, cp932, eucjpms
character_set_filesystem	파일 시스템 문자 집합	바이너리	big5, dec8, cp850, hp8, koi8r, latin1, latin2, swe7, ascii, ujis, sjis, 히브리어, tis620, euckr, koi8u, gb2312, 그리스어, cp1250, gbk, latin5, scut2, 키 macce, 매크로맨, cp852, latin7, utf8mb4, cp1251, utf16, cp1256, cp1257, utf32, 바이너리, geostd8, cp932, eucjpms
character_set_results	쿼리 결과를 클라이언트에 반환하는 데 사용되는 문자 집합	UTF8	big5, dec8, cp850, hp8, koi8r, latin1, latin2, swe7, ascii, ujis, sjis, 히브리어, tis620, euckr, koi8u, gb2312, 그리스어, cp1250, gbk, latin5, scut2, 키 macce, 매크로맨, cp852, latin7, utf8mb4, cp1251, utf16, cp1256, cp1257, utf32, 바이너리, geostd8, cp932, eucjpms
character_set_server	서버의 기본 문자 집합	라틴어1	big5, dec8, cp850, hp8, koi8r, latin1, latin2, swe7, ascii, ujis, sjis, 히브리어, tis620, euckr, koi8u, gb2312, 그리스어, cp1250, gbk, latin5, scut2, 키 macce, 매크로맨, cp852, latin7, utf8mb4, cp1251, utf16, cp1256, cp1257, utf32, 바이너리, geostd8, cp932, eucjpms

열별 문자 집합은 테이블 수준에서 기본 문자 집합을 재정의합니다. 마찬가지로, 모든 테이블 특정 문자 집합은 데이터베이스 수준에서 기본 문자 집합을 재정의합니다. 또한 데이터베이스 특정 문자 집합은 인스턴스 수준에서 기본 문자 집합을 재정의합니다.

인스턴스 수준에서 문자 집합 매개 변수를 확인하려면 다음 쿼리를 사용합니다.

MySQL [(none)]> show variables like '%character%';
+--------------------------+------------------------------------------------+
| Variable_name            | Value                                          |
+--------------------------+------------------------------------------------+
| character_set_client     | utf8                                           |
| character_set_connection | utf8                                           |
| character_set_database   | latin1                                         |
| character_set_filesystem | binary                                         |
| character_set_results    | utf8                                           |
| character_set_server     | latin1                                         |
| character_set_system     | utf8                                           |
| character_sets_dir       | /rdsdbbin/oscar-5.6.10a.31.101/share/charsets/ |
+--------------------------+------------------------------------------------+
8 rows in set (0.00 sec)

특정 데이터베이스의 기본 문자 집합을 확인하려면 다음 쿼리와 유사한 쿼리를 사용합니다. 이 예에서는 데이터베이스 이름 'TEST'를 사용합니다.

MySQL [(none)]> SELECT SCHEMA_NAME,
    -> DEFAULT_CHARACTER_SET_NAME
    -> FROM INFORMATION_SCHEMA.SCHEMATA
    -> WHERE SCHEMA_NAME='TEST';
+-------------+----------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME |
+-------------+----------------------------+
| TEST        | utf8                       |
+-------------+----------------------------+
1 row in set (0.00 sec)

테이블 수준 및 열 수준 문자 집합을 확인하려면 명령을 사용할 수 있습니다 show create table <table_name>.

인스턴스 수준에서 파라미터를 변경하려면 파라미터 그룹에서 값을 설정하십시오. 이렇게 하려면 다음 단계를 수행하십시오.

사용자 지정 파라미터 그룹을 생성합니다.
매개변수를 필요한 값으로 수정하십시오.
데이터베이스에 로그인하고 다음 명령을 사용하여 매개변수 값을 봅니다.

> show variables like '%character%';

인스턴스가 이미 사용자 지정 파라미터 그룹을 사용 중인 경우 해당 그룹에서 파라미터를 수정할 수 있습니다. 자세한 내용 은 RDS 설명서의 DB 파라미터 그룹 작업을 참조하십시오.

그러나 , 및 매개변수 character_set_client는 character_set_connection를 character_set_results사용하여 쿼리할 때 매개변수 그룹에 표시된 문자 집합을 사용하지 않습니다 . MySQL 문서 에 "이러한 변수의 세션 값은 클라이언트가 서버에 연결할 때 클라이언트가 요청한 문자 집합을 사용하여 설정됩니다." 즉, 연결할 때마다 문자 집합을 명시적으로 지정해야 합니다.> show variables like '%character%';

SET NAMES명령문 과 함께 세션 수준 매개변수를 사용하여 이를 수행할 수 있습니다 . SQL 문 SET NAMES 'char_set_name'은 다음 세 가지 SQL 문과 동일합니다.

> set character_set_client = char_set_name;
> set character_set_connection = char_set_name;
> set character_set_results = char_set_name;

예를 들어 다음과 같이 SQL 문 SET NAMES utf8mb4을 사용하여 필요한 모든 매개변수( character_set_client, character_set_connection, character_set_results)를 로 설정할 수 utf8mb4있습니다.

MySQL [(none)]> SET NAMES utf8mb4
    -> ;
Query OK, 0 rows affected (0.02 sec)
MySQL [(none)]>  show variables like '%character%';
+--------------------------+------------------------------------------------+
| Variable_name            | Value                                          |
+--------------------------+------------------------------------------------+
| character_set_client     | utf8mb4                                        |
| character_set_connection | utf8mb4                                        |
| character_set_database   | latin1                                         |
| character_set_filesystem | binary                                         |
| character_set_results    | utf8mb4                                        |
| character_set_server     | latin1                                         |
| character_set_system     | utf8                                           |
| character_sets_dir       | /rdsdbbin/oscar-5.6.10a.31.101/share/charsets/ |
+--------------------------+------------------------------------------------+
8 rows in set (0.00 sec)

SET NAMES애플리케이션에서 모든 연결에 대해 명령을 실행하지 않으려면 init_connect매개변수를 사용할 수 있습니다. 다음은 init_connect이러한 매개변수의 값을 로 설정하는 데 사용하는 예입니다 utf8mb4.

-- setting the 'init_connect' to 'SET NAMES utf8mb4' via parameter group
mysql> show variables like '%init_connect%'; +---------------+-------------------+ 
| Variable_name | Value             | 
+---------------+-------------------+ 
| init_connect  | SET NAMES utf8mb4 | 
+---------------+-------------------+

또는 MySQL 설명서 에 언급된 대로 매개변수 그룹에서 다음 매개변수를 그림과 같이 수정할 수 있습니다. 이러한 설정은 클라이언트 정보를 무시하고 기본 서버 문자 집합을 사용합니다.

character-set-client-handshake=0 
skip-character-set-client-handshake=1

문자 집합 매개변수를 변경해도 기존 테이블의 문자 집합은 수정되지 않습니다. 그러나 매개변수 변경 후 생성된 모든 테이블에는 새 문자 집합이 있습니다. ALTER TABLE기존 테이블의 문자 집합을 변경하려면 다음과 같은 명령문 을 사용해야 합니다 .

ALTER TABLE <table_name>
CHARACTER SET <desired_character_set>;

마찬가지로 데이터베이스별 및 열별 설정을 변경하려면 다음과 같이 ALTER DATABASE및 문을 사용합니다.ALTER TABLE

ALTER DATABASE <database_name> CHARACTER SET = utf8mb4  ;
ALTER TABLE <table_name> CHANGE column_name <column_name> <datatype> CHARACTER SET <desired_character_set>;

MySQL의 utf8 문자 집합은 문자당 최대 3바이트를 사용하지만 utf8mb4 문자 집합은 문자당 4바이트를 사용합니다. utf8 문자 집합은 중국어, 일본어 및 한국어 문자와 같은 다양한 다국어 문자를 저장할 수 있습니다. 그러나 저장할 수 없는 특정 문자가 있습니다. 예를 들어, 유니코드 문자 눈사람(U+2603) ☃과 같은 다양한 이모티콘은 utf8에 저장할 수 없지만 utf8mb4에 저장할 수 있습니다. utf8 문자 집합 등의 모든 문자를 저장할 수 있으므로 utf8mb4를 사용하는 것이 좋습니다.

데이터 정렬과 관련된 매개변수

데이터 정렬 은 문자 집합의 문자를 비교하는 규칙 집합을 정의합니다. 각 문자 집합에는 하나 이상의 데이터 정렬이 있으며 하나 이상의 기본 데이터 정렬이 있습니다.

명령을 사용하여 SHOW CHARACTER SET특정 문자 집합에 대한 기본 데이터 정렬을 가져올 수 있습니다. MySQL 설명서의 information_schema.collations 테이블에서 기본 데이터 정렬을 찾을 수도 있습니다 .

각 데이터 정렬 이름에는 네 부분이 있습니다. 순서대로 다음과 같습니다.

문자 집합 이름입니다.
예를 들어 ja일본어와 같은 언어입니다. 특별한 "언어"는 바이너리이며, 이는 각 바이트가 하나씩 직접 비교됨을 의미합니다.
UCA 9.0.0 기반 데이터 정렬인지 여부입니다. UCA 9.0.0 기반 데이터 정렬에는 이름에 0900이 있습니다.
문자에 악센트가 있는지, 대소문자를 구분하는지 또는 둘 다인지에 따라 최대 3개의 수정자.

예를 들어 데이터 정렬 utf8mb4_ja_0900_as_cs은 지원되는 문자 집합으로 utf8mb4, 언어로 일본어, UCA 9.0.0 기반 데이터 정렬을 기반으로 하는 900, 대소문자 구분을 위한 cs를 표시합니다. 총 5개의 악센트 및 대소문자 수정자가 있습니다.

ai: 악센트를 구분하지 않음, ä는 a와 같습니다.
as: 악센트 구분, ä는 같지 않습니다.
ci: 대소문자를 구분하지 않고 A와 같습니다.
cs: 대소문자를 구분하며 A와 같지 않습니다.
ks: 가나 구분(일본어만 해당).

다음은 RDS MySQL에서 데이터 정렬과 관련된 사용 가능한 매개변수입니다.

매개변수	설명	기본값
collation_connection	연결 문자 집합의 데이터 정렬	utf8_general_ci
collation_server	서버의 기본 데이터 정렬	latin1_swedish_ci

문자 집합과 마찬가지로 인스턴스, 데이터베이스, 테이블 및 열 수준에서 데이터 정렬을 설정할 수 있습니다. 모든 열 관련 데이터 정렬은 테이블 수준에서 기본 데이터 정렬을 재정의합니다. 마찬가지로 모든 테이블 관련 데이터 정렬은 데이터베이스 수준에서 기본 데이터 정렬을 재정의합니다. 또한 데이터베이스별 데이터 정렬은 인스턴스 수준에서 기본 데이터 정렬을 재정의합니다.

인스턴스 수준에서 데이터 정렬 매개변수를 확인하려면 다음 쿼리를 사용할 수 있습니다.

MySQL [(none)]> show variables like '%collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

특정 데이터베이스의 기본 데이터 정렬을 확인하려면 다음 쿼리를 사용할 수 있습니다. 이 예에서 데이터베이스 이름은 test입니다.

MySQL [(none)]> SELECT SCHEMA_NAME,
    -> DEFAULT_COLLATION_NAME
    -> FROM INFORMATION_SCHEMA.SCHEMATA
    -> WHERE SCHEMA_NAME = 'test';
+-------------+------------------------+
| SCHEMA_NAME | DEFAULT_COLLATION_NAME |
+-------------+------------------------+
| test        | utf8_general_ci        |
+-------------+------------------------+
1 row in set (0.00 sec)

테이블 수준 및 열 수준 데이터 정렬 값을 확인하려면 를 사용할 수 있습니다 show create table <table_name>.

인스턴스 수준에서 파라미터를 변경하려면 파라미터 그룹에서 값을 설정하십시오. 이렇게 하려면 다음 단계를 수행하십시오.

사용자 지정 파라미터 그룹을 생성합니다.
매개변수를 필요한 값으로 수정하십시오.
데이터베이스에 로그인하고 다음 명령을 사용하여 매개변수 값을 봅니다.

> show variables like ‘%collation%’;

데이터 정렬 매개변수를 변경해도 기존 테이블의 데이터 정렬은 수정되지 않습니다. 그러나 매개변수 변경 후에 생성된 모든 테이블에는 새 데이터 정렬이 있습니다. ALTER TABLE기존 테이블의 데이터 정렬을 변경하려면 다음과 같은 명령문 을 사용해야 합니다 .

ALTER TABLE <table_name> COLLATE  <desired_collation>;

마찬가지로 데이터베이스별 및 열별 설정을 변경하려면 다음과 같이 ALTER DATABASE및 문을 사용합니다.ALTER TABLE

ALTER DATABASE <database_name>  COLLATE  <desired_collation> ;
ALTER TABLE <table_name> CHANGE column_name <column_name> <datatype> COLLATE  <desired_collation>;

slow_query_log

이 매개변수는 느린 쿼리를 식별하는 데 매우 유용합니다. 값은 느린 쿼리 로그를 비활성화하려면 0(또는 OFF)이고 로그를 활성화하려면 1(또는 ON)일 수 있습니다. 기본값은 이 기능을 비활성화하는 0입니다.

쿼리가 느린지 여부를 결정하는 임계값은 다른 매개변수인 에 의해 정의됩니다 long_query_time. 매개변수 는 long_query_time쿼리 실행 시간을 초 단위로 정의합니다. 테이블이나 파일에서 느린 쿼리를 캡처할 수 있습니다. 수행하는 작업은 또는 와 log_output같을 수 있는 다른 매개변수의 값에 따라 다릅니다 . 기본값은 테이블입니다. 자세한 내용은 이 YouTube 동영상 을 참조하십시오 .TABLEFILE

log_queries_not_using_indexes

이것은 문제가 있는 쿼리를 식별하는 데 자주 사용되는 또 다른 매개변수입니다. 이 매개변수를 1(활성화)로 설정하면 인덱스를 사용하지 않는 모든 쿼리를 느린 쿼리 로그에 기록합니다. 기본적으로 이 매개변수는 활성화되어 있지 않으며 값은 0입니다.

innodb_file_per_table

활성화 되면 innodb_file_per_table(값을 기본값인 1로 설정하여) InnoDB는 새로 생성된 각 테이블에 대한 데이터와 인덱스를 시스템 테이블스페이스 대신 별도의 .ibd 파일 에 저장합니다. 기본적으로 RDS는 이 매개변수를 활성화합니다. 즉, 각 테이블스페이스의 데이터는 자체 .ibd 파일에 저장됩니다. 0으로 설정하여 비활성화 하면 innodb_file_per_table 모든 테이블도 시스템 테이블스페이스에 할당됩니다. 이 경우 테이블이나 인덱스를 삭제하거나 시스템 테이블스페이스에 할당된 테이블에서 데이터를 삭제하거나 자르면 이전에 차지한 공간이 재사용 가능한 공간으로 표시됩니다. 그러나 이 명령은 파일 시스템에 공간을 확보하지 않습니다.

innodb_file_per_table 이 경우 개별 InnoDB 테이블을 삭제하고 해당 테이블이 DB 인스턴스에 사용하는 스토리지를 회수할 수 있도록 1로 설정하는 것이 좋습니다 . 대부분의 경우 innodb_file_per_table매개변수를 1로 설정하는 것이 가장 좋습니다.

그러나 데이터베이스의 테이블 수가 매우 많으면 다른 접근 방식을 취할 수 있습니다. 예를 들어 표준(마그네틱) 또는 범용 SSD 스토리지를 사용할 때 1,000개 이상의 테이블이 있고 프로비저닝된 IOPS 스토리지를 사용할 때 10,000개가 넘는 테이블이 있습니다. 이 경우 데이터베이스 충돌 복구에 소요되는 시간을 개선하려면 이 값을 0으로 설정해야 합니다. 이 개선 사항은 이 매개변수를 0으로 설정하면 개별 테이블스페이스가 생성되지 않기 때문에 발생합니다.

연결 시간 초과와 관련된 매개변수

다음은 연결 시간 초과와 관련된 일부 매개변수입니다. MySQL 오류 로그에서 시간 초과 관련 오류를 제거하기 위해 이러한 매개변수를 조정해야 하는 경우가 많습니다.

net_write_timeout

이 매개변수는 쓰기를 중단하기 전에 블록이 연결에 기록될 때까지 기다리는 시간(초)을 나타냅니다. 권장 값은 120초입니다. 요구 사항에 맞게 조정해야 할 수도 있습니다(기본값은 60초). 일반적으로 오류 로그의 'Got timeout' 메시지는 mysqld에 설정된 값에 의해 발생하고 오류 로그의 'Got error' 메시지는 비정상적으로 종료된 연결에 의해 발생합니다.

max_allowed_packet

이 매개변수는 하나의 패킷 또는 생성된 문자열이나 중간 문자열의 최대 크기 또는 mysql_stmt_send_long_data() C API 함수에서 보낸 매개변수를 나타냅니다. 기본 설정은 4MB입니다. 64MB 값을 권장하며 요구 사항에 맞게 매개변수를 조정해야 할 수도 있습니다. max_allowed_packet행 에 클라이언트 값 보다 많은 데이터가 있으면 오류가 보고됩니다. 큰 BLOB 열이나 긴 문자열 을 사용하는 경우 이 값을 늘려야 합니다 . 사용하려는 가장 큰 BLOB 만큼 큰 값으로 설정하십시오 . 프로토콜 제한 max_allowed_packet 은 1GB입니다.

wait_timeout

이 매개변수는 서버가 비대화형 연결을 닫기 전에(비대화형 시간 초과) 비대화형 연결에서 활동을 기다리는 시간(초)을 나타냅니다. 기본값은 28,800입니다. 클라이언트가 wait_timeout몇 초 동안 아무 작업도 하지 않으면 MySQL 서버는 연결을 종료합니다. 이 변수에 대한 적절한 설정은 특정 환경에 따라 다릅니다.

대규모 트랜잭션(예: 대규모 행 기반 대량 업데이트, 삽입 또는 삭제 wait_timeout)을 재생하고 재생 시간이 wait_timeout. wait_time이 문제를 방지하려면 600(10분) 이상으로 설정할 수 있습니다 . 가치는 실제로 다른 "나쁜 관행" 이 wait_timeout있을 때만 요소가 됩니다. 예를 들어 InnoDB 테이블에 고유 키를 포함하지 않는다는 것은 행 기반 로그 대량 업데이트가 소스 인스턴스에서 처음에 했던 것보다 재생 시 훨씬 더 오래 걸린다는 것을 의미합니다.

Interactive_timeout

MySQL 매뉴얼에 따르면 이 매개변수는 대화형 연결을 닫기 전에 서버가 대화형 연결에서 활동을 기다리는 시간(초)입니다. 기본값은 28,800입니다. 대화 형 클라이언트CLIENT_INTERACTIVE 는 옵션을 사용하여 수행하는 클라이언트로 정의됩니다 mysql_real_connect (). 이 매개변수의 경우 적절한 설정은 특정 환경에 따라 다릅니다. 오류가 발생하면 오류가 사라질 때까지 값을 점진적으로 늘려야 할 수 있습니다.

skip_name_resolve

--skip-name-resolve 이 매개변수는 옵션 값에서 설정됩니다 . OFF(RDS의 경우 0)이면 mysqld 는 클라이언트 연결을 확인할 때 호스트 이름을 확인합니다. ON(RDS의 경우 1)이면 mysqld 는 IP 번호만 사용합니다. 이 경우 부여 테이블의 모든 Host열 값은 IP 주소 또는 localhost여야 합니다. 따라서 이 매개변수는 연결 시 DNS 조회를 방지하는 데 사용됩니다. 기본적으로 꺼져 있습니다(0으로 설정).

이 매개변수가 꺼져 있으면 오류 로그에 다음 경고가 표시될 수 있습니다.

2017-03-03 13:10:00          [-]2017-03-03 13:07:22 3396 [Warning] IP address ‘<ip-address-rep-instance>’ could not be resolved: Temporary failure in name resolution

innodb_print_all_deadlocks

이 매개변수가 활성화되면(1로 설정) InnoDB 사용자 트랜잭션과 관련된 교착 상태를 MySQL 오류 로그에 기록합니다. SHOW ENGINE INNODB STATUS그렇지 않으면 이 정보는 명령 을 사용하여 마지막 교착 상태에 대해서만 사용할 수 있습니다 . 이 옵션을 사용하여 애플리케이션 논리에 교착 상태를 감지하고 작업을 재시도하는 적절한 오류 처리가 없는 이유를 해결할 수 있습니다. 기본적으로 이 매개변수는 비활성화되어 있으며 0으로 설정되어 있습니다.

max_connections

이 매개변수는 MySQL이 허용하는 최대 동시 연결 수를 제한하는 데 사용됩니다. 더 이상 사용할 수 있는 연결이 없으면 연결을 시도할 때 다음 오류가 반환됩니다.

ERROR 1040 (HY000): Too many connections

의 현재 값은 max_connections다음 명령을 사용하여 찾을 수 있습니다.

mysql> SHOW GLOBAL VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'max_connections';
+-----------------+----------------+
| VARIABLE_NAME   | VARIABLE_VALUE |
+-----------------+----------------+
| MAX_CONNECTIONS | 500            |
+-----------------+----------------+
1 row in set (0.00 sec)

기본적으로 이 매개변수는 DBInstanceClassMemory값에서 계산되는 공식을 기반으로 합니다. 이 매개변수는 RDS 인스턴스의 사용 가능한 총 메모리에 따라 자동으로 조정됩니다. 공식은 다음과 같습니다.

max_connections = DBInstanceClassMemory/12582880

이러한 값은 변경하지 않는 것이 좋습니다. 더 많은 연결이 필요한 경우 더 큰 RDS 인스턴스 크기로 업그레이드하는 것이 좋습니다.

허용할 연결 수를 결정하고 최적 값을 조정할 때 상태 값을 max_used_connection확인하는 것이 좋습니다 max_used_connections. 서버 상태 변수 는 max_used_connections서버가 시작된 이후 동시에 사용된 최대 연결 수를 보고합니다. 다음 명령을 사용하여 값을 확인하십시오.

mysql> show global status like '%Conn%';

max_connect_errors

이 매개변수는 서버가 호스트를 차단하기 전에 발생할 수 있는 연결 오류 수를 나타냅니다. 호스트 의 max_connect_errors연속 연결 요청이 연결에 성공하지 못한 채 중단되면 서버는 해당 호스트의 추가 연결을 차단합니다. 기본값은 100이며 보안 요구 사항 및 환경에 맞게 조정할 수 있습니다.

예를 들어 , max_connect_errors=5000호스트 X의 5,000개 연결 요청이 중단된 후 다음과 같은 오류가 발생합니다.

Host X is blocked because of many connection errors ().

다음 명령을 사용하여 호스트 차단을 해제합니다.

mysql> flush hosts;
mysql> show global variables like 'max_connect_errors'; 
+| Variable_name      | Value | +--------------------+-------+ | max_connect_errors | 5000| +--------------------+-------+ 
1 row in set (0.00 sec)

SQL

결론

이 블로그 게시물 시리즈에서는 MySQL용 RDS에서 가장 일반적으로 사용자 지정되는 매개변수에 대한 모범 사례를 제공합니다. RDS MySQL을 사용하기 전에 잠재적인 운영 문제를 방지하기 위해 사용자 지정 매개변수 값을 검토하는 것이 좋습니다.

728x90

저작자표시

'DB' 카테고리의 다른 글

ProxySQL 시작하기 (설치) (1)	2023.01.13
SQL Server Maintenance Solution (0)	2022.08.04
Amazon RDS for MySQL의 파라미터 구성 모범 사례, 2부: 복제와 관련된 파라미터 (0)	2022.08.04
Amazon RDS for MySQL의 파라미터 구성 모범 사례, 1부: 성능 관련 파라미터 (0)	2022.08.04
MySQL status (0)	2013.08.05

세계정복

Amazon RDS for MySQL의 파라미터 구성 모범 사례, 3부: 보안, 운영 관리 용이성 및 연결 제한 시간과 관련된 파라미터

보안 관련 매개변수

init_connect

old_passwords

default_password_lifetime

max_user_connections

작업 관리와 관련된 매개변수

log_warnings

성능 스키마

Event_scheduler

명시적_defaults_for_timestamp

문자 집합과 관련된 매개변수

데이터 정렬과 관련된 매개변수

slow_query_log

log_queries_not_using_indexes

innodb_file_per_table

연결 시간 초과와 관련된 매개변수

net_write_timeout

max_allowed_packet

wait_timeout

Interactive_timeout

skip_name_resolve

innodb_print_all_deadlocks

max_connections

max_connect_errors

결론

'DB' 카테고리의 다른 글

티스토리툴바

Amazon RDS for MySQL의 파라미터 구성 모범 사례, 3부: 보안, 운영 관리 용이성 및 연결 제한 시간과 관련된 파라미터

보안 관련 매개변수

init_connect

old_passwords

default_password_lifetime

max_user_connections

작업 관리와 관련된 매개변수

log_warnings

성능 스키마

Event_scheduler

명시적_defaults_for_timestamp

문자 집합과 관련된 매개변수

데이터 정렬과 관련된 매개변수

slow_query_log

log_queries_not_using_indexes

innodb_file_per_table

연결 시간 초과와 관련된 매개변수

net_write_timeout

max_allowed_packet

wait_timeout

Interactive_timeout

skip_name_resolve

innodb_print_all_deadlocks

max_connections

max_connect_errors

결론

'DB' 카테고리의 다른 글

관련글

티스토리툴바