Real MySQL 8.0 읽으면서 정리하기 (1) - 시스템 설정

0. 이 글의 목적

Real MySQL 8.0을 읽으면서 내가 정리하고 싶은 부분에 대해 작성하기 위함

 

 

1. 환경

container : docker

db : mariadb 10 (아무튼 mariadb는 mysql 서비스를 fork 해서 만들었기 때문에 이걸로 진행)

 

2. 서버 설정

MySQL 서버는 단 하나의 설정 파일을 사용한다.

확장자는 운영체제의 환경에 따라 다르나 보통 리눅스 환경에서 사용한다고 가정하고 cnf 파일로 진행한다.

리눅스 : my.cnf

윈도우즈 : my.ini

 

단 하나의 설정 파일을 사용한다고 했는데 이는 공통적으로 my.cnf라는 이름을 사용한다는 것이다.

문제는 이 파일이 여러 경로에 존재할 수 있는데 어느 디렉토리에 있는 my.cnf를 참조하고 있는지 아래 명령어로 확인이 가능하다.

 

mysqld --verbose --help

이 명령어는 내가 어떤 설정을 하고 있는지 별 걸 다 보여준다.

그래서 grep으로 필요한 내용만 보는 게 편하다.

mysqld --verbose --help | grep my.cnf

보면 여러 경로가 존재한다.(버전에 따라 차이가 있을 수 있다.)

1. /etc/my.cnf
2. /etc/mysql/my.cnf
3. ~/.my.cnf

mysql에서는 위에서부터 차례대로 my.cnf파일을 찾기 시작한다.

 

그렇다면 설정 파일 내부는 어떻게 되어있을까?

3. my.cnf

보면 대괄호로 묶인 영역이 있다.

[client-server] 가 바로 설정 그룹이다. 이 그룹의 역할은 단순히 서버 실행만을 위한 것이 아닌 다양한 MySQL의 서비스가 동작하는 데 있어 사용되는 설정값이다.

오픈 소스 중 다른 사람이 사용한 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/

 

이렇게 설정된 값은 서비스가 실행되며 값이 읽어진 다음 시스템 변수에 저장된다.

 

시스템 변수는 아래 명령어로 확인이 가능하다.

 

show global variables;
## 또는
show variables;

시스템 변수는 적용 범위에 따라 글로벌 변수와 세션 변수로 분류된다.

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

이 두 속성 둘 다 가지고 있는 변수가 존재한다. both 로 표현되는데 글로벌 시스템 변수의 값을 변경해도 현재 커넥션의 변수 값은 변경되지 않고 유지된다.

 

 

SET 명령어를 통해 현재 서버의 설정 값을 변경할 수 있지만 실제 설정 파일(my.cnf)을 변경한 것이 아니므로 재기동 시 초기화된다.

SET GLOBAL max_connections=5000;

다만 MySQL8.0부터는 SET PERSIST를 사용해서 설정 파일에도 변경이 가능해졌다. 이 경우 my.cnf파일이 아닌 mysqld-auto.cnf라는 파일을 새로 만들어 설정 값을 저장한다.

SET PERSIST GLOBAL max_connections=5000;

 

문제는 모든 변수가 기동 중 적용이 가능한 것이 아니라는 점이다. 정적인 변수의 값은 재기동시 적용이 가능하며 SET PERSIST 명령어 수행 시 에러가 발생한다. 이 경우 SET PERSIST_ONLY 명령어를 통해 재 기동시 적용하도록 할 수 있다.

 

여기서 하나 특이한 점은 SET PERSIST, SET PERSIST_ONLY 명령어를 통해 생성된 .cnf파일은 json 형식으로 되어있다.

이 설정파일에는 누가 언제 변경했는지에 대한 메타데이터 또한 존재한다.

{ "Version" : 1 ,
   "mysql_server" : {
     "max_connections" : {
       "Value" : "5000" ,
       "Metadata" : { "Timestamp" : 1603531428710224 ,
                      "User" : "matt.lee" , "Host" : "localhost" }
},
"mysql_server_static_options" : {
       "innodb_doublewrite" : {
         "Value" : "ON" ,
         "Metadata" : { "Timestamp" : 1603531680005055 ,
 } }
} }
"User" : "matt.lee" , "Host" : "localhost" }

 

이렇게 변경된 값들을 초기화하기 위해 mysqld-auto.cnf파일을 직접 삭제하는 경우 서비스가 꼬일 수 있고 최악의 경우 mysql 서버가 기동이 안될 수 있다. 그래서 안전하게 진행하기 위해 RESET PERSIST 명령어를 사용한다.

 

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

 

4. 여기서는

my.cnf 가 어떤 역할을 하는지 간략하게 알아보았다.

사람이 싫어하는 걸 해야 성장한다는데 나에게는 DB가 그런 영역 중 하나이다. 참고 잘 공부해야겠다.