MySQL 유저관리3

패스워드 세팅

사용자 패스워드를 세팅하는 경우, 다음과 같은 구문이 된다. 실행에 필요한 권한은 mysql권한테이블에 대해서 UPDATE권한이다.

SET PASSWORD FOR '유저명'@'호스트' = PASSWORD('평문패스워드')

패스워드 세팅은 mysqladmin명령어로도 가능하다.

mysqladmin -u 유저명 password "평문패스워드"

이때 mysqladmin명령어를 실행한 호스트가 MySQL서버위에 인식되어 '유저명'@'호스트' 형식으로 해석된다.

유저명 변경

유저명을 변경하는 데에는 다음 구문을 사용한다.

유저명을 변경하는 데에는 CREATE USER권한, 또는 권한테이블에 대한 UPDATE권한이 필요하다.

RENAME USER '지금 유저명'@'호스트' TO '새로운 유저명'@'호스트' [, old_user TO new_user] ...

권한정보를 다시 읽어들이기

권한테이블 내용을 다시 메모리에 읽어들이기 위해서는 다음과 같이 한다.

shell$ mysqladmin -uroot reload

mysql> FLUSH PRIVILEGES;

GRANT, REVOKE, CREATE USER, DROP USER, SET PASSWORD FOR문을 실행한 경우는 수동으로 다시 읽어들 일 필요는 없다.

MySQL 유저관리2

유저권한 확인

유저의 권한을 확인하는 경우 다음과 같은 구문이 된다.

SHOW GRANTS FOR '유저명'@'호스트'

MySQL에서는 유저는 호스트하고 한 쌍이라는 것을 잊지말아야한다.

"USAGE" 행만 표시되는 경우, "아무권한도 가지고 있지않다."라는 의미이다.

유저권한 삭제

유저권한을 삭제하는 경우는 REVOKE문을 사용한다. 실행하는 데에는 GRANT OPTION과 권리를 삭제하는 권한을 갖고 있을 필요가 있다.

유저명과 호스트명을 한 쌍이라는 것을 잊지말아야한다.

모든 권한을 삭제해도 유저 자체가 삭제되는 경우는 없다.

REVOKE 권한 [(필드명 [,필드명 ...] )] [,권한 [(필드명 [,필드명 ...] )] ] ...

ON {

[TABLE] {테이블명|*|*.*|데이터베이스명.*}

|PROCEDURE 데이터베이스명.스토어드프로시져명

|FUNCTION 데이터베이스명.FUNCTION명

}

FROM '유저명'@'호스트' [,'유저명'@'호스트' ]...

REVOKE예제

mysql>REVOKE DELETE ON test.* FROM 'user'@'localhost';

유저삭제

유저를 삭제하는 구문은 다음과 같이 된다. 유저를 삭제하려면 CREATE USER 또는 mysql권한 테이블에 대해서 DELETE권한이 필요하게 된다.

MySQL 4.1까지는 유저에 어떠한 권한이 있으면 삭제가 되지 않았다.

DROP USER '유저명'@'호스트'

MySQL 유저관리1: 유저추가

유저를 추가하는 경우 CREATE USER문이던지 GRANT문을 사용한다.

존재하지 않는 유저인 경우에는 자동적으로 추가된다.

1. CREATE USER '사용자명'@'호스트' [IDENTIFIED BY [PASSWORD] '평문 패스워드'] [, '사용자명'@'호스트' [IDENTIFIED BY [PASSWORD] '평문 패스워드']] ...

CREATE USER로 새로이 생성된 유저에는 권한은 없다.

2. GRANT 권한 [(필드명[,필드명...])] [,권한 [(필드명[,필드명...])] ] ...

ON {

[TABLE] {테이블명|*|*.*|데이터베이스명.*}

| PROCEDURE 데이터베이스명.스토어드프로시져명

| FUNCTION 데이터베이스명.FUNCTION명

}

TO '사용자명'@'호스트' [IDENTIFIED BY [PASSWORD] '평문 패스워드']

[, '사용자명'@'호스트' [IDENTIFIED BY [PASSWORD] '평문 패스워드']] ...

