[Real MySQL 8.0] MySQL 서버 설정과 시스템 변수

설정 파일

MySQL 은 일반적으로 유닉스 계열은 my.cnf 윈도우즈 계열은 my.ini 라는 이름의 하나의 설정파일을 사용합니다.

MySQL 은 설정파일 Path 를 정적으로 지정하고 있지 않기 때문에, 지정된 디렉터리 내에서 순차적으로 탐색하여

최초 발견된 것을 사용합니다.

 

그렇다면 여러 설정파일을 만들었을때, 현재 MySQL 에서 어떤 설정파일을 바라보고 있는지가 필요할 때가 있을 것 입니다.

그럴때는 아래 명령어를 통하여 추리 할 수 있습니다.

mysqld --verbose --help | grep my.cnf
mysql --help | grep my.cnf

 

출력은 다음과 같습니다.

 

order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /opt/homebrew/etc/my.cnf ~/.my.cnf 

# 어떤 디렉토리를 우선으로 읽는지를 출력해줍니다.

 

MySQL 은 실행 시점에서 설정파일을 참조하여 초기값을 셋팅합니다.

실행 시점에서만 참조하기 때문에, 실행 이후 설정파일을 변경해도 재시작 전에는 적용이 되지 않습니다.

 

cat my.cnf

1 # Default Homebrew MySQL server config
  2 [mysqld] -- 그룹명 입니다.일반적으로 mysql 의 각 서브 프로그램별로 그룹명을 프로그램명과 동일하게 짓습니다.
  3 # Only allow connections from localhost
  4 bind-address = 127.0.0.1
  5 mysqlx-bind-address = 127.0.0.1
~                                                                               
~

my.conf 의 설정값은 그룹별로 격리되어 적용됩니다. 

 

 

시스템 변수

MySQL 은 기본 설정들과 설정파일을 읽어 초기화 하면서 셋팅들을 별도로 저장해두고,

그 셋팅에 의해서 동작하게 됩니다. 이를 시스템 변수라고 합니다.

 

시스템 변수는 아래 명령어로 조회가 가능 합니다.

 

SHOW VARIABLES 
SHOW GLOBAL VARIABLES # 글로벌 변수 조회
SHOW VARIABLES LIKE '변수 명' # 검색은 Like 명령어로 수행합니다 '%' 연산자를 추가할 수 있습니다.

 

시스템 변수는 글로벌 변수와 세션변수, 그리고 동적변수와 정적변수로 분류할 수 있습니다.

글로벌 변수와 세션 변수

• 글로벌 변수 : 전역적인 값입니다. 일반적으로 서버 자체의 설정등이 포함됩니다.
   ex) innodb_buffer_pool_size, key_buffer_size
• 세션 변수: 접속한 클라이언트 커넥션 세션 별로 가지는 값입니다. 세션 변수는 한번 설정된 후 서버에서 강제로 변경이 불가능 합니다.
  ex) auto_commit 
• BOTH : 글로벌 변수와 세션 변수 두가지 모두의 속성을 지닌 값입니다. 세션 변수 중에사도 my.conf 에 명시하여 초기화가 가능한 설정 값입니다. mysql 은 이런 값들을 보관하고 있다가 커넥션이 맺어지는 순간에 기본값으로 지정합니다.

동적 변수와 정적 변수

동적변수와 정적변수는 런타임에서 변경이 가능한지 유무의 차이로 분류한 것입니다.

시스템 변수를 설정 파일에서 변경하면, 서버를 재기동하여야만 적용이 가능합니다.

이때, 동적 변수는 설정 파일 변경이 아닌 명령어로 런타임에서 변경이 가능합니다.

 

SET [변수 명]=[값]

 

이렇게 적용한 동적 글로벌 변수는 즉시 서버에 적용됩니다.

하지만 재 기동시, 원래 초깃 값으로 설정되므로 영구히 적용하고 싶다면 my.cnf 파일에도 꼭 적용시켜주어야 합니다.

 

