MySQL 설정 관련

개발자와 DBA를 위한 Real MySQL 책을 보고 정리한 내용이다.

MySQL 설정 파일

MySQL 서버는 단 하나의 설정 파일(my.cnf)만 사용한다.

설정 파일의 경로가 하나로 고정돼어 있지않고 MySQL 서버에 지정된 여러 개의 디렉토리를 순차적으로 탐색하면서 처음 발견된 my.cnf 파일을 시작될 때만 참조해 사용하게 된다.

순서는 다음과 같다.

1. /etc/my.cnf
2. /etc/mysql/my.cnf
3. /usr/local/mysql/etc/my.cnf
4. ~/.my.cnf

1, 2, 4 번 파일 경로는 어느 MySQL에서나 동일하게 검색하는 경로이며, 3 번 파일은 컴파일될 때 MySQL 프로그램에 내장된 경로이다.

MySQL 서버용 설정 파일 1, 3

하나의 장비( 서버 머신)에 2개 이상의 MySQL 서버(인스턴스)를 실행하는 경우에는 1, 2번이 충돌이 발생할 수 있어 3번 파일을 사용

직접 MySQL을 컴파일해서 설치한 경우에는 my.cnf 파일 경로가 다르게 설정될 수도 있다.

다음 명령어로 확인 가능하다.

$ mysqld --verbose --help

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


$ mysql --help

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

mysqld 명령어는 MySQL 서버 실행 명령어로, 실제 MySQL 서버를 기동할 수 있어 --verbose, --help를 넣어주어야 한다.

설정 파일 구성

/usr/local/etc/my.cnf

# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1

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

위 설정파일에는 나와 있지 않지만 socket, port 같은 설정은 모든 프로그램에 공통적으로 필요한 설정값이므로 각 그룹에 대해 여러번 호출될 수 있다. 당연한 얘기지만 각 그룹에 대한 socket과 port는 다를 것이다.

MySQL 시스템 변수의 특징

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

mysql> SHOW VARIABLES
mysql> SHOW GLOBAL VARIABLES

...
| version_comment                                          | Homebrew
| version_compile_machine                                  | x86_64
| version_compile_os                                       | macos11.3
| version_compile_zlib                                     | 1.2.11
| wait_timeout                                             | 28800
| windowing_use_high_precision                             | ON
...

시스템 변수는 글로벌과 세션으로 구분되어 지며, 시스템 변수가 어떻게 서버, 클라이언트에 영향을 미치는지 판단하려면 각 변수가 글로벌 변수인지, 세션 변수인지 구분할 수 있어야 한다.

시스템 변수 에서 확인 할수 있으며, Scope에 Gobal 또는 Session으로 표기되어 있다.

각 시스템 변수마다 다음과 같이 표기 되어 있으며 의미는 다음과 같다.

Command-Line Format	MySQL 서버의 명령행 인자로 설정될 수 있는지 여부를 나타냄
System Variable	시스템 변수인지 아닌지 여부를 나타낸다.
Scope	시스템 변수 적용 범위 (Global, Session)
Dynamic	시스템 변수가 동적인지 정적인지 구분(Yes, No)
SET_VAR Hint Applies	힌드 지원 여부(Yes, No)
Type	Enumeration
Default Value	ON
Valid Values	ON``OFF``DISABLED

개인적으로 각 글로벌 변수에 대한 특징을 외우지 않아도 필요할 때마다 들어가서 확인하면 자동적으로 외워질 것이라고 생각한다.

글로벌 변수와 세션 변수

