[MySQL] PERSIST, RESET PERSIST를 통한 서버 파라미터 관리

■ Dynamic Parameter, Static Parameter(동적 파라미터, 정적 파라미터)

Static parameter는 시스템에 바로 적용시 문제가 되는 파라미터들이 대부분입니다. 그래서 보통 DBMS 재시작을 요구하며 이때 적용됩니다.

MySQL 파라미터는 크게 Dymic Parameter, Static Parameter가 있습니다.

이 다이나믹 파라미터는 현재 운영중인 MySQL DBMS에서 바로 변경이 가능하고 신규 컨넥션부터 바로 변경된 파라미터로 운영이 됩니다.

또한 다이나믹 파라미터는 현재 접속되어 있는 세션에만 적용할수도 있고 전체 파라미터에 설정을 할수도 있습니다.

그러나 한가지 문제가 DBMS를 재시작하게 되면 글로벌로 적용된 파라미터라도 my.cnf 환경파일에 작성되지 않은 파라미터라면 초기화되어 없어진다는 점입니다.

즉 적정값으로 설정해둔 파라미터가 초기화되어 기존 기본 파라미터로 되돌아 간다는 점입니다.

이때 my.cnf에 작성하지 않고도 파라미터를 영구적으로 적용하는 방법이 있는데 이것이 set persist 명령을 이용한 파라미터 영구 설정 방법입니다.

지금부터 이 방법을 알아보겠습니다.

 

■ 적용 방법

MySQL Client에서 SET PERSIST, SET PERSIST_ONLY라는 명령을 통해 파라미터를 적용하게 됩니다. 이 파라미터를 사용하게 되면 MySQL Data Directory에 mysqld-auto.cnf라는 파일이 생성되게 됩니다. SET PERSIST명령을 한번도 사용하지 않았다면 이 파일은 생성되지 않습니다.

▶︎ 명령문 설명

• SET PERSIST,  SET @@PERSIST.<서버 파라미터> : mysqld-auto.cnf 파일에 영구적으로 쓰여지며 현재 파라미터에도 적용되게 됩니다. 글로벌로 적용됩니다.

• SET PERSIST_ONLY,  SET @@PERSIST_ONLY.<서버 파라미터> : mysqld-auto.cnf 파일에 영구적으로 쓰여지기만 합니다. 현재 파라미터에 적용은 되지 않습니다. STATIC 서버 파라미터는 이 명령문으로만 적용됩니다.

 

1. 사용법

# SET PERSIST 문법
mysql> SET PERSIST max_connections = 1000;
mysql> SET @@PERSIST.max_connections = 1000;


# SET PERSIST_ONLY
mysql> SET PERSIST_ONLY back_log = 100;
mysql> SET @@PERSIST_ONLY.back_log = 100;

 

 

위의 명령어를 실행하고 MySQL Data Directory를 확인해보면 다음과 같이 파일이 생성된것을 확인해 볼 수 있습니다.

jq라는 오픈소스 프로그램을 사용하면 일렬로 나열되는 내용을 json 표기법으로 포맷팅해서 보여주게 됩니다.

shell> ls -l
...
-rw-r----- 1 mysql mysql      175 Dec 13 13:49  mysqld-auto.cnf
...

shell> cat mysqld-auto.cnf
{"Version": 2, "mysql_dynamic_parse_early_variables": {"max_connections": {"Value": "1000", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1702475340945048}}}}

shell> cat mysqld-auto.cnf | jq
{
  "Version": 2,
  "mysql_dynamic_parse_early_variables": {
    "max_connections": {
      "Value": "1000",
      "Metadata": {
        "Host": "localhost",
        "User": "root",
        "Timestamp": 1702475340945048
      }
    }
  }
}

 

2. SET PERSIST의 특징

• SET PERSIST는 런타임에 이루어집니다.

• SET PERSIST는 영구적입니다. 서버를 다시 시작할 때마다 적용됩니다.

