설정 파일

운영체제별 설정 파일

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

설정 파일의 경로

• 설정 파일의 경로는 고정되어 있지 않다.
• 실제 MySQL 서버는 단 하나의 설정 파일(my.cnf)만 사용하지만, 설정 파일이 위치한 디렉터리는 여러 곳일 수 있다.
• MySQL 서버는 지정된 여러 개의 디렉터리를 순차적으로 탐색하면서 처음 발견된 “my.cnf”파일을 사용한다.
  • 직접 MySQL을 컴파일해서 설치한 경우에는 이 디렉터리가 다르게 설정될 수도 있다.

어느 설정 파일을 읽는지 확인

• mysqld 프로그램을 실행하는 방법과 mysql 클라이언트 프로그램으로 확인하는 방법이 있다.
  • 실수로 my.cnf 파일을 여러 경로에 만들어서 MySQL 서버가 어느 디렉터리의 my.cnf 파일을 참조해서 기동했는지 알아내야 할 경우 사용한다.

1. mysqld 프로그램으로 설치된 MySQL 서버가 어느 디렉터리에서 “my.cnf”파일을 읽는지 확인

   • mysqld 프로그램은 MySQL 서버의 실행 프로그램으로, 서비스용으로 사용되는 서버에서 이미 MySQL 서버가 실행 중인데 다시 mysqld 프로그램을 시작한다거나 하지 않도록 주의해야 한다.
   • mysqld 프로그램을 --verbose --help 옵션을 주어 실행한다.

   ## mysqld 프로그램에 옵션을 주어 실행
   mysql --verbos --help

2. mysql 클라이언트 프로그램으로 확인

   • 가능하면 이 방법을 사용하는 것이 좋다.

   ## mysql 클라이언트 프로그램으로 확인
   mysql --help

• 두 명령 모두 상당히 많은 내용이 출력된다.
  • 내용 상단에 ‘Default options are read …’라는 부분을 보면 MySQL 서버나 클라이언트 프로그램이 어디에 있는 my.cnf(또는 my.ini) 파일을 참조하는지 확인할 수 있다.
  • MySQL 서버가 어느 디렉터리의 my.cnf 파일을 먼저 읽는지(우선순위가 높은지)를 확인할 수 있다.

경로

• 책의 예제에 따르면 다음과 같은 순서로 파일을 찾는다.
  1. /etc/my.cnf 파일
  2. /etc/mysql/my.cnf 파일
  3. /usr/etc/my.cnf 파일
  4. ~/ .my.cnf 파일
• 1번, 2번, 4번 파일은 어느 MySQL에서나 동일하게 검색하는 경로이다.
• 3번 파일은 컴파일될 때 MySQL 프로그램에 내장된 경로다.
  • 컴파일할 때 설정한 MySQL의 홈 디렉터리나 MySQL 홈 티렉터리 밑의 etc 디렉터리에 있는 my.cnf 파일이 표시된다.
• MySQL 서버용 설정 파일은 주로 1번이나 2번을 사용한다.
  • 하나의 장비(서버 머신)에 2개 이상의 MySQL 서버(인스턴스)를 실행하는 경우에는 1번과 2번은 충돌이 발생할 수 있다.
  • 위의 경우 공유된 디렉터리가 아닌 별도 디렉터리에 설정 파일을 준비하고 MySQL 시작 스크립트의 내용을 변경하는 방법을 사용한다.
  • 하나의 서버에 2개 이상의 MySQL 서버(인스턴스)를 실행하는 형태로 서비스하는 곳은 흔하지 않다.
• Window(본인)의 경우 아래 경로가 보였다.
  1. C:\WINDOWS\my.ini
  2. C:\WINDOWS\my.cnf
  3. C:\my.ini
  4. C:\my.cnf
  5. C:\Program Files\MySQL\MySQL Server 8.0\my.ini
  6. C:\Program Files\MySQL\MySQL Server 8.0\my.cnf
• Window의 경우, my.ini는 보통 MySQL basedir 하위에 있다.

  -- basedir 확인
  show variables like '%dir';

• .msi 인스톨러로 MySQL을 설치했을 경우 my.ini 파일은 %PROGRAMDATA% Directory 하위에 위치하게 된다.

  • my.ini 경로: %PROGRAMDATA%\MySQL\MySQL Server 8.0

    ## %PROGRAMDATA% 경로 확인
    echo %PROGRAMDATA%

