[Real MySQL 8.0]MySQL 8.0과 Docker - 설정 파일

서버 설정과 설정 파일

MySQL서버 당 하나의 설정 파일을 갖는다.

• Linux/Unix 계열 - my.cnf
• Windows 계열 - my.ini

 

설정 파일 우선순위

MySQL 서버가 기동될 때 이 설정 파일을 참조하여 올라온다. 이 설정 파일의 경로는 고정되어있지 않는다. MySQL은 순차적으로 지정된 여러 개의 디렉터리를 탐색하면서 가장 처음 발견한 my.cnf 파일을 참조한다.

 

직접 MySQL을 설치한 경우(컴파일) 디렉토리 위치는 다르게 설정될 수 있다. 그 때는 아래의 두 커맨드를 사용하여 확인할 수 있다.

mysqld --verbose --help

mysql --help (권장)

 

 

실제로 Docker에서도 디렉토리 우선순위를 확인할 수 있다.

root@8e3ce86d5d64:/realmysql8# mysql --help
...
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
The following groups are read: mysql client
...

 

이 커맨드를 통해 여러 디렉토리에 my.cnf를 만들더라도 디렉토리의 우선순위를 확인할 수 있다.

 

my.cnf 파일

설정 파일에는 여러 개의 설정 그룹을 담을 수 있다. 각 프로그램은 자신의 그룹명을 기준으로 필요한 설정만 읽어간다(한 파일이어도 독립적임). 대체로 실행 프로그램 이름을 그룹명으로 사용한다.

• mysqld -> MySQL 서버 관련 설정
• mysql -> MySQL 클라이언트 관련 설정
• mysqldump -> 데이터 덤프 유틸리티 관련 설정

MySQL 서버는 기동될 때 설정 파일을 읽어 기존의 메모리나 작동 방식을 초기화하고 다시 재설정한다.

 

시스템 변수

시스템 변수란 MySQL 서버의 동작에 영향을 미치는 변수들이다.

 

시스템 변수를 MySQL에서 확인하는 SQL문

mysql> SHOW GLOBAL VARIABLES;

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

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

 

 

해당 페이지에는 MySQL에서 사용하는 변수를 다음과 같이 4가지로 분류해놨다.

• Cmd-Line : cmd 창에서도 변경이 가능한 지
• Option file: my.cnf에서 변경 가능한 지
• System Var: 시스템 변수인지 (궁금해서 훑어봤는데 다 시스템변수다.)
• Var Scope: 적용 범위 - Global/Session/Both(Global + Session)
• Dynamic: 동적 변수 / 정적 변수 구분

Var Scope

변수의 적용 범위를 나타내며 총 3개의 범위를 갖고있다.

• 글로벌 변수: MySQL 서버 인스턴스에 영향을 줌(ex. innodb_buffer_pool_size, key_buffer_size)
• 세션변수: MySQL 클라이언트가 MySQL 서버에 접속할 때 기본으로 부여하는 옵션의 기본값을 제어할 때(ex. autocommit)
• Both: Global + Session

범위가 Both일 경우 설정 파일에 초기값을 명시하여 MySQL 서버가 기억만 하고 있다가 클라이언트와의 커넥션이 생성되는 순간에 해당 커넥션의 기본값으로 사용된다.
범위가 Session일 경우 설정 파일에 초기값을 명시할 수 없으며, 커넥션이 생성되는 순간에 해당 커넥션에만 유효한 설정 변수다. (직접 SET을 이용해서 설정해야한다. ex. debug_sync)

 

Dynamic

서버가 기동 중인 상태에서 변경 가능한 지 유무를 나타내며 2가지로 나뉜다.

• 정적 변수: 서버가 기동 중인 상태에서 변경 불가
• 동적 변수: 서버가 기동 중인 상태에서 변경 가능(영구적인 저장이 필요한 경우 SET PERSIST)

SET을 통해 변경되는 변수들은  my.cnf 파일에 적용된 것은 아니기 때문에 재기동 되면 초기화된다.
SET PERSIST 명령을 이용하면 실행 중인 MySQL 서버에도 적용되고 my.cnf 파일에도 적용된다.

 

