[Real MySQL 8.0] 2. 설치와 설정 (2부)

차_현·2025년 1월 14일
CSmysqlrealmysql8.0
0
post-thumbnail

이전 포스트에 이어서, 계속해서 공부해보자.

서버 설정

MySQL 서버는 단 하나의 설정 파일만 사용하는데, 유닉스 계열에서는 my.cnf 라는 이름을 사용한다. 서버를 시작할 때에만 해당 파일 my.cnf를 참조하는데, MySQL 서버는 지정된 여러 개의 디렉토리를 순차적으로 탐색하면서 처음 발견된 my.cnf 파일을 사용한다.

my.cnf를 파일을 찾기 위해서는 순회하는 디렉터리 경로가 궁금하다면 아래 명령어들로 확인을 해볼 수 있다. 단, mysqld 프로그램은 MySQL 서버의 실행 프로그램으로 해당 option을 빠트리면 이미 실행중인 MySQL 서버가 실행중일 때, 다시 mysqld 프로그램을 시작할 수가 있기 때문에(실제 서버를 기동할수 있기에), 두번째 스크린샷 처럼 mysql 클라이언트 프로그램으로 확인해보는 것이 좋다.

shell> mysqld --verbose --help
shell> mysql --help

해당 명령어의 결과를 보면, 많은 내용이 출력되는데

위의 스크린샷은 출력결과의 중간 부분을 캡처한 것이다. ‘Default options are read from the following files …’ 라는 부분을 보면 MySQL 서버나 클라이언트 프로그램이 어디에 있는 my.cnf(또는 윈도우 계열에서는 my.ini) 파일을 참조하는지 확인할 수 있다.

해당 캡처 이미지를 보면 여러 경로들이 출력되는 것을 확인할 수 있는데, 실제 MySQL 서버는 단 하나의 설정 파일(my.cnf)만 사용하지만, 설정 파일이 위치한 디렉터리는 여러 곳일 수 있다는 소리이다.

위 4개의 경로중 3번째 경로를 제외한 나머지 경로는, 어느 MySQL에서나 동이랗게 검색되는 경로이고, 3번째 경로는 컴파일될 때 MySQL 프로그램에 내장된 경로이다. 즉, 컴파일 할 때 설정한 MySQL의 홈 디렉터리나 MySQL 홈 디렉터리 밑의 etc 디렉터리에 있는 my.cnf 파일이 표시된다.

MySQL 시스템 변수

MySQL 서버는 시작할 때, 설정 파일의 내용을 읽어 메모리나 작동 방식을 초기화하고, 접속된 사용자를 제어하기 위해서 이러한 설정 값들을 변수에 저장하는데, 이러한 변수를 ‘시스템 변수’ 라고 한다.

시스템 변수는 SHOW VARIABLES 명령이나 SHOW GLOBAL VARIABLES 명령어로 확인할 수 있다. 이 변수들이 실제로 서버와 클라이언트에 어떠한 영향을 미치는지 알기 위해서는 각 변수가 글로벌 변수인지 세션 변수인지를 구분할 수 있어야 한다.

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

해당 링크 문서의 중간 부분을 캡쳐한 것인데, MySQL 서버에서 제공하는 모든 시스템 변수의 목록과 간단한 설명 정도를 확인할 수 있다. 해당 표의 컬럼은 앞에서부터 Name, Cmd-Line, Option File, System Var, Var Scope, Dynamic 순서이다.

  • Cmd-Line: MySQL 서버의 명령행 인자로 설정될 수 있는지 여부를 나타낸다. 즉, 이 값이 ‘Yes’ 이면 명령행 인자로 이 시스템 변수의 값을 변경하는 것이 가능하다는 의미이다.
  • Option file: MySQL의 설정파일인 my.cnf로 제어할 수 있는지 여부를 나타낸다. 옵션 파일이나 설정 파일 또는 configuration 파일 등은 전부 my.cnf파일을 지칭하는 것으로 같은 의미로 사용된다.
  • System Var: 시스템 변수인지 아닌지를 나타내며, MySQL 서버의 설정 파일을 작성할 때, 각 변수명에 사용된 하이픈 이나 언더바의 구분에 주의해야한다.
  • Var Scope: 시스템 변수의 적용 범위를 나타낸다. 이 시스템 변수가 영향을 미치는 곳이 MySQL 서버 전체를 대상으로 하는지(Global), MySQL 서버와 클라이언트 간의 커넥션인지(Session) 구분한다. 그리고 스크린샷에는 있지 않지만, Both 라는 값도 찾아볼 수 있는데, Global, 글로벌과 Session, 세션 모두에 적용된다고 보면 된다.
  • Dynamic: 시스템 변수가 동적인지 정적인지 구분한다.