• SET PERSIST는 설정은 로컬 클라이언트 또는 원격 호스트에서 연결하는 클라이언트에서 설정이 가능합니다. 이는 하나의 클라이언트에서 여러 MySQL 서버를 원격으로 접속하여구성하는 편리함을 제공합니다.

• 시스템 변수를 유지하는데 있어 MySQL 서버 호스트에 대한 로그인 액세스 권한이나 옵션 파일에 대한 파일 시스템 액세스 권한이 필요하지 않습니다. 설정을 유지하는 기능은 MySQL 권한 시스템을 사용하여 제어됩니다.

• 충분한 권한을 가진 관리자는 SET PERSIST 명령을 사용하여 서버를 재구성한 다음 RESTART 명령문을 실행하여 서버가 변경된 설정을 즉시 사용하도록 할 수 있습니다 .

• SET PERSIST 설정은 오류에 대한 즉각적인 피드백을 제공합니다. my.cnf 환경 파일에 수동으로 입력한 설정의 오류는 DBMS재시작 이후에나 에러 명령을 통해 오류를 확인할 수 있지만 SETSET PERSIST 문은 구문 오류가 있는 설정은 즉각적인 에러를 발생시키기 때문에 잘못된 설정 가능성을 방지합니다.

• 플러그인에 의해 생성되는 서버 파라미터 변수는 플러그인이 설치되었을때만 설정이 가능합니다. 

• persisted_globals_load 시스템 변수를 통해 mysqld-auto.cnf 파일을 무시할지 포함해서 실행할지 여부를 결정할 수 있습니다.

■ 적용 해제 방법

▶︎ 명령문 설명

mysqld-auto.cnf에서 파라미터를 제거하는 방법입니다.

• RESET PERSIST : mysqld-auto.cnf 파일의 모든 내용을 삭제합니다.

• RESET PERSIST system_var_name; : mysqld-auto.cnf 파일에서 특정 파라미터만 삭제합니다.

• RESET PERSIST IF EXISTS system_var_name : RESET PERSIST를 실행할때 만약 mysqld-auto.cnf파일 안에 특정 파라미터가 없다면 에러가 발생합니다. 에러를 예방하기 위해 mysqld-auto.cnf파일안에 파라미터가 존재할 경우에만 삭제하고 없을 경우 그냥 종료합니다.

# mysqld-auto.cnf 파일에 존재하지 않는 Back Log 파라미터 삭제
shell> RESET PERSIST back_log;
ERROR 3615 (HY000): Variable back_log does not exist in persisted config file


# mysqld-auto.cnf 파일의 모든 파라미터 삭제
mysql> RESET PERSIST;
shell> cat mysqld-auto.cnf | jq
{
  "Version": 2
}

 

■ SET PERSIST의 운영

SET PERSIST명령을 이용하여 파라미터를 설정할 경우 performance_schema.persisted_variables 테이블에 저장됩니다.

select * from performance_schema.persisted_variables;
+-----------------+----------------+
| VARIABLE_NAME   | VARIABLE_VALUE |
+-----------------+----------------+
| max_connections | 1000           |
| back_log        | 100            |
+-----------------+----------------+
2 rows in set (0.00 sec)

 

참고로 performance_schema.variables_info를 확인해보면 파라미터가 설정된 위치, 누가 변경하였는지, 변경된 날짜는 언제인지등의 파라미터 변경사항들에 대해 추적이 가능합니다.

이중에서 PERSIST 명령으로 설정한 내용만 확인해 보겠습니다.

