[MySQL] MySQL 서버 설정과 시스템 변수

서버 설정

MySQL 서버는 일반적으로 단 하나의 설정 파일을 사용하고 파일명은 아래와 같다

- 유닉스 계열 : my.cnf

- 윈도우 계열 : my.ini

 

MySQL 서버는 지정된 여러 개의 디렉토리를 순차적으로 탐색하면서 처음 발견된 my.cnf 파일을 사용하는데,

순차적으로 탐색하는 디렉터리 경로는 아래 명령어들로 확인해볼 수 있다.

mysqld --verbose --help
mysql --help

 

출력내용 중 Default options are read from the ... ~  

• /etc/my.cnf
• /etc/mysql/my.cnf
• /usr/etc/my.cnf
• ~/.my.cnf

MySQL 서버는 위에서부터 디렉토리를 순차적으로 탐색한다.

 

** 너무 출력 내용이 많기 때문에 grep으로 필요한 내용만 볼 수도 있음

mysql --help | grep 'my.cnf'

 

서버 파일 위치 확인

find / -name my.cnf 2>/dev/null

 

1. 설정 파일의 구성

 

MySQL 설정 파일은 여러 개의 설정 그룹을 담을 수 있으며, 대체로 실행 프로그램 이름을 그룹명으로 사용한다.

 

mysqld 프로그램은 여기서 대괄호로 묶인 [mysqld] 영역을 참조한다.

 

다른 사람이 사용하는 my.cnf 예시 (출처 : https://gist.github.com/oinume/fc9b72bd8b14ab07e94c)

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
skip-character-set-client-handshake
default-storage-engine = InnoDB
character-set-server = utf8
transaction-isolation = READ-COMMITTED

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 127.0.0.1
#
# * Fine Tuning
#
key_buffer		      = 16M
max_allowed_packet	= 16M
thread_stack		    = 192K
thread_cache_size   = 16
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 300
table_open_cache       = 64
thread_concurrency     = 10
table_open_cache       = 32
thread_concurrency     =  4

#
# * Query Cache Configuration
#
query_cache_type   = 1
query_cache_limit	= 1M
query_cache_size   = 8M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
slow_query_log       = 1
slow_query_log_file	= /var/log/mysql/mysql-slow.log
long_query_time      = 1
#log-queries-not-using-indexes

#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
#log_bin			= /var/log/mysql/mysql-bin.log
expire_logs_days	= 14
max_binlog_size         = 1G
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_per_table = 1
skip-innodb_doublewrite
innodb_additional_mem_pool_size = 12M
innodb_buffer_pool_size         = 256M
innodb_log_buffer_size          = 8M
innodb_log_file_size            = 8M
innodb_flush_log_at_trx_commit  = 0
innodb_flush_method             = O_DIRECT
innodb_support_xa               = OFF


[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition

[isamchk]
key_buffer		= 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

 

** 설정 파일의 각 그룹은 같은 파일을 공유하지만 서로 무관하게 적용된다

 

2. MySQL 시스템 변수의 특징

시스템 변수(System Variables) :

MySQL 서버가 가동하면서 설정 파일의 내용을 읽어 저장하는 여러가지 설정 값

SHOW GLOBAL VARIABLES 또는 SHOW VARIABLES 명령어로 확인할 수 있음

 

이런식으로 출력된다

 

시스템 변수(설정) 값이 어떻게 MySQL 서버와 클라이언트에 영향을 미치는지 알기 위해선 각 변수가 글로벌 변수인지 세션 변수인지를 구분할 수 있어야 한다.

 

2-1. 글로벌 변수와 세션 변수

MySQL 의 시스템 변수는 적용 범위에 따라 글로벌 / 세션 변수로 나뉜다.

 

글로벌 변수

- 하나의 MySQL 서버 인스턴스에서 전체적으로 영향을 미치는 시스템 변수

- 서버에 하나만 존재하는 InnoDB 버퍼 풀 크기(innodb_buffer_pool_size), 쿼리 캐시의 크기(query_cache_size) 등과 같이

MySQL 서버 자체에 관련된 설정이 대부분

 

세션 변수

- MySQL 클라이언트가 서버에 접속할 때 기본적으로 부여하는 옵션의 기본값을 제어하는 데 사용

- 클라이언트 필요에 따라 개별 커넥션 단위로 다른 값으로 변경할 수 있는 변수

ex)

SET SESSION sort_buffer_size = 4194304;
# 현재 클라이언트 세션에서만 sort_buffer_size 값을 4MB로 변경

 

재접속하면 아래 이미지와 같이 기본값을 가짐

 

 

세션 범위의 시스템 변수 가운데 my.cnf 에 명시해 초기화할 수 있는 변수는 대부분 범위(Var_Scope)가 "Both" 라고 명시되어 있음

순수하게 세션 범위인 변수는 설정 파일에 초기 값을 명시할 수 없다. → MySQL 서버의 기본값을 사용

 

2-2. 정적 변수와 동적 변수

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

 

정적 변수 (Static Variables)

서버를 재시작해야 적용되는 변수

값을 변경하려면 MySQL 서버를 중지하고 설정 파일(my.cnf 또는 my.ini)에서 해당 변수를 수정한 후 서버를 재시작해야 함

 

동적 변수 (Dynamic Variables)

서버가 실행 중일 때도 값을 변경할 수 있는 변수

SET GLOBAL 또는 SET SESSION 명령을 통해 변경 가능하며, 서버를 재시작하지 않고도 설정이 즉시 적용

→ my.cnf 파일까지는 업데이트 되지 X, 변경된 값을 유지하려면 설정 파일도 변경해주어야 함 (8.0부터 SET PERSIST 명령어로 변경 유지 가능 my.cnf 대신 별도의 mysqld-auto.cnf 파일에 기록) 

SET PERSIST 로 실행중인 서버의 시스템 변수 변경 + 동시에 자동으로 설정파일로 기록

정적변수에 SET PERSIST 명령어로 값을 변경하려 할 경우 오류가 발생함 > 설정파일을 수정해야 함

 

설정 범위가 "Both" 로 글로벌 변수이면서 세션 변수인 경우 글로벌 변수의 값을 변경하더라도 이미 존재하는 커넥션의 세션 변수 값은 변경되지 않고 유지

 

: ex) Var_Scope(범위) = 'both'인 동적 변수 'join_buffer_size'

 