글로벌 변수와 세션변수

‘적용범위’ 에 따라서 글로벌 변수와 세션변수로 나뉜다. 세션별로 적용되는 시스템 변수의 경우 글로벌 변수 뿐만 아니라 세션 변수에도 동시에 존재한다. 이럴때 이제, 위에서 언급한 것 처럼 Var Scope에 ‘Both’로 표기가 된다.

그럼 이제 글로벌 변수가 뭔지 그리고 세션 변수는 뭔지 한번 알아보자.

  • 글로벌 변수: 하나의 MySQL 서버 인스턴스에서 전체적으로 영향을 미치는 시스템 변수를 의미한다. MySQL 서버에서 단 하나만 존재하는 InnoDB 버퍼 풀의 크기(innodb_buffer_pool_size), MyISAM의 키 캐시 크기(key_buffer_size) 와 같이 주로 MySQL 서버 자체에 관한 설정일 때가 대부분이다.
  • 세션 변수: MySQL 클라이언트가 MySQL 서버에 접속할 때, 기본으로 부여하는 옵션의 기본값을 제어하기 위해 사용되는 변수이다. 따라서 클라이언트의 필요에 따라 개별 커넥션 단위로 다른 값으로 변경할 수 있는 변수이다. 예를 들어, 각 클라이언트에서 쿼리 단위로 자동 커밋을 수행할지 여부를 결정하는 autocommit 변수가 있다. 이 변수를 ON으로 설정하면 해당 서버에 접속하는 모든 커넥션은 기본으로 자동 커밋 모드로 시작되지만, 각 커넥션에서 autocommit 변수에 OFF로 설정한다면, 자동 커밋 모드를 비활성화 할 수 있다. 세션변수는 커넥션별로 설정값을 다르게 할 수는 있지만, 한번 연결된 커넥션의 세션 변수는 서버에서 강제로는 변경할 수 없다.
  • 세션 범위의 시스템 변수 가운데, MySQL 서버의 설정 파일 my.cnf에 명시해 초기화 할 수 있는 변수는 대부분 범위가 ‘Both’라고 명시돼 있다. 순수하게 세션 범위만 표기되어 있는 시스템 변수는 설정 파일에 초기 값을 명시할 수 없어, 커넥션이 만들어지는 순간부터 해당 커넥션에서만 유효한 설정 변수를 나타내게 된다.

동적변수와 정적변수

MySQL 서버의 시스템 변수는 ‘서버가 기동 중인 상태에서 변경 가능한지 여부’에 따라 동적변수와 정적 변수로 나뉜다. 그리고 MySQL 서버의 시스템 변수는 아래 2경우로 구분할 수 있다.

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

디스크에 저장되어 있는 설정파일(my.cnf)을 변경하더라도 MySQL 서버가 재시작하기 전에는 적용되지 않는다. 하지만 SHOW 명령어로 MySQL 서버에 적용된 변숫값을 확인하거나 SET 명령어로 값을 바꿀 수 있다.

하지만 SET 명령을 통해 변경되는 시스템 변수의 값이 my.cnf 설정 파일에 직접적으로 반영되는 것은 아니기 때문에, 현재 기동 중 MySQL 인스턴스에 한해서만 유효하다.

MySQL 서버가 다시 시작한다면, 다시 설정 파일의 내용으로 초기화되기 때문에, 설정을 영구적으로 적용하고 싶다면 my.cnf 파일도 반드시 변경해야 한다.

MySQL 8.0 버전부터는 SET PERSIST 명령어를 이용하면, 실행 중인 MySQL 서버의 시스템 변수를 변경할 수 있고, 그리고 자동적으로 설정 파일로도 기록이 될 수 있다. SHOW나 SET 명령어에서 위의 캡처 이미지 속의 명령어와 같이 GLOBAL(글로벌) 키워드를 사용하면 글로벌 시스템 변수의 목록과 내용을 읽고 변경할 수 있다. 그리고 GLOBAL(글로벌) 키워드를 빼게 되면 자동으로 세션 변수를 조회하고 변경한다.

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

만일, 설정 파일의 내용을 변경하고 싶다면 SET PERSIST 명령어를 사용하면 된다. SET PERSIST 명령어를 사용하는 경우 변경된 시스템 변수는 my.cnf 파일이 아닌 별도의 파일에 기록된다.

