MySQL해킹

어떤 새로운 Native　SQL함수를 추가하려는 경우나  새로운 데이터형을 추가하려는 경우 각각 DDL문과 DML문에 있어서 심볼로 먹힐려면 SQL파서를 개조하지않으면 SQL구문해석할 때에문법에러처리되게 된다

그래서 여기서부터는 SQL파서 개조에 대해서 이야기해 볼 까한다.

보통 SQL에서는 "SELECT ... FROM .. "등의 문법에 따라서 SQL문을 작성해서 서버에 송신하지만 여기에서는 "Hello World"라는 명령어를 새로운 SQL 명령어로서 MySQL에 추가해 볼 것이다.

또,   비교적 간단한 것으로 시스템 변수 (my.cnf에서 지정가능하고 SHOW VARIABLES에서 확인가능)의 추가,  상태변수( SHOW STATUS로 확인가능)의 추가에서도 소개해 볼까한다.


이런 해킹은 현실적으로 그다지 필요하지 않을지도 모르겠지만 MySQL를 업무로 다루는 기술자에게는 "몰라, 해킹않해!" 보다는  "해킹할 수 있는 지식도 있고 내부도 잘 알고 있지만  여러가지 이유로 공식바이너리를 사용해!" 라는 자세가 필요하다고 본다.

 

파티셔닝5

서브 파티셔닝

RANGE와 LIST일 때 각 파티션을 더 분할할 수가 있다.  매뉴얼에서는 이것을 서브파티션이라고 부른다.

예1>
CREATE TABLE ptrange( a INT, b INT, c CHAR(10) )
    PARTITION BY RANGE(a)
      SUBPARTITION BY HASH(a)
     (
       PARTITION pt0 VALUES LESS THAN ( 1000)
      (
          SUBPARTITION s0,
          SUBPARTITION s1
      ),
      PARTITION pt1 VALUES LESS THAN MAXVALUE
     (
         SUBPARTITION s2,
         SUBPARTITION s3
      )
    );

예2>
CREATE TABLE ptrange( a INT, b INT, c CHAR(10) )
    PARTITION BY RANGE(a)
      SUBPARTITION BY HASH(a)
         SUBPARTITIONS 2
        (
          PARTITION pt0 VALUES LESS THAN (1000),
          PARTITION pt1 VALUES LESS  THAN MAXVALUE
        );

MySQL의 파티셔닝에 대해서  정리해보았다.  꽤 유연하게 파일을 분할할 수 있다는 것을 알 수 있다.   한개의 테이블이 큰 경우에는  파티셔닝의 채용을 검토 해 보자.

파티셔닝4

파티셔닝의 방법 

MySQL의 파티셔닝방법에는  RANGE, LIST, HASH, KEY의 4가지 방법이 제공된다.
이것은 PARTITION BY로 지정한다.

• RANGE: 지정된 컬럼 값에 의해서 파티셔닝을 한다.  컬럼명을 지정하는 경우에는 정수형이어야하고 함수식을 지정하는 경우에는  정수를 리턴하는 함수이어야 한다.  조건의 판정에는 VALUES LESS THAN를 사용한다. MAXVALUE는 최대값을 의미한다.

CREATE TABLE ptrange( a INT)

   PARTITION BY RANGE(a) (

     PARTITION pt0 VALUES LESS THAN ( 1000 ), 

     PARTITION pt1 VALUES LESS THAN MAXVALUE ); 

• LIST: 지정된 값에 의해서 파타셔닝을 한다.  컬럼명만을 지정하는 경우에는 정수형이어야하고 함수식을 지정하는 경우에는 정수를 리턴하는 함수이어야 한다.  조건을 지정할때에는 각 파티션정의에 VALUES IN(값 [값,...])을 사용한다. 

 CREATE TABLE ptlist( a INT UNSIGNED)

   PARTITION BY LIST(a) (

     PARTITION pt0 VALUES IN (1,4),

     PARTITION pt1 VALUES IN (2,5),

     PARTITION pt2 VALUES IN (3,6)  );

• HASH: 지정된 값에 따라서 MySQL이 행을 자동으로 나눈다.  컬럼명만을 지정하는 경우에는 정수형이어야하고 함수식을 지정하는 경우에는 정수를 리턴하는 함수이어야 한다.  

CREATE TABLE pthash(a INT, b INT, c CHAR(10) ) 

     PARTITION BY HASH(a) 

     PARTITION 2;

• KEY: 지정된 값에 따라서 MySQL이 행을 자동으로 나눈다.  0개이상의 컬럼명(함수)를 지정할 수 있다.  값을 지정하지 않은 경우 테이블의 PRIMARY KEY가 자동적으로 채용된다. 

CREATE TABLE ptkey(a INT, b INT, c CHAR(10) ) 

    PARTITION BY KEY(a, b, c)

    PARTITIONS 2;

 CREATE TABLE ptkey( a INT PRIMARY KEY, b INT, c CHAR(10) ) 

    PARTITION BY KEY()

    PARTITIONS 2;

파티셔닝3

파티셔닝의 제한

5.1.12-beta에서 다음과 같은 제한이 있다.

• 파티셔닝된 테이블은 스토리지엔진이 같아야 된다. 
• 외부키 제약은 사용할 수 없다. 
• FULLTEXT인덱스를 사용할 수 없다. 
• GIS컬럼을 사용할 수 없다. 
• 임시테이블, MERGE스토리지엔진, CSV스토리지엔진은 파티셔닝할 수 없다. 

파티셔닝의 지정 

파티셔닝의 지정은 다음과 같이 한다.

CREATE TABLE 테이블명 (컬럼 정의) 

 PARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY(expr) }

[ PARTITIONS 파티션갯수]

CREATE TABLE 테이블명(컬럼정의)
 PARTITION BY { RANGE(expr) | LIST(expr) }
[ SUBPARTITION BY { [LINEAR] HASH() | [LINEAR] KEY() }
    [ SUBPARTITIONS 서브파티션갯수 ]
]
(파티션정의 [, 파티션정의] ... )

