메모장입니다.

PHP로 password 함수 와 동일한 결과값을 얻을때(old_password아님)

strtoupper('*'.sha1(sha1('문자열', true)));

프로시저/function/트리거등을 백업하고 import 할때 주의할 점

dump 시에는 주석이 저장되나 import 할때 --comments 옵션을 사용하지 않으면 주석은 제거되어 import 되므로 주의할 것.

특정한 페이지에서만 myql connection time을 늘려야 할때 아래쿼리를 날려주면 됨

SET INTERACTIVE_TIMEOUT=86400

프로시저(procedure) exit 하기

begin 앞에 라벨명을 붙이고 LEAVE 라벨명; 하면 해당 블럭을 탈출한다.

my.cnf 에 클라이언트쪽 설정

[mysql]

no-auto-rehash  ##db변경시 테이블정보를 읽어들이지 않음(자동완성용, 테이블 많을 시 느려짐)
show-warnings  ##warning 발생 시 출력
prompt=\\u [\\d]>\\_ ##프롬프트 변경(참고)

my.cnf 에 언어셋

character_set_server = utf8

선언없을 경우 latin1이 기본이 되서 create database하면 latin1 언어셋의 db가 생성된다.

show variables like '%char%'; 하면 character_set_database 가 latin1으로 나오는게 그런 이유인데.

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_general_ci;

해서 변경해주던지 utf8로 새로 생성해야 한다. 꼬이면 귀찮아짐

Root 비밀번호 변경
MySQL 5.7.6 이후

ALTER USER `root`@`localhost` IDENTIFIED BY '비밀번호';

MySQL 5.7.5 이전

SET PASSWORD FOR `root`@`localhost` = PASSWORD('비밀번호');

mysql 로그인 후 You must reset your password using ALTER USER statement before executing this statement 같은 메세지가 뜬다면 아래처럼 최초비번을 다시 변경해줘야 함

SET PASSWORD = PASSWORD('비밀번호');

Your password does not satisfy the current policy requirements 와 같이 비번 설정이 안되는 경우 validate_password 플러그인 때문에 비밀번호 형식에 제한이 생긴다.

my.cnf 에 validate_password = off 로 하면 플러그인을 사용하지 않는다.

레벨만 설정할 경우 validate_password_policy = LOW(기본 8자이상), MEDIUM(숫자/소문자/대문자/특문포함)등으로 설정

기본 SQL-MODE 설정

my.cnf에 sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION" 추가

STRICT_TRANS_TABLES = not null 등의 조건을 엄격히 검사

NO_ENGINE_SUBSTITUTION = 잘못된 엔진이거나 엔진정의가 없으면 기본엔진 사용

MySQL 5.6.6 부터 직접 아이디/비밀번호를 입력하지 않고 별도의 키파일로 접속이 가능하도록 하는 mysql_config_editor 파일이 추가 되었다.

이 파일로 계정정보를 만들게 되면 리눅스의 경우 접속한 계정에 .mylogin.cnf 라는 파일로, 윈도우의 경우 %APPDATA%\MySQL에 접속정보가 암호화 되어 저장되게 된다. 아이디/비번을 사용하여 mysqldump를 사용하는 경우에 insecure warnning 이 발생하니 이 방법을 사용하는게 좋다.

.mylogin.cnf 는 소유자만 rw 할수 있는 권한 600 권한이어야만 동작되고 아닌경우에는 동작되지 않도록 되있으니 주의

비밀번호에 # 문자가 포함될 경우 정상적으로 로그인이 되지 않으니 주의

1. mysql_config_editor 설정추가

mysql_config_editor set --login-path=설정명 --host=localhost --user=root --password --port=3306 --socket=소켓위치

--login-path 를 생략할 겨우 client 로 저장이 되지 주의

--port, --socket 는 생략가능

2. 사용하기 

mysqldump --login-path=설정명

mysql --login-path=설정명

3. 접속정보 삭제

mysql_config_editor remove --login-path=설정명

mysql_config_editor remove --login-path=설정명 --user  #접속정보 중 user 만 삭제

4. 접속정보 전체 삭제

mysql_config_editor reset

5. 접속정보 표시

mysql_config_editor print --all

mysql_config_eidtor print --login-path=설정명

mysql 5.7.9? 였던가 부터는 초기 비번을 랜덤하게 설정해 버려서 사용하기 위해서는 비번을 설정해줘야 함

1. 인증없는 모드로 임시실행 후 root 접속

mysqld_safe --skip-grant-tables &

또는 my.cnf 에 skip-grant-tables 옵션 추가 후 service mysqld restart

---

mysql -u root

2. root 비밀번호 변경

- 5.7 이전

update mysql.user set password=password('비번') where user='root';

- 5.7 이후

update mysql.user set authentication_string=password('비번') where user='root';

3. 서비스 정지

service mysqld stop 나 mysql.server stop

4. my.cnf 아래 부분 추가/수정

5.7.9부터인가? validate plugin이 기본동작된다. password 함수 사용 시 특정 형식에 맞지 않으면 오류가 나는데 그걸 방지하기 위해서 플러그인을 off 하거나 설정을 변경한다.

기본 sql-mode 가 변경 되어서 혼란이 온다면 이부분도 수정

character-set-server = utf8

sql-mode=""

validate-password=off //설정을 끄거나 아래처럼 룰을 변경

validate_password_policy = LOW

validate-password-mixed-case-count = 0

validate-password-length = 1

validate-password-special-char-count = 0

5. 서비스 시작

service mysqld start 또는 mysql.server start

6. 접속 후 아래와 같은 오류나 나올 경우

mysql -uroot -p

mysql> SELECT 1;

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

한번 더 비밀번호를 설정해 줌

mysql> SET PASSWORD = PASSWORD('비번'); //password 함수를 사용하는건 deprecated 되었다고 함

mysql> SET PASSWORD = '비번';

1. wget을 통해 리눅스 버전에 맞는 RPM Bundle 을 다운로드

2. yum install  mysql-community-devel-5.7.11-1.el6.x86_64.rpm 과 같이 rpm을 설치해줌

  (devel, libs, common, client, server 순으로 설치)

3. /etc/my.cnf 수정(옵션은 알아서...)

[client]

default-character-set = utf8

port = 3306

socket = /var/lib/mysql/mysql.sock

[mysqld]

socket=/var/lib/mysql/mysql.sock

datadir=/var/lib/mysql

skip-external-locking

key_buffer_size = 384M

max_allowed_packet = 1M

table_open_cache = 512

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size = 32M

#dns query

skip-name-resolve

#connection

max_connections = 1000

max_connect_errors = 1000

wait_timeout= 60

#slow-queries

slow_query_log = /var/lib/mysql/slow-queries.log

long_query_time = 3

symbolic-links=0

### log

log-error=/var/log/mysqld.log

pid-file=/var/lib/mysql/mysqld.pid

###chracter

character-set-client-handshake=FALSE

character-set-server = utf8

##Password Policy

validate_password_policy=LOW

