[MySQL] 서버 Parameter 설정

■ MySQL 서버 파라미터 특징

 MySQL서버 환경을 설정할 때 몇가지 특징이 있습니다. 이 특징들에 대해 알아보고 파라미터를 어떻게 설정하고 적용하는지 알아봅니다.

 

■ 서버 파라미터의 Dynamic적용 여부

MySQL 파라미터는 바로 적용할 수 있는 파라미터와 서버를 재시작해야 적용되는 파라미터가 있습니다.

이 파라미터는 다음 페이지에서 확인 가능합니다. 한글로 번역해 놓았습니다. 보기가 편리하시리라 생각합니다.

Dynamic 항목에서 Dynnamic이 YES면 바로 적용이 가능하며 NO이면 서버가 재시작 되어야 적용이 됩니다.

MySQL 파라미터 한글번역 : https://github.com/myacelink/MySQL

 

■ my.cnf 기본 위치

우분투나 CentOS등의 리눅스 운영체제에서 데이터베이스를 사용하기 위해 MySQL이나 MariaDB를 설치해 사용하는 경우 시스템 환경과 DB 활용 스타일에 맞게 my.cnf 설정 파일을 이용해 최적화 작업을 설정해야 합니다.

MySQL서버를 설치할때 환경파일 위치를 기본 위치(/etc/my.cnf)이외에 다른 디렉토리서 읽어들이도록 설정했다면 패키지를 설치한 사람 이외에는 알기가 어렵습니다.

이럴때 다음 명령어로 MySQL, MariaDB에서 불러들이는 my.cnf 파일이 어떤 것인지 확인할 수 있습니다. 또한 Show Variables 명령을 통해 각종 위치를 확인할 수 있습니다.

 

▶︎ my.cnf 위치 확인하기

shell > mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 

 

윈도우 역시 리눅스와 같은 요령으로 폴더를 찾아볼 수 있는데 grep 명령어를 사용할 수는 없으므로 mysql 콘솔에 접속한 다음 위 명령어를 통해 my.ini 위치를 찾아볼 수 있습니다.

C:\Program Files\MySQL\MySQL Server 5.7\my.ini
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

윈도우 버전 같은 경우 my.cnf 파일 대신 my.ini 파일이 그 역할을 대신하고 있으며 MariaDB와 MySQL의 디렉토리가 약간씩 다르고 버전마다 파일 위치가 상이한데 기본적으로 MySQL이 설치된 루트폴더에 위치하고 있습니다.

 

• 디렉토리 호출 순서 정리

+ Linux

File Name	Purpose
/etc/mysql/my.cnf	Global options
/etc/my.cnf	Global options
SYSCONFDIR/my.cnf	Global options
$MYSQL_HOME/my.cnf	Server-specific options (server only)
defaults-extra-file	The file specified with --defaults-extra-file, if any
~/.my.cnf User-specific	options
~/.mylogin.cnf User-specific	login path options (clients only)

 

 

 

+ Windows

File Name	Purpose
%WINDIR%\my.ini, %WINDIR%\my.cnf	Global options
C:\my.ini, C:\my.cnf	Global options
BASEDIR\my.ini, BASEDIR\my.cnf	Global options
defaults-extra-file	The file specified with --defaults-extra-file, if any
%APPDATA%\MySQL\.mylogin.cnf	Login path options (clients only)

 

 

▶︎ 각종 디렉토리 확인.

MySQL 클라이언트 툴에서 다음과 같은 명령어를 실행합니다.

mysql> SHOW VARIABLES WHERE Variable_Name LIKE "%dir";
+---------------------------+----------------------------------+
| Variable_name             | Value                            |
+---------------------------+----------------------------------+
| basedir                   | /usr/local/mysql/                |
| character_sets_dir        | /usr/local/mysql/share/charsets/ |
| datadir                   | /usr/local/mysql/data/           |
| innodb_data_home_dir      |                                  |
| innodb_log_group_home_dir | ./                               |
| innodb_tmpdir             |                                  |
| lc_messages_dir           | /usr/local/mysql/share/          |
| plugin_dir                | /usr/local/mysql/lib/plugin/     |
| slave_load_tmpdir         | /tmp                             |
| tmpdir                    | /tmp                             |
+---------------------------+----------------------------------+
10 rows in set (0.00 sec)

 

■ 파라미터 설정방법

▶︎ my.cnf의 섹션 정의

my.cnf에 보면 섹션들이 있습니다.

[client], [mysqld], [myisamchk]....

위의 것들은 각각의 데몬 혹은 프로그램들이 이용될때 쓰이는 것입니다.

예를 들어 [client]는 클라이언트가 접속이 되었을때, [mysqld]는 mysql server가 구동중일때, myisamchk는 myisamchk 프로그램이 수행될때 참조되는 환경 파라미터들입니다. mysqld는 [mysqld] 와 [server] 그룹에서 옵션을 읽습니다. mysqld_safe 는 [mysqld], [server], [mysqld_safe], 및 [safe_mysqld] 그룹에서 옵션을 읽습니다. mysql.server는 [mysqld] 와 [mysql.server] 그룹에서 옵션을 읽습니다.

▶︎ MySQL 기본 파라미터

기본적으로 알아야 하는 Variable은 다음과 같습니다. 가장 기본적인 설정으로 거의 필수 옵션이라 할 수 있습니다.

Variable	설명
autocommit	autocommit 모드 설정, 1인 경우 autocommit설정, 0인 경우 disable
binlog_cache_size	트랜잭션 실행동안 바이너리 로그를 저장하기 위한 캐쉬 사이즈
binlog-format	바이너리 로그의 format을 설정함.
datadir	data directory 설정
key_buffer_size	key_buffer_size 설정
log_bin	바이너리 로그를 생성할지의 여부를 설정함.
log-slow-queries	long_query_time보다 오래 실행되는 쿼리를 로그로 남기는 작업을 설정함.
long_query_time	slow query 기준이 되는 값(초)
max_allowed_packet	가장 크게 생성될 수 있는 packet사이즈를 설정함.
max_binlog_size	바이너리 로그 파일의 최대 크기 설정값
max_connections	동시에 접근 가능한 커넥션 갯수 설정
open-files-limit	최대 오픈 파일 수 설정
port	접속시 사용할 port 설정
read_buffer_size	읽기 버퍼 크기 설정
read_only	데이터를 수정할 수 없게 설정함.
server-id	서버의 유니크한 id 설정
slow-query-log	log-slow-queries와 같음
slow_query_log_files	slow-query-log 파일 이름을 설정함.
socket	사용할 소켓 파일 설정
sort_buffer_size	정렬 버퍼 설정
storage_engine	기본적으로 사용할 스토리지 엔진 값을 설정
tmpdir	temporary table 생성하는 디렉토리 설정
wait_timeout	대기 시간 초과시 접속 종료 설정

 

▶︎ 파라미터 적용방법

MySQL 서버는 위에서 설명한대로 my.cnf라는 파라미터 파일을 통해 서버 환경을 구성하거나 지금 설명하는 mysqld_safe라는 프로그램을 실행시킬때 서버 바라미터를 옵션형식으로 추가하여 실행하는방법이 있습니다. 서버 실행시 시작할 수 있는 파라미터는 보통 대쉬(-) 2개로 표현을 합니다.

shell > mysqld_safe --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

 

▶︎ 현제 적용 파라미터 확인 방법

MySQL명령에서 쿼리의 where절 형식으로 조회를 할 수 있습니다.

mysql> show variables like '%connection%';