파티션정의:
   PARTITION 파티션명
     [ VALUES { LESS THAN (expr) | MAXVALUE } ] <--RANGE인 경우
     [ VALUES IN (값 리스트) ]   <--LIST인 경우
     [ 테이블 옵션]
     [ TABLESPACE [=] (NDB Cluster의 테이블영역명) ]
     [ NODEGROUP [=] MySQL Cluster의 node_group_id ]
     [(서브 파티션정의 [, 서브파티션정의 ]...) ]

서브파티션정의:
   SUBPARTITION 서브파티션명
     [ 테이블옵션 ]
     [ TABLESPACE [=] (NDB Cluster의 테이블영역명) ]
     [ NODEGROUP [=] MySQL Cluster의 node_group_id ]

테이블옵션:
  보통 CREATE TABLE문에서 지정할 수 있는 ENGINE= 키워드등의 테이블옵션. 파티션정의구문에서는 다음을 사용할 수 있다.
       [STORAGE] ENGINE [=] 스토리지엔진명
       COMMENT [=] 코멘트
       DATA DIRECTORY [=] '.MYD파일의 저장디렉토리'
       INDEX DIRECTORY [=] '.MYI파일의 저장디렉토리'
      MAX_ROWS [=] '테이블에 들어가는 최대 행수'
      MIN_ROWS [=] '테이블에 들어가는 최소 행수'
     

파티셔닝2

파티셔닝 개요

파티셔닝에서는 데이터를 여러개의 기록영역에 나누어서 기록하는 것을 의미한다.
MySQL의 파티셔닝은 수평 파티셔닝(horizontal partitioning)이라고 불리우는 방법으로 수행한다.

이것은 어떤 컬럼의 값에 따라서  레코드를 기록하는 장소를 바꾸는 방법이다.
이외에도 수직형 파티셔닝(vertical partitioning)처럼 나누는 방법도 있지만 MySQL은 서포트하고 있지 않다. (현재는 잘 모름.. )

다시말하면 수직형 파티셔닝에서는  "이 컬럼은  이쪽 파티션에 기록하고 저컬럼은 저쪽 파티션에 기록한다" 처럼 컬럼에 착목한 분할이 된다.


파티셔닝기본동작

파티셔닝이 어떻게 동작하는지  사용해보면서 알아보자.

 MyISAM테이블  mycom의 작성 

mysql> CREATE TABLE mycom( a INT) ENGINE=MyISAM
     PARTITION BY RANGE(a) (
         PARTITION pt0 VALUES LESS THAN (1000) ENGINE=MyISAM, 
         PARTITION pt1 VALUES LESS THAN (2000) ENGINE=MyISAM,
        PARTITION pt2 VALUES LESS THAN  MAXVALUE  ENGINE=MyISAM) ;


 이 때 데이터베이스의 디렉토리에는 다음과 같은 파일이 작성된다.

데이터베이스 디렉토리의 내용(MyISAM테이블)
mycom#P#pt0.MYD
mycom#P#pt0.MYI
mycom#P#pt1.MYD
mycom#P#pt1.MYI
mycom#P#pt2.MYD
mycom#P#pt2.MYI
mycom.frm
mycom.par

다시 말하면,  어떤 MyISAM 테이블을 파티셔닝하면  다음과 같은 파일이 생성된다.

테이블.frm
테이블.par
테이블#P#파티션이름.MYD(데이터)
테이블#P#파티션이름.MYI(인덱스)

.par파일은 바이너리 파일로  파티셔닝 정보를 기록한다.

.par파일의 내용 (일부)
shell# hexdump -C mycom.par
00000000 08 00 00 00 7e 7d 3a 00 03 00 00 00 09 09 09 00 
00000010 0c 00 00 00 70 74 30 00 70 74 31 00 70 74 32 00 
00000020

 그럼, InnoDB의 경우는 어떻게 될까?   아까전에 만든 mycom테이블을 ALTER TABLE문으로 InnoDB로 변경해 보자.

데이터베이스디렉토리는 다음과 같은 파일이 생성된다.

mycom.frm
mycom.par

InnoDB  형식에 따라서  테이블의  데이터와 인덱스부분은  InnoDB의  테이블 영역으로 이동되었다.   그럼 InnoDB의 --innodb_file_per_table옵션을 사용했을 때에는 파티셔닝은 어떻게 될까?  실행해보면  파일은 다음과 같이 되었다.

mycom#P#pt0.ibd
mycom#P#pt1.ibd
mycom#P#pt2.ibd
mycom.frm
mycom.par


이론 그대로이다.

파티셔닝1

파티셔닝은  MySQL 5.1에서 추가된 기능이다.  이것은  한개의 테이블을 여러개로 나누어 사용하는 기능으로 I/O분산을 위한 것이다.

I/O를 분산시킨다는 의미로는 MySQL에서 다음과 같은 방법이 제공되었다.

• 데이터베이스 디렉토리를 심볼릭 링크로 해서 분산 배치 
• InnoDB에서는 테이블스페이스 파일(innodb_data_file_path)를 여러개의 파일로 분할 
• InnoDB에서는 테이블단위로 파일을 작성(innodb_file_per_table)
• MyISAM에서는 MERGE 테이블 
• MyISAM에서는 RAID테이블( MySQL5.0에서 페지: 옛날 OS에서는 한개의 파일 사이즈가 4G제한이 있었지만 현재는 그런 제한이 없어졌으므로 자연스럽게 없어짐... )
• MyISAM에서 파일단위로 심볼릭 링크를 사용해서 분산배치 

그러나, 이것은  어떤 특정 스토리지엔진에서만 가능하다던지 또 사용방법도 각각 다르므로 범용적이라고 할 수 없었다.   그중에는 서버의 설정작업등의 변경작업도 필요하므로 관리자는 별도로 지식을 익혀두어야 했다.

MySQL5.1에서 도입된 파티셔닝기능은 MyISAM과 InnoDB둘다 동작하는 공통의 인터페이스(사용방법)이어서 통일감도 있고,  조작은 SQL문의 실행만으로 완료할 수 있으므로 관리자도 이용자도 편하게 되어졌다. 