정확한 변수명을 모를 경우 %나 _와 같은 패턴을 이용하여서 검색이 가능하다.

mysql> SHOW GLOBAL VARIABLES LIKE '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 151   |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.00 sec)

-- 현재 기동 중인 MySQL 인스턴스에만 적용되는 변수 설정
mysql> SET GLOBAL max_connections=500;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE '%max_connections%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 500   |
| mysqlx_max_connections | 100   |
+------------------------+-------+
2 rows in set (0.00 sec)

해당 설정은 my.cnf를 직접적으로 수정한 것은 아니기 때문에 MySQL이 재기동 시 my.cnf에 작성된 내용으로 반영된다.

설정을 영구적으로 적용하고 싶다면, my.cnf 파일을 수정하자

 

SHOW, SET 커맨드 시 GLOBAL 키워드를 넣으면 글로벌 변수로 조회되고 GLOBAL 키워드를 넣지 않으면 세션 변수로 조회된다.

 

만약 시스템 변수의 범위가 'Both'인 경우 글로벌 시스템 변수의 값을 변경해도 이미 존재하는 커넥션의 세션 변수값은 변경되지 않고 유지된다. 

mysql> SHOW GLOBAL VARIABLES LIKE 'join_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'join_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.01 sec)

mysql> SET GLOBAL join_buffer_size=524288;
Query OK, 0 rows affected (0.00 sec)

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

-- 이미 존재하는 커넥션이기 때문에 세션 변수값은 유지된다.
mysql> SHOW VARIABLES LIKE 'join_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)

 

 

SET PERSIST

동적 변수는 MySQL 서버에서 SET GLOBAL 명령을 변경하면 MySQL 서버에 반영된다. 하지만 현재 기동된 MySQL에만 적용되지 MySQL의 설정 파일에는 적용되지 않는다.

만약 기존의 MYSQL이 기동 중인 상태에서 max_connections의 값을 5000으로 바꾼 후 재기동 했을 시 예전의 max_connections의 값으로 서버가 시작되면 오류가 발생할 수 있다.

mysql> SET GLOBAL max_connections=5000;
Query OK, 0 rows affected (0.00 sec)

 

이러한 문제를 해결하기 위해 8.0에서는 SET PERSIST 명령어를 사용할 수 있다. 해당 명령어는 변경된 값을 현재 MySQL 적용하는 것 뿐만 아니라 mysqld-auto.cnf에 변경 내용을 추가로 기록해둔다. SET PERSIST는 동적인 변수에 한해 가능하다.

mysql> SET PERSIST max_connections=5000;
Query OK, 0 rows affected (0.01 sec)

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

MySQL이 재기동 될 시 my.cnf뿐만 아니라 mysqld-auto.conf의 파일을 참조해서 시스템 변수에 적용한다.

 

실제로 mysqld-auto.cnf 내용을 확인하면 max_connections의 value가 5000으로 바뀐 것을 확인할 수 있다. 호스트, 유저, 시간 정보와 같은 메타 정보도 포함된 것을 확인할 수 있다.

root@8e3ce86d5d64:/var/lib/mysql# cat /var/lib/mysql/mysqld-auto.cnf
{"Version": 2, "mysql_dynamic_parse_early_variables": {"max_connections": {"Value": "5000", "Metadata": {"Host": "localhost", "User": "root", "Timestamp": 1737078125040697}}}}root@8e3ce86d5d64:

 

SET PERSIST_ONLY 명령어는 정적인 변수를 영구적으로 변경하고자 할 때 사용할 수 있다. 정적인 변수(Read-only variables)들의 경우 실행 중인 MySQL 서버에서 변경할 수 없다. SET PERSIST_ONLY 명령어는 전역 시스템 변수를 mysqld-auto.cnf 파일에 저장(PERSIST)하고, 실행 중인 서버의 전역 값을 변경하지는 않는다. 서버를 재시작한 후에 적용된다.

공식 문서상에서 소개하는 PERSIST_ONLY는 읽기 전용 변수(Read-only variables)들을 설정할 때 유용하다고 한다.