mysql> SELECT * 
FROM performance_schema.variables_info
WHERE VARIABLE_SOURCE = 'PERSISTED'
ORDER BY VARIABLE_NAME;
+----------------------------------------------------------+-----------------+---------------+----------------------+----------------------+----------------------------+----------+-----------+
| VARIABLE_NAME                                            | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE            | MAX_VALUE            | SET_TIME                   | SET_USER | SET_HOST  |
+----------------------------------------------------------+-----------------+---------------+----------------------+----------------------+----------------------------+----------+-----------+
| activate_all_roles_on_login                              | COMPILED        |               | 0                    | 0                    | NULL                       | NULL     | NULL      |
| admin_address                                            | COMPILED        |               | 0                    | 0                    | NULL                       | NULL     | NULL      |
| admin_port                                               | COMPILED        |               | 0                    | 65535                | NULL                       | NULL     | NULL      |
| admin_ssl_ca                                             | COMPILED        |               | 0                    | 0                    | NULL                       | NULL     | NULL      |
......
| binlog_expire_logs_auto_purge                            | COMPILED        |               | 0                    | 0                    | NULL                       | NULL     | NULL      |
| binlog_expire_logs_seconds                               | GLOBAL          | /etc/my.cnf   | 0                    | 4294967295           | NULL                       | NULL     | NULL      |
| binlog_format                                            | GLOBAL          | /etc/my.cnf   | 0                    | 0                    | NULL                       | NULL     | NULL      |
.....
| max_binlog_stmt_cache_size                               | COMPILED        |               | 4096                 | 18446744073709547520 | NULL                       | NULL     | NULL      |
| max_connect_errors                                       | GLOBAL          | /etc/my.cnf   | 1                    | 18446744073709551615 | NULL                       | NULL     | NULL      |
| max_connections                                          | DYNAMIC         |               | 1                    | 100000               | 2023-12-13 13:49:00.945237 | root     | localhost |
| wait_timeout                                             | COMPILED        |               | 1                    | 31536000             | NULL                       | NULL     | NULL      |
| warning_count                                            | COMPILED        |               | 0                    | 18446744073709551615 | NULL                       | NULL     | NULL      |
| windowing_use_high_precision                             | COMPILED        |               | 0                    | 0                    | NULL                       | NULL     | NULL      |
| xa_detach_on_prepare                                     | COMPILED        |               | 0                    | 0                    | NULL                       | NULL     | NULL      |
+----------------------------------------------------------+-----------------+---------------+----------------------+----------------------+----------------------------+----------+-----------+

 

■ SET PERSIST의 적용 순서

MySQL이 시작될때 my.cnf 호출순서에 의해 모두 호출이 된 후 제일 마지막에 mysqld-auto.cnf가 적용이 됩니다. 즉 my.cnf와 mysqld-auto.cnf 두군데에 파라미터가 적용되어 있을 경우 가장 마지막에 적용되는 mysqld-auto.cnf 내용이 적용이 됩니다. 만약 mysqld-auto.cnf 파일내용이 적용되는것을 원치 안을경우 my.cnf안에 persisted_globals_load 파라미터를 설정하여 적용되지 않게 할 수 있습니다. 기본값은 'ON' 입니다.

 

 

■ mysqld-auto.cnf 분석

{
  "Version": 2,
  "mysql_static_variables": {
    "back_log": {
      "Value": "100",
      "Metadata": {
        "Host": "localhost",
        "User": "root",
        "Timestamp": 1704114855572465
      }
    },
    "large_pages": {
      "Value": "ON",
      "Metadata": {
        "Host": "localhost",
        "User": "root",
        "Timestamp": 1704114821744322
      }
    }
  },
  "mysql_dynamic_variables": {
    "sync_binlog": {
      "Value": "0",
      "Metadata": {
        "Host": "localhost",
        "User": "root",
        "Timestamp": 1704114488964456
      }
    },
    "thread_cache_size": {
      "Value": "2000",
      "Metadata": {
        "Host": "localhost",
        "User": "root",
        "Timestamp": 1704114489020733
      }
    },
    "innodb_flush_log_at_trx_commit": {
      "Value": "0",
      "Metadata": {
        "Host": "localhost",
        "User": "root",
        "Timestamp": 1704114488971498
      }
    }
  },
  "mysql_dynamic_parse_early_variables": {
    "max_connections": {
      "Value": "2000",
      "Metadata": {
        "Host": "localhost",
        "User": "root",
        "Timestamp": 1704114489859986
      }
    },
    "table_open_cache": {
      "Value": "2000",
      "Metadata": {
        "Host": "localhost",
        "User": "root",
        "Timestamp": 1704114489036103
      }
    }
  }
}

