MySQL해킹

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

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

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

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


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

 

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: 모든 바이너리로그를 삭제
  

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로 표시됨.

MySQL성능측정2

Super-Smack(http://vegan.net/tony/supersmack/)은 MySQL벤치마킹툴이다.

특징으로는 다음과 같은 것이 있다.

• MySQL과 PostgreSQL에서 동작
• C++로 만들어졌으므로 쓸데없는 준비가 필요없다. (자바나 펄이면 MySQL용 드라이버가 필요), C의 AP를 사용해서 서버에 접속하므로 쓸 데 없는 잡음이 들어가지 않는다. 개조나 확장하기 쉬운 구조로 되어있다. 예를 들면 MySQL고유의 처리는 mysql-client.cc파일에 정리되어져 있으므로 이것을 참고로 Firebird대응도 가능할 것이다.
• 시나리오 파일(smack파일)로 자유롭게 실행하는 쿼리를 여러개 지정할 수 있다.
• 복수의 클라이언트를 가상으로 fork()로 생성하고 각 클라이언트(자식 프로세스)로 쿼리를 실행할 수 있다. 모든 클라이언트는 같은 시나리오로 동작한다.
• 데이터(영문숫자)를 생성하는 툴이 부속되어있다.
• 쿼리는 mysql_query()와 PQexec()를 실행. stored procedure는 사용하지 않음.
• SELECT결과는 fetch하지 않는다.

■컴파일

소스를 풀어놓은 후 configure, make한다. MySQL를 지원하려면 --with-mysql, --with-mysql-lib=, --with-mysql-include=를 지정한다.

make한 후 super-smack(이것이 본체)라는 명령어와 gen-data(데이터 생성 툴)이라는 명령어가 생기게 된다.

■사용방법

super-smack -d {pg|mysql} 시나리오파일 [인수 [인수] ...]

(pg: postgresql, mysql:MySQL, 인수는 시나리오 파일에서 사용하는 변수($1, $2...)가 된다. )

gen-data [-n행수 또는 --num-rows=행수] [-f포맷 또는 --format=포맷]

데이터를 생성하는 명령어로 생성할 레코드 수 , 레코드의 포맷을 지정한다.

■시나리오 파일 기술

다음의 블럭을 기술한다. (main, client, query, dictionary, table )

• main{} : 근간이 된다. 접속, 절단 그리고 어느 쿼리를 몇번이나 실행할 것인지 등의 기본이 되는 동작의 지시를 한다.
• client{} : 여러개 정의가 가능하다. 접속정보나 쿼리단위 정의를 한다.
• query{}: 여러개 정의가 가능하다. 쿼리를 실제로 기술하는 블럭이다.
• dictionry{}:여러개 정의가 가능하다. 필수조건은 아니다. 쿼리에 부여하는 값을 룰을 기술한다.
• table{}:여러개 정의가 가능하다. 필수조건은 아니다. 테이블의 존재를 체크해 없으면 생성한다. 또 레코드수를 체크해서 적으면 일단 테이블을 drop하고 테이블작성과 레코드 작성을 수행한다.

■결점

Super-Smack은 만능은 아니로 다음과 같은 결점도 있다.

• 여러개의 연속되는 쿼리에서 같은 값을 사용할 수 없다. 예를 들어 SELECT * FROM tbl WHERE col=$dict; UPDATE tbl SET col2=xxx WHERE col=$dict; 처럼 연속되는 쿼리의 $dict에는 다른값이 들어 가 버린다.
• 같은 쿼리내에서도 같은 값을 사용할 수 없다. SELECT $dict, $dict; 라고 해도 예를 들어 SELECT 1, 999;처럼 다른 값이 처리된다.
• 전에 실행한 SELECT의 결과를 새로운 쿼리에서 사용할 수 없다. 예를 들어 SELECT price FROM item WHERE id=1; 에서의 price값을 재 사용할 수 없다.

MySQL - 모니터링1

데이터베이스 목록을 표시

SHOW DATABASES
또는
SELECT * FROM information_schema.SCHEMATA [WHERE...]

테이블 목록을 표시

SHOW TABLES
또는
SHOW TABLE STATUS
또는
SELECT * FROM information_schema.TABLES [WHERE ...]

테이블정의를 표시

DESC 테이블명
DESCRIBE 테이블명
EXPLAIN 테이블명

테이블정보를 표시

SHOW CREATE TABLE 테이블명

컬럼정보를 표시

SHOW COLUMNS FROM 테이블명
SHOW FULL COLUMNS FROM 테이블명
SELECT * FROM information_schema.COLUMNS [WHERE ...]


인덱스정보를 표시

SHOW INDEX FROM 테이블명
SELECT * FROM information_schema.STATISTICS [WHERE ...]

테이블관리6- mysqlcheck옵션

• 모드관련

--analyze, -a

--check, -c

--optimize, -o

--repair, -r

• CHECK용 옵션

--auto-repair

--check-only-changed, -C

--extened, -e

--fast, -F

--medium-check, -m

--quick, -q

• 5.1로 업그레이드체크용 옵션

--check-upgrade, -g

--fix-db-names

--fix-table-names

• REPAIR용 옵션

--extended, -e

--use-frm

• 접속용 옵션

--compress

--host=호스트명, -h호스트명

--password[=패스워드] -p[패스워드]

--port=포트번호, -P포트번호

--protocol={TCP|SOCKET|PIPE|MEMORY}

--socket=소켓파일명, -S소켓파일명

--user=유저명, -u유저명

• 그외 옵션

--all-databases, -A

--all-in-1, -1

--character-sets-dir=디렉토리명

--databases, -B

--debug[=debug_options], -#[debug_options]

--default-character-set=캐릭터셋

--force, -f

--help, -h

--silent, -s

--tables

--verbose, -v

--version, -V

테이블관리5- mysqlcheck

mysqlcheck명령어에 의한 테이블 관리

mysqlcheck명령어로는 ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE, REPAIR TABLE문을 실행한다.

기본적인 구문은 다음과 같다.

mysqlcheck [모드] [옵션] 데이터베이스명 [테이블명]

mysqlcheck [모드] [옵션] --tables 테이블명 [테이블...]

mysqlcheck [모드] [옵션] --databases 데이터베이스명 [데이터베이스명...]

mysqlcheck [모드] [옵션] --all-databases

myisamchk는 모드 지정에 따라서 동작을 바꾼다. mysqlcheck명령어 표준 동작은 --check(-c)이다.

mysqlcheck명령어 이름을 변경하면(mysqlcheck을 복사, 또는 심볼릭 링크를 사용) 기본 동작이 바뀜으로 주의해야한다.

mysqlrepair = myisamchk --repair

mysqlanalyze = myisamchk --analyze

mysqloptimize = myisamchk --optimize

테이블관리4- myisamchk

MySQL서버의 MyISAM테이블 관리에 관한 옵션

• --myisam_repair_threads=#

:MyISAM 복구를 위해서 몇개의 쓰레드를 생성할 것인가를 지정한다.

• --myisam_sort_buffer_size=#

:REPAIR, CREATE INDEX, ALTER INDEX 할 때의 취득되는 작업용 메모리. 인덱스 소트중에도 사용된다.

• --myisam-recover[=option[,option..]] (option:DEFAULT, BACKUP, FORCE, QUICK)

:MySQL서버 기동후 처음으로 MyISAM 테이블을 열 때 그 테이블이 정상적으로 닫혀지지 않았을 때나 크래쉬한 마크가 있는 경우 자동적으로 그 테이블 복구를 수행한다.

옵션은 콤마로 복수 지정가능하다.

BACKUP: 복구하는 MYD파일의 백업을 작성한다. 파일명은 "테이블명-일시.BAK"이 된다.

FORCE: MYD파일에서 행이 삭제되던지 말던지 복구를 수행한다.

QUICK: MYD 각 행을 체크하지 않는다.

DEFAULT: 기본값. 위 3개를 지정하지 않는 것과 같다.

테이블관리3- myisamchk

myisamchk는 MyISAM테이블을 체크하고 복구하는 전용명령어이다.

서버가 테이블을 사용하지 않는 상태(갱신하지 않는, 열려있지 않은 상태)에서 myisamchk를 사용하지 않으면 안된다.

mysql명령어에서 --skip-external-locking 옵션을 사용했을 경우 MySQL 서버를 정지시키지 않아도 myisamchk를 사용할 수 있다.

사용전에는 FLUSH TABLES을 실행해서 일단 테이블을 닫아주어야한다.

또, 체크중에는 LOCK TABLES을 사용해서 클라이언트가 체크중에 있는 테이블에 접근할 수 없도록 해둘 필요가 있다.

기본적인 사용방법은 아래와 같다.

myisamchk [옵션] {테이블명|MYI파일명}

인수로는 테이블명 또는 MYI파일을 지정한다. 복수 지정가능하다.

myisamchk옵션은 my.cnf파일의 [myisamchk]그룹에 기술할 수 있다.

myisamchk는 작업용 파일을 TMPDIR환경변수에 있는 디렉토리밑에 작성한다. (없는 경우는 /tmp디렉토리)

만약 파티션에 여유가 없을 경우에는 out of memory가 나올 수 있다.

그럴 때에는 --tmpdir옵션으로 작업용 파일을 두는 디렉토리를 지정해야한다.

메모리사이즈관련 옵션

--sort_buffer_size, --key_buffer_size, --read_buffer_size, --write_buffer_size

체크관련 옵션

--fast ( -F ), --check-only-changed ( -C ), --medium-check( -m) , --extend-check( -e )

--check (-c), --force( -f), --information( -i ), --read-only( -T ) , --update-state ( -U)

복구관련 옵션

복구할 때 myisamchk는 일시 작업용 파일인 .TMM(인덱스), .TMD(데이터)를 사용한다.

--recover(-r), --safe-recover( -o)

--backup( -B), --character-sets-dir=디렉토리, --correct-checksum, --data-file-length=사이즈(-D 사이즈), --extend-check(-e), --force(-f), --keys-used=정수( -k 정수), --max-record-length=길이,--parallel-recover(-p), --quick(-q), --set-collation=collation명, --sort-recover(-n), --tmpdir=디렉토리(-t 디렉토리), --unpack(-u)

기타옵션

--analyze(-a), --block-search=옵셋(-b옵셋), --description(-d), --set-auto-increment[=정수](-A정수) , --sort-index( -S) , --sort-records=정수(-R 정수), -- verbose(-v), --silent(-s), --version(-V), --help(-h)

테이블관리2- 관리SQL

ANALYZE TABLE

문법: ANALYZE [LOCAL| NO_WRITE_TO_BINLOG] TABLE 테이블명 [, 테이블명]

동작테이블: MyISAM, InnoDB

실행에 필요한 권한: SELECT && INSERT

myisamchk옵션: -a, -analyze

내용: ANALYZE TABLE은 테이블의 인덱스분포를 해석하고 그것을 기록한다.

ANALYZE을 MyISAM테이블에 적용시켰을 경우에는 read lock이 걸리고 InnoDB테이블에 적용시켰을 경우에는 write lock이 걸린다.

NO_WRITE_TO_BINLOG 키워드나 LOCAL키워드가 지정되지 않는 한 ANALYZE TABLE은 바이너리 로그에 기록된다.

사용예

mysql> ANALYZE TABLE tb1\G

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

Table: test.tb1

Op: analyze

Msg_type: status

Msg_text: Table is already up to date

1 row in set (0.00 sec)

mysql> show index from tb1\G

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

Table: tb1

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: user_id

Collation: A

Cardinality: 1

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

1.InnoDB에서 ANALYZE TABLE

InnoDB경우 ANALYZE TABLE을 실행하면 SHOW INDEX에 표시되는 인덱스의 카디넬러티(cardinality :인덱스내 유일키의 수를 나타내는 항목)가 결정된다. 그러나 이것은 어디까지나 추정치로 각각의 인덱스 트리를 랜덤으로 조사해서 계산한다.

따라서 ANALYZE TABLE을 반복적으로 실행하면 다른 값이 나타날 수 있다.

2.JOIN과 ANALYZE TABLE

MySQL에서는 JOIN동작을 할때만 이 인덱스 카디넬러티값을 이용한다.

만약 JOIN할 때 확실히 최적화되어 있지 않다고 생각된다면 ANALYZE TABLE이나 OPTIMIZE TABLE을 실행하면 개선될지도 모른다.

그래도 해결되지 않는 경우는 FORCE INDEX구를 SQL문에 사용하는 방법도 있다.

JOIN할 때는 max_seek_for_key에 설정된 횟수이상은 키 스캔을 하지 않도록 옵티마이져에 알리지만 5.1.12-beta 표준치는 4294967295회이므로 보통은 키 스캔을 전부 수행하게된다.

로그파일감시

에러로그 파일, slow query 로그파일, General로그파일을 감시하고 싶다는 것은 당연한 요구 일 것이다.

현재 다음과 같은 방법을 사용할 수 있다.

1. syslog에 보낸다.
2. swatch, logsurfer, logcheck등의 로그 감시툴을 사용
3. FIFO특수파일을 이용한 자작 툴을 제작

1.syslog에 보낸다.

mysqld_safe을 개조해서 mysqld에서 받은 에러 메세지를 syslog에 보내는 방법이다.

mysqld_safe안에서 에러 출력을 logger등의 명령어에 보내면 실현가능하다.

syslog에 보낸 경우 syslog감시 툴을 사용할 수 있다.

mysqld_safe가 생성하지 않는 로그파일을 감시하는 경우는 tail등으로 파일을 볼 필요가 있다.

이 때 주의점은 파일이 변경된 경우 예를 들어 tail -f 실행중에 로그파일을 이동하고 FLUSH LOGS를 실행할 때등이다.

이 같은 경우에는 tail은 이동된 쪽의 파일을 보고 있기때문에 (최초로 열린 파일 디스크립터를 보고 있다. ) 로테이트로 새로 생긴 파일은 보지 않는 것을 주의해야한다.

GNU tail에서는 tail --follow=name --retry /data/host.log처럼 이 상황에 대처할 수 있는 옵션이 제공되고 있다.

로그활용8 - 바이너리로그

mysqlbinlog을 사용한 데이터 복구

mysqlbinlog를 사용해서 데이터를 복구하는 데에는 파이프라인을 직접 이용하는 방법과

일단 파일로 출력한 다음에 그것을 사용하는 방법이 있다.

shell$ ./bin/mysqlbinlog ./data/host-bin.000001 | ./bin/mysql

shell$./bin/mysqlbinlog ./data/host-bin.000001 < bin.000001

shell$./bin/mysql > bin.000001

바이너리로그 이벤트 확인

SHOW BINLOG EVENTS를 실행하면 바이너리로그 이벤트를 확인할 수 있다.

LIMIT구문이 없으면 모든 이벤트가 나오므로 주의해야한다.

SHOW BINLOG EVENTS

[IN '로그파일명'] [FROM 위치] [LIMIT [오프셋,] 갯수]

로그활용7 - 바이너리로그

바이너리로그 목록표시

현재 존재하는 바이너리 로그 리스트를 출력하는 경우는 SHOW BINARY LOGS문, 또는 SHOW MASTER LOGS문을 사용합니다.

바이너리로그 리스트를 표시

mysql> SHOW BINARY LOGS;

또는

mysql>SHOW MASTER LOGS;

바이너리로그 삭제

바이너리로그 파일을 삭제하는 방법은 PURGE MASTER LOGS TO문을 사용한다.

바이너리로그 삭제

mysql>PURGE MASTER LOGS TO 'server-bin.000005';

PURGE MASTER LOGS TO문으로 바이너리로그 파일명을 지정한다. 지정된 바이너리로그 보다 작은 숫자 파일이 제거된다. 위 예를 보면 server-bin.000004까지의 번호 파일이 삭제되고 server-bin.000005은 삭제되지 않는다.

바이너리로그 변환

바이너리로그 파일을 에디터나 페이저로 보더라도 의미를 알 수 없다.

확실히 읽기 위해서 텍스트로 변환시켜주는 것이 mysqlbinlog명령어이다.

다음처럼 조작한다.

mysqlbinlog명령어 실행

shell$ ./bin/mysqlbinlog ./data/host-bin.000001

..

..

바이너리로그에는 오퍼레이터가 실행하지 않은 것도 기록된다.

mysqlbinlog를 실행하면 그 정보도 SQL문으로 출력된다.

그것이 주석이나 SET문이다.

주석은 # 이나 /* 로 시작한다.

mysqlbinlog로 생성된 SQL문을 원래대로 데이터를 복원하지 않으면 안되기때문에 SET문에서는 TIMESTAMP 나 AUTO_INCREMENT 값이 복원전과 복원후가 틀리지 않도록 지정한다.

또, SQL문 실행시에는 캐릭터셋도 문제가 되기 때문에 @@session.character_set_client등으로 지정한다.

[# at숫자]는 바이너리 로그 파일에 있어서 기술위치(바이트)이다.

[# at숫자]에서부터 [# at숫자]사이를 이벤트라고 부른다.

로그활용6 - 바이너리 로그

sync_binlog(--sync-binlog)

표준으로는 이 값은 0으로 바이너리 로그의 디스크로의 동기를 위한 기록은 실행되지 않는다.

바이너리로그를 동기처리 하기 위해서는 sync_binlog옵션으로 사용한다.

--sync_binlog[=숫자]

또 SET문으로 동적으로 유효로 하는 경우도 가능하다. 다음처럼 조작한다.

바이너리로그 동기를 유효로 하기

mysql> SET GLOBAL sync_binlog=1;

sync_binlog가 유효인 경우 MySQL서버는 바이너리로그 기록에 fdatasync()를 사용한다.

지정한 값이 1이고 트랜잭션인 경우 트랜잭션마다 fdatasync()를 실행하고 트랜잭션이지 않은 경우는 한문장마다 실행한다.

이것으로 트랜잭션 단위로 확실히 바이너리 로그에 기록된다.

값이 1이상인 경우는 지정된 회수의 이벤트가 발생한 후에 flush를 수행한다.

디스크 I/O는 늘어나지만 안전성을 요구하는 경우에는 1을 추천한다.

그외 바이너리로그에 관한 옵션

• binlog-do-db=데이터베이스명 : 지정된 데이터베이스변경만을 바이너리 로그에 기록한다.
• binlog-ignore-db=데이터베이스명 : 지정된 데이터베이스변경만 기록하지 않는다.
• binlog-row-event-max-size=수: 표준값1024, binlog_format=ROW인 경우 1이벤트 최대사이즈(바이트수) 256배수를 지정한다.
• log-bin-trust-function-creators: 표준값0(무효) 1로 지정하면 stored procedure나 트리거 작성은 SUPER권한을 가진 유저만 실행가능하게 되고 바이너리로그를 부시지않는 것만 작성이 허용된다. binlog_format=ROW의 경우는 항상 바이너리로그는 안전

로그활용5 - 바이너리 로그

바이너리 로그는 갱신쿼리를 기록한 것으로 리커버리및 replication에 사용되는 중요한 로그파일이다.

실행된 갱신계 쿼리문이 이 파일에 기록된다.

또, 기록순서는 트랜잭션도 고려하고 있다.

log-bin(--log-bin)

log-bin은 바이너리 로그를 유효로 했을 경우에 사용한다. 다음과 같이 지정한다.

log-bin[=파일명 접두어]

파일명이 생략된 경우는 datadir/호스트명-bin.NNNNNN이 된다.

NNNNNN는 6자리 숫자로 MySQL이 자동으로 부여하는 수치이다.

이것은 000000부터 시작된다.

바이너리로그는 표준으로는 1G바이트 크기에 도달하면 자동으로 로테이트한다.

이 때 파일의 숫자부분에 1이 더해진 파일이 생기고 꼿에 새로운 로그가 기록된다.

현재의 로그는 가장 숫자가 큰 파일에 기록되고 있다는 것이다

로테이트는 FLUSH LOG문으로 강제적으로 실행하는 것도 가능하다.

log-bin-index(--log-bin-index)

log-bin-index는 바이너리 로그 인덱스 파일의 파일명을 변경한다. 바이너리로그 인덱스파일은

바이너리 로그 파일의 목록을 가지고 있는 파일이다. 현재 어떤 바이너리 로그 파일이 있는지를 나타낸다. 표준으로는 datadir/호스트명-bin.index라는 파일에 보존된다.

FLUSH LOGS나 PURGE MASTER LOGS TO를 실행하면 이 파일도 자동으로 변경된다.

바이너리로그 인덱스파일의 파일명을 변경하는 경우는 다음처럼 지정한다.

log-bin-index=파일명

max_binlog_size(--max_binlog_size)

max_binlog_size는 한개의 바이너리로그 파일의 최대 사이즈(바이트)를 지정한다.

여기에서 지정된 사이즈보다 파일이 커지는 경우 자동으로 로테이트한다.

다음처럼 지정한다.

max_binlog_size=숫자

또 SET으로 MySQL서버 기동시에도 변경하는 것이 가능하다.

MySQL서버 기동중에 변경하기

mysql> SET GLOBAL max_binlog_size=104857600;

binlog_cache_size(--binlog-cache-size)

MySQL은 바이너리로그에 써 내리는 내용을 캐쉬하지만 그 캐쉬 사이즈(바이트)를 지정하는 경우는 binlog_cache_size옵션을 사용한다.

binlog_cache_size=1048576

SET문을 사용해서 MySQL기동시에 동적으로 변경하는 것이 가능하다.

캐쉬사이즈를 변경

mysql> SET GLOBAL binlog_cache_size=1048576;

binlog_format(--binlog-format)

MySQL 5.1.5에서 바이너리 로그 포맷에 행 기준 기술방법이 도입되었다.

종래 SQL문을 기록한 바이너리 로그에서는 replication을 실행할 때에 slave 서버도 순서대로 SQL문을 실행하지 않으면 안되었기때문에 시간이 걸리는 쿼리를 실행할 때에는 slave내용은 마스터에 대해서 매우 늦어지는 경우가 있었다.

그러나 행 기준 포맷 바이너리로그를 사용하면 replication할 때에는 행의 변경만이 전달되어지기 때문에 slave처리도 빠르게 된다.

바이너리 로그의 포맷 변경에는 binlog_format 옵션을 사용한다. 기본 포맷은 종래와 마찬가지로 SQL문을 저장한다. 버젼 5.1.8부터는 SQL문 포맷이나 행 기준 포맷, 모두 섞어서 기록할 수 있게도 되었다.

bin_format={ROW|STATEMENT|MIXED}

binlog_format에 주어지는 값은 다음과 같다.

1또는 STATMENT SQL문장을 기록

2또는 ROW 행 기준 바이너리로그를 기록

3또는 MIXED 보통은 STATEMENT와 같은 동작을 하지만 다음과 같은 경우 자동으로 ROW으로 전환된다.

UDF이나 UUID()를 사용했을 경우

Cluster리플리케이션을 사용했을 경우

2또는 ROW를 지정하면 텍스트로 변환한 바이너리로그는 다음과 같이 기재된다.

이것은 다른 SQL문과 마찬가지로 mysql명령어로 처리가능하다.

행기준 바이너리 로그 파일

BINLOG '

3w0rRRMBAAAAJgAAACYAAAAAAA4AAAAAAAABHR1c3QAAWEAAQM=

';

또 SET문으로 동적으로 변경하는 경우는 다음처럼 지정한다.

replication포맷을 SQL문 레벨로 변경

mysql> SET GLOBAL binlog_format="SATATEMENT";

다음과 같은 경우는 SET문으로 동적으로 포맷을 변경하는 것이 불가능하다.

1. stored procedure나 트리거 안

2.NDB가 유효한 경우

3.세션이 ROW기준으로 되어있고 일시 테이블을 사용하고 있는 경우

로그활용4 - slow query log

slow-query-log(--slow-query-log)

처리에 시간이 걸린 쿼리를 기록하기 위한 옵션이다.

쿼리 실행에 long_query_time에 세팅된 초수(표준 10초)이상 시간이 걸린 경우 기록된다.

기록장소는 mysql.slow_log테이블이던지 slow_query_log_file변수에 지정된 파일(--log-slow-quries옵션에 지정된 파일)이다.

로그의 출력위치를 테이블이나 파일로 할 것인지 하는 것은 log-output옵션에서 지정한다.

MySQL서버 기동중에 slow query log를 얻기위한 지시예

mysql> SET GLOBAL slow_query_log=1;

초수를 3초로 변경하는 예

mysql> SET GLOBAL long_query_time=3;

log-slow-queries(--log-slow-queries)

처리에 시간이 걸린 쿼리를 기록하는 로그파일이다. 이 파일은 FLUSH LOGS로는 로테이트할 수 없다. 또 서버 재기동시에도 로테이트 하지 않는다.

다음 처럼 지정한다.

log-slow-queires[=파일명]

파일명을 생략하면 datadir/호스트명-slow.log로 된다.

또, MySQL서버 기동중에도 slow query log를 기록할건지 말건지 지정하는 것이 가능하다.

MySQL서버 기동중에 slow query log를 유효로 하기

mysql> SET GLOBAL slow_query_log=1;

slow query log의 로그파일을 지정하는 경우는 다음 처럼 조작한다.

mysql> SET GLOBAL slow_query_log_file="/tmp/slow";

또한 SHOW VARIABLES로 봤을 때의 log_slow_queries는 slow query log가 기록되고 있는지에대한 여부를 나타내는 변수로 이것을 SET로 변경하는 것은 불가능하다.

log_queries_not_using_indexes(--log_queries_not_using_indexes)

log_queries_not_using_indexes를 지정하면 인덱스를 사용하지 않은 쿼리도 slow query log에 기록할 수 있다.

log_queries_not_using_indexes

다음처럼 서버기동중에는 SET로 변경하는 것이 가능하다.

MySQL서버기동중에 변경하기

mysql> SET GLOBAL log_queries_not_using_indexes=ON;

long-query-time(--long-query-time)

long-query-time에서는 지정한 초수보다 처리에 시간이 걸린 쿼리를 기록하게 된다.

다음 처럼 지정한다.

long-query-time=초수

또, SET에서 MySQL서버 기동시에도 변경가능하다.

MySQL서버 기동중에 변경하기

mysql> SET long_query_time=5;

로그활용3 - 일반로그

log(--log)

MySQL서버처리를 상세하게 기록하는 로그파일이다. 언제 어떤유저(포함하는 호스트)가 접속해서 어떤 쿼리를 실행했는가가 상세하게 기록된다.

기록은 MySQL서버가 접수한 순서대로 기록된다. 트랜잭션 상태는 고려하는 않는다.

어플리케이션과 연계 디버그나 보안감사에 사용할 수 있다.

이 옵션을 지정하면 general-log옵션이 자동적으로 ON이 된다.

다음처럼 지정한다.

log[=파일명]

파일이름을 생략하면 datadir/호스트명.log파일이 된다. 이 파일은 FLUSH LOGS로는 로테이트되지 않는다. 또 서버 재기동시에도 로테이트하지 않는다.

이 파일을 로테이트하려면 Unix계열에서는 다음처럼 한다.

log를 로테이트한다.

root@shell# mv hostname.log hostname.log.0

root@shell# mysqladmin flush-logs

general-log(--general-log)

general-log는 MySQL5.1이상에서의 기능이다. 이 옵션을 지정하면 mysql.general_log테이블에 General로그가 출력된다.

옵션은 다음처럼 지정한다.

general-log

또, MySQL서버 기동중에 SET문으로 값을 변경하면 유효로 할 수 있다.

MySQL서버기동중에 general-log를 유효로 세팅

mysql> SET GLOBAL general_log=1;

mysql.general_log테이블이 존재하지 않는 경우는 mysql_fix_privilege_tables스크립트를 실행하고 테이블을 작성해야한다. 이 테이블은 CSV스토리지엔진으로 작성되어 있다.

또, 이 옵션이 지정된 경우 보통 --log로 지정된 파일에는 로그를 기록하지 않는다.

주의점은 이 테이블의 캐릭터셋이다.

general_log

CREATE TABLE `general_log`(

...

중략

`command_type` varchar(64) DEFAULT NULL,

`argument` mediumtext

) ENGINE=CSV DEFAULT CHARSET=utf8

이 처럼 실행한 SQL문은 argument헤더에 utf8캐릭터셋으로 기록된다.

만약 UTF-8로 변환불능한 문자가 쿼리에 포함되어 있었을 경우는 이 부분 기록은 옳바르지 않게 된다.

덧붙여 말하면 mysql.general_log테이블에 대해서 DELETE/UPDATE/INSERT문은 실행할 수 없다. FLUSH LOGS를 실행해도 내용은 없어지지 않지만 TRUNCATE TABLE문을 사용해서 내용을 전부 제거하는 것은 가능하다. 또 이 옵션이 무효인 경우(general로그를 출력하지 않을 때)에는 ALTER TABLE문으로 스토리지엔진을 바꾸거나 DROP TABLES문으로 파기하는 것도 가능하다.

log-output(--log-output)

log-output는 general로그와 slow query로그 출력위치를 지정한다.

log-output=값[,값]

값에는 TABLE, FILE, NONE을 지정할 수 있다.

TABLE: mysql.general_log테이블에 쓴다.

FILE: --log옵션으로 지정된 파일에 쓴다.

NONE:로그를 출력하지 않는다. 다른 지정보다 우선도가 높다.

또 , 값을 "," 로 복수 지정하는 것도 가능하다.

log-output=FILE,TABLE

이 경우는 파일과 테이블 양쪽에 출력하게 된다.

log-output는 MySQL서버기동중에 SET문을 사용해서 변경하는 것도 가능하다.

MySQL서버 기동중에 출력위치를 변경

mysql>SET GLOBAL log_output="FILE,TABLE";

로그활용1

로그의 종류

MySQL로그에는 데이터베이스내에서 일어나는 여러가지 사건들이 기록되기때문에 로그를 읽는 것으로 데이터베이스서버 운용에 빠질 수 없는 중요한 정보를 얻는 것이 가능하게 된다.

MySQL 5.1.12-beta로그에는 다음과같은 것이 있다.

• 에러로그(log-error/ log-warnings)
• General로그(log/ general-log)
• slow query로그(log-slow-queries / slow-query-log)
• 바이너리 로그(log-bin)
• ISAM로그(log-isam)

log-isam은 MyISAM 변경을 기록하는 파일이다.

개발자대상 디버그 정보가 기록되기때문에 보통은 사용하지 않는다.

MySQL은 기본적으로는 로그를 기록하지 않기때문에 로그를 기록할 경우는 mysqld 기동시에 기록하고픈 로그를 옵션으로 지정하던지 설정파일 my.cnf(또는 my.ini)의 [mysqld] 그룹에 기술한다.

MySQL 백업4

InnoDB Hot Backup

InnoDB Hot Backup은 InnoDB개발회사인 Innobase Oy Inc(http://www.innodb.com/)에서 제공되는 유료 백업 툴이다.

이 툴은 서버를 정지하지 않고 InnoDB파일을 백업할 수 있다.

ibbackup이라는 명령어가 상품이다.

또 ibbackup은 /etc/my.cnf파일과 MyISAM테이블등은 백업하지 않는다.

ibbackup은 호출하는 innobackup이라는 Perl스크립트도 제공된다. 이것은 GPL v2이다.

innobackup은 frm파일과 MyISAM파일도 동시에 백업한다.

BACKUP TABLE

MySQL 5.2이후에는 없어질 예정이다. MyISAM테이블에서만 동작한다.

BACKUP TABLE 테이블명 [,테이블명] ... TO '저장할 디렉토리'

디렉토리는 풀 패스로 지정한다.

버퍼를 flush한 후 지정된 디렉토리에 .frm과 .MYD파일을 복사한다.

.MYI파일은 myisamchk 나 REPAIR TABLE USE_FRM으로 언제든지 재작성가능하다.

또, 백업중에는 테이블에 READ lock이 걸린다.