• 글로벌 변수 - 하나의 MySQL 서버 인스턴스에서 전체적으로 영향을 미치는 시스템 변수를 의미하고, 주로 MySQL 서버 자체에 관련된 설정일 때가 많다. 대표적으로 쿼리 캐시의 크기, MyISAM의 키 캐시 크기, InnoDB의 InnoDB 버퍼 풀의 크기등이 가장 대표적인 글로벌 영역의 시스템 변수이다.
• 세션 변수 - MySQL 클라이언트가 MySQL 서버에 접속할 때 기본적으로 부여하는 옵션의 기본값을 제어하는 데 사용된다. 다른 DBMS에서도 거의 비슷하겠지만 MySQL에서도 각 클라이언트가 처음에 접속하면 기본적으로 부여한느 디폴트 값을 가지고 있다. 클라이언트가 별도로 그 값을 변경하지 않은 경우에는 그대로 값이 유지되지만 클라이언트의 필요에 따라 개별 커넥션 단위로 다른 값으로 변경할 수 있는 것이 세션 변수이다. 대표적으로는 Autocommit이 있다. 이 Autocommit은 DBMS(Oracle, MSSQL, PostgreSQL 등), DDL, DML에 따라 디폴트가 on일수도 off 일수도 있다.
• 설정 파일(my.cnf)에 명시해 초기화할 수 있는 변수는 대부분 범위가 Both (또는 Global, Session)로 명시돼어 있다. 이렇게 명시된 세션 변수는 MySQL 서버가 기억하고 있다가 실제 클라이언트와의 커넥션이 생성되는 순간 해당 커넥션의 기본값으로 사용되는 값이다.

동적 변수와 정적 변수

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

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

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

특정 시스템 변수를 찾고자 할때는 '%'를 사용해 검색하는 것도 가능하다.

mysql> SHOW GLOBAL variables;

mysql> SHOW GLOBAL variables like '%connection%'

my.cnf 설정 파일

[mysqld] 설정 그룹

책에서는 InnoDB 플러그인을 사용하는 MySQL 5.1 버전을 기준으로 작성

/usr/local/mysql                    ## MySQL 홈 디렉토리
        \ bin                                    ## MySQL 서버와 클라이언트 및 유틸리티가 저장된 디렉토리
        \ data                                ## MySQL 서버의 데이터 파일 (MyISAM 및 InnoDB의 모든 데이터 파일)
        \ logs                                ## 바이너리 로그와 릴레이 로그를 포함한 각종 로그 디렉토리
        \ tmp                                    ## MySQL의 내부 임시 테이블이나 소켓 파일이 저장되는 디렉토리
        \ ...

my.cnf

## -----------------------------------------------------------------------------------------
## [SERVER] MySQL Server Configuration
## -----------------------------------------------------------------------------------------

[mysqld]
## MySQL 서버 기본 옵션 -----------------------------------------------------------------------
server-id                            = 1

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

chracter-set-server                    = utf8
collation-server                    = utf8_general_ci
default-storage-engine                 = InnoDB
skip-name-resolve
skip-external-locking

## MySQL 스케줄러를 사용하려면 아래 event-scheduler 옵션을 ON으로 변경
event-scheduler                     = OFF
sysdate-is-now

back_log                            = 100
max_connections                        = 300
max_connect_errors                    = 999999
thread_cache_size                    = 50
table_open_cache                    = 400
wait_timeout                        = 28800

max_allowed_packet                    = 32M
max_heap_table_size                    = 32M
tmp_table_size                        = 512K

sort_buffer_size                    = 128K
join_buffer_size                    = 128K
read_buffer_size                    = 128K
read_rnd_buffer_size                = 128K

query_cache_size                    = 32M
query_cache_limit                    = 2M

group_concat_max_len                = 1024

## 마스터 MYSQL 서버에서 '레코드 기반 복제'를 사용할 때는 READ-COMMITED 사용 가능
## 복제에 참여하지 않는 MySQL 서버에서는 READ-COMMITTED 사용 가능
## 그 외에는 반드시 REPEATABLE-READ로 사용
transaction-isolation                  = REPEATABLE-READ

## InnoDB 플러그인 옵션 -----------------------------------------------------------------------
innodb_use_sys_malloc                = 1
innodb_stats_on_metadata            = 1
innodb_stats_sample_pages            = 8
innodb_max_dirty_pages_pct            = 90
innodb_adaptive_hash_index             = 1
innodb_file_format                    = barracuda
innodb_strict_mode                    = 0
innodb_io_capacity                    = 600
innodb_write_io_threads                = 4
innodb_read_io_threads                = 4
innodb_autoinc_lock_mode             = 1
innodb_adaptive_flushing            = 1
innodb_change_buffering                = inserts
innodb_old_blocks_time                = 500
ignore_builtin_innodb