### INNODB Spectific options

default-storage-engine = InnoDB

#skip-innodb

#innodb_additional_mem_pool_size = 16M

#innodb_buffer_pool_size = 1024MB

#innodb_data_file_path = ibdata1:10M:autoextend

innodb_write_io_threads = 8

innodb_read_io_threads = 8

innodb_thread_concurrency = 16

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 8M

innodb_log_file_size = 128M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

4. Mysql 5.7부터 비밀번호가 랜덤으로 세팅 되므로 safe 모드로 들어가서 비번을 변경한다.

/etc/my.cnf 에 skip-grant-tables=TRUE 를 넣어서 service mysqld start 를 하거나..

mysqld_safe --skip-grant-tables & 를 사용한다.

#주의 skip-grant-tables 옵션을 사용할때 my.cnf에 validate_password_policy 옵션이 존재하면 error로 mysql이 시작되지 않으니 주의할 것.

5. mysql 로들어가서 아래처럼 비밀번호를 변경

use mysql;

UPDATE user SET authentication_string=PASSWORD('비밀번호') WHERE User = 'root';

flush privileges;

6. MySQL safe mode를 종료한다.

service mysqld stop 이나 mysqladmin -uroot shutdown으로 종료.

7. /etc/my.cnf에 validate_password_policy=LOW 이 옵션 추가.

이 옵션은 비밀번호를 제한할때 사용하는건데 LOW로 해야 기존처럼 아무거나 사용할 수 있다.

아니면 복잡하게 생성해야 됨

8. 정상적으로 mysql을 시작하고 root로 로그인하고 명령을 사용할려면 아래같은 오류가 뜬다.

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

말그대로 비밀번호를 다시!! 바꿔줘야 한다는 말인데 아까 설정한 것을 그대로 해줘도 된다.

ALTER USER 'root'@'localhost' IDENTIFIED BY '루트비번';

flush privileges;

9. 이제 정상적으로 사용이 가능.

MySQL 연산자 중에 ~ 연산자가 있다.

이 연산자는 bit 열의 1의보수를 구하는 말그대로 비트를 거꾸로 뒤집는 연산자(invert)이다.

이 연산자를 사용하면 결과값으로 unsigned 64-bit integer. 즉 BIGINT UNSIGNED 값이 나온다.

그럼 0을 1의 보수로 구하면 어떻게 될까? 바로 BIGINT UNSIGNED의 최대값이 나오게된다.

이값을 shift 연산이나 Bit and 연산을 하면 쉽게 구할수 있다.

mysql> select ~0;

+----------------------+

| ~0                   |

+----------------------+

| 18446744073709551615 |

+----------------------+

1 row in set (0.00 sec)

mysql> SELECT ~0 as bigint_unsigned

    -> ,      ~0 >> 32 as int_unsigned

    -> ,      ~0 >> 40 as mediumint_unsigned

    -> ,      ~0 >> 48 as smallint_unsigned

    -> ,      ~0 >> 56 as tinyint_unsigned

    -> ,      ~0 >> 1  as bigint_signed

    -> ,      ~0 >> 33 as int_signed

    -> ,      ~0 >> 41 as mediumint_signed

    -> ,      ~0 >> 49 as smallint_signed

    -> ,      ~0 >> 57 as tinyint_signed

    -> \G

*************************** 1. row ***************************

      bigint_unsigned: 18446744073709551615

         int_unsigned: 4294967295

 mediumint_unsigned: 16777215

    smallint_unsigned: 65535

      tinyint_unsigned: 255

         bigint_signed: 9223372036854775807

             int_signed: 2147483647

    mediumint_signed: 8388607

       smallint_signed: 32767

         tinyint_signed: 127

1 row in set (0.00 sec)

mysql> SELECT ~0 as bigint_unsigned

    -> ,      ~0 & 0xFFFFFFFF as int_unsigned

    -> ,      ~0 & 0xFFFFFF as mediumint_unsigned

    -> ,      ~0 & 0xFFFF as smallint_unsigned

    -> ,      ~0 & 0xFF as tinyint_unsigned

    -> ,      ~0 & 0x7FFFFFFFFFFFFFFF as bigint_signed

    -> ,      ~0 & 0x7FFFFFFF as int_signed

    -> ,      ~0 & 0x7FFFFF as mediumint_signed

    -> ,      ~0 & 0x7FFF as smallint_signed

    -> ,      ~0 & 0x7F as tinyint_signed

    -> \G

*************************** 1. row ***************************

   bigint_unsigned: 18446744073709551615

      int_unsigned: 4294967295

mediumint_unsigned: 16777215

 smallint_unsigned: 65535

  tinyint_unsigned: 255

     bigint_signed: 9223372036854775807

        int_signed: 2147483647

  mediumint_signed: 8388607

   smallint_signed: 32767

    tinyint_signed: 127

1 row in set (0.00 sec)

1. 파티션 지원 확인

SHOW VARIABLES LIKE '%partition%';

SHOW PLUGINS;

2. 파티션 추가

CREATE TABLE `테이블` (

  ....

)

PARTITION BY RANGE(함수(`필드명`)) (

  PARTITION `파티션명1` VALUES LESS THAN (값),

  PARTITION `파티션명2` VALUES LESS THAN (값),

  PARTITION `파티션명3` VALUES LESS THAN (MAXVALUE)

);

CREATE TABLE 이후 추가

ALTER TABLE `테이블` PARTITION BY RANGE(함수(`필드명`)) (

  PARTITION `파티션명1` VALUES LESS THAN (값),

  PARTITION `파티션명2` VALUES LESS THAN (값),

  PARTITION `파티션명3` VALUES LESS THAN (MAXVALUE)

);

# THAN (표현식) 에는 THAN (함수(값)) 형태로도 가능

#표현식에 사용할 수 있는 함수 종류 및 파티션의 종류는 아래 링크에서 참고

http://wyseburn.tistory.com/entry/MySQL-%ED%8C%8C%ED%8B%B0%EC%85%94%EB%8B%9D

3. 파티션 분할

ALTER TABLE `테이블` REORGANIZE PARTITION `파티션명3` INTO (

  PARTITION `파티션명3` VALUES LESS THAN (값),

  PARTITION `파티션명4` VALUES LESS THAN (MAXVALUE)

);

4. 파티션 삭제

ALTER TABLE `테이블` DROP PARTITION `파티션명1`;

5. 파티션 관리

ALTER TABLE REBUILD PARTITION;
ALTER TABLE OPTIMIZE PARTITION;
ALTER TABLE ANALYZE PARTITION;
ALTER TABLE REPAIR PARTITION;
ALTER TABLE CHECK PARTITION;

6. 파티션 완전삭제

ALTER TABLE `테이블` REMOVE PARTITIONING;

좋은 글이 있어서 퍼옴
출처 : http://www.linuxchannel.net/docs/mysql-timeout.txt

-------------------------------------------------------------------------------------------------------

[MySQL] time out(wait_timeout) 계산과 설정