파티셔닝을 이용하는 것으로 지금까지 보다 편하게  I/O분산배치를 시행할 수 있게 되었다.

다만,  파티셔닝이 지금까지의 수법을 완전히 대체할 수 있느냐라고는 할 수 없다.
예를 들어 innodb_data_file_path등은 DB셋업할 때 고려해야할 사항이다.

또 MERGE테이블에서는  myisampack으로 각각의 테이블을 작게 할 수 있으므로  이미 존재하는 테이블을 분할 할 때는 파이셔닝보다 편리할 때도 있다.

각각 때와 장소에 맞게 적절히 사용할 필요가 있다.

파티셔닝기능이 유효인지 확인하는 방법에는 SHOW VARIABLES로  have_partitioning의 값을 보면 알 수 있다.   이 값이 YES이면 파티셔닝기능이 들어있다라고 할 수 있다.

만약 값이 NO로 되어 있거나 원래 have_partitioning자체가 나타나지 않은 경우 사용하고 있는 mysqld 바이너리에 파티셔닝이 포함되어 있지 않는 것이다.

서버 소스를  configure할 때 --with-partition를 지정해서 컴파일하면 될 것이다.

Replication : 행 레벨 replication

MySQL 5.1.5-alpha에서 행 레벨 replication이 도입되었다.   구현 아이디어로는  바이너리 로그의 기술을 변경하자라는 것이다.

행 레벨 replication은 종래의 replication하고 비교해서  다음과 같은  잇점이 있다.

• UDF나 FOUND_ROWS(), LOAD_FILE(), SYSDATE(), USER(), UUID()를 사용한 경우에도 replication이 가능
• 복잡한 SQL문을 slave가  순서대로 실행처리하는 것보다도 빠를 것이다라고 생각되어짐. 

단점으로는 다음과 같은 것이 있다.

• 로그양이 증가한다. 
• 처리가 큰 경우 Rollback된 데이터도  로그되는 경우가 있다. 
• 인간이 바이너리로그를 보아도 판별되지 않는다. 
• Slave가 어떤 SQL문을 받아 처리하는 지 알 수가 없다. 

소스로 빌드하는 경우는 configure옵션에서 다음과 같이 지정하면 행 레벨 replication은 포함되지 않는다.

shell$ ./configure --without-row-based-replication 

행 레벨 replication의 도입(binlog_format) 

행 레벨  replication을 도입하려면 binlog_format변수에 바이너리로그 기술방법을 지정할 필요가 있다.  mysqld의 옵션처리하려면  --binlog-format이다.

mysqld --binlog-format={ROW|1|STATEMENT|2|MIXED|3}

SET문으로도 변경가능하다.


mysql>SET GLOBAL binlog_format=2;

다음과 같은 경우는 SET문으로 동적으로 포맷을 변경할 수 없다.

• 스토어드 함수나 트리거 내부
• NDB가 유효한 경우 
• 세션이  ROW베이스로 되어있고  임시 테이블을 사용하고 있을 경우 

1이나 STATEMENT는  종래와 마찬가지로  바이너리 로그에는 SQL문으로 변경사항을 기록한다.    행 레벨 replication으로 될 수 없다.
다음과 같은경우 replication할 수 없다.

• UDF를 사용했을 경우
• 다음과 같은 함수를 사용했을 경우 FOUND_ROWS(), LOAD_FILE(), USER(), UUID()
• SYSDATE() 를 --sysdate-is-now옵션이 없는 상태에서 사용한 경우 

2또는 ROW의 경우는 행레벨 replication이 가능하게 된다.  MySQL Cluster의 Replication을 수행할 경우에는  이 값으로 해야한다.  바이너리 로그에는 다음과 같이 기록된다.

BINLOG '
 3w0rRRMBAAAAJgAAACYAAAAAA4AAAAAAAAABHRlc3QAAWEAAQM=
';

값은 base64로  인코딩되어 있다.  이 값은  테이블 핸들러(스토리지엔진에 대한 핸들러)에 전달하는 값이다 ( handler::ha_write_row(), handler::ha_update_row(), handler::ha_delete_row()등) 

3또는  MIXED는 MySQL 5.1.8에서 추가되었다.  보통은 STATEMENT와 마찬가지로 동작한다.  다음과 같은 경우는 ROW와 마찬가지로 바이너리로그를 기록한다.

• UDF나 UUID()를 사용한 경우 
• NDB테이블에 대해서 INSERT, UPDATE, DELETE등의 데이터 조작 
• INSERT DELAYED를 실행했을 경우

Replication : Slave에서의 설정과 조작 5

• SHOW WARNINGS:　Replication에러가 나왔을 경우 에러 메세지를 볼 수 있다.
• SET GLOBAL SQL_SLAVE_SKIP_COUNTER:  지정된 수만큼 이벤트의 실행을 건너뛴다. 
• SELECT MASTER_POS_WAIT() : 지정된 바이너리 로그파일의 위치까지의 이벤트를  SQL스레드가 실행할 때까지 대기한다. SQL함수인 것을 주목하자.  다음과 같이 사용한다. SELECT MASTER_POS_WAIT('Master 바이너리로그 파일명', 바이너리로그 파일의 위치 [,타임아웃] ) 타임아웃은 0보다 큰 숫자를 지정한 경우, 지정된 초수를 넘어서 기다리게 되는 경우 에러를 낸다.  지정이 없으면 계속 기다린다.   반환되는 값은 이벤트의 수이다. NULL은  SQL스레드가 움직이고 있지 않던가 인수에러이던지 지정된 파일이 존재하지 않던지 하는 경우이다.  -1은 타임아웃이 발생한 상태이다.
• START SLAVE (IO_THREAD|SQL_THREAD):  Slave를 시작한다. 
• STOP SLAVE (IO_THREAD|SQL_THREAD):Slave를 정지시킨다.
• RESET SLAVE:  Slave정보를 삭제한다. master.info, relay-bin.#, relay-bin.index, relay-log.info가 삭제되어 초기상태로 된다. 
• LOAD DATA FROM MASTER:  Master데이터의 복사.  현재는 비추천기능. mysqldump등의 백업툴을 사용할 것을 추천한다.  MyISAM테이블만이 대상으로  그 외의 것은 Net error reading from master라는 에러메세지가 나온다. 또 데이터양이 많은 경우 타임아우이 발생할 가능성이 있다. 
• LOAD TABLE 테이블명 FROM MASTER: Master의 테이블 복사.  이것도 현재 비추천기능.