## 아래의 plugin-load 설정(innodb 플러그인 라이브러리 목록까지 포함)은
## 반드시 한 줄에 모두 작성해야 함
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;...

## InnoDB 기본 옵션 ------------------------------------------------------------------------
## InnoDB를 사용하지 않는다면 innodb_buffer_pool_size를 최소화하거나
## InnoDB 스토리지 엔진을 기동하지 않도록 설정해야함
innodb_buffer_ pool_size            = 10G
innodb_additional_mem_pool_size        = 15M
innodb_file_per_table                = 1
innodb_data_home_dir                = /usr/local/mysql/data
innodb_data_file_path                = ib_system:100M:autoextend
innodb_autoextend_increment            = 100
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_support_xa                    = OFF
innodb_thread_concurrency            = 0
innodb_lock_wait_timeout            = 60
innodb_flush_log_at_trx_commit        = 1
innodb_force_recovery                = 0
innodb_flush_method                    = O_DIRECT
innodb_doublewrite                    = 1
innodb_sync_spin_loops                = 20
innodb_table_locks                    = 1
innodb_thread_sleep_delay             = 1000
innodb_max_purge_lag                = 0
innodb_commit_concurrency            = 0
innodb_concurrency_tickets            = 500

## MyISAM 옵션 ------------------------------------------------------------------------------
## InnoDB를 사용하지 않고 MyISAM만 사용한다면 key_buffer_size를 4GB까지 설정
key_buffer_size                        = 32M
bulk_insert_buffer_size                = 32M
myisam_sort_buffer_size                = 1M
myisam_max_sort_file_size            = 2G
myisam_repair_threads                = 1
myisam_recover
ft_min_word_len                        = 3

## 로깅 옵션 ---------------------------------------------------------------------------------
pid-file                            = /usr/local/mysql/logs/mysqld.pid
log-warnings                        = 1
log-error                            = /usr/local/mysql/logs/mysqld

## General 로그를 사용하려면 아래 설정은 그대로 유지하고
## MySQL 서버에 로그인한 후 "SET GLOBALgeneral_log=1" 명령으로 활성화
general_log                            = 0
general_log_file                    = /usr/local/mysql/logs/general_query.log

log_slow_admin_statements
slow-query-log                        = 1
long_query_time                        = 1
slow_query_log_file                    = /usr/local/mysql/logs/slow_query.log

## 복제 옵션 ---------------------------------------------------------------------------------
## 만약 현재 서버가 마스터 MySQL이라면 아래주석 해제
#log-bin                             = /usr/local/mysql/logs/binary_log
#binlog_cache_size                    = 128K
#max_binlog_size                    = 512M
#expire_logs_days                    = 14
#log-bin-trust-function-creators    = 1
#sync_binlog                        = 1

## 현재 서버가 슬레이브 MySQL 이라면 아래 주석을 해제
#relay-log                            = /usr/local/mysql/logs/relay_log
#relay_log_purge                    = TRUE
#read_only

## 현재 서버가 슬레이브이면서 마스터 MySQL인 경우
## 현재 MySQL 서버로 복제되는 쿼리를 바이너리 로그에 저장하려면 아래 주석을 해제
#log-slave-updates

[client] 설정 그룹

## -----------------------------------------------------------------------------------------
## [CLIENT] MySQL Common Client Configuration
## -----------------------------------------------------------------------------------------

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

[mysql] 설정 그룹

## -----------------------------------------------------------------------------------------
## [MYSQL CLIENT] MySQL Command Line Client Configuration
## -----------------------------------------------------------------------------------------

[mysql]
default-character-set             = utf8
no-auto-rehash
show-warnings
prompt=\u@\h:\d\_\R;\m:\\s>
pager="less -n -i -F -X -E"