- 작성자 : 김칠봉 <san2(at)linuxchannel.net>
- 작성일 : 2003-08-11(보완, 그래프 추가)
           2003-08-04
- 내  용 : life time 계산에 의한 wait_timeout 설정
- 수  준 : 초중급 이상
- 키워드 : MySQL, wait_timeout, interactive_timeout, connect_timeout,
           max_connections, Aborted_clients, Connections,
           Max_used_connections, Slow_queries, Questions, Uptime

*주1)
이 문서에 대한 최신 내용은 아래 URL에서 확인할 수 있습니다.

http://www.linuxchannel.net/docs/mysql-timeout.txt

*주2)
이 문서에서 사용한 life time, EXP, CUR, PAS, POL, DEF, LPT ... 와 같은
용어는 필자가 자의적으로 붙인 가칭용어로써 공식적인 용어가 아닙니다.

---------------------------------------------------------
목차

0. 배경

1. MySQL의 time out
  1-1. connect_timeout
  1-2. interactive_timeout
  1-3. wait_timeout

2. 연결 취소율(POC)과 connection life time
  2-1. 연결 취소율(POC)
  2-2. connection life time

3. 임계 life time
  3-1. 현재 최대(최악) 예상 임계 life time (EXP)
  3-2. 현재 평균 임계 life time (CUR)
  3-3. 지난 과거 최대 임계 life time (PAS)
  3-4. 지난 과거 유추 최대 임계 life time (POL)

4. 임계 life time(LPT) 계산(예제)

5. wait_timeout 계산 및 보정

6. 결과 확인

7. 후기
---------------------------------------------------------


0. 배경

아주 바쁜 MySQL 서버에서는 간혹 'Too many connections' 이라는 에러를 만날
수 있을 겁니다.

대부분 이 에러를 해결하기 위해서,

  - max_connections
  - wait_timeout

이 두개의 파라메터를 튜닝하면서 설정하는 것이 일반적입니다.

그런데, MySQL 매뉴얼에는 이 에러에 대한 자세한 설명이 빠져 있습니다.

예를들어, 실제 Max_used_connections 이 한계 max_connections 에 도달하지 않았는데도
불구하고 이런 에러를 만나면 상당히 난처합니다.

이런 경우는 대부분  max_connections 값을 올리고, wait_timeout 값을 줄여서 튜닝
하곤 하지만 역시 정확한 튜닝이 어렵습니다.

실제로 좀더 정확하게 튜닝하기 위해서는,

  - 시스템 전체 상황(실제 어느 정도로 바쁜지에 대한 상대적 수치),
  - 초당 connections 수,
  - 커넥션당 평균 쿼리 요청수,
  - 커넥션당 생성된 평균 쓰레드 수
  - 초당 평균 전송량
  - DISK에 생성된 임시 테이블 생성 비율
  - Slow_queries
  - 한계 도달 N 초 계산
  - 커넥션 life time

이런 값들을 계산 및 고려하여,

  - max_connections
  - wait_timeout
  - back_log
  - thread_cache_size
  - key_buffer_size
  - record_buffer (read_buffer_size)
  - record_rnd_buffer
  - sort_buffer_size
  - 기타 메모리 설정

이런 파라메터에 설정을 해줘야 합니다.

실제 MySQL 서버의 파라메터 설정은,

  1) 사용가능한 최대 전체 데이터베이스 크기와 각 테이블 평균 크기 계산
  2) MySQL 이 사용하는 시스템 물리적 메모리 크기
  3) 1)에 의한 shared 메모리와 쓰레드 메모리 할당 및 계산
  4) 2)과 3)에 의한 최대 동시 접속 가능한 max_connections 계산
  5) time out 설정
  6) 그 외 설정
  7) 시스템을 운영하면서 지난 통계 데이터에 의한 설정값 다시 튜닝

이와 같은 순서로 튜닝해 나가야 합니다.

2)번과 3)번 같은 경우는

  MySQL 이 사용할 물리적 메모리 총합 =
  (
      key_buffer_size +
      (read_buffer_size + sort_buffer_size) * max_connections
  )

이와 같은 공식을 사용합니다.
전자의 key_buffer_size 는 모든 쓰레드들이 항상 공유해서 사용하는 shared
메모리이고, 그 밑의 두개는 각 쓰레드마다 사용하는 not shared 메모리입니다.

간략하게 정리하면 다음과 같습니다.

  MySQL caches(all threads shared)
  (
      - key_buffer_size	        : 8MB < INDEX key
      - table_cache             : 64 < number of open tables for all threads
      - thread_cache_size       : 0 < number of keep in a cache for reuse
  )

  MySQL buffers(not shared)
  (
      - join_buffer_size        : 1MB < FULL-JOIN
      - myisam_sort_buffer_size : 8MB < REPAIR, ALTER, LOAD
      - record_buffer           : 2MB < sequential scan allocates
      - record_rnd_buffer       : 2MB < ORDER BY(avoid disk)
      - sort_buffer             : 2MB < ORDER BY, GROUP BY
      - tmp_table_size          : 32MB < advanced GROUP BY(avoid disk)
  )

  MySQL memory size
  (
      - INDEX(key)              : 8MB < key_buffer_size (shared)
      - JOIN                    : 1MB < join_buffer_size (not shared)
      - RECORD(read)            : 2MB < record_buffer (not shared)
                                : 2MB < record_rnd_buffer (not shared)
      - SORT                    : 8MB < myisam_sort_buffer_size (not shared)
                                : 2MB < sort_buffer (not shared)
      - TABLE(temporary)        : 32MB< tmp_table_size(not shared)
  )

  MySQL timeout
  (
      - connect_timeout         : 5 > bad handshake timeout
      - interactive_timeout     : 28800 > interactive to re-interactive timeout
      - wait_timeout            : 28000 > none active to re-active timeout
  )

  MySQL connections
  (
       - max_connections        : 100 < 'to many connections' error
       - max_user_connections   : 0(no limit) < user limit
  )


이 글은 메모리 설정에 관한 내용은 상당히 방대하므로 여기에서는 제외하고,
MySQL 의 time out 계산에 관한 내용입니다.

  이 글의 전제 조건
  (
      - MySQL 서버가 상당히 바빠서 time out 설정이 필요하다.
      - 서버의 바쁜 정도와 데이터베이스 크기에서 계산한
        max_connections 설정값이 현재 MySQL 서버의 한계점이다.
        (초과시 서버 증설이 필요함)
      - 연결 취소율(Aborted_clients*100/Connections)이 높은 경우
        (튜닝 기준 0.1% 또는 0.5% 이상)
  )

  time out 설정 목적
  (
      - 제한된 자원(메모리)의 효율적 이용
      - MySQL 성능 극대 (한계점에 극대, 한계점을 초과하지 않음)
      - 원할한 커넥션 처리
      - 절대적으로 불필요한 커넥션을 강제로 close(불필요한 커넥션 반환)
      - 기타
  )


1. MySQL의 time out

