'팁&테크/MySQL'에 해당되는 글 23건

  1. 2016.05.23 MySQL 관련(메모)
  2. 2016.05.09 [MySQL] mysql_config_editor 사용법
  3. 2016.04.08 MySQL 5.7 설치 관련
  4. 2016.03.28 MySQL 5.7.9~이상 설치관련
  5. 2016.02.01 MySQL 수치형 데이타 최대값 구하기
  6. 2015.07.01 MySql 파티셔닝 정리
  7. 2014.06.25 [MySQL] time out(wait_timeout) 계산과 설정
  8. 2014.05.08 mysqldump 사용법
  9. 2013.03.20 MySQL 테이블과 인덱스 설계시 주의해야 할 13 가지
  10. 2012.09.12 MySQL 리플리케이션(간단정리)
2016.05.23 17:58

MySQL 관련(메모)

Prepared Statement는 5.1.21버전 이상에서 지원
Prepared Statement 사용 시 “?” 인자가 사용 시에도 캐싱됨
5.1.17에서는 “?”가 포함된 Prepared Statement 결과는 캐싱되지 않음
5.1.17 이전 버전에서는 모든 결과값이 캐싱되지 않음

MySQL shutdown 시에도 포트를 물고 있고 프로세스에는 없는 경우(유령ㅡㅡ;;)
MySQL SLAVE 리플리케이션 중에 mysqladmin 으로 shutdown 시 프로세서도 죽고 socket 파일, pid 파일도 삭제 되었는데도 포트가 그대로 열려있고(사용하는 프로세서는 공백) 
telnet localhost 3306 으로 접속하면 정상적으로 mysql에 접속되나 mysql 커맨드 라인은 socket 파일이 없어 접속이 안되는 난감한 경우가 발생함
정확한 원인은 파악되지 않았으나 unix 소켓 프로토콜은 정지 되었으니 tcp 프로토콜이 살아 있는 경우였음.
mysqladmin -uroot -p --port=3306 --protocol=TCP 
명령으로 정지시키니 정상적으로 모든 작업이 종료됨 

외래키 제한 설정
SET FOREIGN_KEY_CHECKS = 0; //외래키 제한 해제
SET FOREIGN_KEY_CHECKS = 1; //외래키 제한 설정


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 라벨명; 하면 해당 블럭을 탈출한다.


1
2
3
4
5
6
7
CREATE
    PROCEDURE 프로시저명()
    라벨명:BEGIN
        IF 조건 THEN
            LEAVE 라벨명;
        END IF;
    END$$
cs



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 = 잘못된 엔진이거나 엔진정의가 없으면 기본엔진 사용







Trackback 0 Comment 0
2016.05.09 17:49

[MySQL] mysql_config_editor 사용법

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=설정명


Trackback 0 Comment 0
2016.04.08 14:23

MySQL 5.7 설치 관련

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 = '비번';



Trackback 0 Comment 0
2016.03.28 18:08

MySQL 5.7.9~이상 설치관련

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. 이제 정상적으로 사용이 가능.


  

Trackback 0 Comment 0
2016.02.01 18:45

MySQL 수치형 데이타 최대값 구하기

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)




Trackback 0 Comment 0
2015.07.01 15:19

MySql 파티셔닝 정리

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;




Trackback 0 Comment 0
2014.06.25 09:50

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


좋은 글이 있어서 퍼옴

출처 : 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)이 더 올라가야 정상입니다. 이 의미는 비정상적인 커넥션을 반환하는 비율이므로, 성공적인 튜닝이라고 할 수 있습니다.


Trackback 0 Comment 0
2014.05.08 11:07

mysqldump 사용법

mysqldump 의 사용법

백업뿐만 아니라, 데이터의 이관이나 복제등과 같이 어떤 MySQL 서버에서 다른 서버로 데이터를 옮기는 경우
MySQL 패키지에 기본적으로 포함되어 있는 mysqldump라는 도구를 자주 사용하게 된다.

mysqldump 명령과 옵션의 기본 구성
mysqldump [DB 접속 정보] [dump 옵션] [dump 대상 옵션] > /tmp/dump_data.sql
mysqldump는 기본적으로 모든 출력물을 화면(stdout)으로 보내게 된다. 
그래서 이 결과를 백업용이나 데이터 이관용으로 사용하기 위해서는 표준 출력을 파일로 리다이렉션해서 저장해야 한다.

DB 접속 정보
  • --host : 데이터베이스 호스트 정보
  • --user : 접속 데이터베이스 계정
  • --password : 접속 데이터베이스 계정의 비밀번호
  • --port : 접속 포트 번호
  • --socket : 접속시 사용할 소켓 파일의 경로

