7. MySQL의 백업 방안
- 데이타 베이스의 생명은 저장된 자료일것이다. 이장에서는 자료를 백업할수있는 방안에 대해 알아보기로 한다.
7.1. 파일
- 앞에서 설명한것처럼 데이타베이스명으로 디렉토리가 생성되며 테이블은 그 안에서 파일로 생성된다. 이 파일을 저장해놓기전에 먼저 LOCK TABLES을 통해 LOCK을 걸어 주는 것이 좋다. 백업본을 만드는 동안에 다른 쓰레드에서 질의가 들어올수있기 때문 이다.
- 이 방법은 dump하는 시간과 공간을 많이 줄여준다. 하지만 문제가 있을수도 있다.
7.2 mysqldump
- SQL문장으로 백업을 해준다. 시간이 많이 걸리며 특정 컬럼은 저장하지 못하는 경우가 있다.
Usage: mysqldump [OPTIONS] database [tables]
-?, --help 도움말을 출력한다.
--add-drop-table CREATE 앞에 DROP TABLE을 포함
--add-locks INSERT 문에 ADD LOOCKS문을 포함
--delayed Insert 문에 DELAYED을 포함
-f, --force SQL 에러가 있더라도 계속해서 수행
-h, --host=... 특정 호스트에 연결
-l, --lock-tables 모든 테이블에 READ LOCK을 건다.
--opt --quick --add-drop-table --add-locks
--extended-insert --use-locks 한것과 같다.
-p, --password[=...] 패스워드 입력
-P, --port=... 연결하고자 하는 서버의 포트번호
-q, --quick 쿼리를 큐에 바로 표준출력을 한다.
-S, --socket=... 소켓을 이용하여 연결
-T, --tab=... 주어진 디렉토리에 각각의 테이블을 텍스트 파일로 생성
.sql => SQL문장이 저장
.txt => 데이타 파일 ( core )
-u, --user=# 새로운 유저로 연결
-V, --version 버전을 보여준다.
-w, --where= 오직 선택된 레코드만 출력
Use -T (--tab=...) with --fields-...
--fields-terminated-by=...
--fields-enclosed-by=...
--fields-optionally-enclosed-by=...
--fields-escaped-by=...
--lines-terminated-by=...
예)
shell> mysqldump --where=d_no=1 school std
# MySQL dump 5.13
#
# Host: localhost Database: school
#--------------------------------------------------------
# Server version 3.22.22-log
#
# Table structure for table \std\
#
CREATE TABLE std (
no smallint(6) DEFAULT \0\ NOT NULL auto_increment,
first_name varchar(20) DEFAULT \ NOT NULL,
last_name varchar(20),
age tinyint(4),
birth date,
d_no tinyint(4),
sex enum(\x\,\y\) DEFAULT \x\,
PRIMARY KEY (no)
);
#
# Dumping data for table \std\
# WHERE: d_no=1
#
INSERT INTO std VALUES (1,\길동\,\홍\,20,\1980-01-01\,1,\x\);
INSERT INTO std VALUES (4,\기순\,\홍\,21,\1979-09-24\,1,\y\);
INSERT INTO std VALUES (6,\순이\,\김\,33,\1968-12-24\,1,\y\);
shell> mysqldump --tab=/tmp --lock-tables --opt school
shell> /tmp/*.sql /tmp/*.txt
/tmp/depart.sql /tmp/depart.txt /tmp/std.sql /tmp/std.txt
shell> more /tmp/std.sql
# MySQL dump 5.13
#
# Host: localhost Database: school
#--------------------------------------------------------
# Server version 3.22.22-log
#
# Table structure for table \std\
#
DROP TABLE IF EXISTS std;
CREATE TABLE std (
no smallint(6) DEFAULT \0\ NOT NULL auto_increment,
first_name varchar(20) DEFAULT \ NOT NULL,
last_name varchar(20),
age tinyint(4),
birth date,
d_no tinyint(4),
sex enum(\x\,\y\) DEFAULT \x\,
PRIMARY KEY (no)
);
shell> more /tmp/std.txt
1 길동 홍 20 1980-01-01 1 x
2 기철 홍 24 1976-10-23 2 x
3 기자 홍 22 1978-02-01 2 \N
4 기순 홍 21 1979-09-24 1 y
8 말자 이 \N \N \N x
7 영수 박 34 1967-05-14 2 x
6 순이 김 33 1968-12-24 1 y
5 철수 이 33 1968-03-21 2 x
7.3 mysqlimport
- 이것은 백업을 받는것과는 무관하지만 mysqldump와 반대되는 개념으로 이장에서 설명하기로 한다.
- 이것은 앞에서 설명한 LOAD 구문과 같다.
Usage: mysqlimport [OPTIONS] database textfile...
-?, --help 도움말을 출력한다.
-d, --delete 테이블에 입력하기전에 모든 레코드를 지운다.
-f, --force SQL 에러가 있더라도 계속해서 수행
-h, --host=... 특정 호스트에 연결
-l, --lock-tables 모든 테이블에 READ LOCK을 건다.
-L, --local 클라이언트로부터 파일을 읽어들인다.
-p, --password[=...] 패스워드 입력
-P, --port=... 연결하고자 하는 서버의 포트번호
-r, --replace 중복된 키가 있을때 새로운 키로 입력
-S, --socket=... 소켓을 이용하여 연결
-u, --user=# 새로운 유저로 연결
-V, --version 버전을 보여준다.
--fields-terminated-by=...
--fields-enclosed-by=...
--fields-optionally-enclosed-by=...
--fields-escaped-by=...
--lines-terminated-by=...
8. MySQL 튜닝하기
모든 DBMS는 대부분 현재의 상태에 맞게 설정이 해주는 것이 좋다. 가령 접속자수가 많은 경우 mysqld을 갯수를 많이 확보함으로써 빠른 처리를 할수 있을것이다.
이장에서는 MySQL의 서버 세팅에 대해 알아보기로 한다.
8.1 mysqld
MySQL을 실행하기 위해서는 주로 safe_mysqld라는 것을 이용한다. 이것은 mysql을 안정하게 실행하기위해 만들어 놓은것으로 여러가지 설정을 여기에서 해주는 것이 좋다.
그럼 먼저 safe_mysqld을 보기로 하자.
shell> vi /usr/bin/safe_mysqld
echo \Starting mysqld daemon with databases from $DATADIR\
=> 이부분 이후가 실제적으로 처리되는 부분이다. 앞에서 설명을 했지만 myqsld에 여러가지 옵션을 추가해주면된다.
그럼 mysqld의 여러가지 옵션에 대해 알아보자.
[/pre]
Usage: mysqld [OPTIONS]
-b, --basedir=path 인스톨된 경로. 이것을 이용한 상대 경로를 지정
--chroot=path msqld 데몬의 루트 디렉토리를 path로 바꾼다.
-h, --datadir=path 데이타 베이스 루트를 path로 둔다.
--enable-locking 시스템 락킹(Locking)을 사용한다.
-?, --help 도움말을 출력한다.
-L, --language=... 클라이언트의 에러 메세지의 언어 선택
-l, --log[=file] Log connections and queries to file
--log-update[=file] 데이타가 Update될때마다 모든 SQL을 file에 기록한다.
refresh, flush-logs나 서버가 재 시작할때마다
file.#으로 번호가 매겨져 차례로 저장
--pid-file=path Pid file used by safe_mysqld
-P, --port=... 연결하고자 하는 서버의 포트번호
-O, --set-variable var=option
변수에 값을 지정한다.
-Sg, --skip-grant-tables
grant table 없이 시작한다. 이것은 모든 유저들이
모든권한을 가지고 수행할수 있음을 의미한다.
( grant table이 문제가 있을때만 사용)
--skip-locking Don\t use system locking. To use isamchk one has
to shut down the server.
--skip-name-resolve 호스트네임을 resolve하지않는다.
IP\s 또는 \localhost\
--skip-networking TCP/IPf를 통한 연결을 허용하지 않는다.
--skip-host-cache 호스트네임 캐쉬를 사용하지 않는다.
-S, --socket=... 소켓을 이용하여 연결
-t, --tmpdir=path Path for temporary files
--user=user_name user_name으로 mysqld을 실행한다.
-V, --version 버전을 보여준다.
shell> mysqld -h
.....................................
............중 략 .................
.....................................
Possible variables for option --set-variable (-O) are:
back_log current value: 5
connect_timeout current value: 5
delayed_insert_timeout current value: 300
delayed_insert_limit current value: 100
flush_time current value: 0
join_buffer current value: 131072
key_buffer current value: 8388600
long_query_time current value: 10
max_allowed_packet current value: 1048576
max_connections current value: 100
max_connect_errors current value: 10
max_delayed_insert_threads current value: 20
delayed_queue_size current value: 1000
max_join_size current value: 4294967295
max_sort_length current value: 1024
net_buffer_length current value: 16384
record_buffer current value: 131072
sort_buffer current value: 2097144
table_cache current value: 64
tmp_table_size current value: 1048576
thread_stack current value: 65536
wait_timeout current value: 28800
[/pre]
[ 문태준씨 번역 ]
-back_log
mysql이 가질 수 있는 최대 연결 요청의 수. 이것은 main mysql 스레드가 매우 짧은 시간 동안 매우 많은 연결 요청을 받을 때 기능을 한다. 이때 메인 스레드가 연결을 체크하고 새로운 스레드를 시작하는데는 약간의 시간이 걸린다.(그러나 아주 짧은 시간임) back_log 값은 mysql이 순간적으로 새로운 요청에 답하는 것을 멈추기전에 이 짧은 시간동안 얼마나 많은 요청을 쌓아두고 있는지를 지정한다. 매우 짧은 시간동안 매우 많은 연결이 예상될때
만 이 값을 증가시켜야 한다.
다른 말로 이 값은 tcp/ip 연결을 받는 listen queue의 크기이다. 각 운영체제마다 이러한 큐의 크기에 한계가 있다. Unix system call listen(2) 매뉴얼페이지에 자세한 정보가 있다. back_log값의 한계는 운영체제 문서를 확인해봐라. back_log를 최대값보다 더 높여도 효과가 없다.
-connect_timeout
Bad handshake에 반응하기 전에 연결 패킷을 mysql 서버에서 기다리는 시간.(초)
-join_buffer
(인덱스를 사용하지 않는 조인의) full-join에서 사용하는 버퍼의 크기. 버퍼는 두 테이블 사이에서 각 full-join마다 한번 할당이 된다. 인덱싱을 추가하지 못할 때 조인 버퍼를 증가시키면 full join의 속도를 향상시킬 수 있다. (일반적으로 빠르게 조인을 하는 가장 좋은 방법은인덱스를 추가하는 것이다)
-key_buffer
인덱스 블락은 버퍼링되고 모든 스레드에서 공유한다. 키 버퍼는 인덱스 블락에서 사용하는 버퍼의 크기이다. 인덱스가 많은 테이블에서 delete나 insert 작업을 많이 하면 키 버퍼값을 증가시키는 것이 좋다. 더 빠른 속도를 내려면 LOCK TABLES를 사용하자. [Lock Tables]
참고.
-max_allowed_packet
한 패킷의 최대 크기. 메시지 버퍼는 net_buffer_length 바이트로 초기화되지만 필요하면 최대 허용 패킷 바이트를 증가시킬 수 있다.기본값은 큰 패킷을 잡기에는 작다. 거대 BLOB 컬럼을 사용한다면 값을 증가시켜야 한다. 사용자가 원하는 최대 blob만큼 크게 해야 한다.
-max_connections
동시 클라이언트 숫자. mysqld가 필요로하는 파일 지시자(descriptor)의 숫자만큼 값을 늘려야 한다. 밑에서 파일 디스크립터 제한에 대한 내용을 참고하자.
-max_connect_errors
호스트에서 최대 연결 에러이상의 interrupted 연결이 있으면 더 많은 연결을 위해 호스트는 block화 된다. FLUSH HOSTS 명령으로 호스트의 block을 해제할 수 있다.
-max_join_size
최대 조인 크기이상으로 레크도를 읽는 조인을 하면 에러가 난다. 만약 사용자가 where 문을 사용하지 않고 시간이 많이 걸리면서 몇백만개의 레코드를 읽는 조인을 수행하려 하면 이 값을 설정한다.
-max_sort_length
BLOB나 TEXT 값으로 정열할때 사용하는 바이트의 숫자. (각 값중 오직 첫번째 max_sort_length 바이트만 사용된다. 나머지는 무시된다)
-net_buffer_length
질의에서 통신 버퍼가 초기화되는 크기. 일반적으로 바뀌지 않지만 매우 적은 메모리를 가지고 있을 때 예상되는 질의에 맞게 세팅할 수 있다. (이것은 클라이언트에 가는 예상된 sql 문의 길이이다. 질의문이 이 크기를 넘으면 버퍼는 자동으로 max_allowed_packet 바이트까지 증가한다)
-record_buffer
순차적인 검색을 하는 각 스레드에서 각 검색 테이블에 할당하는 버퍼 크기. 순차적인 검색을 많이 하면 이 값을 증가시켜야 한다.
-sort_buffer
정렬이 필요한 각 스레드에서 할당하는 버퍼 크기. order by 나 group by 오퍼레이션을 빠르게 하려면 이 값을 증가시킨다.
-table_cache
모든 스레드에서 열 수 있는 테이블의 숫자. mysqld가 필요로 하는 파일 디스크립터의 숫자만큼 이 값을 증가시켜라. mysql은 각 유일한 오픈 테이블에서 두개의 파일 디스크립터가 필요하다. 파일 디스크립터 제한을 참고한다.
-tmp_table_size
임시 테이블이 이 값을 넘으면 mysql은 \The Table tbl_name is full\이라는 에러 메시지를 낸다. 매우 많은 group by 질의를 사용하면 이 값을 증가시켜야 한다.
-thread_stack
각 스레드의 스택 사이즈. creash-me test(**역자주 : 데이터베이스의 벤치마킹을 하는 테스트입니다. 말그대로 데이터베이스를 죽여주지요) 에서 잡히는 많은 제한은 이 값에 달려있다. 기본값은 일반적으로 충분히 크다.
-wait_timeout
연결을 끊기전에 연결 활동(activity)을 서버에서 기다리는 시간(초).
table_cache 와 max_connections는 서버가 열 수 있는 최대 파일 갯수에 영향을 미친다. 이 값을 증가시키면 운영시스템에서 오픈 파일 디스크립터의 per-process 숫자의 한계까지 올릴 수 있다.
그러나 많은 시스템에서 이 한계를 증가시킬수 있다. 이렇게 하려면 각 시스템에서 이 한계를 변화시키는 방법이 매우 다양하므로 운영체제 문서를 참고해야 한다.
table_cache 는 max_connections 와 관계가 있다. 예를 들면 200개의 연결이 있으면 최소 200 * n 의 테이블 캐쉬를 가져야 한다. 여기서 n은 조인에서 테이블의 최대 숫자이다.
mysql은 매우 유용한 알고리즘을 사용하기 때문에 일반적으로는 매우 적은 메모리로 사용할 수 있으며 메모리가 많을 수록 성능이 더 많이 향상된다.
[참고]
- 설정 파일을 둘수도 있다. /usr/share/mysql/my-example.cnf 의 파일을 수정하여 /etc/my.cnf로 두면 된다.
8.2 isamchk 이용하기
- 이것은 ISAM table을 check/repair하기 위한 프로그램이다.
Usage: isamchk [OPTIONS] tables[.ISM]
-a, --analyze key의 분포를 분석한다. 조인을 빠르게 한다.
-d, --description 테이블에 대한 정보를 출력한다.
-e, --extend-check 테이블을 상세하게 점검. 일반적으로 이 옵션이 없어도
모든 에러를 찾을수 있다.
-f, --force 이전 임시 파일을 덮어쓴다. 에러가 발생하면 자동으로
-r 옵션으로 재시작한다.
-?, --help 도움말 출력
-i, --information 테이블에 대한 정보를 출력
-k, --keys-used=# -r 옵션과 같이 사용. ISAM에서 맨처음 key만 업데이트
insert빠르게할때 사용할수 있다.
-l, --no-symlinks 복구할때 심볼릭 링크를 따르지 않는다. 일반적으로
isamchk는 심볼릭 링크가 가리키는 테이브를 복구한다.
-q, --quick 빠르게 복구하기 위해 -r옵션과 같이 사용 ( data파일은
건드리지 않는다) 두번째 -q를 지정하여 원래의 데이타
파일을 사용하도록 할 수 있다.
-r, --recover 복구모드. 유일하지 않는 unique key를 제외하고 거의
복구된다.
또한 단편화된 레코드를 모우고 불필요한 공간을
제거한다.
-o, --safe-recover 구식의 복구방법. -r을 사용하는 것보다 느리다.하지만
-r이 할수 없는 경우 사용할수 있다.
-O, --set-variable var=option
변수의 설정을 바꾼다.
-s, --silent 에러만 출력. -ss는 매우 적은 출력.
-S, --sort-index index block을 sort한다. 응용 프로그램에서
read-next의 속도 증가.
-R, --sort-records=#
index에 따라 레코드 정렬. 이 작업을 하면 데이타를
지역화시킬수 있고 속도증가의 효과가 있다.
( 처음 정렬시 매우 느리다. )
-u, --unpack pack-isam으로 압축된 테이블 압축 해제
-v, --verbose 정보를 출력. -d와 -e를 함께 사용. v을 추가하여
더 다양한 정보를 볼수 있다.
-V, --version 정보를 출력
-w, --wait LOCK이 걸려 있으면 대기
--set-variable (-O)로 변경가능한 변수 :
key_buffer_size current value: 520192
read_buffer_size current value: 262136
write_buffer_size current value: 262136
sort_buffer_size current value: 2097144
sort_key_blocks current value: 16
decode_bits current value: 9
만일 메모리가 여유가 있다면 사이즈를 크게 잡아주면 isamchk가 빨라진다.
문제가 생기기전에 항상 점검을 하는 것이 좋다. -s 모드로 하면 error만 보여주므로 항상 체크를 하도록 하자. ( crontab에 등록하는 것이 좋다.)
isamchk는 \.ISD\ 파일의 복사본을 만들어 작업을 한다. 복사본을 가지고
작업을 마치면 이전의 파일이름으로 바꾸면서 작업을 끝낸다. -q 옵션은 임시 파일을 만들지 않는다. 즉 ISD 파일이 문제가 없다고 보고 새로운 ISM 파일만을 만든다. 만일 문제가 있다고 생각되어지면 -q 옵션을 주는 것이 안전하다. 두개의 -q옵션을 주면 ISD 파일을 수정할려고 시도한다. 이 경우는 디스크 공간이 없을 경우만 빼고 바람직한 방법이 아니다.
복구의 4단계
먼저 문제가 발생한 테이블이 있는 디렉토리로 이동을 한다.
isamchk를 실행유저가 쓰기 권한을 가지고 있는지 확인을 한다.
1단계: 테이블 점검
shell> isamchk *.ISM
문제가 있는 테이블을 찾아 낸다.
2단계 : 쉽고 안전한 복구
shell> isamchk -r -q tbl_name ( 빠른 복구 )
데이타 파일은 손대지 않고 인덱스 파일 복구를 시도한다.
또 문제가 있으면 먼저 데이타 파일을 백업한후 다음을 실행한다.
shell> isamchk -r tbl_name
데이타 파일에서 정확하지 않은 레코드를 삭제하고 인덱스를 재구성 이렇게 해도 복구가 되지 않으면
shell> isamchk -o tbl_name
3단계: 어려운 복구
인덱스 파일의 첫 16k 블락이 파괴되거나 정확하지 않은 정보를 가지고 있을 때 또는 인덱스 파일이 없는 경우에만 이번 단계까지 온다. 이경우 새로운 인덱스 파일을 만들어야 한다.
먼저 데이타 파일을 백업한다.
shell> mysql db_name
mysql> DELETE FROM tbl_name;
mysql> quit
한후 백업한 데이타 파일을 다시 복사(!!)한다.
shell> isamchk -r -q
로 복구가 가능할것이다.
4단게: 매우 어려운 복구
description 파일 또한 손상을 입었을 경우에만 이번 단계까지 온다.
description 파일은 테이블을 만든 이후에 변경이 되지 않기 때문에, 이러한 경우는 결코 생겨서는 안된다.
백업본에서 description 파일을 복구해서 3단계로 가 인텍스 파일을 복구 한다. 만일 백업본이 없다면 최소한 테이블의 구성은 알아야한다.
[참고]
디렉토리밑에 만들어지는 테이블 관련 파일들
tbl_name.frm 테이블 정의(형식) 파일
tbl_name.ISD 데이타 파일
tbl_name.ISM 인덱스 파일
8.1MySQL을 이용한 웹데이타 베이스 구축하기
예제) PHP을 이용
|