MySQL 서버 설정

김민우·2022년 12월 29일
DBMSmysqlsql데이터베이스
0

MySQL

목록 보기
1/7

일반적으로 MySQL 서버는 단 하나의 설정 파일을 사용하는데 운영체제마다 이름은 다음과 같다.

  • 유닉스 계열(리눅스 포함) : my.cnf
  • 윈도우 계열 : my.ini

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

또한, 직접 MySQL을 컴파일해서 설치한 경우에는 이 디렉토리가 다르게 설정될 수도 있다. 만약 설치된 MySQL 서버가 어느 디렉토리에서 my.cnf파일을 읽는지 궁금하다면 다음과 같이 mysqld 프로그램을 --verbose --help 옵션을 주어 실행해보면 된다.

실제 MySQL 서버는 단 하나의 설정 파일(my.cnf)만 사용하지만 설정 파일이 위치한 디렉토리는 여러 개일 수 있다. 디렉토리는 다음 총 4가지 이다.

  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 서버가 이들 중 어느 디렉토리의 my.cnf를 참조해서 가동했는지는 알아내기가 쉽지 않다. 이러한 경우에는 위의 명령어 --verbose --help 으로 MySQL 서버의 가장 높은 디렉토리 우선순위를 확인할 수 있다.

1. 설정 파일의 구성

MySQL 설정 파일은 하나의 my.cnf나 my.ini 파일에 여러 개의 설정 그룹을 담을 수 있으며, 대체로 실행 프로그램 이름을 그룹명으로 사용한다는 것을 알아두자.

예를 들어, mysqldump 프로그램은 [mysqldump] 설정 그릅우르, mysqld 프로그램은 설정 그룹의 이름이 [mysqld]인 영역을 참조한다.

어떤 설정 파일이 MySQL 서버만을 위한 설정 파일이라면 [mysqld] 그룹만 명시해도 무방하다. 하지만 MySQL 서버뿐 아니라 MySQL 클라이언트나 백업을 위한 mysqldump 프로그램이 실행될 때도 이 설정 파일을 공용으로 사용하고 싶다면 [mysql] 또는 [mysqldump] 등의 그룹을 함께 설정해둘 수 있다. 

[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

일반적으로 각 그룹을 사용하는 프로그램은 성격이 다르며, 각 프로그램이 필요로 하는 설정 내용이 상이하므로 위 예제 처럼 중복되는 설정이 나열되는 경우는 거의 없지만 socket이나 port 같은 설정은 모든 프로그램에 공통적으로 필요한 설정값임을 참고하자.

이 예제의 설정 파일을 사용하는 MySQL 서버(mysqld) 프로그램은 3306 포트를 사용한다. 하지만 MySQL 클라이언트(mysql) 프로그램은 3304번 포트를 이용해 MySQL 서버에 접속하려고 할 것이다. 즉, 설정 파일의 각 그룹은 같은 파일을 공유하지만 서로 무관하게 적용된다는 의미이다.

2. MySQL 시스템 변수의 특징

MySQL 서버는 기동하면서 설정 파일의 내용을 읽어 메모리나 작동 방식을 초기화하고, 접속된 사용자를 제어하기 위해 이러한 값들을 별도로 저장해둔다. MySQL 서버에서는 이렇게 저장된 값을 시스템 변수(System Variables)라고 한다.

각 시스템 변수는 다음 예제와 같이 MySQL 서버에 접속해 SHOW VARIABLES 또는 SHOW GRLOBAL VARIABLES라는 명렁어로 확인할 수 있다.

시스템 변수(설정) 값이 어떻게 MySQL 서버와 클라이언트에 영향을 미치는지 판단하려면 각 변수가 글로벌 변수인지 세션 변수인지 구분할 수 있어야 한다.
이를 위해서는 우선 글로벌 변수와 세션 변수가 무엇이고 서로 어떤 관계가 있는지 명확히 이해해야 한다.
(시스템 변수에 관한 자세한 설명은 MySQL 서버의 메뉴얼의 시스템 변수를 참고하자.)

시스템 변수가 가지는 5가지 속성이 존재하는데 의미는 다음과 같다.

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

3. 글로벌 변수와 세션 변수

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

글로벌 변수

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

세션 변수

  • MySQL 클라이언트가 MySQL 서버에 접속할 때 기본으로 부여하는 옵션의 기본값을 제어하는데 사용된다.
  • 각 클라이언트가 처음에 접속하면 기본적으로 부여하는 기본값을 가지고 있다, 별도로 그 값을 변경하지 않은 경우에는 그대로 값이 유지되지만, 클라이언트의 필요에 따라 개별 커넥션 단위로 다른 값으로 변경할 수 있는 것이 세션 변수이다. (여기서 기본 값은 글로벌 시스템 변수이고, 각 클라이언트가 가지는 값이 세션 시스템 변수이다.)
  • 예) 각 클라이언트에서 쿼리 단위로 자동 커밋을 수행할지 여부를 결정하는 autocommit 변수

4. 정적 변수와 동적 변수

MySQL 서버의 시스템 변수는 MySQL 서버가 기동 중인 상태에서 변경 가능한지에 따라 동적 변수와 정적 변수로 구분된다.

MySQL 서버의 시스템 변수는 디스크에 저장돼 있는 설정 파일(my.cnf)을 변경하는 경우와 이미 기동 중인 MySQL 서버의 메모리에 있는 MySQL 서버의 시스템 변수를 변경하는 경우로 구분할 수 있다. 디스크에 저장된 설정 파일의 내용은 변경하더라도 MySQL 서버의 시스템 변수를 변경하는 경우로 구분할 수 있다.

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

