기술자료/리눅스
Mysql binlog 이용백업방법
인성정보
2010. 11. 1. 12:01
반응형
./mysql-bin.xxxxxxx 의 정체!!!
반말로 적어서 죄송 고치기도 뭐하고 해서... ^^;;
어느님이 정리해서 올려달라고 해서 올립니다.
회사 직원의 실수로 Mysql 을 특정 DB를 날렸다.
결국 복구는 했지만 백업화일을 만들어 두지 않아서 나름 귀찮고 힘든 과정이었다.
그래서 mysql 를 날렸을때 복구하는 방법을 간단하게나마 설명하고자 한다.
root 계정으로 작업하고 /usr/local/mysql 을 기본 디렉토리로 본다는 가정하에.. ^^
mysqldump를 이용한 백업화일이 존재하는 경우
/usr/local/mysql/bin/mysql -u root -p < BackUp.sql
가장 쉽게 복구하는 방법이다.
하지만 이 경우 문제점이 있다.
Cron 등의 프로그램으로 매일 백업을 받는다고 하더라도...
백업받은 시점과 DB를 날린 시점에서의 데이터는 다를수 있기 때문이다.
그런 부분을 커버 할수 있는것이 binlog 이다.
원래 binlog는 replication 을 위한 것인데... replication 이 아닌 복구용도로도 훌륭한 데이터이다.
/etc/my.cnf 에..
log-bin 라는 부분이 주석처리 되어 있지않으면..
mysql 은 기본적으로 /usr/local/mysql/data 디렉토리에 Host명-bin.000001 Host명-bin.000002 .... 과 같은
형식으로 만들어 질것이다. mysql을 재시작할때마다 Host명-bin.0000001 에서 숫자부분이 늘어난 화일이
생성되며 그 화일에 새롭게 기록한다.
※ mysql binlog 디렉토리 설정
my.cnf 에서
log_bin = /usr/local/mysql/data/mysql-bin.log <== 이런식의 설정도 가능하다.
bin 로그에는 기본적으로 DDL(Create, Alter, Drop)문과 DML중 Insert Update Delect 문등이 기록되는데..
이 기록들은 기본적으로 데이터에 변화를 주는 SQL문들이다..
bin 로그를 보려면 log의 데이터를변환해야한다.
binlog는 기본적으로 바이너리로 되어 있기에 텍스트 형태로 푸는 과정이 필요하다.
/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/Host명-bin.000001 >> 1.sql
이와 같은 과정으로 모든 binlog를 푼다..
vi 등으로 보면 시간 및 각각의 sql문들이 보일것이다..
※ 로그화일 쪼개기
가끔 log 화일이 너무 커서 제대로 인식이 안되는 경우가 있다.
이런 경우 split 이라는 훌륭한 툴이 있다.
split -l 100000 1.sql
10만 라인별로 1.sql 을 xaa, xab, aac..... 순으로 화일이 생성되면서 짜른다.
자 이제 복구를 하자라고 하고 싶지만..
여기서 주의 할것이 있다..
처음에... drop database 같은 sql 을 날렸다면
이 역시 binlog에 포함되어 있을것이다.
맨 마지막 로그 화일을 열어서 그부분을 삭제 또는 주석처리하자..
그렇지 않으면 애써 복구한것을 마지막에 다시 또 날리는 되는 결과를 .....
이제서야 복구를 할수 있을것이다.
/usr/local/mysql/bin/mysql -u root -p < 1.sql
만약 여기서도 5만줄쯤에서 오류가 났다라고 한다면...
1.sql 에서 1~49999 까지는 실행이 되었다.
그러므로 1~49999 를 삭제(vi 로 연다음 d49999 이라고 입력하고 Enter ...)한다.
그리고 문제가 되는 첫줄(1~49999를 삭제하기전에는 오류가 난 5만번째라인)의 SQL 부분을 해결한다음...
저장....
그리고 다시 복구과정을 반복
/usr/local/mysql/bin/mysql -u root -p < 1.sql
어느님이 정리해서 올려달라고 해서 올립니다.
회사 직원의 실수로 Mysql 을 특정 DB를 날렸다.
결국 복구는 했지만 백업화일을 만들어 두지 않아서 나름 귀찮고 힘든 과정이었다.
그래서 mysql 를 날렸을때 복구하는 방법을 간단하게나마 설명하고자 한다.
root 계정으로 작업하고 /usr/local/mysql 을 기본 디렉토리로 본다는 가정하에.. ^^
mysqldump를 이용한 백업화일이 존재하는 경우
/usr/local/mysql/bin/mysql -u root -p < BackUp.sql
가장 쉽게 복구하는 방법이다.
하지만 이 경우 문제점이 있다.
Cron 등의 프로그램으로 매일 백업을 받는다고 하더라도...
백업받은 시점과 DB를 날린 시점에서의 데이터는 다를수 있기 때문이다.
그런 부분을 커버 할수 있는것이 binlog 이다.
원래 binlog는 replication 을 위한 것인데... replication 이 아닌 복구용도로도 훌륭한 데이터이다.
/etc/my.cnf 에..
log-bin 라는 부분이 주석처리 되어 있지않으면..
mysql 은 기본적으로 /usr/local/mysql/data 디렉토리에 Host명-bin.000001 Host명-bin.000002 .... 과 같은
형식으로 만들어 질것이다. mysql을 재시작할때마다 Host명-bin.0000001 에서 숫자부분이 늘어난 화일이
생성되며 그 화일에 새롭게 기록한다.
※ mysql binlog 디렉토리 설정
my.cnf 에서
log_bin = /usr/local/mysql/data/mysql-bin.log <== 이런식의 설정도 가능하다.
bin 로그에는 기본적으로 DDL(Create, Alter, Drop)문과 DML중 Insert Update Delect 문등이 기록되는데..
이 기록들은 기본적으로 데이터에 변화를 주는 SQL문들이다..
bin 로그를 보려면 log의 데이터를변환해야한다.
binlog는 기본적으로 바이너리로 되어 있기에 텍스트 형태로 푸는 과정이 필요하다.
/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/Host명-bin.000001 >> 1.sql
이와 같은 과정으로 모든 binlog를 푼다..
vi 등으로 보면 시간 및 각각의 sql문들이 보일것이다..
※ 로그화일 쪼개기
가끔 log 화일이 너무 커서 제대로 인식이 안되는 경우가 있다.
이런 경우 split 이라는 훌륭한 툴이 있다.
split -l 100000 1.sql
10만 라인별로 1.sql 을 xaa, xab, aac..... 순으로 화일이 생성되면서 짜른다.
자 이제 복구를 하자라고 하고 싶지만..
여기서 주의 할것이 있다..
처음에... drop database 같은 sql 을 날렸다면
이 역시 binlog에 포함되어 있을것이다.
맨 마지막 로그 화일을 열어서 그부분을 삭제 또는 주석처리하자..
그렇지 않으면 애써 복구한것을 마지막에 다시 또 날리는 되는 결과를 .....
이제서야 복구를 할수 있을것이다.
/usr/local/mysql/bin/mysql -u root -p < 1.sql
만약 여기서도 5만줄쯤에서 오류가 났다라고 한다면...
1.sql 에서 1~49999 까지는 실행이 되었다.
그러므로 1~49999 를 삭제(vi 로 연다음 d49999 이라고 입력하고 Enter ...)한다.
그리고 문제가 되는 첫줄(1~49999를 삭제하기전에는 오류가 난 5만번째라인)의 SQL 부분을 해결한다음...
저장....
그리고 다시 복구과정을 반복
/usr/local/mysql/bin/mysql -u root -p < 1.sql
출처 : http://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=tipntech&wr_id=53491&page=1
추가 : mysql bin log를 바이너리로 만들때 character set 을 설정 안해주면 한글이 깨질지도... ^^;
옵션을 적어봤습니다.
--character-sets-dir=name
Directory where character sets are.
-d, --database=name List entries for just this database (local log only).
-D, --disable-log-bin
Disable binary log. This is useful, if you enabled
--to-last-log and are sending the output to the same
MySQL server. This way you could avoid an endless loop.
You would also like to use it when restoring after a
crash to avoid duplication of the statements you already
have. NOTE: you will need a SUPER privilege to use this
option.
-f, --force-read Force reading unknown binlog events.
-?, --help Display this help and exit.
-H, --hexdump Augment output with hexadecimal and ASCII event dump.
-h, --host=name Get the binlog from server.
-o, --offset=# Skip the first N entries.
-p, --password[=name]
Password to connect to remote server.
-P, --port=# Use port to connect to the remote server.
-j, --position=# Deprecated. Use --start-position instead.
--protocol=name The protocol of connection (tcp,socket,pipe,memory).
-r, --result-file=name
Direct output to a given file.
-R, --read-from-remote-server
Read binary logs from a MySQL server
--open_files_limit=#
Used to reserve file descriptors for usage by this
program
--set-charset=name Add 'SET NAMES character_set' to the output.
-s, --short-form Just show the queries, no extra info.
-S, --socket=name Socket file to use for connection.
--start-datetime=name
Start reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
--stop-datetime=name
Stop reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
--start-position=# Start reading the binlog at position N. Applies to the
first binlog passed on the command line.
--stop-position=# Stop reading the binlog at position N. Applies to the
last binlog passed on the command line.
-t, --to-last-log Requires -R. Will not stop at the end of the requested
binlog but rather continue printing until the end of the
last binlog of the MySQL server. If you send the output
to the same MySQL server, that may lead to an endless
loop.
-u, --user=name Connect to the remote server as username.
-l, --local-load=name
Prepare local temporary files for LOAD DATA INFILE in the
specified directory.
-V, --version Print version and exit.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
character-sets-dir (No default value)
database (No default value)
disable-log-bin FALSE
force-read FALSE
hexdump FALSE
host (No default value)
offset 0
port 3306
position 4
read-from-remote-server FALSE
open_files_limit 64
set-charset (No default value)
short-form FALSE
socket /tmp/mysql.sock
start-datetime (No default value)
stop-datetime (No default value)
start-position 4
stop-position 18446744073709551615
to-last-log FALSE
user (No default value)
local-load (No default value)
추가 : mysql bin log를 바이너리로 만들때 character set 을 설정 안해주면 한글이 깨질지도... ^^;
옵션을 적어봤습니다.
--character-sets-dir=name
Directory where character sets are.
-d, --database=name List entries for just this database (local log only).
-D, --disable-log-bin
Disable binary log. This is useful, if you enabled
--to-last-log and are sending the output to the same
MySQL server. This way you could avoid an endless loop.
You would also like to use it when restoring after a
crash to avoid duplication of the statements you already
have. NOTE: you will need a SUPER privilege to use this
option.
-f, --force-read Force reading unknown binlog events.
-?, --help Display this help and exit.
-H, --hexdump Augment output with hexadecimal and ASCII event dump.
-h, --host=name Get the binlog from server.
-o, --offset=# Skip the first N entries.
-p, --password[=name]
Password to connect to remote server.
-P, --port=# Use port to connect to the remote server.
-j, --position=# Deprecated. Use --start-position instead.
--protocol=name The protocol of connection (tcp,socket,pipe,memory).
-r, --result-file=name
Direct output to a given file.
-R, --read-from-remote-server
Read binary logs from a MySQL server
--open_files_limit=#
Used to reserve file descriptors for usage by this
program
--set-charset=name Add 'SET NAMES character_set' to the output.
-s, --short-form Just show the queries, no extra info.
-S, --socket=name Socket file to use for connection.
--start-datetime=name
Start reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
--stop-datetime=name
Stop reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
--start-position=# Start reading the binlog at position N. Applies to the
first binlog passed on the command line.
--stop-position=# Stop reading the binlog at position N. Applies to the
last binlog passed on the command line.
-t, --to-last-log Requires -R. Will not stop at the end of the requested
binlog but rather continue printing until the end of the
last binlog of the MySQL server. If you send the output
to the same MySQL server, that may lead to an endless
loop.
-u, --user=name Connect to the remote server as username.
-l, --local-load=name
Prepare local temporary files for LOAD DATA INFILE in the
specified directory.
-V, --version Print version and exit.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
character-sets-dir (No default value)
database (No default value)
disable-log-bin FALSE
force-read FALSE
hexdump FALSE
host (No default value)
offset 0
port 3306
position 4
read-from-remote-server FALSE
open_files_limit 64
set-charset (No default value)
short-form FALSE
socket /tmp/mysql.sock
start-datetime (No default value)
stop-datetime (No default value)
start-position 4
stop-position 18446744073709551615
to-last-log FALSE
user (No default value)
local-load (No default value)
반응형