티스토리 뷰

서버 설정

my.cnf 설정 파일의 위치

MySQL 서버는 단 하나의 설정 파일만 사용하는데, 유닉스 계열에서는 my.cnf 라는 이름을 사용한다.
서버를 시작할 때만 해당 파일을 참조하는데, 지정된 여러 개의 디렉터리를 순회하면서 처음 발견된 my.cnf 파일을 사용한다.

my.cnf 를 찾기 위해 순회하는 디렉터리 경로가 궁금하다면 다음의 명령어들로 확인해볼 수 있다.
단, mysqld 프로그램은 MySQL 서버의 실행 프로그램으로 해당 option 을 빠뜨리면 실제 서버를 기동할 수 있으니 두번째 명령어로 확인해 보는 것이 좋다.

shell> mysqld --verbose --help
shell> mysql --help

해당 명령어의 결과를 살펴보면 상당히 많은 내용 안에 다음과 같은 내용이 있다.

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

MySQL 시스템 변수

MySQL 서버는 시작할 때 설정 파일의 내용을 읽어 여러가지 설정값들을 변수에 저장해놓는데, 이를 시스템 변수 라고 한다.
시스템 변수는 SHOW VARIABLES 명령어나 SHOW GLOBAL VARIABLES 명령어로 확인할 수 있는데, 이 변수들이 실제로 서버와 클라이언트에 어떤 영향을 미치는지 알기 위해서는 각 변수가 글로벌 변수인지 세션 변수인지를 구분할 수 있어야 한다.

글로벌 변수와 세션 변수

  • 글로벌 변수 : 하나의 MySQL 서버 인스턴스에서 전체적으로 영향을 미치는 시스템 변수이다. 쿼리 캐시의 크기(query_cache_size), InnoDB 버퍼 풀의 크기(innodb_buffer_pool_size) 와 같이 MySQL 서버 자체에 관련된 설정이 대부분이다.
  • 세션 변수 : MySQL 클라이언트가 서버에 접속할 때 기본적으로 부여하는 옵션의 기본값을 제어하는 데 사용된다. 따라서 클라이언트의 필요에 따라 개별 커넥션 단위로 다른 값으로 변경할 수 있는 변수이다.
  • 세션 범위의 시스템 변수 가운데 my.cnf 에 명시해 초기화할 수 있는 변수는 대부분 범위가 "Both" 라고 명시돼 있다. 순수하게 세션 범위인 변수는 설정 파일에 초기 값을 명시할 수 없다.

동적 변수와 정적 변수

MySQL 서버의 시스템 변수는 서버가 기동 중인 상태에서 변경 가능한지 여부에 따라 동적 변수와 정적 변수로 나뉜다.
변경하고자 하는 값이 동적 변수라면 굳이 서버를 재시작할 필요 없이 SET 명령으로 값을 변경할 수 있다.
하지만 my.cnf 파일까지는 업데이트 되지 않으니 추후에도 변경된 값을 사용하고 싶다면 설정 파일도 변경해주어야 한다.

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

my.cnf 설정 파일

my.cnf 설정 파일에서 가장 중요한 설정 그룹은 [mysqld] 설정 그룹이다.
주요 내용을 살펴보자.

server-id = 1

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

character-set-server = utf8
collation-server = utf8_general_ci
default-storage-engine = InnoDB
skip-name-resolve

event-scheduler = OFF

max_connections = 300
thread_cache_size = 50
wait_timeout = 28800

sort_buffer_size = 128K

query_cache_size = 32M
query_cache_limit = 2M

transaction-isolation = REPEATABLE_READ

innodb_buffer_pool_size = 10G

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_lock_wait_timeout = 60
innodb_flush_log_at_trx_commit = 1

general_log = 0
general_log_file = /usr/local/mysql/logs/general_query.log
slow-query-log = 1
long_query_time = 1
slow_query_log_file = /usr/local/mysql/logs/slow_query.log

log-bin = /usr/local/mysql/logs/binary_log
max_binlog_size = 512M
expire_logs_days = 14
binlog_cache_size = 128K

