미스터 역마살
binlog 본문
728x90
반응형
binlog란?
Mysql 서버에서 CREATE, DROP, ALTER와 같은 DDL과 DML을 통해 DB에 변경사항이 생길때 그 변화된 이벤트를 기록하는 이진 파일이 있는데 이것이 바로 바이너리 로그(binlog)라고 한다. slow나 select등 조회 문법은 제외되며 binlog는 2가지의 중요한 용도가 있다.
binlog용도
1. 복제 구성에 사용
Mysql에서는 Replication이라는 부하분산 기능을 제공하는데 이때 binlog를 사용한다. binlog는 master-slave구조에서 master db에 생성이 되고 slave db는 master db의 binlog를 읽어들여 똑같이 이벤트를 발생시켜 master와 동일한 구조로 만들게 된다.
Replication 순서
- Master db가 트랜잭션이 커밋되는 순간 binlog에 이벤트를 생성하여 기록을한다.
- 슬레이브는 IO Thread를 통해서 마스터에 이벤트를 요청하고 받는다.
- 마스터는 이벤트를 요청 받으면 binlog dump thread를 통해서 클라이언트에게 이벤트를 전송한다.
- IO Thread는 전송받은 dump log를 이용하여 relay log를 만든다
- SQL Thread는 relay log를 읽어서 이벤트를 다시 실행하여 slave에 데이터를 복사한다.
2. 특정시점에 복구에 사용
db를 사용하다 보면 데이터 삭제나 db가 어떤 이유로 장애나 crash가 발생할 시 복구를 해야할 때가 있다. 이때 특정 시점으로 돌아가야 하는데 특정 시점 시간으로 돌아갈때 binlog를 사용한다.
binlog 활성화 방법
binlog 활성화 방법은 2가지 있다.
1. 서버구동시 설정 방법
mysql 구동시에 binlog를 활성화 시키는 방법인데 이 방법은 mysql을 재시작하는 경우는 적용이 다시 안되기 때문에 추천하지 않는 방법 이다.
[root@localhost]# mysqld_safe --log-bin=systemv-bin-log &
## --log-bin=VALUE 과 같은 형식으로 사용할수 있는데 VALUE에는 파일경로/파일명을 쓸수 있다.
## VALUE값을 생략하면 data_dir 디렉토리에 hostname-bin 이라는 이름으로 생성된다.
## (라고 모든 MySQL 공식 도큐먼테이션에서 밝히지만 5.5버전 이후에서 테스트 했을시 mysqld-bin 으로 생성되더라.
## hostname 변경시 발생될 수 있는 변수 상황을 피하기 위함이라 생각한다.)
2. my.cnf 설정
my.cnf 파일에다가 binlog를 설정하는 방법이다. binlog는 정적 파라미터 이기 때문에 서버 restart를 해야 적용이 된다.
[mysqld]
# 절대 경로를 지정해 줄수도 있고 log-bin만 설정 해도 된다.
# 경로지정을 생략하면 data_dir 디렉토리에 hostname-bin 이라는 이름으로 생성된다.
log-bin=/var/log/mysql_bin
server-id=1
binlog_format=ROW
expire_logs_days=7
binlog_checksum=NONE
binlog_row_image=FULLmy.cnf 설정 옵션
- log-bin
binlog를 활성화 하기 위한 옵션으로 binlog 파일이 저장될 경로나 파일 이름을 설정 할수 있다. 경로나 파일명 없이 log-bin만 적을 경우나 파일명만 적을 경우default로 /var/lib/mysql 경로에 생성 된다. - server-id
master와 slave의 서버는 다르면 되며 아무 숫자나 사용이 가능하다. - binlog_format
binlog에 저장되는 포맷을 설정하는 값으로써 ROW, STATEMENT, MIXED 3가지의 설정 값이 있다.
Mysql 5.7.6 까지는 기본값이 STATEMENT이고 그 이후 버전은 ROW가 기본값이다.(클러스터는 예외로 MIXED)- ROW : 행기반 방식이다. STATEMENT방식과는 좀 다르게 개별 테이블 행이 어떻게 영향을 받는지를 나타내는 이벤트를 binlog에 쓴다. SQL 문법이 아니라 결과값을 binlog에 저장하게 된다. 따라서 테이블은 항상 pk를 사용하여 행을 효율적으로 식별 할 수 있도록 하는것이 중요하다.
- STATEMENT : 가장 전통적인 방식이며 마스터에서 실행한 SQL을 그대로 바이너리 로그에 작성하고 그 로그를 슬레이브로 전송하여 슬레이브에도 실행하게 하는 방식이다.
- MIXED : 혼합로깅 방식이며 기본적으로 STATEMENT방식로깅이 사용되지만 특정 로깅의 경우 ROW 방식의 로깅으로 전환된다.
- isolation level이 read-committed일 경우 현재 트랜잭션이 종료되지 않았더라도 다른 트랙잭션에서 동일한 데이터의 commit이 일어나면 현재 트랜잭션에서도 변경된 값이 보이게 된다.이런 환경에서 statement방식을 사용하게 되면 트랜잭션 단위로 순서대로 로깅하기 때문에 복구나 슬레이브동기화시 원하는 바와 달리 다른 결과가 나타날 수 있다.
- binlog에 저장되는 포맷을 설정하는 값으로써 ROW, STATEMENT, MIXED 3가지의 설정 값이 있다.
- expire_logs_days
binlog 파일 보관 기간(일 단위), default 0, 즉 binlog를 삭제하지 않음 - binlog_cache_size
트랜잭션을 처리하는 스레드가 시작되면 binlog_cache_size라는 옵션에 설정되어 있는 크기만큼 버퍼를 할당 한다. 그리고 명령문을 이곳에 저장하게 된다.
크기가 큰 트랜잭션을 자주 사용하는 db의 경우 binlog_cache_size에 저장하지 못해 disk에 쓰게 되는데 이런경우 binlog_cache_size를 늘려주어 성능을 향상 시킬 수 있다. default값은 32kb이다. - max_binlog_size
binlog 파일의 최대 크기 지정(단위 G,M,K,bytes), default 1G, MAX도 1G - binlog_checksum
Replication 구조에서 binlog event가 문제없이 전달되고 있음을 보장하기 위한 수단에서 도입된것으로 5.6 부터 도입이 됨
문제란?- 하드웨어 문제 : 메모리 fault, 디스크 깨짐
- 소프트웨어 문제 : I/O thread 버그 등
- 네트워크 문제 : 네트워크 중단 등
- binlog_row_image-
binlog_format을 row로 했을때 로깅한 컬럼 set을 설정할수 있다 (FULL, MINIMAL, NOBLOB)
- FULL : 변경 전/후 이미지를 전체 binlog에 기록(Default)
- MINIMAL : 최소한의 값만 로깅, 변경된 행을 식별하는 데 필요한 이전 이미지의 열만 기록
- NOBLOB : BLOB, TEXT와 같이 사이즈가 큰값을 제외하고 로깅
binlog 설정에 대해 더 자세한 내용은 아래 링크 참조
https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html
MySQL :: MySQL 5.7 Reference Manual :: 16.1.6.4 Binary Logging Options and Variables
16.1.6.4 Binary Logging Options and Variables You can use the mysqld options and system variables that are described in this section to affect the operation of the binary log as well as to control which statements are written to the binary log. For additi
dev.mysql.com
binlog 읽기 위한 권한
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO {ID}@'{IP}' IDENTIFIED BY '{PWD}';
binlog 삭제 방법
purge master logs to ‘mysql-bin.000020’;
binlog를 SQL로 변환
1. binlog 저장 경로 찾기
2. mysqlbinlog 를 이용하여 파일 변환 하기
mysqlbinlog /var/lib/mysql/mysql_bin.000002 > binlog.sql
3. 변환 파일 확인
cat binlog.sql
mysqlbinlog 옵션
일반적으로 볼때
## 일반적으로 볼때
mysqlbinlog log_file
...
# at 218
#080828 15:03:08 server id 1 end_log_pos 258 Write_rows: table id 17 flags: STMT_END_F
BINLOG '
fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
'/*!*/;
...
# at 302
#080828 15:03:08 server id 1 end_log_pos 356 Update_rows: table id 17 flags: STMT_END_F
BINLOG '
fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
'/*!*/;
...
# at 400
#080828 15:03:08 server id 1 end_log_pos 442 Delete_rows: table id 17 flags: STMT_END_F
BINLOG '
fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
-v옵션 : SQL 형식으로 보여줌
## -v 옵션을 사용하면 사람이 읽을 수 있는 SQL로 출력된다.
$> mysqlbinlog -v log_file
...
# at 218
#080828 15:03:08 server id 1 end_log_pos 258 Write_rows: table id 17 flags: STMT_END_F
BINLOG '
fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
'/*!*/;
### INSERT INTO test.t
### SET
### @1=1
### @2='apple'
### @3=NULL
...
# at 302
#080828 15:03:08 server id 1 end_log_pos 356 Update_rows: table id 17 flags: STMT_END_F
BINLOG '
fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
'/*!*/;
### UPDATE test.t
### WHERE
### @1=1
### @2='apple'
### @3=NULL
### SET
### @1=1
### @2='pear'
### @3='2009:01:01'
...
# at 400
#080828 15:03:08 server id 1 end_log_pos 442 Delete_rows: table id 17 flags: STMT_END_F
BINLOG '
fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
'/*!*/;
### DELETE FROM test.t
### WHERE
### @1=1
### @2='pear'
### @3='2009:01:01'
-vv 옵션 :SQL에 컬럼 메타정보도 함께 보여줌
## -vv 옵션은 컬럼의 메타정보도 함께 출력된다.
$> mysqlbinlog -vv log_file
...
# at 218
#080828 15:03:08 server id 1 end_log_pos 258 Write_rows: table id 17 flags: STMT_END_F
BINLOG '
fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
'/*!*/;
### INSERT INTO test.t
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='apple' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
### @3=NULL /* VARSTRING(20) meta=0 nullable=1 is_null=1 */
...
# at 302
#080828 15:03:08 server id 1 end_log_pos 356 Update_rows: table id 17 flags: STMT_END_F
BINLOG '
fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
'/*!*/;
### UPDATE test.t
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='apple' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
### @3=NULL /* VARSTRING(20) meta=0 nullable=1 is_null=1 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='pear' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
### @3='2009:01:01' /* DATE meta=0 nullable=1 is_null=0 */
...
# at 400
#080828 15:03:08 server id 1 end_log_pos 442 Delete_rows: table id 17 flags: STMT_END_F
BINLOG '
fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
'/*!*/;
### DELETE FROM test.t
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='pear' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
### @3='2009:01:01' /* DATE meta=0 nullable=1 is_null=0 */
-base64-output=DECODE-ROWS 와 --verbose 옵션의 조합 : binary 값도 없애고 보여줌
shell> mysqlbinlog -v --base64-output=DECODE-ROWS log_file
...
# at 218
#080828 15:03:08 server id 1 end_log_pos 258 Write_rows: table id 17 flags: STMT_END_F
### INSERT INTO test.t
### SET
### @1=1
### @2='apple'
### @3=NULL
...
# at 302
#080828 15:03:08 server id 1 end_log_pos 356 Update_rows: table id 17 flags: STMT_END_F
### UPDATE test.t
### WHERE
### @1=1
### @2='apple'
### @3=NULL
### SET
### @1=1
### @2='pear'
### @3='2009:01:01'
...
# at 400
#080828 15:03:08 server id 1 end_log_pos 442 Delete_rows: table id 17 flags: STMT_END_F
### DELETE FROM test.t
### WHERE
### @1=1
### @2='pear'
### @3='2009:01:01'
자세한 내용은 아래 링크 참조
MySQL :: MySQL 5.7 Reference Manual :: 4.6.7.2 mysqlbinlog Row Event Display
MySQL :: MySQL 5.7 Reference Manual :: 4.6.7.2 mysqlbinlog Row Event Display
4.6.7.2 mysqlbinlog Row Event Display The following examples illustrate how mysqlbinlog displays row events that specify data modifications. These correspond to events with the WRITE_ROWS_EVENT, UPDATE_ROWS_EVENT, and DELETE_ROWS_EVENT type codes. The --b
dev.mysql.com
binlog를 이용하여 특정 테이블 시점 복구하기
1. 테스트 db & 테이블 생성
CREATE DATABASE db_test;
USE DATABASE db_test;
/* TABLE 생성 및 데이터 입력*/
create table binlogtest (name varchar(10));
insert into binlogtest values ('1');
insert into binlogtest values ('2');
insert into binlogtest values ('3');
insert into binlogtest values ('4');
insert into binlogtest values ('5');
insert into binlogtest values ('6');
commit;
select * from binlogtest;
+------+
| name |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
2. mysqldump를 이용한 백업
mysqldump --single_transaction --databases db_test --flush_logs -u root -p > db_test_dump.sql
3. 데이터 추가 삽입
insert into binlogtest values ('7'),('8'),('9'),('10');
commit;
select * From binlogtest;
+------+
| name |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
4. database drop 시키기
DROP DATABASE db_test;
5.binlog 파일 .sql 파일로 변환
## 현재 mysql_bin.000001 ~ mysql_bin.000004 까지 총 4개의 binlog 파일이 존재함
[root@ip-10-0-1-201 mysql]# ll
total 122984
....
-rw-r----- 1 mysql mysql 1509 Sep 17 13:17 mysql_bin.000001
-rw-r----- 1 mysql mysql 7381 Sep 18 14:23 mysql_bin.000002
-rw-r----- 1 mysql mysql 3338 Sep 18 14:52 mysql_bin.000003
-rw-r----- 1 mysql mysql 2429 Sep 18 16:14 mysql_bin.000004
-rw-r----- 1 mysql mysql 1912 Sep 18 16:16 mysql_bin.000005
-rw-r----- 1 mysql mysql 95 Sep 18 16:14 mysql_bin.index
....
## 마지막 binlog 파일에서 drop database 전까지 찾아서 그 부분에서 stop-position을 해야함
mysqlbinlog --verbose -d db_test ../mysql_bin.000001 > data_backup_bin_01.sql
mysqlbinlog --verbose -d db_test ../mysql_bin.000001 > data_backup_bin_02.sql
mysqlbinlog --verbose -d db_test ../mysql_bin.000001 > data_backup_bin_03.sql
mysqlbinlog --stop-position="468" --verbose -d db_test ../mysql_bin.000004 > data_backup_bin_04.sql
6. mysqldump를 이용한 복구 진행
mysql -u root -p < db_test_dump.sql
7. 확인
show databases;
+--------------------+
| Database |
+--------------------+
| classicmodels |
| db_test |
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
/* 추가입력 전 까지 복구 완료 */
select * from db_test.binlogtest;
+------+
| name |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
8. 백업 이후 추가입력건 까지 복구 시키기(binlog)
mysql -u root -p < data_backup_bin_01.sql
mysql -u root -p < data_backup_bin_02.sql
mysql -u root -p < data_backup_bin_03.sql
mysql -u root -p < data_backup_bin_04.sql
9. 확인
/* 복구완료 */
select * from db_test.binlogtest;
+------+
| name |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
참고 페이지
binlog를 이용한 백업&복구
Binlog를 사용하여 백업&복구에 사용할 수 있습니다. 시점복구(PIT)가 가능합니다. 즉, 특정 시점의 binlog파일을 이용하여 특정시점이후의 데이터가 복구가 가능하다는 것입니다. 이론적으로, mysqldu
bae9086.tistory.com
MySQL 특정 테이블만 시점복구 하기
MySQL 특정 테이블만 시점복구 하기 얼마 전 서비스 로직 이슈로 특정 데이터들이 CASCADE 삭제되어 데이터가 유실되는 일이 발생했습니다. 영향 받은 테이블들은 전체 테이블들 중 두개였고 전체
kimdubi.github.io
binlog 일단위로 파일 만들기
#!/bin/bash
# LOG Backup date
LogDate=$(date + '%Y%m%d' -d '1 day ago');
# Date Home Path
DatePath="/SSP_DEV_DATA/data01/";
# Log Home Path
DataLogPath=$DataPath."/log/slow";
# Mysql Home Path
mysqlPath="/SSP_DEV/mysql5/bin/";
echo $LogDate
echo $DataPath
cd $DataLogPath;
mv mysql-slow.log mysql-slow-$LogDate.log
cd $mysqlPath
./mysqladmin -uroot -pssp_bespin456 --sockect=/[mysqld경로]/mysqld.sock flush-logs;
728x90
'Database > MYSQL' 카테고리의 다른 글
| Replication (0) | 2022.08.11 |
|---|---|
| binlog 와 redo log 차이 (0) | 2022.08.11 |
| Mysql 쿼리 힌트 (0) | 2022.08.09 |
| Mysql 프로파일링 (0) | 2022.08.08 |
| My sql 실행 계획 (2/2) (0) | 2022.08.08 |
'Database/MYSQL' Related Articles
more
Comments