Replication : Slave에서의 설정과 조작 4

SHOW SLAVE STATUS의 내용 

• Slave_IO_State: I/O스레드의 상황 
• Maser_Host: Master의 호스트
• Master_User: 접속 유저
• Master_Port: Master의 포트번호 
• Connect_Retry: --master-connect-retry의 값
• Master_Log_File: I/O스레드가 현재 읽어들이고 있는 Master의 바이너리로그 
• Read_Master_Log_Pos: I/O스레드가 현재 읽어들이고 있는 Master의 바이너리로그의 위치 
• Relay_Log_File: SQL스레드가 현재 읽어들여 실행중인 Relay로그 파일 
• Relay_Log_Pos: SQL스레드가 현재 읽어들여 실행중인 Relay로그 파일의 위치 
• Relay_Master_Log_File: SQL스레드가 가장 최후에 실행한 이벤ㅌ가 Master의 어느 바이너리로그 파일에 있는가
• Slave_IO_Running: I/O스레드가 동작하고 있는가 
• Slave_SQL_Running: SQL스레드가 동작하고 있는가
• Replicate_Do_DB: --replicate-do-db의 지정값
• Replicate_Ignore_DB: --replicate-ignore-db 의 지정값
• Replicate_Do_Table: --replicate-do-table의 지정값 
• Replicate_Ignore_Table: --replicate-ignore-table의 지정값
• Replicate_Wild_Do_Table: --replicate-wild-do-table의 지정값
• Replicate_Wild_Ignore_Table: --replicate-wild-ignore-table의 지정값
• Last_Errno: 최후에 발생한 에러 번호 
• Last_Error: 최후에 발생한 에러 메세지 
• Skip_Counter: SQL_SLAVE_SKIP_COUNTER 지정된 값
• Exec_Master_Log_Pos: SQL스레드가 최후에 실행한 이벤트가 Master의 바이너리로그의 어느 위치가 되는 가
• Relay_Log_Space: Relay로그 사이즈.  모든 Relay로그의 합계 
• Until_Condition: START SLAVE의 UNTIL의 지정 
• Until_Log_File: START SLAVE의 UNTIL의 지정 
• Until_Log_Pos: START SLAVE의 UNTIL의 지정 
• Master_SSL_Allowed: SSL통신을 수행하고 있는지 
• Master_SSL_CA_File: CA의 CERTIFICATE파일
• Master_SSL_CA_Path: CA의 CERTIFICATE파일을 보존하는 디렉토리 
• Master_SSL_Cert: CERTIFICATE 파일 
• Master_SSL_Cipher: Cipher
• Master_SSL_Key: 키 파일 
• Seconds_Behind_Master: Slave가 어느정도 Master로부터 지연되고 있는가. 0인 경우에는 I/O스레드에 완전히 잘 따라가고 있음. 갱신등으로 늦어지고 있는 경우는  늦어진 추정 초수가 나타남. 

Replication : Slave에서의 설정과 조작 3

Master정보의 변경(CHANGE MASTER TO)

Master정보등의 변경을 수행한다. 구문은 다음과 같다.

 CHANGE MASTER TO 변경지정 [,변경지정]

변경지정 키워드를 모두 지정할 필요는 없고 필요한 것만을 「,」로 지정한다.
이 실행에 따라서 master.info , relay-log.info파일이 변경된다.

변경지정 키워드는 아래와 같다.

• MASTER_HOST='호스트명'
• MASTER_USER='유저명'
• MASTER_PASSWORD='패스워드'
• MASTER_PORT=포트번호
• MASTER_CONNECT_RETRY=시도횟수 
• MASTER_LOG_FILE='Master의 바이너리로그파일명'
• MASTER_LOG_POS=Master의 바이너리로그 파일의 위치 
• RELAY_LOG_FILE='relay로그파일명'
• RELAY_LOG_POS=relay로그파일의 위치 
• MASTER_SSL= {0|1}
• MASTER_SSL_CA = 'CA의 CERTIFICATE파일명'
• MASTER_SSL_CAPATH='CA의 CERTIFICATE 파일저장 디렉토리'
• MASTER_SSL_CERT ='CERTIFICATE파일명'
• MASTER_SSL_KEY='개인키 파일명'
• MASTER_SSL_CIPHER='Cipher지정'

Slave상태확인 (SHOW SLAVE STATUS)

Slave의 상태를 확인하기 위해서는 SHOW SLAVE STATUS를 사용한다.
Slave_IO_Running과 Slave_SQL_Running중 둘중 하나가 No이면 Replication은 정지하고 있는 것이다.  장해로 SQL스레드만 정지하고 있으면 Relay_Master_Log_File과 Exec_Master_Log_pos 쌍과  Master_Log_File과 Read_Master_Log_Pos쌍의 값이 달라지게 된다.

Replication : Slave에서의 설정과 조작 2

Slave에 관한 SQL문 

• GRANT REPLICATION CLIENT ON *.* : SHOW SLAVE STATUS를 실행할 수 있는 권한을 부여 
• CHANGE MASTER TO : Master 변경, 바이너리 로그의 읽기 위치 변경수행 
• SHOW SLAVE STATUS: Slave 서버의 상황
• SHOW WARNINGS : warning의 확인
• SHOW SLAVE HOSTS: Slave리스트를 출력. Slave서버는 --report-host=옵션의 지정이 필요하다. REPLICATION CLIENT권한이 필요
• SET GLOBAL SQL_SLAVE_SKIP_COUNTER=갯수 : 지정된 갯수만큼만 이벤트 실행을 건너뜀.
• SELECT MASTER_POS_WAIT() :  지정된 바이너리로그의 위치까지 실행되기까지 대기 
• START SLAVE: Slave개시
• STOP SLAVE: Slave정지 
• RESET SLAVE: Master 정보 삭제 
• LOAD DATA FROM MASTER: Master로부터 데이터를 복사 
• LOAD TABLE 테이블명 FROM MASTER: Master로부터 테이블을 복사 