[REQUIRE

NONE |

[{SSL|X509}]

[CIPHER 'cipher' [AND]]

[ISSUER 'issuer' [AND]]

[SUBJECT 'subject' ]

]

[WITH 리소스* [리소스*] ...]

리소스*={

GRANT OPTION

| MAX_QUERIES_PER_HOUR 수

| MAX_UPDATES_PER_HOUR 수

| MAX_CONNECTIONS_PER_HOUR 수

| MAX_USER_CONNECTIONS 수

}

ON구절은 MySQL 5.0.6에서부터 사용할 수 있게 되었다. 부여하는 권한은 TABLE, PROCEDURE, FUNCTION가운데 지정한다. 또 REQUIRE구절은 SSL통신을 수행하고자 할 경우 사용한다. WITH구절은 리소스 제한을 하고 싶은 경우, GRANT문을 실행하고 있는 유저의 권한을 부여하고 싶을 경우 지정한다.

권한부여예

mysql>GRANT INSERT, SELECT, UPDATE, DELETE ON test.* TO 'user'@'localhost' IDENTIFIED BY 'pass';

MySQL 유저인증과 권한

MySQL에서는 유저명, 패스워드, 권한등은 전부 "mysql" 데이터베이스의 여러개의 테이블(권한 테이블이라고 불리움)에 기록된다.

유저명과 호스트명(IP address)이 셋트로 인증에 사용되고 유저명이 같아도 호스트가 다르면 다른 것으로 취급된다.

유저는 OS계정이나 데이터베이스, MySQL서버 프로세스하고는 전혀 관계가 없다.

또. "데이터베이스 소유자" 라는 개념도 없다.

부여된 권한에 따라서 데이터베이스나 테이블에 관한 조작이 제한되는 것뿐이다.

권한 정보는 기동시에 테이블에서 메모리로 로드된다.

초기 인스톨 상태에서는 모든 조작이 허용되는 'root'@'localhost'(패스워드 없음)가 존재한다.

운용전에 패스워드를 설정하는 것을 잊어버려서는 안된다.

또 'root'라는 유저명을 변경해도 상관없다.

유저명과 호스트

유저명과 호스트는 세트로 인증에 사용된다. 호스트에는 호스트명또는 IP주소, 네트워크지정이 가능하다. 네트워크 지정의 경우는 비트마스크 형식으로는 지정되지 않는 것을 주의해야한다.

유저명과 호스트 예

'user1'@'server1'

'user1'@'server1.domain.jp'

'user1'@'192.168.0.1'

'user1'@'192.168.0.0/255.255.255.192'

'user1'@'192.168.0.%'

'user1'@'%'

''@'127.0.0.1'

• 127.0.0.1과 localhost

Unix계열 MySQL서버의 경우 MySQL은 "127.0.0.1"과 "localhost"를 다른 걸로 다룬다. 127.0.0.1는 서버에 TCP/IP를 사용해서 접속해온 클라이언트, localhost는 소켓파일을 사용해서 접속해온 클라이언트로 다루게 된다.

• 와일드카드

호스트 부분의 "%"는 와일드카드로 어떠한 문자열에도 매치한다. 호스트에 지정한 경우는 "모든것"을 의미한다. "_"는 한 문자에 매치한다. 그리고 유저명에는 "%", "_"의 와일드카드는 지정할 수 없다.

• Anonymous유저

유저부분을 공백으로 한 경우, 어떠한 유저라도 매치하게 된다. MySQL 초기 인스톨 상태의 경우, test데이터베이스에 대해서 이 Anonymous유저가 접근할 수 있도록 되어있다.

실제 운용으로 들어갈 때에는 시큐리티 홀이 되기쉽기때문에 Anonymous유저를 삭제할 것을 추천한다.

Anonymous유저의 경우 mysql.user.User컬럼,mysql.db.User 컬럼, mysql.tables_priv.User컬럼, mysql.columns_priv.User컬럼, mysql.procs_priv.User컬럼이 공문자로 되어있다.

이것에 해당되는 레코드를 삭제하고 FLUSH PRIVILEGES를 실행하면 Anonymous유저를 삭제할 수 있게된다.

Anonymous유저 확인예

mysql>SELECT * FROM mysql.user WHERE mysql.user.User = '';