MySQL 8.0 버전 부터는 PERSIST 명령어를 통하여 설정파일 변경 없이도, 재 기동시에도 적용되도록 지정해줄 수 있습니다.

 

SET PERSIST [변수명]=[값]
SET PERSIST_ONLY [변수명]=[값]

PERSIST 명령어를 수행하면, 값을 즉시 적용하고 mysql-auto.cnf 라는 파일에 해당 설정 값을 저장합니다.

mysql-auto.cnf 파일이 존재하면, mysql 은 기동될때 my.cnf 파일과 함께 해당 파일을 읽어 초깃값을 셋팅합니다.

PERSIST_ONLY 명령은 mysql-auto.cnf 파일에 값만 저장하고, 런타임의 설정값은 변경하지 않을때 사용합니다.

 

mysql-auto.cnf 파일은 json 형태로 시스템 변수들을 언제 누가 변경했는지와 같은 메타데이터도 함께 보관합니다.

 

mysql-auto.cnf 메타데이터

mysql-auto 의 메타데이터 정보는

performance_schema.variables_onfo 뷰와 performance_schema.persisted_variables 테이블로 조회가 가능합니다.

 

mysql> SELECT * 
FROM performance_schema.variables_info a 
INNER JOIN performance_schema.persisted_variables b 
ON a.variable_name=b.variable_name

 

출력은 다음과 같습니다.

 

+-----------------+-----------------+---------------+-----------+-----------+----------------------------+----------+-----------+-----------------+----------------+
| VARIABLE_NAME   | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME                   | SET_USER | SET_HOST  | VARIABLE_NAME   | VARIABLE_VALUE |
+-----------------+-----------------+---------------+-----------+-----------+----------------------------+----------+-----------+-----------------+----------------+
| max_connections | DYNAMIC         |               | 1         | 100000    | 2022-03-04 18:44:56.410407 | root     | localhost | max_connections | 5000           |
+-----------------+-----------------+---------------+-----------+-----------+----------------------------+----------+-----------+-----------------+----------------+

설정된 PERSIST 값을 삭제할 때에는, auto 파일을 직접 수정하기 보다는

RESET PERSIST [변수명] (없을시 all) 명령어를 이용하여 사용하는 편이 안전합니다.

 

세션 스코프의 시스템 변수의 경우, 수정이 불가능 합니다.

BOTH 시스템 변수의 경우에 글로벌 설정을 변경해도 현재 맺어져 있는 커넥션의 세션에는 적용되지 않습니다. 

물론 BOTH 역시 동적 변수의 경우에만 런타임에서 변경이 가능합니다.

 

 

시스템 변수의 목록

 

시스템 변수의 종류는 아래 공식 문서 링크에서 확인할 수 있습니다.

https://dev.mysql.com/doc/refman/8.0/en/server-system-variable-reference.html

 

MySQL :: MySQL 8.0 Reference Manual :: 5.1.5 Server System Variable Reference

 

자주 쓰는 설정값과 설명은 별도 포스팅 하고 공식 문서를 보는 방법만 간략히 기술하겠습니다.

 

• Cmd-Line: 명령줄 옵션으로 전달해서 변경이 가능한지 여부입니다. 해당 옵션이 YES 인 경우 명령행 인자로 전달하여 옵션을 지정할 수 있습니다.
• Option file: 설정 파일 (my.cnf , my.ini) 로 제어가 가능한지 여부를 나타냅니다.
• System Var: 시스템 변수인지 아닌지를 나타냅니다.
  8.0 버전부터는 네이밍이 시스템 변수는 '_' 구분자만을 이용하고, 명령줄 옵션의 경우 '-' 구분자를 이용하도록 통일 되었다고 합니다.
• Var Scope: 변수의 스코프를 나타냅니다. Global, Session, Both 가 존재합니다.
• Dynamic: 동적변수인지 정적변수인지 여부를 나타냅니다.