Slave가 작성하는 파일 

• master.info:  Replication상태를 유지하기위해서 이용되는 파일. Slave서버가 자동으로 생성한다.  임의로 변경하지 않도록 주의한다.  다음과 같은 내용을 포함하고 있다.  

1. 이 파일의 행수 
2. Master_Log_File
3. READ_Master_Log_Pos
4. Master_Host
5. Master_User
6. 패스워드
7. Master_Port
8. Connect_Retry
9. Master_SSL_Allowed
10. Master_SSL_CA_File
11. Master_SSL_CA_Path
12. Master_SSL_Cert
13. Master_SSL_Cipher
14. Master_SSL_Key

디폴트로 datadir/밑에 작성되지만 --master-info-file=옵션으로 변경가능하다.  파일명도 같은 방법으로 변경할 수 있다.  

• relay-log.info: Slave서버의 relay-log상황을 기록하는 파일로 자동적으로 생성된다.  임의로 편집해서는 안된다.  다음과 같은 내용을 포함하고 있다. 

1. Relay_Log_File
2. Relay_Log_Pos
3. Relay_Master_Log_File
4. Exec_Master_Log_Pos

디폴트로 datadir/밑에 작성되지만 --relay-log-info-file=옵션으로 변경가능하다.  파일명도 같은 방법으로 변경할 수 있다.  

• 호스트명-relay-bin.NNNNNN:  relay로그파일이다.  N은 숫자로 000001에서 순차적으로 증가한다.  999999의 다음은 1000000가 된다.   호스트명-relay-bin의 부분은 --relay-log=옵션으로 변경가능하다.  또, 디폴트로는 datadir/에 작성되지만 장소는 --relay-log=옵션으로 변경가능하다. 

• 호스트명-relay-bin.index:현재 가지고 있는 relay로그 리스트이다. 

Replication : Slave에서의 설정과 조작 1

Slave로 동작시키기 위해서는 최소한 다음의 옵션을 my.cnf에 기술해야한다.

server-id = 번호
master-host = 호스트
master-user = 유저명
master-password = 패스워드

Slave에 관한 옵션은 다음과 같다.

• log-slave-updates :Slave겸 Master로 할 때에 지정한다. 
• master-connect-retry=기본값은60sec  Master와의 접속이 끊긴후 몇초 기다리고 재접속을 시도할까
• master-host=호스트 : Master의 호스트명 
• master-port=포트번호 : Master의 포트번호 
• master-user=유저명:  Master에 접속할 때 사용하는 유저명 
• master-password=패스워드: Master에 접속할 때 사용하는 패스워드 
• master-info-file=파일명 : master.info파일의 파일명(패스)지정 
• master-retry-count= 횟수: Master와의 통신이 끊겼을 경우 최대로 몇번 재접속을 시도할 것인가
• master-ssl : Master와의 통신을 SSL로 수행
• master-ssl-ca=파일: CA의 CERTIFICATE파일 
• master-ssl-capath=디렉토리명 : CA의 CERTIFICATE파일을 보존하고 있는 디렉토리 
• master-ssl-cert=파일명: CERTIFICATE파일의 지정
• master-ssl-cipher=Cipher : Cipher의 지정 
• master-ssl-key=파일명 : private key 파일명 
• relay-log=파일명 : relay로그 파일인  호스트명-relay-bin의 지정 
• relay-log-index=파일명:  호스명-relay-bin.index파일의 지정
• relay-log-info-file=파일명: relay-log.info파일의 지정 
• replicate-do-db=데이터베이스명 :  Replication대상이 되는 데이터베이스명 
• replicate-do-table=테이블명:  Replication대상이 되는 테이블명 
• replicate-ignore-db=데이터베이스명: Replication 대상외로 하고픈 데이터베이스명 
• replicate-ignore-table=테이블명: Replication 대상외로 하고픈 테이블명 
• replicate-rewrite-db="원래 데이터베이스명->Slave에서의 데이터베이스명" : Master의 데이터베이스명을 Slave에서는 다른이름으로 취급
• replicate-wild-do-table=와일드 카드 : Replication대상이 되는 테이블명을 와일드카드로 지정 
• replicate-wild-ignore-table=와일드 카드: Replication대상외로 하고픈  테이블명을 와일드 카드로 지정
• report-host=호스트명 : SHOW SLAVE HOSTS에서 표시되는 이름을 지정 
• report-user=유저명: SHOW SLAVE HOSTS에 표시되는 유저명을 지정. Master에서는 --show-slave-auth-info옵션을 지정하고 있어야함.
• report-password=패스워드: SHOW SLAVE HOSTS에 표시되는 패스워드를  지정. Master에서는 --show-slave-auth-info옵션을 지정하고 있어야함.
• report-port=포트명: SHOW SLAVE HOSTS에서 표시되는 포트번호를 지정 
• skip-slave-start: mysqld기동시에 Slave를 기동하지 않음. 
• slave-load-tmpdir="디렉토리 [:디렉토리]":Slave에서 임시 파일을 작성하는 디렉토리를 지정.  「:」로 복수지정가능 
• slave-skip-errors="에러번호[,에러번호]" : 지정된 에러가 발생하더라도 Slave처리를 정지시키지 않음. 「,」로 복수지정 
• net_retry_count=횟수 : 읽어들이기가 중단된 경우, 몇번 다시 시도할 것인가 지정 
• net_buffer_length=바이트수:  통신에 사용하는 버퍼 
• net_read_timeout=초수:  읽어들이기가 중단된 경우, 몇초 기다려서 에러를 낼 것인가 지정 
• net_write_timeout=초수: 써넣기가 중단된 경우, 몇초를 기다려서 에러를 낼 것인가 지정 
• relay_log_purge : 필요없는 relay로그를 제거
• relay_log_space_limit=바이트수:  relay로그의 합계 사이즈가 이 값을 넘으면 SQL스레드가 처리를 해서  공간이 생길때까지  I/O스레드는 처리를 정지
• slave_compressed_protocol: 통신내용을 zlib압축
• slave_net_timeout=초수: 데이터통신중  이 초수 이상으로 Master로부터 응답이 없으면 Slave는 접속을 끊고  재접속을 시도
• slave_transaction_retries=횟수 : SQL스레드가 트랜잭션 실패했을 경우  바로 에러로 하지 않고 몇번 재시도할 것인지 지정. 