dump 옵션
  • --single-transaction : 
    dump를 하나의 트랜잭션을 이용해서 실행함
    (InnoDB 스토리지 엔진을 사용하는 테이블에 대해서는 Lock없이 일관된 덤프를 받을 수 있음).
    자세한 내용은 아래 "일관된 데이터 덤프 받기" 참조
  • --flush-logs : dump를 시작하는 시점에 binary log를 rotation 시키도록 한다.
    Binary log를 증분 백업용으로 사용하는 경우, 풀백업과 증분 백업의 구분을 명확히 할 수 있다.
  • --master-data : 
    이 옵션이 명시되면, dump 파일의 헤더 부분에 CHANGE MASTER TO 구문을 포함시키며,
    이 구문에는 덤프 시작 시점의 Binary log 파일명과 위치 정보 및 호스트 정보를 포함하고 있다.
    이 값을 1로 설정하면 CHANGE MASTER TO 구문이 실제 실행 가능한 형태로 포함되며,
    2로 설정되면 SQL 코멘트 형태로 참조만 할 수 있도록 포함된다.
    가끔 Binary log가 활성화되지 않은 서버에서 실행 시 에러를 유발하기도 하므로
    반드시 먼저 테스트를 해볼 것을 권장한다.
  • --opt | --skip-opt : 
    opt 옵션은 여러 개의 옵션들을 묶어 놓은 별명과 같은 옵션이며
    (--add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert,
    --lock-tables, --quick, --set-charset) 옵션들을 포함한다.
    때때로(create db, table등을 포함할지 말지 제어시) mysqldump의 옵션을 상세하게
    제어하고자 할 경우에는 skip-opt 옵션으로 비활성화시킨 후, 필요한 것들만 직접 명시해주는 것이 좋다.
  • --quick : 
    일반적으로 mysqldump는 테이블의 데이터들을 모두 Client의 메모리에 모두 로딩한 후,
    파일에 쓰기를 시작하게 되는데, 이 옵션이 활성화되면 Client의 메모리에 버퍼링하지 않고
    바로 파일로 쓰거나 화면으로 출력하게 된다. 이 옵션은 opt 옵션에 포함되어서 자동으로 활성화되기 때문에,
    (큰 테이블이 있는 경우) skip-opt를 사용하는 경우에는 quick 옵션을 별도로 명시해줘야 한다.
  • --extended-insert : 
    mysqldump는 테이블의 데이터를 INSERT 문장 형태로 출력하게 되는데,
    이 옵션을 사용하면 확장 형태의 INSERT문장으로 데이터를 덤프하게 된다.
    이런 형태의 덤프는 다시 로드하는 작업을 빠르게 해줄 수 있다.
    (확장 형태의 INSERT 문장이라는 것은
    "INSERT INTO tab VALUES ('1','2'),('2','3'),('3','4');"와 같은 형태의 INSERT 문장을 의미한다.)
  • --add-drop-database : 
    덤프 파일의 내용에 "DROP DATABASE ..." 명령을 포함하지 않도록 한다.
  • --add-drop-table : 
    덤프 파일의 내용에 "DROP TABLE ..." 명령을 포함하지 않도록 한다.
  • --no-create-db : 
    mysqldump 의 옵션에 --databases 또는 --all-databases 옵션이 명시되지 않으면,
    기본적으로 "CREATE DATABASE ..." 명령이 포함되는데,
    종종 이 명령이 필요치 않은 경우가 있을 수 있으며 이 때에는 이 옵션을 활성화 해주면 된다.
  • --no-create-info : 
    덤프 파일의 내용에 "CREATE TABLE ..." 명령을 포함하지 않도록 한다.
  • --create-options : 
    덤프 파일에 "CREATE TABLE ..." 명령이 출력되는 경우,
    초기 테이블 생성시 사용했던 테이블 생성 옵션들을 모두 포함하도록 한다.
  • --no-data : 
    데이터는 덤프하지 않고 테이블의 구조만 덤프하도록 한다.
  • --hex-blob : 
    덤프 대상 테이블중에서 이진 값을 가지는 컬럼의 값들을 16진수 문자열로 출력한다.
  • --routines : 
    덤프시에 스토어드 프로시져와 함수를 출력하도록 한다.
  • --triggers : 
    덤프시에 트리거를 출력하도록 한다.