SET PERSIST

앞서 살펴봤듯이, MySQL 서버의 시스템 변수는 ‘서버가 기동 중인 상태에서 변경 가능한지 여부’ 에 따라서 동적변수와 정적변수로 나누었다. 동적 변수 같은 경우에는 MySQL 서버에서 SET GLOBAL 명령으로 변경하면 즉시 MySQL 서버에 변경된다.

max_connections라는 시스템 변수는 MySQL 서버로 접속할 때 접속할 수 있는 최대 커넥션의 개수를 제한하는 동적 시스템 변수인데, MySQL 서버에 커넥션을 많이 사용 중이라면 최대 연결 가능 커넥션의 개수를 더 늘리기 위해서 아래와 같이 MySQL 서버의 시스템 변수를 변경한다.

mysql> SET GLOBAL max_connections=5000;

하지만 이렇게 변경을 하고 나서, 설정 파일에도 이 변경 사항을 적용을 해야하는데, MySQL 서버의 설정 파일에 변경 내용을 적용하는 것을 까먹을? 수도 있으니,,, 이를 위해 SET PERSIST 명령을 MySQL 8.0 버전부터 사용할 수 있게 되었다.

mysql> SET PERSIST max_connections=5000;
mysql> SHOW GLOBAL VARIABLES LIKE 'max_connections';

위와 같이 SET PERSIST 명령을 사용한다면, MySQL 서버는 변경된 값을 즉시 적용하고, 별도의 설정 파일(mysqld-auto.cnf)에 변경 내용을 추가로 기록해 둔다. 그리고 MySQL 서버가 재시작 될 때, 기본 설정 파일(my.cnf)뿐만 아니라 자동 생성된 mysqld-auto.cnf 파일을 같이 참조해서 시스템 변수에 적용한다.

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

만약, 현재 실행 중인 MySQL 서버에는 변경 내용을 적용하지 않고, 이 다음 재시작을 위해서 mysqld-auto.cnf파일에만 변경 내용을 기록하고 싶다면 SET PERSIST_ONLY 명령을 사용한다. 

	mysql> SET PERSIST_ONLY max_connections=5000;

또한, 정적 변수를 영구적으로 변경할 때도, SET PERSIST ONLY 명령을 사용할 수 있다.

처음에 봤던 SET PERSIST 명령은 현재 실행 중인 MySQL 서버에서 동적인 변수들의 값을 변경하고 또 mysqld-auto.cnf 파일에도 기록이 되는데, 반면에 정적 변수는 실행중인 MySQL 서버에서 변경할 수 없다. 그래서 정적 변수로 대표적인 innodb_doublewrite 정적 시스템 변수 같은 경우에는 MySQL 서버가 재시작될 때만 변경될 수 있다. 그리고 이렇게 정적 변수를 mysqld-auto.cnf 파일에 기록하고자 할 때, SET PERSIST_ONLY 명령을 사용한다. 

	mysql> SET PERSIST_ONLY innodb_doublewrite=ON;

지금까지 봤던 것 처럼, SET PERSIST 명령이나 SET_PERSIST_ONLY 명령으로 시스템 변수를 변경하면 mysqld-auto.cnf 파일이 생성되는데, 변경된 시스템 변수의 이름, 설정값, 누구에 의해서 변경되었는지 등의 정보가 기록된다. 아래보면 잘 보이지 않지만 위에서 내가 변경했던 동적 시스템 변수 max_connections를 변경한 이력이 기록된 걸 볼 수 있다.

my.cnf 설정 파일

my.cnf 설정 파일에서 가장 중요한 설정 그룹은 [mysqld] 설정 그룹이다. 

server-id = 1

user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
tmpdir = /usr/local/mysql/tmp

character-set-server = utf8
collation-server = utf8_general_ci
default-storage-engine = InnoDB
skip-name-resolve

event-scheduler = OFF

max_connections = 300
thread_cache_size = 50
wait_timeout = 28800

sort_buffer_size = 128K

query_cache_size = 32M
query_cache_limit = 2M

transaction-isolation = REPEATABLE_READ

innodb_buffer_pool_size = 10G

innodb_log_group_home_dir = /usr/local/mysql/data
innodb_log_buffer_size = 16M
innodb_log_file_size = 1024M
innodb_log_files_in_group = 2

innodb_lock_wait_timeout = 60
innodb_flush_log_at_trx_commit = 1

general_log = 0
general_log_file = /usr/local/mysql/logs/general_query.log
slow-query-log = 1
long_query_time = 1
slow_query_log_file = /usr/local/mysql/logs/slow_query.log