Replication : Master에서의 설정과 조작 4

바이너리 로그갱신의 일시적인 ON/OFF ( SET SQL_LOG_BIN)

바이너리 로그의 갱신을 일시적으로 ON/OFF하는 경우는 SET SQL_LOG_BIN을 실행한다.
보통 Replication 대상외로 하고 픈 테이블(권한 테이블)등을 갱신할 때만 사용한다.

구문은 다음과 같다.

SET SQL_LOG_BIN= {0|1}

「1」은 이 세션의 갱신을 바이너리로그에 기록한다. 기본값이다. 「0」은 이 세션의 갱신을 바이너리로그에 기록하지 않는다.

Master이 상태확인(SHOW MASTER STATUS)

현재 써넣고 있는 바이너리로그와 그 위치를 확인하는 경우는 SHOW MASTER STATUS를 실행한다.


바이너리 로그의 목록을 표시(SHOW BINARY LOGS)

현재 유지하고 있는 바이너리 로그를 목록표시하기 위해서는 SHOW BINARY LOGS를 사용한다.
구문은 다음과 같다.

SHOW [MASTER|BINARY] LOGS


바이너리로그내의 이벤트를 표시 ( SHOW BINLOG EVENTS ) 

지정된 바이너리 로그파일의 이벤트를 나타낸다. 구문은 다음과 같다.

SHOW BINLOG EVENTS
[IN '바이너리로그파일'] [FROM 위치1] [LIMIT [오프셋,] 갯수]

바이너리 로그파일명을 지정하지 않는 경우는 최초의 바이너리 로그를 읽어들인다.
LIMIT으로 이벤트의 읽어들일 위치(오프셋)과 표시할 이벤트의 갯수를 지정할 수 있다.
LIMIT가 없는 경우는 모든 이벤트를 표시하게 되어 엄청난 양이 될 수 있으므로 주의해야한다.



지정한 바이너리 로그의 삭제 (PURGE MASTER LOGS)

바이너리 로그를 삭제하려면 PURGE MASTER LOGS를 사용한다. 구문은 다음과 같다.

PURGE {MASTER|BINARY} LOGS TO '바이너리 로그파일'
PURGE {MASTER|BINARY} LOGS BEFORE 'YYYY-MM-DD hh:mm:ss'


TO '바이너리 로그파일'의 지정의 경우는 지정된 파일 한개 전까지가 삭제된다.
BEFORE '날짜시간'도 지정된 일시보다 전의 파일이 삭제된다.

이 조작을 수행하면 호스트명-bin.index파일도 자동으로 변경된다.


Slave정보의 확인(SHOW SLAVE HOSTS)

Slave 정보를 얻기위해서는 SHOW SLAVE HOSTS를 실행한다.
Slave 서버에 --report-host=옵션을 지정하고 있는 것에 한정된다.

실행후에 나타나는 컬럼 Rpl_recovery_rank는 5.1.12-beta버전에서는 사용되어지지 않고 의미도 없다.
또, Master 서버에 --show-slave-auth-info옵션을 지정한 경우는 유저명과 패스워드도 표시된다.

이것은 Slave에서 다음의 옵션으로 지정한 값에 지나지 않는다. 실제로 Replication이 사용하고 있는 패스워드등은 표시되지 않는다.

--report-host=
--report-port=
--report-user=
--report-password=


Master의 초기화
모든 바이너리 로그파일을 삭제하고 Master를 초기화하는 경우는 RESET MASTER를 실행한다.
Replication운용중에 사용하지는 않겠지만 처음에 Replication을 재셋업할 경우등에 사용한다.

주의해서 사용해야한다.

Replication : Master에서의 설정과 조작 3

Slave데이터베이스의 유저명, 호스트, 패스워드 등록

 GRANT REPLICATION SLAVE ON *.*
 TO '유저명'@'호스트' [IDENTIFIED BY '패스워드']

global 권한이므로  ON *.*이 된다.


권한의 부여
 Master와 Slave의 내용을 확인할 수 있도록 SHOW MASTER  STATUS와 SHOW SLAVE STATUS를 실행할 권한을 부여한다.

 GRANT REPLICATION CLIENT ON *.*
 TO '유저명'@'호스트' [IDENTIFIED BY '패스워드']

 global 권한이므로  ON *.*이 된다.
  
바이너리로그의 로테이트(FLUSH LOGS) 

바이너리로그를 로테이트시킨다.  이 조작으로 Replication에서 부정합이 발생하지 않는다.

이 조작을 실행하면 호스트명-bin.index파일도 자동으로 변경된다.  SQL처리중에 FLUSH LOGS가 실행된 경우,  그것이 완료된 뒤에  로그의 로테이트와 .index파일이 변경된다.


Binlog Dump 스레드의 확인 

Master는  바이너리로그에 기록되어있는 쿼리를  Slave에 송신하기 위해서 스레드를 작동한다. (Binlog Dump 스레드)  Binlog Dump 스레드는 SHOW PROCESSLIST로 확인가능하다.

내용은 아래와 같다.

• User, Host : Slave 접속 유저명과 호스트명 
• Command  Binlog Dump: COM_BINLOG_DUMP. 바이너리로그를 보내는 루틴에 들어가 있는 상태. msql_binlog_send()로 보낸다. 
• Connect Out: Slave가 접속중  
• Register Slave :  COM_REGISTER_SLAVE. --report-host=를 지정한 Slave의 정보를 취득처리중. register_slave()가 호출된다.
• State Finished reading one binlog; switching to next binlog: 스레드는 어떤 바이너리로그를 다 읽어들여서 다음의 바이너리 로그파일을 open하려고 하는 상태 