read_only
  • server-id : MySQL 이 내부적으로 자기 자신을 식별하는 아이디 값이다.

  • user : MySQL 이 설치된 서버의 운영체제 계정을 입력한다. MySQL 서버는 입력된 운영체제 계정으로 MySQL 인스턴스를 실행한다.

  • basedir : MySQL 서버의 홈 디렉토리를 명시한다.

  • datadir : MyISAM 의 데이터 파일이 저장되는 디렉토리다.

  • tmpdir : MySQL 서버는 정렬이나 그룹핑과 같은 처리를 위해 내부적으로 임시 테이블을 생성한다. tmpdir 은 내부 임시 테이블의 데이터 파일이 저장되는 위치이며, 쿼리가 종료되면 데이터는 자동으로 삭제된다. 사용자가 생성하는 "CREATE TEMPORARY TABLE" 의 임시 테이블과는 다르다.

  • character-set-server, collation-server : MySQL 서버의 기본 문자집합을 설정한다.

  • default-storage-engine : MySQL 서버 내에서 기본적으로 사용할 스토리지 엔진을 정의한다.

    • MySQL 서버가 내부적으로 생성하는 임시 테이블은 MyISAM 스토리지 엔진만 사용한다.
  • skip-name-resolve : 클라이언트의 접속 시 해당 클라이언트가 접속 허용된 사용자인지 확인하기 위해, 클라이언트의 IP 주소를 이용해 역으로 DNS 명을 가져오는 역 DNS 검색을 하지 않는 옵션이다.

    • 이 옵션은 설정 변수명 자체가 name-resolve 작업을 비활성화한다는 뜻이다.
    • 성능상의 이유로 역 DNS 검색은 비활성화해서 사용하는 것이 일반적이다.
  • event-scheduler : 일정 시간에 반복되는 작업을 목적으로 이벤트 스케줄러라는 기능을 사용할 수 있다.

    • 이벤트 스케줄러는 MySQL 내에서 실행되는 별도의 스레드를 필요로 한다.
  • max_connections : MySQL 서버가 최대한 허용할 수 있는 클라이언트의 연결 수를 제한하는 설정이다.

    • 이 설정을 몇 천 이상으로 너무 높게 설정하면 MySQL 서버가 응답 불능 상태에 빠질 위험이 증가한다. 한 두개의 무거운 쿼리가 자원을 모두 써버리거나 일시적으로 많은 사용자가 몰려드는 상황이 발생한다면, WAS에서도 수많은 커넥션을 생성하게 되고 결국 MySQL 서버가 요청은 받지만 처리할 수 없는 상태에 놓이게 된다.
    • 먼저는 WAS 의 커넥션 풀에서도 적절한 개수의 설정이 중요하고, MySQL의 이 설정도 위와 같은 위험을 막는 최후의 보루임을 기억해야 한다.
  • thread_cache_size : MySQL 서버의 스레드 풀에 최대 몇 개까지의 스레드를 보관할지 결정하는 설정이다.

    • 보통 WAS 쪽에서 커넥션 풀로 커넥션을 관리하기 때문에, 대량의 커넥션 요청이 발생하지 않을 것이므로 이 값을 크게 설정할 필요는 없다.
  • wait_timeout : MySQL 서버에 연결된 클라이언트의 요청 타임아웃 설정이다.

  • sort_buffer_size : 일반적으로 DBMS 에서 가장 큰 부하를 일으키는 작업이 바로 정렬인데, 이 설정은 인덱스를 사용할 수 없는 정렬에 메모리 공간을 얼마나 할당할지 결정하는 설정값이다.

    • 이 크기가 작으면 디스크를 사용할 확률이 높아지고 커지면 각 스레드의 메모리 사용량이 높아져서 메모리 낭비가 심해진다.
    • 권장값은 64KB ~ 512KB 사이이다.
  • query_cache_size, query_cache_limit : 쿼리 캐시에 관련된 캐시 크기 설정값이다.

    • 128MB 이상은 설정하지 않는 것이 좋으며, 데이터가 변경되지 않고 읽기 전용으로만 사용되는 경우 조금씩 늘리면서 성능을 확인한다.
    • 데이터의 변경이 많은 경우 64MB 이상으로는 설정하지 않는 것이 좋다.
  • transaction-isolation : 트랜잭션의 격리 수준을 결정하는 설정값으로 기본값은 REPEATABLE_READ 이다.

    • 일반적으로는 REPEATABLE_READ 와 READ_COMMITTED 이다.
  • innodb_buffer_pool_size : InnoDB 스토리지 엔진에서 가장 중요한 옵션이다. 해당 엔진의 버퍼 풀은 디스크의 데이터를 메모리에 캐싱함과 동시에 데이터의 변경을 버퍼링하는 역할을 수행한다.

    • 일반적으로 OS나 MySQL 클라이언트에 대한 서버 스레드가 사용할 메모리를 제외하고 남는 거의 모든 메모리 공간을 설정한다.
  • innodb_log_group_home_dir : InnoDB와 같이 트랜잭션을 지원하는 RDBMS는 ACID 보장과 동시에 성능 향상을 목적으로 데이터의 변경 이력을 별도의 파일에 순차적으로 기록해 두는데, 이를 트랜잭션 로그 혹은 Redo 로그 라고 한다. 이 로그는 사람이 읽을 수 있는 로그는 아니고 서버의 갑작스런 종료 시 종료되지 않은 트랜잭션을 복구하기 위한 용도로 사용된다. 이 설정값은 해당 로그의 위치값이다.

  • innodb_log_buffer_size : 데이터 변경 시 해당 변경사항을 바로 리두 로그에 기록하면 디스크 I/O 가 빈번해져서 비효율적이다. 이를 위해 버퍼를 사용하는데, 해당 버퍼에 대한 크기 설정값이다.

    • 전역적으로 생성되며, 16~32 MB 정도면 충분하다.
  • innodb_log_file_size, innodb_log_files_in_group : InnoDB의 리두 로그 파일은 1개 이상의 파일로 구성되지만 각 파일을 순환 큐(Circular Queue)처럼 연결해서 사용한다. 이 설정들은 해당 파일 1개의 크기와 몇 개의 파일을 사용할지 결정하는 설정이다.

    • 이 값이 너무 작으면 InnoDB의 버퍼 풀로 설정된 메모리 공간이 아무리 커도 제대로 활용할 수 없어서 InnoDB 전체를 비효율적으로 작동하게 만들 수도 있으니 주의해야 하는 중요한 설정이다.
  • innodb_lock_wait_timeout : InnoDB에서 잠금 획득을 위해 최대 대기할 수 있는 시간을 설정한다.

  • innodb_flush_log_at_trx_commit : InnoDB에서 트랜잭션이 커밋될 때마다 리두 로그를 디스크에 플러시할지 결정하는 옵션이다.

  • general_log, general_log_file : MySQL에는 실행되는 모든 쿼리를 로그 파일로 기록하는 기능이 있는데, 이 로그를 쿼리 로그 혹인 제너럴(General) 로그라고 한다. 쿼리 수행이 많다면 쿼리 로그는 사용하지 않는 편이 좋다.

  • slow-query-log, long_query_time, slow_query_log_file : 지정된 시간 이상으로 수행된 쿼리를 별도의 로그 파일로 남기는데 이를 슬로우 쿼리 로그라고 한다. 어떤 쿼리를 가장 먼저 튜닝해야 할지를 알려주는 중요한 지표이므로 반드시 활성화하고 활용하자.

  • log-bin, max_binlog_size, expire_logs_days : MySQL 에서 복제를 구축하려면 마스터 서버에서 기록하는 바이너리 로그가 필요하다. 슬레이브 서버는 마스터의 바이너리 로그를 가져와서 재실행하는 것으로 마스터와 슬레이브 간의 데이터를 동기화한다.

    • log-bin 은 바이너리 로그 파일의 prefix, max_binlog_size 는 최대 파일의 크기를 제한하고, expire_logs_days 는 최대 로그 보관 일 수이다.
  • binlog_cache_size : 바이너리 로그도 마찬가지로 즉시 디스크에 기록하는 것이 아니라 버퍼링하면서 저장한다. 해당 버퍼의 크기를 결정한다.

  • read_only : 보통의 슬레이브 서버를 읽기 전용으로 만들기 위해 해당 옵션을 사용한다.

    • 글로벌 동적 변수라서 필요한 경우 SET 명령으로 바로 읽기 전용을 해제할 수도 있다.

