Mysql 서버 설정

Mysql 서버는 단 1개의 설정 파일만 사용한다.

• unix계열: my.cnf
• window계열: my.ini

이 파일의 이름은 변경이 불가능하며, Mysql 서버는 시작할 때 이 설정 파일을 참조하여 처음 발견된 설정 파일로 구동된다.

my.cnf 파일의 탐색 순서

이 순서는 mysql을 직접 컴파일해서 설치한 경우에는 순서가 다를 수 있다. 만약 my.cnf 파일의 작동 순서가 궁금하다면 mysql --help 명령어의 Default option are read… 부분에서 확인 할 수 있다.

위 스크린샷에서는 아래와 같은 순서로 설정 파일을 읽어온다.

1
2
3

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

설정 파일의 구성

1
2
3
4
5
6

/mysql
ㄴ  persistent
    ㄴ  data ## Mysql 서버의 데이터 파일(InnoDB의 모든 데이터 파일) 
    ㄴ  logs ## 바이너리 로그와 릴레이 로그, slow-query 로그를 포함한 각종 로그 파일
    ㄴ  tmp ## Mysql의 임시 데이블이 저장되는 디렉토리
ㄴ  share ## Mysql의 소켓 파일이 저장되는 디렉토리

이번 예제에서는 위와 같은 디렉토리 구조로 my.cnf 설정 파일을 구성하였다. my.cnf 파일을 보면서 설정파일에 대한 설명을 이어나가도록 하겠다.

시스템 변수들은 Mysql 5.7 기준으로 설명하였으며, 더 많은 시스템 변수 reference는 여기 Mysql Reference 5.7에서 확인할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

[mysqld]
server_id = 1

slow_query_log
slow_query_log_file = /mysql/persistent/logs/slow_query.log
general_log_file = /mysql/persistent/logs/general.log
log_error = /mysql/persistent/logs/error.log

datadir = /mysql/persistent/data
tmpdir = /mysql/persistent/tmp

socket = /mysql/share/mysql.sock

character_set_server = utf8mb4
collation_server = utf8mb4_bin

max_connections = 2000 


[client]
socket = /mysql/share/mysql.sock

• server_id
  • Mysql이 내부적으로 자기 자신을 식별하는 아이디 값
  • 어떤 값인지는 중요하지 않고, 한 replica group내에서 유일한 값이기만 하면 된다.
• slow-query-log(글로벌 동적 변수), long_query_time, slow_query_log_file
  • mysql에서는 지정된 시간 이상으로 쿼리가 실행되는 경우 해당 쿼리를 별도의 로그로 남긴다.
  • 슬로우 쿼리를 통해 어떤 쿼리를 가장 먼저 튜닝해야 할지를 알려주는 중요한 지표로 사용한다.
  • slow-query-log: 슬로우 쿼리를 활성화,비활성화할지 정한다.
  • long_query_time: 설정된 시간 이상의 쿼리를 슬로우 쿼리로 판단하여 저장(default: 10s)
  • slow_query_log_file: 슬로우 쿼리로 저장되는 파일의 위치
• datadir
  • 데이터가 저장되는 경로
• tmpdir
  • Mysql은 정렬이나 그룹핑과 같은 처리를 위해 내부적으로 임시 테이블을 생성
  • 이 때 사용되는 임시 테이블이 저장되는 경로 지정
• character_set_server, collation-server
  • Mysql 서버의 기본 문자 집합을 설정한다.
  • Collation은 무엇인가?
    • Database의 문자 데이터(CHAR/VARCHAR,TEXT, 등…)에 대한 값 비교시 사용되는 방식을 의미한다. 대소문자 구분(Case Intensitive)을 위한 Collation은 collation 이름의 끝에 “CI”라는 문자가 붙는다.
• default-storage-engine
  • InnoDB가 디폴트 엔진으로 적용된다.(>Mysql 5.7)
  • 그 이전 버전에서는 MyISAM이 디폴트 스토리지 엔진이다.
• max_connections
  • mysql 서버가 최대한 허용할 수 있는 클라이언트의 연결 수
• wait_timeout
  • Mysql 서버에 연결된 클라이언트가 wait_timeout에 지정된 시간동안 아무런 요청 없이 대기하는 경우 Mysql 서버는 해당 커넥션을 강제로 종료한다. (default :28800(8시간))

  • 하지만 커넥션이 예상보다 짧은 시간안에 종료되는 이슈가 있다.

    ex) 방화벽에서는 30분마다 idle한 connection을 종료 => os에서 keep-alive 요청을 보냄 => 이미 방화벽에서 connection은 끊겨있음. => os에서 connection 종료 => mysql의 wait_timeout 시간이 됨 => 이미 os에서 connection이 종료되어 있음

  • 이런 경우 운영체제의 아래 keep alive 설정을 L4 장비보다 짧게 가져가는 방식으로 해결할 수 있다.

    1 2 3 4

    # os network keep alive 설정 (sysctl) net.ipv4.tcp_keepalive_time net.ipv4.tcp_keepalive_probes net.ipv4.tcp_keepalive_intvl

글로벌 변수와 세션 변수

시스템 변수는 적용 범위에 따라 글로벌 변수와 세션 변수로 나뉜다. 만약 글로벌 변수와 세션 변수 두 개 다 존재할 경우에는 Mysql 매뉴얼에 Both라고 표시된다.

• 글로벌 변수
  • 하나의 mysql 서버 인스턴스에서 전체적으로 영향을 미치는 시스템 변수
  • Mysql 서버 자체에 관련된 설정(query_cache_size, innodb_buffer_pool_size)
• 세션 변수
  • Mysql 클라이언트가 Mysql 서버에 접속할 때 기본적으로 부여하는 옵션의 기본값을 제어하는데 사용(autoCommit)
  • Both로 명시된 세션변수는 Mysql 서버가 기억하고 있다가, 클라이언트와 커넥션이 생성되는 순간 기본값으로 사용된다.

동적 변수와 정적 변수

• 동적 변수

  1 2	SET GLOBAL max_connections=1000; SET SESSION sql_log_bin = 'OFF';

  • 서버가 기동중인 상태에서 변경 가능한 변수
  • 메모리상에서 바뀌기 때문에 SET 으로 변경하게 되면 변경 사항이 바로 적용
  • 영구적으로 저장하기 위해서는 my.cnf 변경 필요
• 정적 변수
  • 서버가 기동중인 상태에서 변경 불가능한 변수
  • 서버가 재기동될 때 적용됨