https://dev.mysql.com/doc/refman/8.0/en/set-variable.html

MySQL :: MySQL 8.0 Reference Manual :: 15.7.6.1 SET Syntax for Variable Assignment

 

Real MySQL 8.0 innodb_doublewrite도 정적변수니 수정이 가능하다고 적혀있지만, 일부 non-persistent 변수는 변경이 불가능하다. 8.0.40-debian와 8.4버전으로 진행할 경우 innodb_doublewrite는 non-persistent 변수이기에 mysqld-auto.cnf에 저장할 수 없다고 한다.(그 외 버전에서 실행해본 적 없어서 모르겠다.)

 

mysql> SET PERSIST innodb_doublewrite=ON;
ERROR 1238 (HY000): Variable 'innodb_doublewrite' is a non persistent variable

mysql> SET PERSIST_ONLY innodb_doublewrite=ON;
ERROR 1238 (HY000): Variable 'innodb_doublewrite' is a non persistent variable

 

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

MySQL :: MySQL 8.4 Reference Manual :: 7.1.9.4 Nonpersistible and Persist-Restricted System Variables

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

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

위에는 non persistible 변수 목록이 작성되어있다. innodb_doublewrite는 해당 문서에 작성되어 있지 않지만 시스템 적으로는 non persistent variable로 명시되어 있다.

 

 반면, 읽기 전용 변수 중 back_log의 경우 가능하다.

 

mysql> SET PERSIST back_log=2;
ERROR 1238 (HY000): Variable 'back_log' is a read only variable

mysql> SET PERSIST_ONLY back_log=1;
Query OK, 0 rows affected (0.02 sec)

 

변경된 내용은 mysqld-auto.cnf로 확인이 가능하며(위에서 확인했으니 지나가겠다.), SET PERSIST 또는 SET PERSIST_ONLY 명령으로 변경된 메타데이터는 MySQL 내부에서 SQL문으로도 확인 가능하다.

mysql> SELECT a.variable_name, b.variable_value, a.set_time, a.set_user, a.set_host
FROM performance_schema.variables_info a
INNER JOIN performance_schema.persisted_variables b
ON a.variable_name=b.variable_name
WHERE b.variable_name LIKE 'max_connections'\G
*************************** 1. row ***************************
 variable_name: max_connections
variable_value: 5000
      set_time: 2025-01-17 01:42:05.041348
      set_user: root
      set_host: localhost
1 row in set (0.06 sec)

 

SET PERSIST 또는 SET PERSIST_ONLY 명령으로 변경된 시스템 변수를 삭제해야할 땐 mysqld-auto.cnf를 직접 건드는건 오류가 발생할 확률이 높으니 RESET PERSIST 명령어를 이용하자. (8.0.4-debian에서는 제공되지 않는다. ㄱ- 8.4 버전으로 진행 결과 정상적으로 진행된다.) 

 

-- mysql 8.0.4-debian 버전
mysql> REST PERSIST max_connections;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REST PERSIST max_connections' at line 1
-- mysqld-auto.cnf를 수정하기 보다는 SET으로 임의로 수정하였다..
mysql> SET PERSIST max_connections=100;
Query OK, 0 rows affected (0.00 sec)

mysql> SET PERSIST_ONLY back_log=-1;
Query OK, 0 rows affected (0.01 sec)

-- 8.4에서 진행 시 정상적으로 작동한다.
mysql> RESET PERSIST back_log;
Query OK, 0 rows affected (0.01 sec)

mysql> RESET PERSIST;
Query OK, 0 rows affected (0.00 sec)

 

 

참고용 my.cnf

설정 파일은 회사의 정책을 따르거나 하드웨어 사양과 실제 서비스 환경을 고려하고 모니터링하여 지속해서 튜닝하는 것을 권장하다.

[mysqld]
max_connections=100

innodb_sort_buffer_size=5M

innodb_log_files_in_group=2
innodb_log_file_size=1024M

innodb_buffer_pool_size=200M
innodb_buffer_pool_instances=1
innodb_io_capacity=100
innodb_io_capacity_max=400