MySQL 설정 파일

일반적으로 MySQL 서버는 단 하나의 설정 파일을 사용하는데, 리눅스를 포함한 유닉스 계열에서는 my.cnf라는 이름을 사용하고, 윈도우 계열에서는 my.ini라는 이름을 사용한다. 

MySQL 서버가 시작될 때만 이 설정 파일을 참조하는데, 이 경로가 고정되어있는것은 아니고, 지정된 여러 개의 디렉터리를 순차적으로 탐색하면서 처음 발견된 my.cnf 파일을 사용하게 된다.

 

다음 명령어를 통해 MySQL 서버가 어느 디렉터리에서 my.cnf를 읽는지 알 수 있다.

shell > mysqld --verbose --help
# 또는
shell > mysql --help

 

실행 결과

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /opt/homebrew/etc/my.cnf ~/.my.cnf
...

위의 경로 순서대로 my.cnf 파일을 찾아 해당 설정파일로 MySQL 서버를 띄우게 된다.

나는 homebrew에서 설치해서, 아래 경로의 my.cnf를 읽어온다.

/opt/homebrew/etc/my.cnf

 

homebrew에서 설치했을 경우, MySQL 실행 방법

$ brew services start mysql // 시작
$ brew services stop mysql  // 종료
$ brew services info mysql  // 상태

 

시작 시 실제 실행되는 프로그램 정보

$ brew services start mysql
==> Successfully started `mysql` (label: homebrew.mxcl.mysql)

$ ps -ef | grep mysql
  501 61597     1   0  1:45PM ?? 0:00.02 /bin/sh /opt/homebrew/opt/mysql/bin/mysqld_safe --datadir=/opt/homebrew/var/mysql
  501 61736 61597   0  1:45PM ?? 0:00.36 /opt/homebrew/opt/mysql/bin/mysqld --basedir=/opt/homebrew/opt/mysql --datadir=/opt/homebrew/var/mysql --plugin-dir=/opt/homebrew/opt/mysql/lib/plugin --log-error=MacBook-Air.local.err --pid-file=MacBook-Air.local.pid

brew를 이용해 mysql을 start하면, 위와 같이 mysqld_safe, mysqld가 실행된다. 해당 프로그램에 대해서 간단히 알아보자.

 

 

mysqld

mysqld, also known as MySQL Server, is a single multithreaded program that does most of the work in a MySQL installation. It does not spawn additional processes. MySQL Server manages access to the MySQL data directory that contains databases and tables. 

우리가 사용하는 실질적인 MySQL Server이다. 항상 mysqld를 통해 서버가 실행된다.

 

 

mysqld_safe

mysqld_safe is the recommended way to start a mysqld server on Unix. 
mysqld_safe adds some safety features such as restarting the server when an error occurs and logging runtime information to an error log.

mysqld을 더 안전하게 실행시켜주기 위한 프로그램이며, 내부 스크립트에서 mysqld를 실행시키고 프로세스를 모니터링하고, 에러 로그를 남겨주는 역할을 한다. 따라서 mysqld_safe를 통해 mysqld를 실행시키는게 권장되는 방법이다.

그 외에도 mysql.server, mysqld_multi 등이 있으나 내부적으로 최종적으로는 mysqld를 호출한다.

 

설정 파일

MySQL 설정 파일은 하나의 my.cnf(또는 my.ini) 파일에 여러 개의 설정 그룹을 담을 수 있으며, 대체로 실행 프로그램 이름을 그룹명으로 사용하게 된다.

[mysqld_safe]
...

[mysqld]
...

[mysql] // MySQL 클라이언트 프로그램용
...

[mysqldump] // 백업을 위한 dump 프로그램용
...

 

시스템 변수

MySQL 서버는 기동하면서 설정 파일 내용을 읽어 메모리나 작동 방식을 초기화하고, 접속된 사용자를 제어하기 위해 이러한 값을 별도로 저장해 둔다. 이렇게 저장된 값을 시스템 변수라고 하며, 다음 명령으로 확인할 수 있다.

mysql> SHOW VARIABLES;
+--------------------------------------------+------------------------------+
| Variable_name                              | Value                        |
+--------------------------------------------+------------------------------+
| activate_all_roles_on_login                | OFF                          |
| auto_generate_certs                        | ON                           |
| auto_increment_increment                   | 1                            |
| auto_increment_offset                      | 1                            |
// ...

이러한 값들이 MySQL 서버와 클라이언트에 영향을 미치는지 판단하려면 각 변수가 글로별 변수인지 세션 변수인지 구분할 수 있어야 한다. 이전에 시스템 변수가 가지는 속성에 대해서 알아보자.

 

 

시스템 변수의 속성

시스템 변수 항목 형식