설정 파일의 구성

• MySQL 설정 파일은 하나의 my.cnf나 my.ini 파일에 여러 개의 설정 그룹을 담을 수 있으며, 대체로 실행 프로그램 이름을 그룹명으로 사용한다.
• 예를 들어,
  • mysqldump 프로그램은 [mysqldump] 설정 그룹을
  • mysqld 프로그램은 설정 그룹이 [mysqld]인 영역을
  • mysqld_safe 프로그램은 [mysqld_safe]와 [mysqld] 섹션을 참조한다.

예제

[mysqld_safe]
malloc-lib = /opt/lib/libtcmalloc_minimal.so

[mysqld]
socket = /usr/local/mysql/tmp/mysql.sock
port = 3306

[mysql]
default-character-set = utf8mb4
socket = /usr/local/mysql/tmp/mysql.sock
port = 3304

[mysqldump]
default-character-set = utf8mb4
socket = /usr/local/mysql/tmp/mysql.sock
port = 3305

• 이 예제는 간략한 MySQL 설정 파일의 구성이다.
  • 이 설정 파일이 MySQL 서버만을 위한 설정파일이라면 [mysqld] 그룹만 명시해도 무방하다.
  • MySQL 서버뿐 아니라 MySQL 클라이언트나 백업을 위한 mysqldump 프로그램이 실행될 때도 이 설정 파일을 공용으로 사용하고 싶다면 [myqld] 도는 [mysqldump] 등의 그룹을 함께 설정해 둘 수 있다.
• 일반적으로 각 그룹을 사용하는 프로그램은 성격이 다르며, 각 프로그램이 필요로 하는 설정 내용이 상이하므로 이 예제처럼 중복되는 설정이 나열되는 경우는 거의 없다.
  • socket이나 port 같은 설정은 모든 프로그램에 공통으로 필요한 설정값이라서 위와 같이 각 설정 그룹에 여러 번 설정된 것이다.
• 설정 파일의 각 그룹은 같은 파일을 공유하지만 서로 무관하게 적용된다.
  • MySQL 서버(mysqld) 프로그램인 3306 포트를 사용한다.
  • MySQL 클라이언트(mysql) 프로그램은 3304번 포트를 이용해 MySQL 서버에 접속하려고 할 것이다.

MySQL 시스템 변수의 특징

시스템 변수 (System Variables)

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

  -- 시스템 변수를 확인하는 명령어
  SHOW VARIABLES;
  
  -- 혹은 아래 명령어를 사용해 확인할 수도 있다.
  SHOW GLOBAL VARIABLES;

시스템 변수의 목록

• MySQL 서버의 매뉴얼에서 시스템 변수(Server System Variables)를 설명한 페이지를 보면 MySQL 서버에서 제공하는 모든 시스템 변수의 목록과 간단한 설명을 참고할 수 있다.
• 설명 페이지에 있는 각 변수 항목은 다음 표와 같은 형식으로 구성돼 있다.
  • sql_log_bin 항목은 여기서 찾았다.

Name	Cmd-Line	Option File	System Var	Var Scope	Dynamic
activate_all_roles_on_login	Yes	Yes	Yes	Global	Yes
admin_address	Yes	Yes	Yes	Global	No
admin_port	Yes	Yes	Yes	Global	No
time_zone			Yes	Both	Yes
sql_log_bin			Yes	Session	Yes

시스템 변수의 속성

시스템 변수가 가지는 5가지 속성의 의미는 다음과 같다.

• Cmd-Line
  • MySQL 서버의 명령행 인자로 설정될 수 있는지 여부
  • ‘Yes’면 명령행 인자로 이 시스템 변수의 값을 변경하는 것이 가능하다는 의미다.
• Option File
  • MySQL의 설정 파일인 my.cnf(도는 my.ini)로 제어할 수 있는지 여부
  • 옵션 파일이나 설정 파일 또는 컨피규레이션 파일등은 전부 my.cnf(또는 my.ini) 파일을 지칭하는 것으로 같은 의미로 사용된다.