dump 대상 옵션
  • --all-databases : 
    이 옵션을 명시하면 현재 서버의 모든 데이터베이스를 덤프하게 된다.
    이 옵션으로 덤프를 받게 되면 기본적으로 "USE <데이터베이스명>;" 명령이 덤프 내용에 포함된다.
  • --databases [database1] [database2] ... : 
    이 옵션을 명시하면 현재 서버에서 명시된 데이터베이스들을 덤프하게 된다.
    이 옵션으로 덤프를 받게 되면 기본적으로 "USE <데이터베이스명>;" 명령이 덤프 내용에 포함된다.
  • [database1] : 
    별도의 옵션없이 mysqldump 명령의 마지막에 특정 데이터베이스명만을 명시하면
    해당 데이터베이스의 내용만을 덤프하게 된다.
    이 경우에는 "USE <데이터베이스명>;" 명령이 덤프 파일에 포함되지 않는다.
    그래서 데이터베이스를 덤프 받아서 동일 데이터베이스에 다시 적재하고자 하는 경우
    (덤프받은 파일을 동일 서버의 다른 데이터베이스명으로 적재하고자 하는 경우) "USE <데이터베이스명>;" 때문에
    작업이 쉽지 않은데 이런 경우에는 이 옵션으로 덤프를 받으면
    쉽게 동일 MySQL 서버에 다른 데이터베이스명으로 적재할 수 있다.
  • [database1] [table1] [table2] : 
    별도의 옵션없이 mysqldump 명령의 마지막에 여러개의 오브젝트명을 명시하면,
    mysqldump는 첫번째 오브젝트는 데이터베이스명, 그 다음부터는 테이블명으로 인식하게 된다.
    그래서 해당 데이터베이스에 있는 각 테이블들만을 덤프하게 된다.
  • --ignore-table :
    덤프할때 해당되는 테이블을 제외, 제외할 테이블 갯수만큼 옵션을 사용해야 함
    --ignore-table=db.table1
    --ignore-table=db.table2


일관된 데이터 덤프 받기
mysqldump를 이용하여 일관된 데이터를 덤프받고자 한다면,
lock-tables 옵션 또는 single-transaction 옵션을 사용해야만 한다.
간단히 이 옵션들의 사용에 따른 데이터 잠금 여부를 확인해 본 결과이다.
일반적으로 InnoDB의 경우에는 single-transaction을 사용하며,
MyISAM 의 경우에는 lock-tables 옵션을 사용해야 한다. (물론 MyISAM의 경우 일관된 백업의 의미는 없다)
InnoDB에서 single-transaction 옵션이 사용되면, table에 대한 lock을 걸지 않고 MVCC의 Read view를 이용하기 때문에
mysqldump 도중에도 trasaction (insert, update, delete)이 허용된다.

  • mysqldump --skip-add-locks --single-transaction --skip-lock-tables db1 db2 > test.dump.sql
    ==> 덤프 도중 다른 세션에서 insert, update, delete 가능
  • mysqldump --single-transaction  db1 db2 > test.dump.sql
    ==> 덤프 도중 다른 세션에서 insert, update, delete 가능
  • mysqldump --lock-tables  db1 db2 > test.dump.sql
    ==> 덤프 도중 다른 세션에서 insert, update, delete 불가능


위의 옵션들을 이용한 여러가지 목적 또는 용도로 mysqldump명령

MySQL 테이블 & 루틴 전부 덤프
  mysqldump --user=root --password --opt --single-transaction --hex-blob --master-data=2 --routines --triggers
               --default-character-set=utf8 --databases [데이터베이스명1] [데이터베이스명2] > `hostname`_`date '+%y%m%d'`.dump.sql

MySQL의 루틴만 덤프
  mysqldump --user=root --password --routines --no-create-info --no-data
                   --no-create-db --skip-opt [데이터베이스명] > `hostname`_`date '+%y%m%d'`.dump.sql


View의 덤프
View 는 기본적으로 Algorithm (Merge 또는 Temptable)에 관계없이 
독립적인 데이터를 가지지 않기 때문에 mysqldump로 받아도 실질적인 데이터를 가지지 않는다.
그래서 mysqldump로 --no-create-info 가 명시되지 않는 이상은 테이블과 동일하게 CREATE VIEW 
문장은 백업이 가능하다.


Trackback 0 Comment 0
2013.03.20 16:07

MySQL 테이블과 인덱스 설계시 주의해야 할 13 가지

테이블 설계 시 유의 사항

 

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/

Trackback 0 Comment 0
2012.09.12 19:57

MySQL 리플리케이션(간단정리)

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);

Trackback 0 Comment 0