[MySQL] 설정 파일 my.cnf 옵션 종류

my.cnf 란?

• 유닉스 계열에서 MySQL 서버 설정 파일로 my.cnf 를 사용
  • 이름 변경 불가능
  • 파일 경로 변경 가능
  • 윈도우에서는 my.ini 파일을 사용
• 사용중인 MySQL 서버가 사용하는 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/mysql/etc/my.cnf ~/.my.cnf
  ...

  • 지정된 디렉토리 목록을 순서대로 탐색하며 가장 처음 발견된 my.cnf 파일을 사용함
• MySQL 서버를 기동하며 설정 파일의 내용을 읽어 시스템 변수로 저장
  • SHOW VARIABLES, SHOW GLOBAL VARIABLES 명령어를 사용해 MySQL 서버에 저장된 시스템 변수 확인 가능

설정파일의 구성

• 하나의 my.cnf 파일에 여러 개의 설정 그룹을 담을 수 있음

• 대체로 바이너리 프로그램 이름을 그룹명으로 사용

• 설정 파일 예시

  [client]
  # client 옵션
  default-character-set = utf8 
  port = 3306
  socket = /home/centos/mysql/mysql.sock
  
  [mysqld] 
  # MySQL 서버 기본 옵션
  basedir = /home/centos/mysql
  datadir = /home/centos/mysql/data 
  socket = /home/centos/mysql/mysql.sock
  log-error = /home/centos/mysql/mysql-log/mysqldb.log 
  pid-file = /home/centos/mysql/mysql-log/mysqldb.pid
  user = centos
  innodb_buffer_pool_size = 10G
  
  [mysql] 
  # MySQL 설정그룹
  soket = /home/centos/mysql/mysql.sock
  port = 3304 
  
  [mysqld_safe] 
  # mysqld_safe는 mysqld를 감시하는 데몬. mysqld_safe가 내부에서 mysqld를 실행
  log-error = /home/centos/mysql/mysql-log/mysqldb.log 
  pid-file = /home/centos/mysql/mysql-log/mysqldb.pid

• 옵션 종류

  • port : 접속 시 사용한 port 설정
  • basedir : base directory 경로 설정
  • datadir : data directory 경로 설정
  • socket : 사용할 소켓 파일 경로 설정
  • log-error : 에러로그 파일 경로 설정
  • pid-file : 프로세스 ID 파일 경로 설정
  • user : 접속 유저명
  • join_buffer_size : join 시 테이블을 풀스캔 하기위해 사용하는 버퍼크기
  • sort_buffer_size : 정렬에 필요한 버퍼의 크기
    • ORDER BY 또는 GROUP BY 연산 속도와 관련됨
  • read_buffer_size : 테이블 스캔에 필요한 버퍼크기
  • read_rnd_buffer_size : 디스크 검색을 피하기위한 랜덤 읽기 버퍼크기
  • key_buffer_size : 인덱스 블록에서 사용되는 버퍼의 크기, 모든 thread에서 공유하는 버퍼 크기
  • thread_concurrency : thread 최대 개수
  • max_allowed_packet : 클라이언트 통신에 대해 사용되는 버퍼가 가질수 있는 최대 크기
  • table_cache : MySQL 서버가 한번에 열수 있는 테이블의 개수
  • query_cache_size : 쿼리 캐시 버퍼 사이즈
  • max_connections : 동시에 접근 가능한 커넥션 수 설정
  • max_connect_errors : 발생할 수 있는 최대 에러 개수
  • wait_timeout : 커넥션 최대 대기시간(초)
  • transaction-isolation : isolation 레벨 지정
    • READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE

• InnoDB 관련 옵션

  • innodb_buffer_pool_size : 데이터와 인덱스를 캐시하기 위해 사용하는 메모리 버퍼크기
    • 전용 서버의 경우 전체 RAM의 70% 이상, 그 외는 10% 이상으로 지정
  • innodb_log_buffer_size : Redo 로그 버퍼크기
  • innodb_log_file_size : 로그 파일 사이즈. 버퍼풀 사이즈의 25% 정도로 설정
    • 로그 버퍼 사이즈로 성능에 맞춰 로그를 기록하는 경우 크게 설정
  • innodb_flush_log_at_trx_commit : 커밋 로그 옵션
    • 1로 설정 시 트랜잭션 실행마다 로그 파일에 기록되고 디스크 플러시가 실행
    • 2로 설정 시 성능 최적화를 위해 1분마다 저장
  • innodb_support_xa : 트렌잭션 two-phase commit 지원
    • 디스크 플러시 횟수를 줄여 성능항상
  • innodb_thread_concurrency : InooDB내에 쓰레드 개수
    • 0으로 설정 시 thread간 동시성 비활성화
  • innodb_lock_wait_timeout : 롤백이 진행되기 전에 LOCK을 대기하는 시간(초)
  • innodb_force_recovery : crash 복구 모드 설정