• Has sent all binlog to slave; waiting for binlog to be updated: 모든 바이너리로그를 Slave에 전송이 끝나고  새로운 이벤트를 기다리고 있는 상태 
• Sending binlog event to slave: 바이너리로그를 읽고 Slave에 송신중 
• Waiting to finalize termination: 스레드 종료할 때의 상태

Replication : Master에서의 설정과 조작 2

바이너리 로그 파일

바이너리로그 파일은 호스트명-bin.NNNNNN 형식으로 작성된다.  N은 숫자로 000001에서 순서대로 증가된다.  999999다음은 1000000으로 된다.

호스트명-bin의 부분은 --log-bin=옵션에서 변경가능하다.  또  디폴트로는 datadir/밑으로 작성되어지지만  보존장소도 --log-bin=옵션으로 변경가능하다.


바이너리 로그 인덱스파일 

바이너리 로그 인덱스파일은  바이너리 로그 파일의 리스트를 유지한다.
호스트명-bin.index라는 이름으로 작성된다.

바이너리 로그인덱스 파일의 예 
shell#> cat master-bin.index
./master-bin.000002
./master-bin.000003

Replication : Master에서의 설정과 조작 1

Master데이터베이스 설정과 조작

Master데이터베이스로써 동작시키기 위해서는 최소한 다음 옵션을 지정할 필요가 있다.
옵션은 my.cnf에 기술한다.

Replication의 Master로 기동하기 전에 모든 데이터를 백업하고 설정을 한 후 Master로 기동한다.

server-id = 번호
log-bin [=파일명]

Master에 관한 옵션은 다음의 표를 참조하길 바란다.

• server-id=자연수 : 서버의 ID번호를 지정. 모든 slave, master에서 유일한 숫자를 지정할 필요가 있다.
  
• log-bin[=파일명] : 바이너리로그<호스트명-bin>를 기록한다.
  
• binlog_format={MIXED|STATEMENT|ROW} : 바이너리로그의 서식을 지정한다.
  
• binlog-do-db=데이터베이스명 : 지정된 데이터베이스로의 변경만을 바이너리로그에 기록한다.
  
• binlog-ignore-db=데이터베이스명: 지정된 데이터베이스로의 변경만을 바이너리로그에 기록하지 않음.
  
• binlog-row-event-max-size=바이트수: ROW의 경우 , 한개의 이벤트당 최대 바이트수.
• log-bin-index=이름 : <호스트명-bin.index> 파일명의 지정
• log-bin-trust-function-creators: 스토어드 프로시져 작성의 제한
  
• show-slave-auth-info : SHOW SLAVE HOSTS문으로 Slave정보목록에 사용자명과 패스워드를 추가한 것을 얻을 수 있음. Slave 서버에 -report-host=Slave호스트명과 옵션을 지정한 것만 표시됨
• net_buffer_length=바이트수 : 통신에 사용하는 버퍼
  
• net_read_timeout=초수 : 읽어들이는 중에 통신이 끊겼을 경우, 몇초를 기다려서 에러로 할 것인가 지정
  
• net_write_timeout=초수 : 쓰는 도중에 통신이 끊겼을 경우, 몇초를 기다려서 에러로 할 것인가 지정
  
• init-slave='SQL문' : Slave가 접속해 오면 최초 지정한 SQL문을 실행
  

Master에 관한 SQL문에는 다음과 같은 것이 있다.

• GRANT REPLICATION SLAVE ON *.* : Replication Slave가 접속할 유저를 등록(권한 부여)
• GRANT REPLICATION CLIENT ON *.* : SHOW MASTER STATUS를 실행할 수 있는 권한을 부여
  
• FLUSH LOGS: 바이너리로그를 로테이트
  
• SHOW PROCESSLIST : Replication 스레드를 확인
  
• SET SQL_LOG_BIN={0|1} : 현재 세션 기록을 바이너리로그에 수행할 것인가 하지 않을 것인가 지정
  
• SHOW MASTER STATUS: 바이너리 로그의 써넣기 상황을 확인
  
• SHOW BINARY LOGS: 현재 존재하는 바이너리 로그 파일명을 표시
  
• SHOW BINLOG EVENTS: 바이너리로그 이벤트를 표시
  
• PURGE MASTER LOGS: 지정된 바이너리로그만을 삭제
  
• SHOW SLAVE HOSTS: Slave 리스트를 표시. Slave서버는 --report-host=의 지정을 해둘 필요가 있다.
  
• RESET MASTER: 모든 바이너리로그를 삭제
  

Replication

데이터베이스시스템의 부하를 분산시키고 싶은 경우는 replication을 이용한다.
MySQL의 replication의 특징은 다음과 같다.

• 한 방향(Master에서 Slave로 흘러감)
• 비동기
• Master:Slave = 1:N
• Slave겸 Master가 될 수 있다.
  
• 통신은 TCP/IP를 사용
• 바이너리 로그를 사용
• 갱신은 Master만 가능

MySQL의 Replication은 Master의 바이너리 로그의 내용을 Slave에 보내는 것으로 수행된다.
동작의 흐름은 다음과 같다.

1. Slave I/O스레드가 Master에 접속
2. Master가 Slave를 인증하고 Slave와의 세션 개시
3. Slave I/O스레드가 바이너리 로그파일(파일명, 위치)를 요구
   
4. Master(binlog dump스레드)가 요구되어진 지점으로부터 이벤트를 바이너리 로그에서 읽어들여 Slave 에 전송
   
5. Slave I/O스레드는 받어낸 이벤트를 relay-log에 기록
   
6. Slave SQL스레드가 relay-log내용을 읽어들여 SQL문을 실행
7. Master에 새로운 이벤트가 있으면 Master가 Slave에 송신
   

Event Scheduler 4

이벤트의 수정

이미 등록되어있는 이벤트를 변경하는 경우는 ALTER EVENT문을 사용한다.
구문은 다음과 같다.