mysql>SELECT * FROM mysql.db WHERE mysql.db.User = '';

mysql>SELECT * FROM mysql.tables_priv WHERE mysql.tables_priv.User = '';

mysql>SELECT * FROM mysql.columns_priv WHERE mysql.columns_priv.User = '';

mysql>SELECT * FROM mysql.procs_priv WHERE mysql.procs_priv.User = '';

Anonymous유저 삭제예

mysql>DELETE FROM mysql.user WHERE mysql.user.User = '';

mysql>DELETE FROM mysql.db WHERE mysql.db.User = '';

mysql>FLUSH PRIVILEGES;

테이블관리7 - InnoDB장애대응

InnoDB는 기동시에 자동적으로 리커버리를 수행한다.

대개의 경우 자동복구된다.

그러나 장애 발생 후에 InnoDB가 기동하지 않던지 "SELECT *"한 다음에 크래쉬되었을 경우 다음처럼 mysqld옵션을 시도해 볼 수 있다.

innodb_force_recovery = { 0|1|2|3|4|5|6 }

0: 기본값. 특수한 경우가 아닌 경우이외에는 값을 바꾸지 말 것.

1(SRV_FORCE_IGNORE_CORRUPT): 페이지파괴를 검출했어도 계속 진행. SELECT *로 덤프가능

2(SRV_FORCE_NO_BACKGROUND): purge조작(필요없는 로그를 지우는 조작)시에 일어나는 크래쉬를 방지

3(SRV_FORCE_NO_TRX_UNDO): 트랜잭션 롤백을 동작시키지 않음.

4(SRV_FORCE_NO_IBUF_MERGE): insert buffer merge조작을 수행하지 않음. 테이블 통계정보도 계산되지 않음.

5(SRV_FORCE_NO_UNDO_LOG_SCAN): UNDO로그를 록하지 않음. 불완전한 트랜잭션을 커밋한 것으로 처리.

6(SRV_FORCE_NO_LOG_REDO): 리커버리로 로그의 roll-forward를 수행하지 않음.

테이블관리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)

테이블관리3- 관리SQL

OPTIMIZE TABLE

OPTIMIZE TABLE은 테이블 최적화를 수행한다.

OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE 테이블명 [,테이블명] ..

동작테이블: MyISAM, InnoDB

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

OPTIMIZE를 InnoDB에 실행하면 OPTIMIZE TABLE은 ALTER TABLE에 맵된다.

MyISAM의 경우는 분산되어 기록된 레코드를 정리해 인덱스페이지를 소트하고 테이블의 통계정보를 갱신한다.

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

또, mysqld명령어 옵션으로 --skip-new나 --safe-mode가 지정된 경우 OPTIMIZE TABLE은 ALTER TABLE을 실행한다. 이때 모든 테이블형에 대해서 동작하게 된다.

REPAIR TABLE

REPAIR TABLE은 테이블 복구를 수행한다.

REPAIR [LOCAL|NO_WRITE_TO_BINLOG] TABLE 테이블명 [,테이블명] .. [QUICK] [EXTENDED] [USE_FRM]

동작테이블: MyISAM, ARCHIVE, CSV(5.1.9-beta이후)

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

myisamchk옵션: --recover

옵션 QUICK: MYD파일을 변경하지 않는다. myisamchk명령어를 사용한 경우 myisamchk --recover --quick과 같은 결과를 얻을 수 있다.

옵션 EXTENDED: 레코드별로 인덱스를 생성한다. myisamchk --safe-recover와 같은 결과를 얻을 수 있다.

옵션 USE_FRM: MYI파일이 없는 경우에도 사용할 수 있는 옵션이다. .frm파일과 .MYD파일에서 .MYI파일을 작성한다.

테이블관리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회이므로 보통은 키 스캔을 전부 수행하게된다.

테이블관리1 - 관리SQL

테이블 상태 체크나 최적화, 복구등을 수행하기 위한 SQL을 알아보자. 이것은 MySQL서버 기동중에 사용할 수 있다. 명령어 실행중에는 테이블 락이 걸린다. 테이블(스토리지엔진)에 따라서 동작하는 SQL문은 다르다. 적용범위나 세심한 동작, 제한은 버전이 다르면 변경될 수 있으니까 조심해야한다.