◆ 각각의 글로벌 / 현재 커넥션의 세션 변수값 확인

 

◆ 글로벌 변수값 변경 및 확인

 

◆ 세션 변수값 확인

 

글로벌 변수의 값은 '524288' 로 변경되었지만 현재 커넥션의 세션 변수는 예전 값을 유지하고 있음을 확인

3. SET PERSIST

동적 변수의 경우 값을 변경하고 서버를 재기동 하는 경우 my.cnf 파일을 다시 읽어 오기때문에 기존 설정값으로 가져 오게됨

(앞서 언급했듯, my.cnf 파일까지는 업데이트 되지 X)

장애 등이 발생하거나 긴급하게 조치를 하면서 동적 변수를 설정하고 my.cnf 를 설정하지 않으면 다음 재기동했을 때, 문제가 다시 생길 가능성이 있음

→ 이에 MySQL 8.0 버에서 PERSIST 명령이 도입됨

 

SET PERSIST : 해당 명령으로 동적 변수 변경시 시스템 변수가 바로 변경되고, 변도의 설정 파일 (mysqld-auto.cnf)에 기록되어 서버가 다시 시작될 때 해당 파일을 참조해서 읽어 오게 됨

 

- 현재 max_connections

 

- SET PERSIST 명령으로 해당 값 변경

이미지와 같이 동적 변수가 적용됨

 

mysqld-auto.cnf 파일은 /var/lib/mysql/ 폴더(MySQL 데이터베이스 서버의 기본 데이터 디렉토리, 데이터베이스 파일, 설정 파일, 로그 파일 등이 이 디렉토리에 저장)에 생성

 