서버의 시작과 종료

시작과 종료

다음 커맨드로 서버를 시작하고 종료할 수 있다.

## 리눅스 서비스로 등록되지 않는 경우
shell> /etc/init.d/mysql start
shell> /etc/init.d/mysql stop

## 리눅스 서비스로 등록된 경우
shell> service mysql start
shell> service mysql stop

MySQL 서버 시작 후 기동되는 두 개의 프로세스 중 mysqld 라는 프로세스가 실제 서비스를 하는 MySQL 데몬 프로세스이다.
mysqld_safe 라는 프로세스는 mysqld 가 비정상적으로 종료되는 경우 다시 기동하는 역할을 한다.

InnoDB 엔진의 경우 트랜잭션이 정상 커밋되어도 데이터 파일에 내용이 적용되지 않고 리두 로그에만 기록돼 있을 수 있다.
서버가 종료되고 다시 시작되어도 이 상태가 계속 유지될 수도 있다.
이런 현상이 비정상적인 것은 아니며, 해당 커밋 내용을 데이터 파일에 모두 반영하고 서버를 종료하는 클린 셧다운(Clean shutdown) 을 하기 위해서는 다음과 같이 서버를 종료한다.

mysql> SET GLOBAL innodb_fast_shutdown=0;
shell> mysqladmin -uroot -p shutdown 