MySQL 서버(정확히 `mysqld')의 time out 설정은 크게 3가지가 있습니다.

  mysqld time out
  (
      - connect_timeout (bad handshake timeout)
      - interactive_timeout (interactive 모드에서 connection time out)
      - wait_timeout (none interactive 모드에서 connection time out)
  )

connect_timeout, interactive_timeout 은 튜닝과 좀 거리가 멀고,
실제로 바쁜 서버라면, 반드시 wait_timeout 을 따로 튜닝하여 설정해줘야 합니다.
(실제 이 글의 내용임)


1-1. connect_timeout

이 설정은 mysqld 와 mysql client 가 연결(connection)을 맺기 위해서
mysqld 가 연결 패킷을 기다리는 최대 시간입니다.

즉 TCP 연결을 맺는 과정(3-way handshake)에서, connect_timeout 동안에도
연결 패킷이 들어오지 않으면 연결이 실패(취소가 아님)되고,
bad handshake 로 응답합니다.

  *참고)
  (
      - 연결 실패 : 연결 과정중에서 fail 되는 경우 (Aborted_connects)
      - 연결 취소 : 연결이 된 상태에서 강제로 close 된 경우 (Aborted_clients)
  )

바꾸어 말하면 mysqld 와 mysql client 가 TCP 연결을 맺는 최대 시간으로
이 시간보다 큰 경우는 모두 Aborted_connects 에 해당됩니다.
(단위는 초)

  연결 실패율(POF) =
  (
      Aborted_connects * 100 / Connections
  )

  연결이 실패되는 경우
  (
      - 연결 패킷에 올바른 연결 정보가 없는 경우
      - 특정 user 가 권한이 없는 데이터베이스에 접근할 경우
      - mysqld 접근 password 가 틀린 경우
      - connect_timeout 보다 긴 연결 과정
  )

연결 실패율(POF)이 높은 경우는, 대부분 권한이 없는 데이터베이스 연결이나,
틀린 password 를 사용할 경우가 많습니다.

기본값은 대부분 5(초)로 설정되어 있으며, 따로 튜닝할 필요는 없습니다.
mysqld 의 --warnings 옵션 사용과 xxx.err 파일에 기록됩니다.


1-2. interactive_timeout

interactive 모드에서 time out 을 말합니다.
interactive 모드는 'mysql>' 과 같은 프롬프트 있는 콘솔이나 터미널 모드를
말합니다.

mysqld 와 mysql client 가 연결을 맺은 다음, 다음 쿼리까지 기다리는
최대 시간을 의미합니다.

설정된 interactive_timeout 까지도 아무런 요청(쿼리)이 없으면 연결은
취소되고, 그 이후에 다시 요청이 들어오면 연결은 자동으로 맺어집니다.

interactive_timeout 안에 다시 요청이 들어오면 wait time은 0으로 초기화
됩니다(CLIENT_INTERACTIVE).

  ERROR 2006: MySQL server has gone away
  No connection. Trying to reconnect...
  Connection id:    12002
  Current database: xxx

이와 같은 연결 취소는 Aborted_clients 에 누계되고, wait_timeout 의
결과도 함께 포함됩니다

기본 값은 28800(8시간) 초로 설정되어 있는데 상당히 관대한(?) 설정입니다.
약 1시간(3600) 정도로 설정하는 것을 권장합니다.


1-3. wait_timeout

이 설정은 제일 중요한 파라메터 항목입니다.

interactive 모드가 아닌 경우에 해당되며,
mysqld 와 mysql client 가 연결을 맺은 후, 다음 쿼리까지 기다리는
최대 시간을 의미합니다.

즉 대부분 PHP 나 C, PERL, python 등등의 API 를 이용한 client 프로그램
모드를 말합니다.

interactive_timeout 과 마찬가지로 wait_timeout 까지 아무런 요청(쿼리)이
없으면 연결은 취소되고 그 결과는 Aborted_clients 에 누계됩니다.

wait_timeout 안에 다시 요청이 들어오면 wait time 은 0 으로 초기화 됩니다.
(SESSION.WAIT_TIMEOUT)

  연결 취소율(POC) =
  (
      Aborted_clients * 100 / Connections
  )

  연결이 취소되는 경우(강제 종료됨)
  (
      - 종료(exit) 되기전 mysql_close() 가 없는 경우
      - wait_timeout 이나 interactive_timeout 시간까지 아무런 요청(쿼리)
        이 없는 경우
  )

기본 값은 interactive_timeout 과 마찬가지로 28800(8시간) 초로 설정되어
있는데, 역시 너무 관대한(?) 설정이라고 할 수 있습니다.

앞에서 연결 취소율(POC)을 계산해 보면, MySQL 서버가 어느 정도 비율로 강제
종료하는지 알 수 있습니다.

예를 들어 POC 가 1 % 이라면, 100 개의 커넥션당 하나 정도는 mysql_close()
없이 강제 종료(exit)되고 있다는 의미입니다.

이 값이 0 %에 가까울수록 좋습니다. 이 의미는 클라이언트 프로그램에서
모두 정상적으로 종료했다는 의미입니다.


2. 연결 취소율(POC)과 connection life time

2-1. 연결 취소율(POC)

연결 취소율 값이 지나치게 높으면, wait_timeout 설정이 너무 짧거나,
대부분 client 프로그램이 exit(종료)를 정상적으로 하지 않은 경우이므로 반드시
튜닝이 필요합니다.

특히 서버가 매우 바쁜 경우는, 이 wait_timeout 시간을 짧게 설정하여 불필요한
커넥션을 모두 제거해 줘야합니다(메모리 한계 문제).

  wait_timeout 튜닝이 필요한 경우
  (
      - 보통 POC(연결 취소율)가 1 % 이상인 경우
      - 아주 바쁜 서버에서는 튜닝전 0.01 % 이상인 경우
      - 기타 튜닝 기준에 따라 다름
  )

주의할 점은,

client 프로그램(예: PHP)을 수정하지 않는 상태에서, wait_timeout 을 튜닝하면
튜닝전에 비해서 POC 가 더 올라가야 정상입니다. 이 의미는 비정상적인 커넥션을
반환하는 비율이므로, 정상적인 서비스하에서 이 값이 올라가야 함을 이미합니다.

  warning
  (
      이하 다루는 'life time' 이나 '임계 life time' 등의 내용은 반드시
      wait_timeout 튜닝이 필요한 경우에 해당됩니다.

      만약, wait_timeout 튜닝이 필요하지 않다면 이하 내용을 필요하지 않습니다.
  )


그럼 이제 wait_timeout 값을 계산해 봅시다.

이 값을 계산하기 위해서 mysqld 로그 파일을 분석해야 하는데,
불행히도 시간 기록이 1 초 간격으로 기록되어 있어서 접속이나 close 또는
각 쿼리 시간 통계를 구하기 어렵습니다.
(표본을 하루 단위로 축출하여 계산할 경우, 좀더 정확한 자료가 됨)

따라서, 현재 MySQL 서버의 STATUS 통계 자료를 이용하는 것도 하나의 방법입니다.

  통계를 얻는 방법
  (
      mysql> tee /path/to/mysql.status.txt
      mysql> SHOW STATUS;

      or
      shell> mysqladmin [OPTIONS] extended-status > mysql.status.txt
  )

  주요 MySQL STATUS
  (
      Aborted_clients
      Connections
      Max_used_connections
      Slow_queries
      Questions
      Uptime
      ...
  )

직접적인 평균 wait_timeout 값을 구할 수 없기 때문에,
각 커넥션을 일렬로 늘어놓은 상태의 평균적인 커넥션 life time 값을
구해야 합니다.


2-2. connection life time

'life time' 은 하나의 커넥션이 연결된 후 완전히 close 될 때까지 시간을 말하며,
필자가 자의적으로 붙인 용어입니다.

즉, 여기에서의 life time 은 하나의 커넥션이 살아있는 동안의 시간을 말하며,
이 시간에는 쿼리 실행, connection idle, wait timeout, interactive timeout 등등이
모두 포함되어 있는 time을 말합니다.

  조건
  (
      connection idle ==> wait time out
      interval of connection ==> 0
  )


  가정
  (
      +----------------------------+------------------->+--------->
      |connection                  |               close|connection
      |<-- queries execute time -->|<-- wait timeout -->|
      |<------------- connection life time ------------>|
  )

  life time (하나의 커넥션당 평균)
  (
      = 쿼리 실행 시간(long query 포함)
      += { wait timeout | interactive timeout }
  )


  life time =
  (
      connection
      [ wait time out ]
      [ += 1'th query execute time ]
      [ += wait time out]
      [ += 2'th query execute time ]
      [ += wait time out]
      [ ... ]
      [ += wait time out]
      [ += n'th query execute time ]
      [ += wait time out]
      close
  )


  커넥션 관련 통계
  (
      cps = Connecions / Uptime    ; // 초당 커넥션 수
      spc = Uptime / Connections   ; // 커넥션 주기(초)
      cpq = Question / Connections ; // 커넥션당 요청 쿼리 수
  )

실제 life time 은 mysqld 로그 파일을 분석해봐야 하는데,
이것 역시 상당히 까다롭습니다(exit 시간 기록이 없기 때문).

따라서, 여기에서는 '실제 life time'이 아닌 가상의 '임계 life time'
를 구해서 wait time out 설정에 중요한 자료로 활용하고자 하는 것이
이 문서의 본 내용입니다.


3. 임계 life time

모든 커넥션이 close 되지 않는다는 가정하에서, 즉 모든 커넥션이
한번 접속후 계속적으로 살아있다는 가설을 내리고,
한계 도달 N sec 를 구해서 이에 대한 평균값(AVG)을 구해보면
이 평균값이 커넥션당 '평균 임계 life time'이 됩니다.

바꾸어 말하면,
모든 커넥션들을 꼬리에 꼬리를 무는 가상의 평균적인 일직선으로 만들어
놓고, 한계 도달 N sec 를 구하는 방법입니다
(커넥션과 커넥션 간격을 0으로 봄).

  문제가 되는 경우'
  (
      동시 접속 connection 수(Y)
      |
      + Max connection ------+------+------
      |                     '|      |
      |                    ' |      |
      |                   '  |      |
      |                 .    |      |
      |               .      |      |
      |             .        |      |
      | . . . . . '          |      |
      |                      |      |
      +----------------------+------+-------> X sec
                             N sec
      * 시간이 지남에 따라 급격한 기울기(오목한 분포)
  )

  잘 튜닝된 경우
  (
      동시 접속 connection 수(Y)
      |
      + Max connection ------+------+------
      |                       . ' ' |
      |                     .       |
      |                   '         |
      |                 '           |
      |               .             |
      |             .               |
      | . . . . . '                 |
      |                             |
      +----------------------+------+-------> X sec
                                    N sec
      * 시간이 지남에 따라 완만한 기울기(볼록한 분포)
  )


  가정
  (
      동시 접속 connection 수(Y)
      |
      + Max connection -------------+------
      |              |           . '|
      |              |       . '    |
      |              |   . '        |
      |              + '            |
      |          . ' |              |
      |      . '     |              |
      |  . '         |              |
      |              |              |
      +--------------+--------------+-------> X sec
                   (N+1)/2          N sec
      * 커넥션 간격을 0으로 봄(직선적 기울기)
  )


  한계 도달 N sec 계산법
  (
      1 sec : 1 * cps
      2 sec : 2 * cps
      3 sec : 3 * cps
      ...
      N sec : N * cps => max_connections or Max_used_connections
  )
  or
  (
      N sec : max_connections or Max_used_connections / cps
  )


  최대(최악 상태) 한계 도달 life times 분포와 그 평균값(AVG) 계산법
  (
      1 sec : 1 * cps'th connection : N sec (life time)
      2 sec : 2 * cps'th connection : N - 1 sec
      3 sec : 3 * cps'th connection : N - 2 sec
      ...
      N sec : N * cps'th connection(max) : N - (N -1) sec

      AVG   : (N + 1) / 2 sec (life time) ; // 임계 life time
  )

모든 커넥션이 각각(평균) 이 '임계 life time'와 같거나 더 클 경우
결국 N sec 에 도달해서 full connection 이 된다는 의미입니다.
그 반대로,
커넥션 평균 life time 이 임계 life time 보다 작으면 N sec 이후에서
full connection 이 된다는 결론이 나옵니다.

이것은 mysqld 가 최악의 상태를 말하며, 아주 바쁜 MySQL 서버이거나
아주 바쁜 시간대(rush hour)에 충분히 이런 상황이 될 수 있다는 것을
반증합니다.

이 '임계 life time' 을 구해서 wait_timeout 설정하는데 중요한 자료로
활용하는 것이 본 글의 목적이며, 이 '임계 life time'을 다른 변수값들과
서로 보정하여 최종 wait_timeout 으로 설정하는 내용입니다.


  현재 최대(최악) 예상 임계 life time 계산(EXP)
  (
      N sec = max_connections / cps
            = max_connections * spc
            = max_connections * Uptime / Connections

      임계 life time(EXP)
            = (N + 1) / 2
  )


  현재 평균 임계 life time 계산(CUR)
  (
      N sec = Max_used_connections / cps
            = Max_used_connections * spc
            = Max_used_connections * Uptime / Connections

      임계 life time(CUR)
            = (N + 1) / 2
  )

  지난 과거 최대 임계 life time 계산(PAS)
  (
      N sec = Max_used_connections / maximum of cps
            = Max_used_connections * minimum of spc

      임계 life time(PAS)
            = (N + 1) / 2
  )

  지난 과거 유추 최대 임계 life time 계산(POL)
  (
      N sec = max_connections / maximum of cps
            = max_connections * minimum of spc

      임계 life time(POL)
            = (N + 1) / 2
  )


3-1. 현재 최대(최악) 예상 임계 life time(EXP)

이 값은 MySQL 서버가 시작후 현재까지의 초당 평균 커넥션 수('이하 'cps')
를 기준으로 계산할 때, full connection 인 max_connections 에 도달할 때의
평균 임계 life time 입니다.

즉 모든 커넥션은 각각 절대로 이 EXP 보다 크면 안된다는 의미가 됩니다.
(한계점이므로)

실제로 (wait_timeout > EXP) 경우는 wait_timeout 효과를 기대하기 어렵습니다.

  예를 들어
  (
      wait_timeout = 120
      EXP          =  63
  )

이와 같은 경우가 많은데, 이것을 분석(해석)해 보면,
모든 커넥션의 평균 임계 life time 이 64 초인데 굳이 120 초까지 기다려서
close 하는 경우가 거의 없다는 의미가 됩니다. 물론 평균적인 계산할 때의
얘기입니다.

따라서 최소한 wait_timeout 은 EXP 와 같거나 이 값보다 작게 설정해주는 것이
효과가 있습니다.(크면 별 효과를 기대하기 어려움)


3-2. 현재 평균 임계 life time (CUR)

이 값은 현재까지 최대 동시 접속 커넥션(Max_used_connections)에 도달할 때의
평균 임계 life time 입니다.

즉 life time 이 현재 추세로, 평균적으로 진행할 때의 임계 life time 입니다.

EXP 보다 항상 작거나 같습니다. 서로 같은 경우는 지난 과거에 벌써
full connection 까지 도달했다는 의미가 됩니다.

이 값은 단지 평균적인 현재 추세를 알아보는데 유용합니다.

그런데,

EXP 와 CUR 모두 현재 평균적인 추세에 대한 life time 입니다.
모든 프로그램이 그렇듯이 평균적인 작동원리는 설정은 상당히 위험합니다.
즉 최악의 상태까지 고려해서 프로그램에 임해야 한다는 것입니다.

따라서, EXP와 CUR 값보다 더 작은 임계 life time 을 구해야 하는데,
이것은 지난 과거에 가장 바빴던 cps 로 계산한 POL 해야 합니다.


3-3. 지난 과거 최대 임계 life time (PAS)

지난 과거중에서 최대 cps 값을 선택하여 계산할 때의 임계 life time 입니다.
이 값은 다른 임계 life time 보다 항상 작습니다.

과거중에서 최대 cps 구하는 방법이 조금 까로운데 이것은 매 시간대마다 또는
rush hour 에 체크하여 그 통계의 결과치를 구해야 합니다.

만약 최대 cps 를 구하기 어려우면 현재 평균 cps * (1.5 ~ 2.0) 정도로 계산하면
됩니다.

이 PAS 값은 wait_timeout 값을 구하는데 결정적인 자료로 쓰이며,
CUR 의 보정값이라고 생각하면 됩니다.


3-4. 지난 과거 유추 최대 임계 life time (POL)

EXP 가 현재 평균 cps 값으로 계산한 임계 life time 에 반해서, POL 은 PAS 와
같이 과거중 최대 cps 값으로 계산한 임계 life time 입니다.

즉,
EXP 는 평균적 cps 에 의해서 각 커넥션을 일직선으로 늘어 놓는데 반해서,
POL 은 최대 cps 에 의해서 각 커넥션을 일직선으로 늘어 놓은 상태에서 계산한
life tiem 값입니다.

이 값도 PAS 와 같이 wait_timeout 값을 구하는데 결정적인 자료로 쓰이며,
EXP 의 보정값이라고 생각하면 됩니다.


4. 최종 임계 life time(LPT) 계산(예제)

실제 예를 들어 보겠습니다.

  ex1
  (
      max_connections      = 100
      Max_used_connections =  13
      AVG of cps           = 0.1
      MAX of cps           = 0.3
  ); // 매우 바쁘지 않지만 과거 어느 순간에 극대로 바빴던 경우

  ex2
  (
      max_connections      = 100
      Max_used_connections =  92
      AVG of cps           = 0.8
      MAX of cps           = 1.1
  ); // 비교적 바쁜 서버로써 거의 한계점에 도달하고 있는 경우

  ex3
  (
      max_connections      = 100
      Max_used_connections = 100
      AVG of cps           = 2.4
      MAX of cps           = 2.7
  ); // 아주 바쁜 서버로 과거에 이미 한계점에 초과한 경우

+------+------+------+---------+-----------+-------+-------+
| 구분 |  EXP |  POL |         |  CUR (C%) |  PAS  | (DEF) |
|------+------+------+---------+-----------+-------+-------|
| ex1  |  201 |  167 |         |  66 (33%) |   22  |   44  |
|------+------+------+---------+-----------+-------+-------|
| ex2  |   63 |   46 |         |  58 (92%) |   42  |   16  |
|------+------+------+---------+-----------+-------+-------|
| ex3  |   21 |   19 |         |  21(100%) |   19  |    2  |
+------+------+------+---------+-----------+-------+-------+

* C%  : (CUR * 100 / EXP) ; // 평균 cps 에 대한 임계 도달 사용율
* DEF : CUR - PAS ; // 편차

서버가 바쁠수록 EXP 나 CUR 값이 점점 작아지고, C% 값은 점점 커집니다.

각각 max_connections이 서버의 한계라는 설정에서 EXP 나 CUR 값이 10 보다
작으면 서버의 증설이 필요하다는 것을 의미합니다.
(slow query 10 sec 에 기준을 둔다면)

여기에서 중요한 임계 life time 은 PAS 값인데,
이 PAS 값은 제일 바쁜 상태가 계속된다는 가정이므로 다른 값보다 항상
제일 작습니다.

실제 위의 예를 보면,
ex1이 ex2 보다 평균적으로 더 바쁘지 않지만 PAS 값이 더 작습니다.
이 의미는 과거의 어느 순간에 ex2 보다 더 바빴다는 증거이고 앞으로
그럴 가능성이 있다는 의미입니다.

DEF 값이 크다는 의미는 평균과 최대치의 life time 의 차가 크다는 의미로
서버가 바쁠때와 그렇지 않을때의 차가 크다는 의미입니다.

또한 PAS 값이 10 보다 작으면 서버 증설 필요성이 상당히 높다는 의미가
됩니다. (slow query 10 sec 에 기준을 둔다면)

EXP, POL, CUR, PAS 중에서 튜닝할 임계 life time 값을 선택해야 하는데,
POL 이나 PAS 값 중에서 하나를 선택하면 됩니다.

POL 값을 선택할 경우는 매우 관대한 정책(설정)이 될 것이고, PAS 값을
선택하면 매우 제한적인 설정이 됩니다.

주의할 점은 선택한 임계 life time 이 10 보다 작으면 서버가 아주 바쁜
상태에 목표를 두고 튜닝해야 하므로 주의해야 합니다.

여기에서는 편의상 PAS 값을 선택하겠습니다.

그런데 ex1 같은 경우는 DEF 편차가 상당히 큰데, ex1 비교적 그리 바쁘지
않으므로 그 다음으로 작은 CUR 값을 선택하는 것이 좋습니다.

만약 서버가 전체적으로 비교적 바쁘다고 생각하면 제일 작은 PAS 값을
선택하면 됩니다.

+------+----------------------------------+
| 구분 |  최종 임계 life time (LPT)       |
|------+----------------------------------|
| ex1  |            66                    |
|------+----------------------------------|
| ex2  |            42                    | 
|------+----------------------------------|
| ex3  |            19                    |
+------+----------------------------------+


5. wait_timeout 계산 및 보정

이제 life time 이 결정되었으니 wait_timeout 값을 설정해 봅시다.

앞서 얘기했듯이 life time 은 각 커넥션을 평균적으로 일직선상에
놓았을 경우, 한계 도달 시간을 의미합니다.

즉 각 커넥션은 평균적으로 이 life time 값을 넘어서면 안된다는 의미
입니다. (max_connections 값이 이미 정해진 한계이기 때문에)

LPT 값이 19 이라면(ex3의 경우),

  cpq = 8(예)
  (
      Questions / Connections
  ); // 커넥션당 평균 쿼리 요청수

  LPT =
  (
      (connection)
      (
          [ = wait time out]
          [ += 1'th query execute time ]
      )
      (
          [ += wait time out]
          [ += 2'th query execute time ]
      )
      (
          [ ... ]
      )
      (
          [ += wait time out]
          [ += n'th query execute time ] ; // n => cpq
          [ += wait time out]
      )
      (close)
  ) < 19

이런 조건식이 나옵니다.

그리고
하나의 쿼리가 실행한 시간과 그 다음 쿼리까지의 시간을 평균적으로
계산하면 다음과 같습니다.
  
  2.4 sec = 19 / 8 = LPT / cpq

보통 하나의 쿼리가 실행하는 시간은 상당히 짧은 0.001 ~ 2.0 sec 정도
되는 것이 보통입니다.(물론 예외도 있음)

그러면 대충 wait time out 값을 계산 또는 짐착할 수 있습니다.
즉 평균적으로 2.4 초 보다 항상 작다는 결론이 나옵니다.

그러나

이 값을 곧바로 wait_timeout 값으로 설정하면 상당히 위험합니다.
이 값은 어디까지나 평균적인 수치일 뿐 편차에 대한 고려가 전혀 없습니다.

예를 들어, 각 커넥션마다 쿼리가 하나이면 상관은 없지만,
다음과 같은 경우가 상당히 많기 때문입니다.

  life time
  (
      connection
      (
          = wait time out                 ; // 0.1 sec
          = 1'th query execute time       ; // 0.4 sec
      )
      (실제 쿼리에 상관없는 작업 시간 = 3.1 sec)
      (
          += wait time out                ; // 3.1 sec > 2.4 sec
          += 2'th query execute time      ; // 0.2 sec
      )
      close
  ) < 19 ; // 예제

따라서 앞에서 계산한 2.4 sec 는 실제로 wait_timeout 에 적용하기에는
매우 적절하지 않습니다.

결론적으로

하나의 커넥션이 최소한 하나 이상의 쿼리가 있는 경우가 거의 대부분이므로
이 점을 고려서 계산하면 다름과 같은 최종적인 계산식이 나옵니다.

  wait_timeout
  (
      = LPT - (LPT / cpq)
      = LPT - (LPT * Connections / Questions)
  )
  * 단) LPT > 10
  * 단) cpq > 1
  * 단) wait_timeout > 10 (한계값, slow query 고려)



+------+-------+----------+--------------------+---------+
| 구분 |  LPT  |  cpq(예) |    wait_timeout    |   AVG   |
|------+-------+----------+--------------------+---------|
| ex1  |   66  |     5    |         53         |    59   |
|------+-------+----------+--------------------+---------|
| ex2  |   42  |     7    |         36         |    39   |
|------+-------+----------+--------------------+---------|
| ex3  |   19  |     9    |         17         |    18   |
+------+-------+----------+--------------------+---------+


이 wait_timeout 은 상당히 제한적인 정책입니다.
좀더 관대한 설정을 원한다면 LPT 값을 wait_timeout 값으로 설정하거나
LPT와 계산한 wait_timeout 과 평균값(AVG)을 최종 wait_timeout 값으로
설정하면 됩니다.


6. 결과 확인

이제 최종적으로 wait_timeout 값이 정해졌므로 이 값을 서버에 적용해
봅니다.

  적용 예
  (
      shell> safe_mysqld ... -O wait_timeout=17 &

      or

      [mysqld]
      ...
      set-variable    = wait_timeout=17
  )


튜닝전에 비해서 연결 취소율(POC)이 더 올라가야 정상입니다.
이 의미는 비정상적인 커넥션을 반환하는 비율이므로, 성공적인
튜닝이라고 할 수 있습니다.

mysqldump 의 사용법

테이블 설계 시 유의 사항

1. 반드시 Primary Key를 정의하고 최대한 작은 데이터 타입을 선정한다.

로그 성 테이블에도기본적으로 PK 생성을 원칙으로 함

InnoDB에서 PK는인덱스와 밀접한 관계를 가지므로 최대한 작은 데이터 타입을 가지도록 유지

2. 테이블 Primary Key는 auto_increment를 사용한다.

InnoDB에서는 기본키 순서로 데이터가 저장되므로, Random PK 저장 시 불필요한 DISK I/O가 발생 가능

InnoDB의 PK는절대 갱신되지 않도록 유지

(갱신 시 갱신된 행이후 데이터를 하나씩 새 위치로 옮겨야 함)

3. 데이터 타입은 최대한 작게 설계한다.

시간정보는MySQL데이터 타입 date/datetime/timestamp 활용

IP는INET_ATON(‘IP’), INET_NTOA(int) 함수를 활용

정수 타입으로 저장가능한 문자열 패턴은 최대한 정수 타입으로 저장

4. 테이블 내 모든 필드에 NOT NULL 속성을 추가한다.

NULL을 유지를 위한추가 비용 발생

(NULL 허용 칼럼을인덱싱 할 때 항목마다 한 바이트 씩 더 소요)

5. Partitioning을 적절하게 고려하여 데이터를 물리적으로 구분한다.

데이터 및 인덱스파일이 커질수록 성능이 저하되므로Partitioning 유도

PK 존재 시 PK내부에 반드시 Partitioning 조건이 포함되어야 함

인덱스 설계 시 유의 사항

1. 인덱스 개수를 최소화 한다.

현재 인덱스로Range Scan이 가능한지 여부를 사전에 체크

인덱스도 서버 자원을소모하는 자료구조이므로 성능에 영향을 줌

2. 인덱스 칼럼은 분포도를 고려하여 선정한다.

인덱스 칼럼 데이터의중복이 줄어들수록 인덱스는 최대의 효과를 가짐

하단 쿼리 결과 값이 1에 가까울수록(0.9이상 권고) 인덱스 컬럼으로 적합함

SELECT count(distinctINDEX_COLUMN)/count(*)

FROM TABLE;

3. 커버링 인덱스(Covering Index)를 활용한다.

쿼리 조건이 인덱스안에 포함된 경우 인덱스에서만 연산 유도

인덱스는 일반적으로 행전체보다 작으므로 불필요한 Disk I/O 회피 가능

“MySQL에서 커버링 인덱스로 쿼리 성능을 높여보자!!” 편 참고

4. 스토리지 엔진 별 INDEX 특성을 정확히 인지한다.

InnoDB에서데이터는 PK 순서로 저장되고, 인덱스는 PK를 Value로 가짐

MyISAM은 PK와일반 인덱스의 구조는 동일하나, Prefix 압축 인덱스를 사용

(MyISAM 엔진에서ORDER BY 시 DESC는 가급적 지양)

5. 문자열을 인덱싱 시 Prefix 인덱스 활용한다.

긴 문자열 경우Prefix 인덱스(앞 자리 몇 글자만 인덱싱)를 적용

CREATE INDEX IDX01 ON TAB1(COL(4), COL(4))

Prifix Size는앞 글자 분포도에 따라 적절하게 설정

(하단 결과가 1에가까울 수록 최적의 성능 유지, 0.9이상 권고)

SELECT count(distinctLEFT(INDEX_COLUMN,3))/count(*)

FROM TABLE;

6. CRC32함수 및 Trigger를 활용하여 인덱스 생성한다.

URL/Email같이문자 길이기 긴 경우 유용

INSERT/UPDATE발생 시 Trigger로 CRC32 함수 실행 결과 값을 인덱싱

CRC32 결과값을저장할 칼럼 추가 및 인덱스 생성

alter table user_tbl add email_crc intunsigned not null;

create index idx01_email_crc on user_tbl (email_crc);

InsertTrigger 생성

create trigger trg_user_tbl_insert

beforeinsert on user_tbl

for each row

begin

setnew.email_crc = crc32(lower(trim(new.email)));

end$$

UpdateTrigger 생성

create trigger trg_user_tbl_update

beforeupdate on user_tbl

for each row

begin

if old.email<>new.email then

setnew.email_crc = crc32(lower(trim(new.email)));

end if;

end$$

검색 쿼리

select *

from user_tbl

where email_crc = crc32(lower(trim('mail@domain.com')))

and email='mail@domain.com'

CRC32 결과가중복되어도, email값을 직접 비교하는 부분에서 중복이 제거됩니다.

7. 중복 인덱스 생성 회피

MySQL은 동일한인덱스를 중복 생성해도 에러를 발생하지 않음

Primary Key로구성된 칼럼과 동일한 인덱스를 생성하지 않도록 주의

출처 : http://dev.kthcorp.com/2012/05/15/mysql-table-index-design-tip/

1. master 쪽 my.cnf

[mysqld]

log-bin=mysql-bin

binlog_ignore_db        = mysql

binlog_ignore_db        = information_schema

binlog_ignore_db        = performance_schema

server-id       = 1

binlog_format=mixed

log-bin : 바이너리 로그를 사용하도록 한다.

binlog_ignore_db : 불필요한 db는 바이너리 로그에 저장 안되게 함

server-id : 서버번호

2. slave 쪽 my.cnf

server-id       = 2

master-host     =   <hostname>

master-user     =   <username>

master-password =   <password>

master-port     =  <port>

log-bin=mysql-bin

binlog_format=mixed

slave-skip-errors = all

replicate-do-db = [복제할 DB명]

server-id : 서버 번호 다른 서버와 중복 안되도록 한다

master-host, master-user, master-password, master-port, log-bin,binlog_format 

-> master 서버 접속 환경 설정을 한다. my.cnf 에 설정해도 되지만 mysql 쉘에서 CHANGE MASTER TO로 설정해도 된다. 당연히 CHANGE MASTER TO 로 할 경우 db를 재시작 하면 다시 설정해줘야 한다. 접속 설정은 my.cnf에 해놓고 추가적인 옵션(로그 파일 포지션)등은 CHANGE MASTER TO 명령으로 설정하자

3. 리플리케이션 절차

3-1. master db를 덤프, 또는 FLUSH TABLE 후 READ LOCK 을 건후 데이타 파일 복사(이건 비추임 LOCK 때문에 잠시 서비스 이용이 안되겠죠?)

mysqldump 를 할때 중요한 점은 아래 두 옵션을 반드시 적용해야 한다.(절대 잊으면 안된다!!!)

--master-data[=value]

--single-transaction

[참고]

--master-data 옵션은 dump파일에 CHANGE MASTER TO 로 바이너리 로그와 포지션등의 정보를 포함하도록 하는 명령이다. value로 사용되는 값은 1인 경우 CHANGE MASTER TO 를 명령으로 dump파일에 바로 포함하는 것이고 2인 경우에는 CHANGE MASTER TO 를 주석으로 dump 파일에 포함한다.

이 옵션이 사용 될 경우 [--lock-all-tables] 옵션이 자동으로 ON 되며 모든 테이블에 락이 걸리게된다.

이를 피하기 위해서 --single-transaction 옵션을 추가하면 전체 락이 걸리지 않지만 MyISAM 처럼 row level lock을 지원하지 않는 테이블 형식은 LOCK이 걸리니 이점 주의하자.

3-2. --master-data=1 로 했을 경우 dump 파일을 mysql에 적용하고 바로 start slave를 하면 되고 --master-data=2 일 경우에는 주속에 포함된 내용으로 아래 명령을 실행한 후 start slave를 하면 된다.

CHANGE MASTER TO  MASTER_HOST='마스터아이피', MASTER_USER='유저', MASTER_PASSWORD='비밀번호', MASTER_LOG_FILE='바이너리로그파일명', MASTER_LOG_POS=마스터_포지션넘버;

sql> START SLAVE UNTIL MASTER_LOG_FILE='바이너리로그파일명', MASTER_LOG_POS=마스터_포지션넘버;

4. 리플리케이션 운영

4-1. master 재시작, 다운등 종료시

master -> reset master

slave -> stop slave; reset slave; start slave;

4-2. slave 재시작, 다운등 종료시

slave -> stop slave; reset slave; start slave;

4-3. 연결이 끊어진 경우(보통 slave에 오류발생함)

master, slave 의 상태를 show master status; show slave status; 로 확인해서 오류부분을 잘 확인하자. 실행 방법은 4-1 master 재시작과 동일하다.

master -> reset master

slave -> stop slave; reset slave; start slave;

[참고]

reset master -> bin-log를 지운다.

reset slave -> slave에 저장된 bin-log 를 지우고 position 등의 값을 지운다.

4-4. bin-log 관리

bin-log 파일이 계속 커지므로 아래 와 같은 명령으로 로그를 삭제한다. 날짜는 알아서, 추가적인 옵션은 메뉴얼 찾아봐.

PURGE MASTER LOGS BEFORE DATE_SUB(NOW(),INTERVAL 3 DAY);