하지만 SET 명령을 통해 변경되는 시스템 변수값이 MySQL의 설정 파일인 my.cnf 파일에 반영되는 것은 아님에 주의하자. 현재 기동 중인 MySQL의 인스턴스에만 유효하다. MySQL 서버가 재시작하면 다시 설정 파일의 내용으로 초기화되기 때문에 설정을 영구히 적용하려면 my.cnf 파일도 반드시 변경해야 한다.

MySQL 8.0 버전부터는 SET PERSIST 명령을 이용하면 실행 중인 MySQL 서버의 시스템 변수를 변경함과 동시에 자동으로 설정 파일로도 기록 된다.

SHOW 또는 SET 명령에서 GLOBAL 키워드를 사용하면 글로벌 시스템 변수의 목록과 내용을 읽고 변경할 수 있으며, GLOBAL 키워드를 빼면 자동으로 세션 변수를 조회 및 변경한다. (디폴트는 세션 변수임을 알아두자.)

일반적으로 글로벌 시스템 변수는 MySQL 서버의 기동 중에는 변경할 수 없는 것이 많지만 실시간으로 변경할 수 있는 것도 존재한다. my.cnf 설정 파일을 변경할 때 MySQL 서버를 재시작하는 경우가 많은데, 사실 변경하고자 하는 값이 동적 변수라면 SET 명령으로 간단히 변수값을 변경할 수 있으며, 굳이 MySQL 서버를 재시작하지 않아도 된다. 이처럼 동적으로 시스템 변수값을 변경하는 경우 SET 을 사용하면 설정 파일에 변경 내용이 기록되진 않지만 SET PERSIST를 사용하면 설정 파일에도 변경 내용이 기록된다는 것을 알아두자.

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

5. SET PERSIST

MySQL 서버의 시스템 변수 중 동적 변수는 MySQL 서버에서 SET GLOBAL 명령으로 변경하면 즉시 MySQL 서버에 반영된다. 예를 들어 MySQL 서버의 max_connections 라는 시스템 변수가 있는데, 이 시스템 변수는 MySQL 서버로 접속할 수 있는 최대 커넥션의 개수를 제어하는 동적 시스템 변수이다. 실시간으로 최대 커넥션 개수를 늘리기 위해선 다음과 같이 MySQL 서버의 시스템 변수를 변경하게 될 것이다.

mysql> SET GLOBAL max_connections=5000;

문제는 이렇게 변경한 후 MySQL 서버의 설정 파일에서도 이 내용을 적용해야 하는데, 응급조치를 하다 보면 MySQL 서버의 설정 파일에 변경 내용을 적용하는 것을 잊어버릴 때도 있다. 그리고 시간이 지나서 MySQL 서버를 재시작하면 다시 예전 시스템 변수값을 사용함으로써 장애가 반복적으로 발생할 수 있다. 실제로 이런 상황은 꽤 빈번하게 일어난다.

MySQL 8.0 버전에서는 이러한 문제점을 보완하기 위해 SET PERSIST 명령을 도입했다. 위 예제를 다시 보자.

mysql> SET PERSIST max_connections=5000;
mysql> SHOW GLOBAL VARIABLES LIKE 'max_connections';
  • 설정 파일에 max_connections값이 변경된 것을 확인할 수 있다.

즉, SET PERSIST 명령을 사용하면 MySQL 서버의 설정 파일(my.cnf)에 변경 내용을 수동으로 기록하지 않아도 자동으로 영구 변경이 된다.

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

현재 실행 중인 MySQL 서버에는 변경 내용을 적용하지 않고 다음 재시작을 위해 mysqld-auto.cnf 파일에만 변경 내용을 기록하고자 한다면 SET PERSIST_ONLY 를 사용하면 된다. 이 명령어에 대해 잠깐 알아보자.

SET PERSIST_ONLY 명령어는 정적인 변수의 값을 영구적으로 변경하고자 할 때도 사용할 수 있다. 반대로 SET PERSIST를 사용하면 정적인 변수는 실행 중인 MySQL 서버에서 변경할 수 없다.

SET PERSIST_ONLY 또는 SET PERSIST를 통해서 시스템 변수를 변경하면 JSON 포맷의 mysqld-auto.cnf 파일이 생성된다. 이 파일에는 변경된 시스템 변수의 이름과 설정값, 그리고 추가로 언제 누구에 의해 시스템 변수가 변경됐는지 등의 정보도 함께 기록된다.

SET PERSIST_ONLY 또는 SET PERSIST 명령으로 추가된 시스템 변수의 내용을 삭제해야 할 때도 있다. 이 때는 mysqld-auto.cnf 파일 내용을 절대로 직접 변경하지 말고 다음과 같이 RESET PERSIST 명령어를 사용하자.

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

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

6. my.cnf 파일

MySQL 8.0 서버의 시스템 변수는 대략 570개 수준이며, 사용하는 플러그인이나 컴포넌트에 따라 시스템 변수의 개수는 더 늘어날 수도 있다. 물론 모든 시스템 변수를 공부해야 하는 것은 아니지만 MySQL 서버를 제대로 사용하려면 시스템 변수에 대한 이해가 상당히 많이 필요하다.

profile
김민우
다음 포스트

사용자 및 권한

0개의 댓글