• System Var
  • 시스템 변수인지 아닌지 여부
  • MySQL 서버의 설정 파일을 작성할 때 각 변수명에 사용된 하이픈(’-’)이나 언더스코어(’_’)의 구분에 주의해야 한다.
  • 현재 MySQL 8.0에서는 모든 시스템 변수들이 ‘_’를 구분자로 사용하도록 변경된 것으로 보인다.
  • 명령행 옵션으로만 사용 가능한 설정들은 ‘-’을 구분자로 사용한다.
• Var Scope
  • 시스템 변수의 적용 범위
  • Gobal: 이 시스템 변수가 영향을 미치는 곳이 MySQL 서버 전체를 대상으로 한다.
  • Session: 이 시스템 변수가 영향을 미치는 곳이 MySQL 서버와 클라이언트 간의 커넥션(Session, 세션 또는 커넥션)만이다.
  • 어떤 변수는 세션과 글로벌 범위에 모두 적용(Both)되기도 한다.
• Dynamic
  • 시스템 변수가 동적인지 정적인지 구분

글로벌 변수와 세션 변수

시스템 변수(설정) 값이 어떤 MySQL 서버와 클라이언트에 영향을 미치는지 판단하려면 각 변수가 글로벌 변수인지 세션 변수인지 구분할 수 있어야 한다.

• MySQL의 시스템 변수는 적용 범위에 따라 글로벌 변수와 세션 변수로 나뉜다.
• 일반적으로 세션별로 적용되는 시스템 변수의 경우, 글로벌 변수뿐만 아니라 세션 변수에도 동시에 존재한다.
  • 이러한 경우 MySQL 매뉴얼의 ‘Var Scope’에는 ‘Both’라고 표시된다.

글로벌 범위의 시스템 변수

• 하나의 MySQL 서버 인스턴스에서 전체적으로 영향을 미치는 시스템 변수를 의미한다.
• 주로 MySQL 서버 자체에 관련된 설정일 때가 많다.
• 대표적인 예
  • MySQL 서버에서 단 하나만 존재하는 InnoDB 버퍼 풀 크기(innodb_buffer_pool_size)
  • MyISAM의 키 캐시 크기 (Key_buffer_size)

세션 범위의 시스템 변수

• MySQL 클라이언트가 MySQL 서버에 접속할 때 기본으로 부여하는 옵션의 기본값을 제어하는 데 사용된다.
• 다른 DBMS에서도 비슷하겠지만 MySQL에서도 각 클라이언트가 처음에 접속하면 기본적으로 부여하는 기본값을 갖고 있다.
  • 별도로 그 값을 변경하지 않은 경우 그대로 값이 유지된다.
  • 클라이언트의 필요에 따라 개별 커넥션 단위로 다른 값으로 변경할 수 있는 것이 세션 변수다.
  • 기본값은 글로벌 시스템 변수이며, 각 클라이언트가 가지는 값이 세션 시스템 변수다.
• 대표적인 예
  • 각 클라이언트에서 쿼리 단위로 자동 커밋을 수행할지 여부를 결정하는 autocommit 변수
  • autocommit 을 ON으로 설정해 두면 해당 서버에 접속하는 모든 커넥션은 기본으로 자동 커밋 모드로 시작한다.
• 세션 변수는 커넥션별로 설정값을 서로 다르게 지정할 수 있으며, 한번 연결된 커넥션의 세션 변수는 서버에서 강제로 변경할 수 없다.
• 순수하게 범위가 세션(Session)이라고 명시된 시스템 변수는 MySQL 서버의 설정 파일에 초깃값을 명시할 수 없다.
  • 커넥션이 만들어지는 순간부터 해당 커넥션에서만 유효한 변수를 말한다.

범위가 Both인 시스템 변수

• 세션 범위의 시스템 변수 가운데 MySQL 서버의 설정 파일(my.cnf 또는 my.ini)에 명시해 초기화할 수 있는 변수는 대부분 범위가 ‘Both’라고 명시돼 있다.
• ‘Both’로 명시된 시스템 변수는 MySQL 서버가 기억만 하고 있다가 실제 클라이언트와의 커넥션이 생성되는 순간에 해당 커넥션의 기본값으로 사용되는 값이다.

정적 변수와 동적 변수