크게 mysql_static_variables, mysql_dynamic_variables, mysql_dynamic_parse_early_variables 크게 3가지로 나뉩니다. 버전 1과 버전2로 나뉘는데 위의 예제는 버전 2 입니다. mysql_dynamic_variables, mysql_dynamic_parse_early_variables은 다이나믹 서버 파라미터 번수로 바로 적용이 가능한 변수입니다. mysql_static_variables은 정적 서버 파라미터 변수로 시스템 재부팅시에만 적용됩니다.

 

■ 지속 불가능 및 지속 제한 시스템 변수

모든 시스템 변수가 지속될 수 있는 것은 아니며 특정 제한 조건에서만 지속될 수 있습니다. 시스템 변수가 지속 불가능하거나 지속 제한될 수 있는 몇 가지 이유는 다음과 같습니다.

• 세션 시스템 변수는 지속될 수 없습니다. 세션 변수는 서버 시작 시 설정할 수 없으므로 이를 유지할 이유가 없습니다.

• 전역 시스템 변수에는 서버 호스트에 직접 액세스할 수 있는 사용자만 설정할 수 있도록 민감한 데이터가 포함될 수 있습니다.

• 전역 시스템 변수는 읽기 전용(즉, 서버에서만 설정)일 수 있습니다. 이 경우 서버 시작 시나 런타임 시 사용자가 전혀 설정할 수 없습니다.

• 전역 시스템 변수는 내부용으로만 사용될 수 있습니다.

 

 PERSIST로 사용할 수 없는 시스템 변수는 어떤 상황에서도 지속될 수 없습니다. MySQL 8.0.14부터 지속 제한 시스템 변수는 SET PERSIST_ONLY를 사용하여 유지할 수 있지만 다음 조건을 충족하는 사용자만 사용할 수 있습니다.

• persist_only_admin_x509_subject 시스템 변수는 SSL 인증서 X.509 주체 값으로 설정됩니다.

• 사용자는 암호화된 연결을 사용하여 서버에 연결하고 지정된 제목 값이 포함된 SSL 인증서를 제공합니다.

• 사용자는 SET PERSIST_ONLY를 사용할 수 있는 충분한 권한을 가지고 있습니다(섹션 5.1.9.1, “시스템 변수 권한” 참조).

예를 들어, protocol_version은 읽기 전용이고 서버에서만 설정되므로 어떤 상황에서도 지속될 수 없습니다. 반면, Bind_address는 지속 제한형이므로 위의 조건을 만족하는 사용자가 설정할 수 있습니다.

 

다음 시스템 변수는 PERSIST로 사용할 수 없는 목록입니다. 이 목록은 지속적인 개발에 따라 변경될 수 있습니다.

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

 

■ Persist 명령을 이용할때 민감한 서버 파라미터 변수의 경우

MySQL 8.0.29부터 MySQL Server에는 개인 키나 비밀번호와 같은 민감한 데이터가 포함된 지속형 시스템 변수 값을 안전하게 저장하고 값 보기를 제한하는 기능이 있습니다. 현재 MySQL 서버 시스템 변수는 민감한 것으로 표시되어 있지 않지만 새로운 기능을 사용하면 민감한 데이터가 포함된 시스템 변수를 향후에도 안전하게 유지할 수 있습니다. MySQL 8.0.29로 업그레이드한 후 mysqld-auto.cnf 옵션 파일의 형식은 SET PERSIST 또는 SET PERSIST ONLY 문이 처음 실행될 때까지 동일하게 유지되며, 그 시점에서는 새로운 형식으로 변경됩니다. 관련된 시스템 변수가 민감하지 않은 경우. 새로운 형식에서는 이전 릴리스의 MySQL Server에서 옵션 파일을 읽을 수 없습니다.

 