• Cmd-Line : MySQL 서버의 명령행 인자로 설정될 수 있는지 여부를 나타낸다.
• Option file : MySQL의 설정파일인 my.cnf로 제어할 수 있는지 여부를 나타낸다. 
• System Var : 시스템 변수인지 아닌지를 나타낸다. 보통 시스템 변수일 경우 Variable_name을 언더스코어로 가져가고, 아닐 경우에는 '-'를 구분자로 사용하는 규칙을 가져가고 있다.
• Var Scope : 시스템 변수의 적용 범위를 나타낸다. 
  • Global : MySQL 서버 전체(글로벌 또는 전역) 대상
  • Session : MySQL 서버와 클라이언트 간 커넥션(또는 Session) 대상
  • Both : Global, Session 모두 적용
• Dynamic : 시스템 변수가 동적인지 정적인지 구분하는 변수이다.

 

글로벌 변수와 세션 변수

MySQL의 시스템 변수는 적용 범위에 따라 글로벌, 세션 변수로 나뉜다. 일반적으로 세션별로 적용되는 시스템 변수의 경우 글로벌 변수 뿐만 아니라 세션 변수에도 동시에 존재한다. 이는 디폴트 값을 주기 위함이며, 'Both'로 표시된다.

• 글로벌 범위의 시스템 변수는 하나의 MySQL 서버 인스턴스에서 전체적으로 영향을 미치는 시스템 변수를 의미하며, 서버 자체에 관련된 설정일 때가 많다. 
• 세션 범위의 시스템 변수는 MySQL 클라이언트가 MySQL 서버에 접속할 때 기본으로 부여하는 옵션의 기본값을 제어하는 데 사용된다. 클라이언트의 필요에 따라 개별 커넥션 단위로 다른 값으로 변경할 수 있으며, 기본 값은 글로벌 시스템 변수로 설정되어 있다. 
• 세션 변수 중, MySQL 서버의 설정파일(my.cnf)에 명시해 초기화할 수 있는 변수는 대부분 범위가 'Both'로 명시돼 있다. 
• 순수하게 'Session'으로 명시된 변수는 MySQL 서버의 설정 파일에 초깃값을 명시할 수 없다.

 

정적 변수와 동적 변수

MySQL 서버가 기동 중인 상태에서 SET 명령으로 변수를 변경 가능한지에 따라 동적 변수와 정적 변수로 구분된다. 

• 동적 변수 : MySQL 서버에서 SET GLOBAL, PERSIST 명령으로 변경하면 즉시 MySQL 서버에 반영된다. 
• 정적 변수 : MySQL 기동 상태에서는 변경할 수 없는 변수이다.

 

변수를 변경하는 경우를 다음과 같이 구분할 수 있다.

• 디스크에 저장돼 있는 설정파일(my.cnf)을 변경하는 경우
• 이미 기동중인 메모리에 있는 MySQL 서버의 시스템 변수를 변경하는 경우

 

but, 디스크에 저장된 설정 파일의 내용은 변경하더라도 MySQL 서버를 재시작하기 전에는 적용되지 않는다.

• 하지만 SHOW 명령을 통해 변숫값을 확인하거나, SET을 이용해 값 변경이 가능하다.
• 하지만 SET은 일시적인 변경이기 때문에 이를 영구히 적용시키기 위해서는 my.cnf도 변경해야 한다.
• MySQL 8.0 버전 부터는 SET PERSIST 명령을 이용하면, 실행중인 서버의 변수를 변경함과 동시에 설정 파일로도 기록한다.
  → 이 경우에는 my.cnf가 아닌 별도의 파일(mysqld-auto.cnf)에 기록된다.
  → 이후 서버가 재 실행되면, 자동 생성된 별도의 파일도 같이 참조해서 시스템 변수를 적용한다.
• SHOW나 SET 명령에서 GLOBAL 키워드의 유무에 따라 GLOBAL 변수, Session 변수를 조회하고 변경한다.

 

SET PERSIST, PERSIST ONLY

• SET PERSIST (바로 반영 + 재시작 반영)
  • 글로벌 변수에만 적용되며, 변경 후에 설정 파일로 기록한다.
  • 이 때, 동적 파일의 경우에는 실행중인 서버에서도 변경이 반영된다.
  • 정적 파일의 경우에는 반영되지 않는다.
• SET PERSIST ONLY (재시작 반영)
  • 동적 파일의 경우, 다음 재시작 시 반영되도록 한다.(영구 반영)
  • 정적 파일의 경우, 다음 재시작 시 반영되도록 한다.(영구 반영)
  • 즉, 동적 & 정적 파일 모두 사용할 수 있다.

 

RESET PERSIST

SET PERSIST, PERSIST ONLY로 추가된 변수를 삭제해야 할 경우가 있다. 이 때 mysqld-auto.cnf를 잘못 건드리게 되면 MySQL 서버 자체가 실행되지 않을 수 있으므로 명령을 통해 삭제하는 게 안전하다. 

이 때 사용되는 명령어가 RESET PERSIST이다.

# 특정 시스템 변수만 삭제
mysql> RESET PERSIST max_connections;
mysql> RESET PERSIST IF EXISTS max_connections;

# mysqld-auto.cnf 파일의 모든 시스템 변수 삭제
mysql> RESET PERSIST;