• MySQL 서버의 시스템 변수는 MySQL 서버가 기동 중인 상태에서 변경 가능한지에 따라 동적 변수와 정적 변수로 구분된다.
• MySQL 서버의 시스템 변수는 다음 두 가지 경우로 구분할 수 있다.
  • 디스크에 저장돼 있는 설정 파일(my.cnf 또는 my.ini)을 변경하는 경우
  • 이미 기동 중인 MySQL 서버의 메모리에 있는 MySQL 서버의 시스템 변수를 변경하는 경우

시스템 변수의 값 변경

• 디스크에 저장된 설정 파일의 내용은 변경하더라도 MySQL 서버가 재시작하기 전에는 적용되지 않는다.
  • my.cnf 설정 파일을 변경할 때 MySQL 서버를 재시작하는 경우가 많다.
• SHOW 명령으로 MySQL 서버에 적용된 변숫값을 확인하거나 SET 명령을 이용해 값을 바꿀 수도 있다.
  • 변수명을 정확히 모른다면 % 문자를 이용해 패턴 검색을 하는 것도 가능하다.

-- MySQL 서버에 적용된 변숫값 확인
SHOW GLOBAL VARIABLES LIKE 'max_connections';

-- MySQL 서버에 적용된 변숫값 확인(패턴 검색)
SHOW GLOBAL VARIABLES LIKE '%max_connections%';

-- 변수 값 변경
SET GLOBAL max_connections=500;

• SHOW 나 SET 명령에서 GLOBAL 키워드를 사용하면 글로벌 시스템 변수의 목록과 내용을 읽고 변경할 수 있다.
• SHOW 나 SET 명령에서 GLOBAL 키워드를 빼면 자동으로 세션 변수를 조회하고 변경한다.
• SET 명령을 통해 변경되는 시스템 변숫값은 현재 기동 중인 MySQL 인스턴스에서만 유효하다.
  • MySQL의 설정 파일에 반영되는 것이 아니다.
  • MySQL 서버가 재시작하면 다시 설정 파일의 내용으로 초기화된다.
• MySQL 8.0 버전부터는 SET PERSIST 명령을 이용하면 실행 중인 MySQL 서버의 시스템 변수를 변경함과 동시에 자동으로 설정 파일로도 기록된다

동적인 변수를 변경

• 일반적으로 글로벌 시스템 변수는 MySQL 서버의 기동 중에는 변경할 수 없는 것이 많지만 실시간으로 변경할 수 있는 것도 있다.
• 변경하고자 하는 값이 동적 변수라면 SET 명령으로 간단히 변숫값을 변경할 수 있다.
  • 굳이 MySQL 서버를 재시작하지 않아도 된다.
• 동적으로 시스템 변수값을 변경하는 경우,
  • SET 명령으로 시스템 변수를 변경하면 my.cnf 설정 파일에는 변경 내용이 기록되지 않는다.
  • SET PERSIST 명령을 사용하는 경우 변경된 시스템 변수는 my.cnf 파일이 아닌 별도의 파일에 기록된다.
  • 설정 파일까지 내용을 변경하고자 한다면 SET PERSIST 명령을 사용해야 한다.
• 시스템 변수의 범위가 ‘Both’인 경우에는 글로벌 시스템 변수의 값을 변경해도 이미 존재하는 커넥션의 세션 변숫값은 변경되지 않고 그대로 유지된다.
• MySQL의 시스템 변수 가운데 동적인 변수만 SET 명령을 이용해 변경하는 것이 가능하다.

SET PERSIST

SET 의 문제점과 SET PERSIST

• 동적 변수의 경우 MySQL 서버에서 SET GLOBAL 명령으로 변경하면 즉시 MySQL 서버에 반영된다.
  • MySQL 서버의 설정 파일에 변경 내용을 적용하지 않으면, MySQL 서버를 재시작하면 다시 예전의 시스템 변수 값으로 시작된다는 문제가 있다.
• MySQL 8.0 버전에서는 이러한 문제점을 보완하기 위해 SET PERSIST 명령을 도입했다.
• SET PERSIST 명령으로 시스템 변수를 변경하면,
  • MySQL 서버는 변경된 값을 즉시 적용하고
  • 동시에 별도의 설정 파일(mysqld-auto.cnf)에 변경 내용을 추가로 기록한다.