log-bin = /usr/local/mysql/logs/binary_log
max_binlog_size = 512M
expire_logs_days = 14
binlog_cache_size = 128K

read_only
  • server-id: MySQL 이 내부적으로 자기 자신을 식별하는 아이디 값이다.
  • user: MySQL 이 설치된 서버의 운영체제 계정을 입력한다. MySQL 서버는 입력된 운영체제 계정으로 MySQL 인스턴스를 실행한다.
  • basedir: MySQL 서버의 홈 디렉토리를 명시한다.
  • datadir: MyISAM 의 데이터 파일이 저장되는 디렉토리다.
  • tmpdir: MySQL 서버는 정렬이나 그룹핑과 같은 처리를 위해 내부적으로 임시 테이블을 생성한다. tmpdir 은 내부 임시 테이블의 데이터 파일이 저장되는 위치이며, 쿼리가 종료되면 데이터는 자동으로 삭제된다. 사용자가 생성하는 "CREATE TEMPORARY TABLE" 의 임시 테이블과는 다르다.
  • character-set-server, collation-server: MySQL 서버의 기본 문자집합을 설정한다.
  • default-storage-engine: MySQL 서버 내에서 기본적으로 사용할 스토리지 엔진을 정의한다.
    • MySQL 서버가 내부적으로 생성하는 임시 테이블은 MyISAM 스토리지 엔진만 사용한다.
  • skip-name-resolve: 클라이언트의 접속 시 해당 클라이언트가 접속 허용된 사용자인지 확인하기 위해, 클라이언트의 IP 주소를 이용해 역으로 DNS 명을 가져오는 역 DNS 검색을 하지 않는 옵션이다.
    • 이 옵션은 설정 변수명 자체가 name-resolve 작업을 비활성화한다는 뜻이다.
    • 성능상의 이유로 역 DNS 검색은 비활성화해서 사용하는 것이 일반적이다.
  • event-scheduler: 일정 시간에 반복되는 작업을 목적으로 이벤트 스케줄러라는 기능을 사용할 수 있다.
    • 이벤트 스케줄러는 MySQL 내에서 실행되는 별도의 스레드를 필요로 한다.
  • max_connections: MySQL 서버가 최대한 허용할 수 있는 클라이언트의 연결 수를 제한하는 설정이다.
    • 이 설정을 몇 천 이상으로 너무 높게 설정하면 MySQL 서버가 응답 불능 상태에 빠질 위험이 있다.
      한 두개의 무거운 쿼리가 자원을 모두 써버리거나 일시적으로 많은 사용자가 몰려드는 상황이 발생한다면, WAS에서도 수많은 커넥션을 생성하게 되고 결국 MySQL 서버가 요청은 받지만 처리할 수 없는 상태에 놓이게 된다.
    • 먼저는 WAS 의 커넥션 풀에서도 적절한 개수의 설정이 중요하고, MySQL의 이 설정도 위와 같은 위험을 막는 최후의 보루임을 기억해야 한다.
  • thread_cache_size: MySQL 서버의 스레드 풀에 최대 몇 개까지의 스레드를 보관할지 결정하는 설정이
    • 보통 WAS 쪽에서 커넥션 풀로 커넥션을 관리하기 때문에, 대량의 커넥션 요청이 발생하지 않을 것이므로 이 값을 크게 설정할 필요는 없다.
  • wait_timeout: MySQL 서버에 연결된 클라이언트의 요청 타임아웃 설정이다.
  • sort_buffer_size: 일반적으로 DBMS 에서 가장 큰 부하를 일으키는 작업이 바로 정렬인데, 이 설정은 인덱스를 사용할 수 없는 정렬에 메모리 공간을 얼마나 할당할지 결정하는 설정값이다.
    • 이 크기가 작으면 디스크를 사용할 확률이 높아지고 커지면 각 스레드의 메모리 사용량이 높아져서 메모리 낭비가 심해진다.
    • 권장값은 64KB ~ 512KB 사이이다.
  • query_cache_size, query_cache_limit: 쿼리 캐시에 관련된 캐시 크기 설정값이다.
    • 128MB 이상은 설정하지 않는 것이 좋으며, 데이터가 변경되지 않고 읽기 전용으로만 사용되는 경우 조금씩 늘리면서 성능을 확인한다.
    • 데이터의 변경이 많은 경우 64MB 이상으로는 설정하지 않는 것이 좋다.
  • transaction-isolation: 트랜잭션의 격리 수준을 결정하는 설정값으로 기본값은 REPEATABLE_READ 이다.
    • 일반적으로는 REPEATABLE_READ 와 READ_COMMITTED 이다.
  • innodb_buffer_pool_size: InnoDB 스토리지 엔진에서 가장 중요한 옵션이다. 해당 엔진의 버퍼 풀은 디스크의 데이터를 메모리에 캐싱함과 동시에 데이터의 변경을 버퍼링하는 역할을 수행한다.
    • 일반적으로 OS나 MySQL 클라이언트에 대한 서버 스레드가 사용할 메모리를 제외하고 남는 거의 모든 메모리 공간을 설정한다.
  • innodb_log_group_home_dir: InnoDB와 같이 트랜잭션을 지원하는 RDBMS는 ACID 보장과 동시에 성능 향상을 목적으로 데이터의 변경 이력을 별도의 파일에 순차적으로 기록해 두는데, 이를 트랜잭션 로그 혹은 Redo 로그 라고 한다. 이 로그는 사람이 읽을 수 있는 로그는 아니고 서버의 갑작스런 종료 시 종료되지 않은 트랜잭션을 복구하기 위한 용도로 사용된다. 이 설정값은 해당 로그의 위치값이다.
  • innodb_log_buffer_size: 데이터 변경 시 해당 변경사항을 바로 리두 로그에 기록하면 디스크 I/O 가 빈번해져서 비효율적이다. 이를 위해 버퍼를 사용하는데, 해당 버퍼에 대한 크기 설정값이다.
    • 전역적으로 생성되며, 16~32 MB 정도면 충분하다.
  • innodb_log_file_size, innodb_log_files_in_group: InnoDB의 리두 로그 파일은 1개 이상의 파일로 구성되지만 각 파일을 순환 큐(Circular Queue)처럼 연결해서 사용한다. 이 설정들은 해당 파일 1개의 크기와 몇 개의 파일을 사용할지 결정하는 설정이다.
    • 이 값이 너무 작으면 InnoDB의 버퍼 풀로 설정된 메모리 공간이 아무리 커도 제대로 활용할 수 없어서 InnoDB 전체를 비효율적으로 작동하게 만들 수도 있으니 주의해야 하는 중요한 설정이다.
  • innodb_lock_wait_timeout: InnoDB에서 잠금 획득을 위해 최대 대기할 수 있는 시간을 설정한다.
  • innodb_flush_log_at_trx_commit: InnoDB에서 트랜잭션이 커밋될 때마다 리두 로그를 디스크에 플러시할지 결정하는 옵션이다.
  • general_log, general_log_file: MySQL에는 실행되는 모든 쿼리를 로그 파일로 기록하는 기능이 있는데, 이 로그를 쿼리 로그 혹인 제너럴(General) 로그라고 한다. 쿼리 수행이 많다면 쿼리 로그는 사용하지 않는 편이 좋다.
  • slow-query-log, long_query_time, slow_query_log_file: 지정된 시간 이상으로 수행된 쿼리를 별도의 로그 파일로 남기는데 이를 슬로우 쿼리 로그라고 한다. 어떤 쿼리를 가장 먼저 튜닝해야 할지를 알려주는 중요한 지표이므로 반드시 활성화하고 활용하자.
  • log-bin, max_binlog_size, expire_logs_days: MySQL 에서 복제를 구축하려면 마스터 서버에서 기록하는 바이너리 로그가 필요하다. 슬레이브 서버는 마스터의 바이너리 로그를 가져와서 재실행하는 것으로 마스터와 슬레이브 간의 데이터를 동기화한다.
    • log-bin 은 바이너리 로그 파일의 prefix, max_binlog_size 는 최대 파일의 크기를 제한하고, expire_logs_days 는 최대 로그 보관 일 수이다.
  • binlog_cache_size: 바이너리 로그도 마찬가지로 즉시 디스크에 기록하는 것이 아니라 버퍼링하면서 저장한다. 해당 버퍼의 크기를 결정한다.
  • read_only: 보통의 슬레이브 서버를 읽기 전용으로 만들기 위해 해당 옵션을 사용한다.
    • 글로벌 동적 변수라서 필요한 경우 SET 명령으로 바로 읽기 전용을 해제할 수도 있다.
profile
차_현
이전 포스트

[Real MySQL 8.0] 2. 설치와 설정 (1부)

다음 포스트

[Real MySQL 8.0] 3. 사용자 및 권한 (1부)

0개의 댓글

관련 채용 정보