ALTER EVENT 이벤트명
[ON SCHEDULE 스케줄]
[RENAME TO 이벤트명]
[ON COMPLETION [NOT] PRESERVE]
[COMMENT '주석']
[ENABLE| DISABLE]
[DO [BEGIN] 실행할 sql문; [ 실행할 sql문;] [END] ]

예>
mysql>delimiter ;
mysql>ALTER EVENT test_event ON COMPLETION PRESERVE;


이벤트의 삭제

등록한 이벤트를 삭제하기 위해서는 DROP EVENT문을 사용한다.

DROP EVENT [IF EXISTS] 이벤트명

Event Scheduler 3

이벤트 등록

이벤트 등록을 하기 위해서는 CREATE EVENT문을 사용한다. 구문은 아래와 같다.

CREATE EVENT [IF NOT EXISTS] 이벤트명
ON SCHEDULE 스케줄
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
[COMMENT '주석']
DO [BEGIN] 실행할 sql문; [실행할 sql문]; [END]

스케줄:
{ AT 타임 [+ INTERVAL 간격 [+INTERVAL 간격...]]
| EVERY 간격 [STARTS 타임] [ENDS 타임] }

타임:
{CURRENT_TIMESTAMP | 년월일시의 리터럴}

간격:
수 {YEAR|QUARTER|MONTH|DAY|HOUR|MINUTE|WEEK|SECOND|YEAR_MONTH|DAY
|HOUR|MINUTE| WEEK| SECOND | YEAR_MONTH|DAY_HOUR|DAY_MINUTE| DAY_SECOND| HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}


ON SCHEDULE구에서는 이벤트의 실행시간과 간격을 지정한다. 이것은 필수이다. DO 구 뒤에는 실행할 SQL문을 지정한다. 이것도 필수이다.

이벤트명은 64문자까지이고 대소문자 구분하지 않는다.

유니크한 이름을 설정해야한다.

CURRENT_TIMESTAMP는 현재의 일시를 나타내는 특별한 키워드이다.

ON SCHEDULE AT timestamp는 한번만 실행하는 이벤트의 경우에 사용한다.
Unix의 at같은 것이라고 생각하면 될 것이다. 여기에 지정하는 timestamp에는 날짜와 시간 모두 포함할 필요가 있다. 예를 들어 2010-06-27 11:01:00 처럼 지정한다.

또, 지정하는 날짜는 미래의 시간이 되지 않으면 안된다.

+INTERVAL은 복수 지정이 가능하다. + INTERVAL 1 WEEK + INTERVAL 4 HOUR처럼 지정한다.

ON SCHEDULE EVERY는 이벤트를 반복실행할 때 사용한다. Unix의 cron이라고 생각하면 될 것이다. EVERY구인 경우는 + INTERVAL은 지정불가능이다.
STARTS에서는 개시일시 ENDS로 종료일시를 지정한다.

또, DO이하에 지정하는 SQL문이 한개 인경우에는 BEGIN, END, DELIMITER 지정은 필요없다.
복수의 문을 지정하는 경우는 BEGIN ~END로 문장을 감싼다. 이때 안에 있는 SQL문은「 ;」로 구별하기 때문에 CREATE EVENT문 끝을 의미하는 「 ;」하고 구별할 수 없게 된다.
그래서 stored procedure와 마찬가지로 CREATE EVENT실행전에 DELIMITER 를 지정해서 문장의 끝을 나타내는 마크를 변경해 두어야한다.

ON COMPLETION PRESERVE는 이벤트가 완료하더라고 이벤트의 내용을 유지한채 두게 된다. 보통은 바로 삭제된다.

⧈이벤트 등록예

mysql> delimiter //
mysql> CREATE EVENT test_event
-> ON SCHEDULE EVERY 1 DAY
-> STARTS ' 2010-06-27 11:01:00'
-> ENABLE
-> DO
-> BEGIN
-> DELETE FROM test.log
-> WHERE test.log.artime < NOW();
-> END //

Event Scheduler 2

이벤트는 SHOW문을 실행하던지 INFORMATION_SCHEMA.EVENTS테이블을 SELECT하는 것으로 확인가능하다.

SHOW EVENTS [FROM 스키마명] [LIKE 패턴]
SHOW CREATE EVENT 이벤트명

SHOW EVENTS는 정의되어 있는 이벤트리스트를 얻는다.

SHOW CREATE EVENT는 지정된 EVENT의 CREATE문을 표시한다.

[SHOW EVENTS에서 각 컬럼의 의미]

• Db: 데이터베이스명
  
• Name: 이벤트명
  
• Definer: 이벤트를 작성한 유저
• Type: 반복사용시 RECURRING, 한번만 실행할 시 ONE TIME
• Execute at: RECURRING의 경우는 NULL, ONE TIME의 경우는 실행시간
  
• Interval value: 이벤트의 간격. ONE TIME의 경우는 NULL
• Interval field: 이벤트 간격의 단위. ONE TIME의 경우는 NULL
• Starts: RECURRING인 경우는 개시시간. ONE TIME의 경우는 NULL. UTC로 표시됨.
• Ends: RECURRING인 경우는 종료시간.( 0000-00-00 00:00:00인 경우는 영원히 실행). ONE TIME의 경우는 NULL. UTC로 표시됨.
• Status: ENABLED 나 DISABLED

[INFORMATION_SCHEMA.EVENTS에서 각 컬럼의 의미]
*show events에서 얻을 수 있는 컬럼정보와 설명생략

• EVENT_CATALOG: 항상 NULL
• EVENT_BODY: 항상 SQL
• SQL_MODE: 이벤트가 작성되었을 때의 sql_mode의 값
  
• ON_COMPLETION: 이벤트가 완료되었을 때 이벤트 내용을 삭제할 때는 NOT PRESERVE 유지한다면 PRESERVE
• CREATED: 이벤트 생성일시. UTC로 표시됨.
• LAST_ALTERED: 이벤트 변경일시. UTC로 표시됨.
• LAST_EXECUTED: 최후의 이벤트 실행일시. UTC로 표시됨.