• MySQL 서버가 다시 시작될 때 기본 설정 파일(my.cnf)뿐만 아니라 자동 생성된 파일(mysqld-auto.cnf) 파일을 같이 참조해서 시스템 변수를 적용한다.
  • 즉, SET PERSIST 명령을 사용하면 MySQL 서버의 설정 파일에 변경 내용을 수동으로 기록하지 않아도 자동으로 영구 변경이 된다.

SET PERSIST max_connections=5000;

적용 범위

• SET PERSIST 명령은 세션 변수에는 적용되지 않는다.
• SET PERSIST 명령으로 시스템 변수를 변경하면 MySQL 서버는 자동으로 GLOBAL 시스템 변수의 변경으로 인식하고 변경한다.

SET PERSIST_ONLY

• 현재 실행 중인 MySQL 서버에는 변경 내용을 적용하지 않고 다음 재시작을 위해 mysqld-auto.cnf 파일에만 변경 내용을 기록해두고 싶을 때 사용하는 명령이다.
• 정적인 변수의 값을 영구적으로 변경하고자 할 때도 사용할 수 있다.
  • SET PERSIST 명령은 정적인 변수는 실행 중인 MySQL 서버에서 변경할 수 없다. (MySQL 서버가 재시작될 때만 변경될 수 있다)
  • 정적 변수를 mysqld-auto.cnf 파일에 기록해두고자 할 때 SET PERSIST_ONLY 명령을 활용한다.

-- 정적인 변수의 값으르 영구적으로 변경
SET PERSIST_ONLY innodb_doublewrite=ON;

RESET PERSIST

• SET PERSIST 명령이나 SET PERSIST_ONLY 명령으로 추가된 시스템 변수의 내용을 삭제해야 할 때도 있다.
  • 이때 mysqld-auto.cnf 파일의 내용을 직접 변경하다가 내용상 오류를 만드는 경우 MySQL 서버가 시작되지 못할 수도 있다.
• mysqld-auto.cnf 파일의 내용을 삭제해야 하는 경우에는 RESET PERSIST 명령을 사용하는 것이 안전하다.

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

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

변경 내용 확인

mysqld-auto.cnf 파일

• SET PERSIST 명령이나 SET PERSIST_ONLY 명령으로 시스템 변수를 변경하면 JSON 포맷의 mysqld-auto.cnf 파일이 생성된다.
  • 별도의 들여쓰기나 라인 구분이 되어있진 않다.
• mysqld-auto.cnf 파일에는 다음 정보들이 기록된다.
  • 변경된 시스템 변수의 이름과 설정값
  • 언제 누구에 의해 시스템 변수가 변경됐는지 등의 정보

시스템 변수의 메타데이터

• SET PERSIST 명령이나 SET PERSIST_ONLY 명령으로 변경된 시스템 변수의 메타데이터는 다음 항목들을 통해 참조할 수도 있다.
  • performance_schema.variables_info 뷰
  • performance_schema.persisted_variables 테이블

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

my.conf 파일

• MySQL 8.0 서버의 시스템 변수는 대략 570개 수준이며, 사용하는 플러그인이나 컴포넌트에 따라 수가 더 늘어날 수도 있다.
• MySQL 서버를 제대로 사용하려면 시스템 변수에 대한 이해가 상당히 많이 필요하다.
• (책에서 적어둔 시스템 변수 중 많은 시스템 변수는) MySQL 서버가 실행 중인 서버의 하드웨어 특성과 서비스의 특성에 따라 오히려 성능을 떨어지게 만들 수도 있다.
• 만약 PC에서 테스트용으로 MySQL 서버를 실행하는 경우라면, 아래 설정들은 낮은 수치로 조정해서 사용하는 것이 좋다.
  • max_connections=100
  • innodb_sort_buffer_size=5M
  • innodb_log_files_in_group=2
  • innodb_log_file_size=1024M
  • innodb_buffer_pool_size=200M
  • inoodb_buffer_pool_instances=1
  • innodb_io_capacity=100
  • innodb_io_capacity_max=400

Reference

참고 서적

📔 Real MySQL 8.0

참고 사이트

https://velog.io/@jskim/MySQL-설정-파일-my.ini-또는-my.cnf-찾기
MySQL 서버 매뉴얼의 시스템 변수