CHECK TABLE

문법: CHECK TABLE 테이블명 [,테이블명] ... [체크레벨]

동작테이블: MyISAM, InnoDB, ARCHIVE, CSV(5.1.9-beta이상)
실행에 필요한 권한 : 테이블 접근권한
myisamchk옵션: --check-upgrade, --quick, --fast, --check-only-changed, --medium-check, --extend-check
체크레벨내용:

FOR UPGRADE: 테이블이 MySQL서버에서 올바르게 동작하는지 체크한다. MySQL 5.1.7-beta에서 추가되었음.

QUICK: 부당한 링크체크. 레코드를 스킨하지는 않음.

FAST: 전에 확실히 닫혀지지않았을 것이라고 생각되어지는 테이블만 체크

CHANGED: 전 체크이후에 변경 또는 확실히 닫혀지지 않았을 것이라고 생각되어지는 테이블만 체크

MEDIUM: 기본값. 레코드를 스캔해서 삭제된 링크가 맞는지 그렇지 않은지 체크. 키의 체크섬을 계산해서 확인한다.

EXTENDED: 모든 레코드를 스캔해서 모든 키를 체크한다. 확실하지만 시간이 걸린다.

CHECKSUM TABLE

문법: CHECK TABLE 테이블명 [, 테이블명] ... [QUICK|EXTENDED]

동작테이블: MyISAM

실행에 필요한 권한: 테이블 접근권한

내용: 테이블 체크섬값을 리턴해준다.

로그파일감시3

3.FIFO 특수파일을 이용한 자작툴 제작

예를들어 General로그 파일의 옵션 --log=에 FIFO특수파일을 지정할 수 있다.

Unix계열에서는 문제없이 동작한다.

지정된 FIFO특수 파일을 읽어들이는 툴을 만들면 될 것이다.

자작툴이 FIFO를 읽어들이기 전용으로 오픈하는 것으로 해놓고 MySQL서버를 기동한다.

다음 코드는 FIFO파일을 읽어 그것을 표준출력에 보내는 단순한 C샘플코드이다.

인수처리, FIFO파일을 작성하는 처리, 에러처리, 시그날을 받았을 경우의 처리등 자세한 처리는 하지 않고 있으므로 본격적으로 사용하려면 수정을 해야할 것이다.

FIFO파일은 Linux에서는 mkfifo명령어로 간단히 작성가능하다.

FIFO특수파일 작성

shell$ mkfifo --mode=660 /tmp/mylog

/****샘플코드 ****/

#include <stdio.h>

#include <sys/types.h>

#include <sys/stat.h>

#include <fcntl.h>

#include <unistd.h>

#include <stdlib.h>

#define LOGFIFO "/tmp/mylog"

int main(int argc, char **argv)

{

int fd;

char buf[256];

ssize_t st_rd;

fd=open(LOGFIFO, O_RDONLY|O_NONBLOCK);

if ( fd == -1 ){

printf("can't open FIFO file.¥n");

exit(1);

}

while(1) {

st_rd = read(fd, buf, sizeof(buf)-1);

if ( st_rd > 0 ) {

buf[st_rd] = '¥0';

fputs(buf, stdout);

}

else if ( st_rd == 0 ) {

}

}//while end

close(fd);

exit(0);

}

로그파일감시2

2.swatch, logsurfer, logcheck등의 로그감시 툴을 사용

파일에 로그가 써 졌을 경우, swatch나 logsurfer, logcheck등의 툴을 사용할 수 있다.

swatch와 logsurfer는 데몬으로 파일을 감시한다.

logsurfer는 C로 만들어져 있고 swatch는 Perl로 기술되어 있다.

한개의 파일당 한개의 데몬을 띄울 필요가 있다. logcheck는 쉘 스크립트로 만들어져서 cron으로 기동되는 것을 전제로 하고 있다.

logserfer: http://www.crypt.gen.nz/logsurfer/

swatch: http://swatch.sourceforge.net/ http://sourceforge.net/projects/swatch/

logcheck: http://logcheck.org/

로그파일감시

에러로그 파일, 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;