MySQL 복제 구축

설정 준비

MySQL 복제를 위해서는 각 서버가 중복되지 않는 server-id 값을 가지고 있어야 하고, 마스터 서버는 반드시 바이너리 로그가 활성화돼 있어야 한다.

## 마스터 서버

[mysqld]
server-id = 101
log-bin = binary_log
sync_binlog = 1
binlog_cache_size = 5M
max_binlog_size = 512M
expire_logs_days = 14
log-bin-trust-function-creators = 1
## 슬레이브 서버

[mysqld]
server-id = 102
relay-log = relay_log
relay_log_purge = TRUE
read_only

복제 계정 준비

슬레이브 서버가 마스터로부터 바이너리 로그를 가져오려면 마스터 서버에 접속해 로그인을 해야하는데, 이때 슬레이브가 사용할 계정을 복제용 계정이라고 한다.
복제용 계정은 마스터에 미리 준비되어 있어야 하고, 반드시 REPLICATION SLAVE 권한을 가지고 있어야 한다.

mysql> CREATE USER 'repl_user'@'%' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';

데이터 복사

이제 데이터를 복사하기 위해 Enterprise backup 이나 mysqldump 등을 사용하면 된다.
데이터의 양이 크지 않다면 mysqldump 를 사용하면 된다.

다음 명령은 반드시 한 줄로 입력해야 한다.
테이블, 레코드 잠금을 걸지 않고 InnoDB 테이블 백업을 할 수 있는 '--single-transaction' 설정과, 바이너리 로그의 정보를 백업 파일에 같이 기록하게 해주는 '--master-data=2' 설정을 꼭 포함시킨다.

shell> mysqldump -uroot -p --opt --single-transaction --hex-blob --master-data=2 --routines --triggers --all-databases > master_data.sql

복제 시작

복제를 시작하는 명령은 CHANGE MASTER 명령이다.
mysqldump 로 백업받은 파일의 헤더 부분에서 해당 명령어를 참조할 수 있다.

shell> less /tmp/master_data.sql

...

-- CHANGE MASTER TO MASTER_LOG_FILE='binary_log.000007', MASTER_LOG_POS=2741;

해당 내용에 서버 호스트명, 포트, 복제용 사용자 계정 정보를 추가해 복제를 진행한다.

CHANGE MASTER TO MASTER_LOG_FILE='binary_log.000007', MASTER_LOG_POS=2741, master_host='host_master',master_port=3306,master_user='repl_user',master_password='slavepass'

이 명령 실행 후 "START SLAVE" 명령으로 복제 및 데이터 적재 시간 동안 벌어진 데이터 차이에 대한 동기화를 진행할 수 있다.


참고

위 내용은 이성욱님의 Real MySQL (위키북스) 에서 일부 내용을 간단하게 정리한 것입니다.
세부적인 내용은 책을 직접 구입하셔서 읽어보시는 것을 추천드립니다.