mysqld-auto.cnf 옵션 파일에는 민감한 시스템 변수의 이름과 값이 암호화된 형식으로 저장되며, 이를 해독하기 위해 생성된  키 파일도 함께 저장됩니다. 생성된 파일 키는 키링에 저장된 마스터 키(persisted_variables_key)를 사용하여 암호화됩니다. 서버가 시작되면 지속되는 중요한 시스템 변수가 해독되어 사용됩니다. 기본적으로 옵션 파일에 암호화된 값이 있지만 시작 시 성공적으로 해독할 수 없는 경우 해당 기본 설정이 사용됩니다. 선택 사항인 가장 안전한 설정은 암호화된 값을 해독할 수 없는 경우 서버 시작을 중지합니다.

 

시스템 변수 persist_sensitive_variables_in_plaintext는 SET PERSIST를 사용하여 값을 설정할 때 키링 구성 요소 지원을 사용할 수 없는 경우 서버가 중요한 시스템 변수의 값을 암호화되지 않은 형식으로 저장할 수 있는지 여부를 제어합니다. 또한 암호화된 값을 해독할 수 없는 경우 서버를 시작할 수 있는지 여부도 제어합니다.

 

• 기본 설정인 ON은 키링 구성 요소 지원이 가능한 경우 값을 암호화하고, 지원되지 않는 경우 암호화되지 않은 상태로(경고와 함께) 유지합니다. 다음에 지속형 시스템 변수가 설정될 때 키링 지원이 사용 가능하면 서버는 암호화되지 않은 중요한 시스템 변수의 값을 암호화합니다. ON으로 설정하면 암호화된 시스템 변수 값을 해독할 수 없는 경우에도 서버를 시작할 수 있습니다. 이 경우 경고가 발생하고 시스템 변수의 기본값이 사용됩니다. 이러한 상황에서는 암호가 해독될 때까지 해당 값을 변경할 수 없습니다.

 

• 가장 안전한 설정인 OFF는 키링 구성 요소 지원을 사용할 수 없는 경우 중요한 시스템 변수 값을 유지할 수 없음을 의미합니다. OFF 설정은 암호화된 시스템 변수 값을 해독할 수 없는 경우 서버가 시작되지 않음을 의미합니다.

 

SENSITIVE_VARIABLES_OBSERVER 권한을 사용하면 소유자는 성능 스키마 테이블 global_variables, session_variables, Variable_by_thread 및 persisted_variables에서 민감한 시스템 변수의 값을 보고, SELECT 문을 실행하여 해당 값을 반환하고, 연결에 대한 세션 추적기에서 변경 사항을 추적할 수 있습니다. 이 권한이 없는 사용자는 해당 시스템 변수 값을 보거나 추적할 수 없습니다.

 

민감한 시스템 변수에 대해 SET 명령문이 실행되면 일반 로그 및 감사 로그에 기록되기 전에 해당 값을 "<redacted>"로 바꾸도록 쿼리가 다시 작성됩니다. 이는 키링 구성 요소를 통한 보안 저장소를 서버 인스턴스에서 사용할 수 없는 경우에도 발생합니다.

 

 

■ 참고 사이트

https://hoing.io/archives/62079

MySQL - 파라미터 SET PERSIST 과 RESET PERSIST

https://dev.mysql.com/doc/refman/8.0/en/nonpersistible-system-variables.html

MySQL :: MySQL 8.0 Reference Manual :: 5.1.9.4 Nonpersistible and Persist-Restricted System Variables

https://dev.mysql.com/doc/refman/8.0/en/persisted-system-variables.html

MySQL :: MySQL 8.0 Reference Manual :: 5.1.9.3 Persisted System Variables