* mysqld-auto.cnf 파일은 바로 위 사진 처럼 JSON 포맷으로 생성되어 시스템 변수 이름, 설정값, 언제 누구에 의해 변경되었는지 의 정보 등이 함께 기록

 

SET PERSIST_ONLY :

- 실행중인 서버에는 변경 내용을 적용하지 않고 다음 재기동시만 적용하고 싶은 경우 mysqld-auto.cnf 파일에만 변경 내용을 기록

- 정적 변수 또한 mysqld-auto.cnf에 기록하고 싶은 경우 활용 가능

 

RESET PERSIST : 위의 명령어들로 추가된 시스템 변수 내용을 삭제해야 하는 경우

- RESET PERSIST '변수' , RESET PERSIST IF EXISTS '변수' : 특정 시스템 변수만 삭제

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

 

4. my.cnf 설정파일 옵션

• 옵션 종류
  • port : 접속 시 사용한 port 설정
  • basedir : base directory 경로 설정
  • datadir : data directory 경로 설정
  • socket : 사용할 소켓 파일 경로 설정
  • log-error : 에러로그 파일 경로 설정
  • pid-file : 프로세스 ID 파일 경로 설정
  • user : 접속 유저명
  • join_buffer_size : join 시 테이블을 풀스캔 하기위해 사용하는 버퍼크기
  • sort_buffer_size : 정렬에 필요한 버퍼의 크기
    • ORDER BY 또는 GROUP BY 연산 속도와 관련됨
  • read_buffer_size : 테이블 스캔에 필요한 버퍼크기
  • read_rnd_buffer_size : 디스크 검색을 피하기위한 랜덤 읽기 버퍼크기
  • key_buffer_size : 인덱스 블록에서 사용되는 버퍼의 크기, 모든 thread에서 공유하는 버퍼 크기
  • thread_concurrency : thread 최대 개수
  • max_allowed_packet : 클라이언트 통신에 대해 사용되는 버퍼가 가질수 있는 최대 크기
  • table_cache : MySQL 서버가 한번에 열수 있는 테이블의 개수
  • query_cache_size : 쿼리 캐시 버퍼 사이즈
  • max_connections : 동시에 접근 가능한 커넥션 수 설정
  • max_connect_errors : 발생할 수 있는 최대 에러 개수
  • wait_timeout : 커넥션 최대 대기시간(초)
  • transaction-isolation : isolation 레벨 지정
    • READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE
• InnoDB 관련 옵션
  • innodb_buffer_pool_size : 데이터와 인덱스를 캐시하기 위해 사용하는 메모리 버퍼크기
    • 전용 서버의 경우 전체 RAM의 70% 이상, 그 외는 10% 이상으로 지정
  • innodb_log_buffer_size : Redo 로그 버퍼크기
  • innodb_log_file_size : 로그 파일 사이즈. 버퍼풀 사이즈의 25% 정도로 설정
    • 로그 버퍼 사이즈로 성능에 맞춰 로그를 기록하는 경우 크게 설정
  • innodb_flush_log_at_trx_commit : 커밋 로그 옵션
    • 1로 설정 시 트랜잭션 실행마다 로그 파일에 기록되고 디스크 플러시가 실행
    • 2로 설정 시 성능 최적화를 위해 1분마다 저장
  • innodb_support_xa : 트렌잭션 two-phase commit 지원
    • 디스크 플러시 횟수를 줄여 성능항상
  • innodb_thread_concurrency : InooDB내에 쓰레드 개수
    • 0으로 설정 시 thread간 동시성 비활성화
  • innodb_lock_wait_timeout : 롤백이 진행되기 전에 LOCK을 대기하는 시간(초)
  • innodb_force_recovery : crash 복구 모드 설정
• 참고 : https://velog.io/@inhwa1025/MySQL-%EC%84%A4%EC%A0%95-%ED%8C%8C%EC%9D%BC-my.cnf-%EC%98%B5%EC%85%98-%EC%A2%85%EB%A5%98