Last Updated on 1월 9, 2024 by Jade(정현호)
안녕하세요.
이번 포스팅에서는 MySQL 8 버전에서 사용할 수 있는 파라미터 관리에 대한 SET/RESET PERSIST 에 관한 내용에 대해서 확인해보려고 합니다.
Contents
Overview of Persisted System Variables
MySQL 서버의 시스템 변수는 전체 서버 작동에 영향을 미치는 전역 값(global value), 현재 세션에 영향을 미치는 세션 값(session value) 또는 둘 다를 가질 수 있습니다.
많은 시스템 변수는 동적(dynamic)이며, 현재 서버 인스턴스의 시스템 변수를 변경하기 위해서 SET 문을 사용하여 런타임에 변경할 수 있습니다.
Note
포스팅에서 사용하는 런타임은 "DB 인스턴스 기동 중" 을 의미하며, 런타임시 변경은 "DB 시스템의 기동 운영 중에 Dynamic 으로 시스템 변수를 변경" 을 의미합니다.
SET PERSIST 명령어 또는 data 디렉토리의 mysqld-auto.cnf 파일에 특정 전역 시스템 변수를 유지하여, MySQL 그 다음 시작을 위한 서버 작동에 영향을 주는 데 사용할 수 있습니다.
RESET PERSIST 는 mysqld-auto.cnf 에서 persisted settings(지속 설정)을 제거합니다.
런타임 시 전역 시스템 변수(Global System Parameter) 를 유지하는 기능은 MySQL 서버 시작시에도 설정을 계속 사용할 수 있도록 해줍니다.
MySQL 시작시에 my.cnf 파일을 통해서 또는 SET 명령어를 통해서 런타임시에 많은 시스템 변수를 설정할 수 있습니다 하지만 이러한 방법은 그 다음으로 서버 호스트에 대해서 액세스가 필요하며 또는 지속적으로 시스템 변수가 유지되는 기능은 제공하지 않습니다(재시작시)
- my.cnf 파일을 수정하려면 파일에 대한 직접 액세스가 필요하며 MySQL 서버가 구동되는 호스트에 대한 로그인이 필요하며 이 방법이 편하지는 않습니다.
- MySQL 서버에 접속하여 SET GLOBAL 명령어를 통해서 시스템 변수를 수정할 수 있지만, 이 변경 사항은 현재 실행중인 MySQL 서버에서만 영향을 미치며 재시작에 대해서는 영구적이지 않습니다.
MySQL은 Data 디렉토리의 mysqld-auto.cnf 파일을 통해서 시스템 변수 설정을 유지하는 변형된(새로운) SET 구문의 MySQL 8 버전 부터 제공합니다.
persist 파라미터 적용에 관한 사용 유무는 persisted_globals_load 시스템 변수에 의해서 컨트롤 되며, 기본값은 ON 입니다.
SET PERSIST[_ONLY]
SET PERSIST 을 설정하면 data 디렉토리에 mysqld-auto.cnf 이 생성되고 mysqld-auto.cnf 파일에 내용이 저장됩니다.
mysqld-auto.cnf 파일 먼저 MySQL 서버에서 한 번도 SET PERSIST 를 한적이 없다면 파일이 존재하지 않습니다.
# ls -al data | grep mysqld-auto.cnf #
SET PERSIST 은 아래와 같이 수행하여 시스템 변수를 설정할 수 있습니다.
-- 설정 전 mysql> select @@max_connections; +-------------------+ | @@max_connections | +-------------------+ | 500 | +-------------------+ -- 시스템 파라미터 변경 SET PERSIST max_connections = 2000; -- 또는 SET @@PERSIST.max_connections = 2000; -- 변경 후에 다시 다시 조회 show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 2000 | +-----------------+-------+
파일이 생성되었는지 다시 확인해보도록 하겠습니다.
# ls -al data | grep mysqld-auto.cnf
-rw-r-----. 1 mysql mysql 172 7월 25 05:47 mysqld-auto.cnf
<-- 파일이 생성됨
MySQL 서버에서는 mysqld-auto.cnf 에서 지속(PERSIST) 시스템 변수를 제거하기 위한 RESET PERSIST 명령어 문을 제공합니다.
관련된 파라미터로는 persisted_global_load 이며 해당 파라미터의 설정이 off 로 설정되어 있다면 해당 기능을 사용하지 않게 됩니다.(기본값은 on)
Persist 서버 구성의 특징
- 지속 설정(Persisted settings)은 런타임에 이루어집니다.
- 지속 설정(Persisted settings)은 영구적이고 MySQL 서버 다시 시작 시에도 적용 됩니다.(mysqld-auto.cnf 파일에 기록되기 때문에)
- 수동 입력인 my.cnf 에 입력(기록)의 오류는 MySQL 서버 재시작시에 오류를 발견할 수 있지만, SET 명령어를 통한 시스템 변수 변경은 구문 오류 등이 있을 경우 설정이 성공하지 못하고 서버의 구성을 변경하지 않기 대문에 잘못된 설정의 가능성을 방지합니다.
SET PERSIST 명령어를 통해서 시스템 변수를 변경하는 작업은 마치 오라클 DB에서 "alter system set ... scope=both" 와 유사하다고 할 수 있습니다.
시스템 전역 변수를 data 디렉토리의 mysqld-auto.cnf 파일에 유지하고 입력하려면 PERSIST 키워드 또는 파라미터 앞에 @@PERSIST. 을 붙여서 변경을 진행하면 됩니다.
SET PERSIST max_connect_errors = 99999; SET @@PERSIST.max_connect_errors = 99999; mysql> show variables like 'max_connect_errors'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 99999 | +--------------------+-------+
SET GLOBAL [시스템 변수명] 명령어와 마찬가지로 SET PERSIST 는 전역 시스템 변수의 런타임 값을 변경하지만 SET PERSIST 명령어는 mysqld-auto.cnf 파일에 해당 시스템 변수 값에 대한 내용을 기록 합니다.(기존 변수 설정이 있는 경우 대체)
PERSIST_ONLY
런타임시에 시스템 변수 값을 변경하지 않고 파일에만 해당 내용을 기록하려고 할 경우 _ONLY 를 사용하여 명령어를 수행하면 됩니다.
SET PERSIST_ONLY @@PERSIST_ONLY.
위와 같이 _ONLY 가 붙은 명령어를 사용할 경우 mysqld-auto.cnf 파일에만 기록하고 런타임의 시스템 변수 값을 변경하지는 않습니다.
따라서 해당 명령어는 Dynamic 으로 적용이 불가능한, MySQL 인스턴스의 재시작이 필요한 시스템 변수를 변경할 때 유용할 수 있는 명령어입니다.
마치 오라클 DB에서 "alter system set ... sceop=spfile" 과 같이 scope 절에 spfile 을 사용한 것과 유사한 형태라고 이해하시면 될 것 같습니다.
-- 변경 전 확인 show variables like 'back_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | back_log | 500 | +---------------+-------+ -- 시스템 변수 변경 SET PERSIST_ONLY back_log = 1000; -- 또는 SET @@PERSIST_ONLY.back_log = 1000; -- 변경 후 확인 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | back_log | 500 | +---------------+-------+
Information About Persisted System Variables
show variable 이나 select @@시스템변수 명으로 파라미터 조회할 수 있지만, 이전 단계에서 확인한 것처럼 PERSIST_ONLY 으로 변경한 값은 설정한 내용이 확인이 안되는 부분 과 사용중인 여러 시스템 파라미터 중에서 SET PERSIST[_ONLY] 명령어로 설정한 파라미터에 대해서 확인하고자 할 경우
performance_schema.persisted_variables 에서 내용을 확인할 수 있습니다.
select * from performance_schema.persisted_variables; +--------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +--------------------+----------------+ | max_connections | 2000 | | max_connect_errors | 99999 | | back_log | 1000 | +--------------------+----------------+
performance_schema.variables_info 테이블에는 각 시스템 변수가 가장 최근에 설정된 시간 정보와 설정한 사용자를 보여주는 정보, 시스템 변수의 설정 유형 정보가 담겨 있습니다.
select distinct VARIABLE_SOURCE
from performance_schema.variables_info;
+-----------------+
| VARIABLE_SOURCE |
+-----------------+
| COMPILED |
| COMMAND_LINE |
| EXPLICIT |
| DYNAMIC |
+-----------------+
select * from performance_schema.variables_info
where VARIABLE_NAME ='max_connections'\G
*************************** 1. row ***************************
VARIABLE_NAME: max_connections
VARIABLE_SOURCE: DYNAMIC
VARIABLE_PATH:
MIN_VALUE: 1
MAX_VALUE: 100000
SET_TIME: 2022-07-25 05:47:27.471708
SET_USER: root
SET_HOST: localhost
위의 딕셔너리 테이블 내용이 mysqld-auto.cnf 파일의 내용과 일치하기 때문에 RESET PERSIST 명령어는 persisted_variables 테이블의 내용에 영향을 미칩니다.
반면 RESET PERSIST 는 변수 값을 변경하지 않기 때문에 서버가 다시 시작될 때까지 variables_info 테이블의 내용에 영향을 미치지 않습니다.
variables_info 테이블에는 시스템 변수의 값에 대한 정보가 없습니다.
-- variables_info 테이블 desc 정보
+-----------------+-----------------------------------------------------------------------------------------------------------+----------+
| Field | Type | Default |
+-----------------+-----------------------------------------------------------------------------------------------------------+----------+
| VARIABLE_NAME | varchar(64) | NULL |
| VARIABLE_SOURCE | enum('COMPILED','GLOBAL','SERVER','EXPLICIT','EXTRA','USER','LOGIN','COMMAND_LINE','PERSISTED','DYNAMIC') | COMPILED |
| VARIABLE_PATH | varchar(1024) | NULL |
| MIN_VALUE | varchar(64) | NULL |
| MAX_VALUE | varchar(64) | NULL |
| SET_TIME | timestamp(6) | NULL |
| SET_USER | char(32) | NULL |
| SET_HOST | char(255) | NULL |
+-----------------+-----------------------------------------------------------------------------------------------------------+----------+
* 가로 사이즈에 따라서 일부 컬럼이 편집 되었습니다.
RESET PERSIST 을 실제로 수행하면 variables_info 테이블의 정보가 변경되는 것이 없음을 확인할 수 있습니다.
mysql> RESET PERSIST max_connections;
mysql> select * from performance_schema.variables_info
where VARIABLE_NAME ='max_connections'\G
*************************** 1. row ***************************
VARIABLE_NAME: max_connections
VARIABLE_SOURCE: DYNAMIC
VARIABLE_PATH:
MIN_VALUE: 1
MAX_VALUE: 100000
SET_TIME: 2022-07-25 05:47:27.471708 <--- 시간 정보가 변하지 않음
SET_USER: root
SET_HOST: localhost
SET_HOST는 파라미터 변경을 실행한 Host 입니다. 위의 예시에서는 localhost 에서 수행한 결과이고, 원격(Remote)서 수행하였을 경우 다른 정보로 표기됩니다.
다음 진행할 내용에 의해서 시스템 변수는 다시 적용하도록 하겠습니다.
SET PERSIST max_connections = 2000;
mysqld-auto.cnf
위에서 SET PERSIST[_ONLY] 로 적용한 내용은 data 디렉토리의 mysqld-auto.cnf 에 저장되며 저장되는 형식은 json 형식을 따릅니다.
아래와 같이 그냥 읽게 되면 보는데 조금은 어려운 형태로 출력 됩니다.
# cat mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "max_connect_errors" : { "Value" : "99999" ,
"Metadata" : { "Timestamp" : 1658719120737093 , "User" : "root" , "Host" : "localhost" } }
, "max_connections" : { "Value" : "2000" , "Metadata" : { "Timestamp" : 1658724157388870
, "User" : "root" , "Host" : "localhost" } } ,
"mysql_server_static_options" : { "back_log" : { "Value" : "1000" ,
"Metadata" : { "Timestamp" : 1658719941194905 , "User" : "root" , "Host" : "localhost" } } } } }
* 가로 길이에 따라서 임의로 개행 하였으나, 원래 출력은 가로로 되어 있음
리눅스에서 jq 패키지를 설치하여 사용하거나 파이썬의 json.tool 을 이용하면 보기 편한 형태로 포맷팅 하여 출력할 수 있습니다.
### jq 패키지 이용
# cat mysqld-auto.cnf | jq
{
"Version": 1,
"mysql_server": {
"max_connect_errors": {
"Value": "99999",
"Metadata": {
"Timestamp": 1658719120737093,
"User": "root",
"Host": "localhost"
}
},
"max_connections": {
"Value": "2000",
"Metadata": {
"Timestamp": 1658724157388870,
"User": "root",
"Host": "localhost"
}
},
"mysql_server_static_options": {
"back_log": {
"Value": "1000",
"Metadata": {
"Timestamp": 1658719941194905,
"User": "root",
"Host": "localhost"
}
}
}
}
}
### python 을 이용
# cat mysqld-auto.cnf | python -mjson.tool
{
"Version": 1,
"mysql_server": {
"max_connect_errors": {
"Value": "99999",
"Metadata": {
"Timestamp": 1658719120737093,
"User": "root",
"Host": "localhost"
}
},
"max_connections": {
"Value": "2000",
"Metadata": {
"Timestamp": 1658724157388870,
"User": "root",
"Host": "localhost"
}
},
"mysql_server_static_options": {
"back_log": {
"Value": "1000",
"Metadata": {
"Timestamp": 1658719941194905,
"User": "root",
"Host": "localhost"
}
}
}
}
}
mysql_server_static_options 항목에는 SET PERSIST_ONLY 으로 적용한 시스템 변수 정보가 기재되어 있습니다.
HOST는 파라미터 변경을 실행한 Host 입니다. 위의 예시에서는 localhost 에서 수행한 결과이고, 원격(Remote)서 수행하였을 경우 다른 정보로 표기됩니다.
mysqld-auto.cnf 파일 관리는 MySQL서버에 맡겨야 합니다. 파일 조작을 수동으로 하지 않고 SET PERSIST 또는 RESET PERSIST 문을 사용해서만 수행해야 합니다.
파일을 수동으로 변경하면 서버 시작 시 구문 분석 오류가 발생할 수 있습니다. 이렇게 문제가 생긴다면 MySQL 서버는 오류를 기록하고 시작되지 못하고 종료됩니다.
이럴 경우 my.cnf 파일에서 persisted_globals_load 를 OFF 로 설정하거나, --no-defaults 옵션을 사용하여 서버를 시작하거나 mysqld-auto.cnf 파일을 제거합니다.
파일을 제거하면 다음 MySQL 서버 시작 시 mysqld-auto.cnf 에서 설정한 내용은 적용이 되지 않습니다.
민감한 지속 시스템 변수
MySQL 8.0.29 부터 MySQL Server는 개인 키 또는 암호와 같은 민감한 데이터를 포함하는 지속형 시스템 변수 값을 안전하게 저장하고 값 보기를 제한하는 기능이 추가되었습니다.
현재(8.0.29 버전 기준) 민감한 것으로 표시된 MySQL 서버 시스템 변수는 없지만 새로운 기능을 통해 민감한 데이터가 포함된 시스템 변수를 향후 안전하게 유지할 수 있습니다.
MySQL 8.0.29로 업그레이드한 후 mysqld-auto.cnf 옵션 파일의 형식은 처음 SET PERSIST 또는 SET PERSIST ONLY 문이 발행되고 그 시점에서 관련된 시스템 변수가 민감하지 않더라도 새로운 형식으로 변경됩니다.
새 형식에서 옵션 파일은 이전 릴리스의 MySQL Server에서 읽을 수 없습니다.
옵션 파일 에는 mysqld-auto.cnf 민감한 시스템 변수의 이름과 값이 암호화된 형식으로 저장되며 이를 해독하기 위해 생성된 파일 키가 함께 저장됩니다.
persisted_variables_key 생성된 파일 키는 키링에 저장된 마스터 키 를 사용하여 차례로 암호화됩니다.
서버가 시작되면 지속되는 민감한 시스템 변수가 해독되어 사용됩니다. 기본적으로 암호화된 값이 옵션 파일에 있지만 시작할 때 성공적으로 해독할 수 없는 경우 기본 설정이 사용됩니다.
즉 향후 민감한 데이터가 포함될 수도 있기 때문에 8.0.29 버전부터 mysqld-auto.cnf 파일이 암호화된다는 것을 확인할 수 있는 내용입니다.
Nonpersistible and Persist-Restricted System Variables
SET PERSIST 와 SET PERSIST_ONLY 를 통해서 시스템 변수를 mysqld-auto.cnf 옵션 파일에 유지되도록 할 수 있습니다.
그러나 모든 시스템 변수를 PERSIST 로 설정할 수 있는 것은 아니거나 특정 제한 조건에서만 사용할 수 있습니다. 불가능하거나 제한되는 몇 가지 이유는 다음과 같습니다.
- 세션 시스템 변수는 Persist 기능을 사용할 수 없습니다. 세션 변수는 서버 시작 시 설정할 수 없으므로 유지할 이유가 없습니다.
- 전역 시스템 변수에는 서버 호스트에 직접 액세스할 수 있는 사용자만 설정할 수 있도록 민감한 데이터가 포함될 수 있습니다.
- 전역 시스템 변수는 읽기 전용일 수 있습니다(즉, MySQL 서버에 의해서만 설정됨). 이 경우 서버 시작 시든 런타임 시든 사용자가 전혀 설정할 수 없습니다.
- 전역 시스템 변수는 내부 용도로만 사용할 수 있습니다.
다만 MySQL 8.0.14 부터 지속 제한 시스템 변수는 SET PERSIST_ONLY으로 설정할 수는 있으며, 다음 조건을 충족하는 사용자만 사용할 수 있습니다.
- 시스템 변수는 persist_only_admin_x509_subject SSL 인증서 X.509 subject value으로 설정됩니다.
- 사용자는 암호화된 연결을 사용하여 서버에 연결하고 지정된 Subject value 있는 SSL 인증서를 제공합니다.
- 사용자는 SET PERSIST_ONLY 사용할 수 있는 충분한 권한이 있어야 합니다.
아래 변수들은 nonpersistible 입니다. 향후 추가 개발에 따라서 변동될 수 있습니다.
audit_log_current_session audit_log_filter_id caching_sha2_password_digest_rounds character_set_system core_file have_statement_timeout have_symlink hostname innodb_version keyring_hashicorp_auth_path keyring_hashicorp_ca_path keyring_hashicorp_caching keyring_hashicorp_commit_auth_path keyring_hashicorp_commit_ca_path keyring_hashicorp_commit_caching keyring_hashicorp_commit_role_id keyring_hashicorp_commit_server_url keyring_hashicorp_commit_store_path keyring_hashicorp_role_id keyring_hashicorp_secret_id keyring_hashicorp_server_url keyring_hashicorp_store_path large_files_support large_page_size license locked_in_memory log_bin log_bin_basename log_bin_index lower_case_file_system ndb_version ndb_version_string persist_only_admin_x509_subject persisted_globals_load protocol_version relay_log_basename relay_log_index server_uuid skip_external_locking system_time_zone version_comment version_compile_machine version_compile_os version_compile_zlib
지속 제한 시스템 변수는 읽기 전용이며 커맨드라인이나 옵션 파일에서 설정할 수 있는 변수입니다. (persist_only_admin_x509_subject 및 persisted_globals_load 제외) 향후 추가 개발에 따라서 변동될 수 있습니다.
audit_log_file audit_log_format auto_generate_certs basedir bind_address caching_sha2_password_auto_generate_rsa_keys caching_sha2_password_private_key_path caching_sha2_password_public_key_path character_sets_dir daemon_memcached_engine_lib_name daemon_memcached_engine_lib_path daemon_memcached_option datadir default_authentication_plugin ft_stopword_file init_file innodb_buffer_pool_load_at_startup innodb_data_file_path innodb_data_home_dir innodb_dedicated_server innodb_directories innodb_force_load_corrupted innodb_log_group_home_dir innodb_page_size innodb_read_only innodb_temp_data_file_path innodb_temp_tablespaces_dir innodb_undo_directory innodb_undo_tablespaces keyring_encrypted_file_data keyring_encrypted_file_password lc_messages_dir log_error mecab_rc_file named_pipe pid_file plugin_dir port relay_log relay_log_info_file replica_load_tmpdir secure_file_priv sha256_password_auto_generate_rsa_keys sha256_password_private_key_path sha256_password_public_key_path shared_memory shared_memory_base_name skip_networking slave_load_tmpdir socket ssl_ca ssl_capath ssl_cert ssl_crl ssl_crlpath ssl_key tmpdir version_tokens_session_number
Reference
Reference URL
• dev.mysql.com/persisted-system-variables
• dev.mysql.com/nonpersistible-system-variables
• dev.mysql.com/system-variable-in-mysql-8-0-14
• dev.mysql.com/sysvar_persisted_globals_load
연관된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
