[MySQL] binlog 는 언제 생성될까 ( binary log )

 

- 목차

 

들어가며.

이번 글에서는 MySQL 의 binlog 파일이 언제 생성되며,

데이터 변경 사항이 binlog 파일에 기록되는 시점에 대해서 알아보려고 합니다.

이 글에서는 실습을 위주로 진행하며, Docker Container 를 기반으로 실습을 진행합니다.

 

 

MySQL Container 실행하기.

아래 명령어를 통해서 MySQL Docker Container 를 실행합니다.

docker run --platform linux/amd64 -d --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=1234 mysql:8.0.23

 

 

 

그리고 binlog 관련 설정들을 살펴보겠습니다.

 

log_bin 설정이 ON 인지 확인하기.

log_bin 설정이 ON 인 경우에 binlog 파일이 생성되고, 파일 내부에 데이터 변경 사항이 기록됩니다.

show global variables like 'log_bin';

+-------------+-----+
|Variable_name|Value|
+-------------+-----+
|log_bin      |ON   |
+-------------+-----+

 

binary logs 상태 확인하기.

show binary logs 명령어를 통해서 관리 중인 binlog 들을 확인할 수 있습니다.

 

show binary logs;

+-------------+---------+---------+
|Log_name     |File_size|Encrypted|
+-------------+---------+---------+
|binlog.000001|3040347  |No       |
|binlog.000002|157      |No       |
+-------------+---------+---------+

 

binlog 의 실제 위치 확인하기.

datadir 설정값은 MySQL 의 실질적인 데이터들이 저장되는 물리적인 위치입니다.

binlog 파일들 뿐만 아니라 Tablespace, Undo Log 등의 파일들 또한 위치합니다.

show global variables like '%datadir%';

+-------------+---------------+
|Variable_name|Value          |
+-------------+---------------+
|datadir      |/var/lib/mysql/|
+-------------+---------------+

 

datadir 의 위치가 /var/lib/mysql 임을 알게되었기 때문에 해당하는 위치를 조회해보겠습니다.

docker exec -it mysql ls -al /var/lib/mysql | grep binlog

-rw-r----- 1 mysql mysql  3040347 Nov 25 23:46  binlog.000001
-rw-r----- 1 mysql mysql      157 Nov 25 23:46  binlog.000002
-rw-r----- 1 mysql mysql       32 Nov 25 23:46  binlog.index

 

 

테이블과 데이터 생성하기.

docker exec -it mysql mysql -uroot -p1234;

create table test_table (
    id int,
    name varchar(32)
);

 

 

 

binlog 생성 시점 알아보기.

이제 MySQL 과 Table 이 생성되었기 때문에 데이터를 생성하며 binlog 의 변화를 살펴보겠습니다.

 

Row 생성하기.

아래와 같이 2개의 Row 들을 생성한 이후에 mysqlbinlog 명령어를 통해서 binlog 파일의 변경사항을 확인할 수 있습니다.

insert into test_table (id, name) values (1, 'Andy');
insert into test_table (id, name) values (2, 'Brad');

docker exec -it mysql mysqlbinlog /var/lib/mysql/binlog.000002 --base64-output=decode-rows -v

 

< binlog.000002 의 기록 > 

// 생략...
### INSERT INTO `mysql`.`test_table`
### SET
###   @1=1
###   @2='Andy'
// 생략...
### INSERT INTO `mysql`.`test_table`
### SET
###   @1=2
###   @2='Brad'
// 생략...

 

 

아래와 같이 Row 가 추가될 수록 binlog 의 크기를 꾸준히 증가합니다.

insert into test_table (id, name) values (3, 'Chris');
show binary logs;
+-------------+---------+---------+
|Log_name     |File_size|Encrypted|
+-------------+---------+---------+
|binlog.000001|3118706  |No       |
|binlog.000002|1313     |No       |
+-------------+---------+---------+

insert into test_table (id, name) values (4, 'Daniel');
show binary logs;
+-------------+---------+---------+
|Log_name     |File_size|Encrypted|
+-------------+---------+---------+
|binlog.000001|3118706  |No       |
|binlog.000002|1609     |No       |
+-------------+---------+---------+

insert into test_table (id, name) values (5, 'Emma');
show binary logs;
+-------------+---------+---------+
|Log_name     |File_size|Encrypted|
+-------------+---------+---------+
|binlog.000001|3118706  |No       |
|binlog.000002|1905     |No       |
+-------------+---------+---------+

 

 

Transaction Commit or Rollback.

binlog 는 트랜잭션이 완료될 때에 데이터의 변경 사항이 반영됩니다.

예를 들어, 아래와 같이 Rollback 되는 Transaction 내부의 DML 은 binlog 에 반영되지 않습니다.

start transaction;
insert into test_table (id, name) values (6, 'Fabian');
rollback;
show binary logs;

+-------------+---------+---------+
|Log_name     |File_size|Encrypted|
+-------------+---------+---------+
|binlog.000001|3118706  |No       |
|binlog.000002|2497     |No       |
+-------------+---------+---------+

 

반면 start transaction 와 commit 사이의 DML 은 binlog 에 반영됩니다.

start transaction;
insert into test_table (id, name) values (6, 'Fabian');
commit;
show binary logs;

+-------------+---------+---------+
|Log_name     |File_size|Encrypted|
+-------------+---------+---------+
|binlog.000001|3118706  |No       |
|binlog.000002|2795     |No       |
+-------------+---------+---------+

 

max_binlog_size 를 초과하는 경우.

binlog 파일의 최대 사이즈는 max_binlog_size 옵션으로 설정할 수 있습니다.

기본값은 1GB 로 설정되어 있구요.

binlog 파일이 max_binlog_size 을 초과하는 경우에 새로운 binlog 파일이 생성됩니다.

 

테스트를 위해서 max_binlog_size 를 1MB 로 변경한 이후에 binlog 파일의 생성을 확인해보겠습니다.

cat <<EOF> /tmp/custom.cnf
[mysqld]
max_binlog_size = 1048576
EOF

docker run -d --name mysql \
--platform linux/amd64 \
--mount type=bind,source=/tmp/custom.cnf,target=/etc/mysql/conf.d/custom.cnf \
-e MYSQL_ROOT_PASSWORD=1234 -p 3306:3306 mysql:8.0.23

 

 

max_binlog_size 를 변경한 새로운 MySQL 의 max_binlog_size 값은 1MB 로 설정됩니다.

show global variables like '%max_binlog_size%';

+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| max_binlog_size | 1048576 |
+-----------------+---------+
1 row in set (0.04 sec)

 

1만개의 Row 를 생성하는 Procedure 를 통해서 binlog 파일의 변화를 확인해보겠습니다.

아래의 결과처럼 1만개의 Row 는 1MB 사이즈를 넘는 용량을 차지하며,

Procedure 가 실행될 때마가 새로운 binlog 파일이 생성됩니다.

delimiter $$
DROP PROCEDURE IF EXISTS dataBulkInsert$$
create procedure dataBulkInsert()
begin
declare i int default 1;
    start transaction;
    while(i<10000) DO
        insert into test_table (id, name) value (i, concat('name',i));
        set i=i+1;
    end while;
end $$

call dataBulkInsert();
show binary logs;

 

+-------------+---------+---------+
|Log_name     |File_size|Encrypted|
+-------------+---------+---------+
|binlog.000001|3118727  |No       |
|binlog.000002|179      |No       |
|binlog.000003|1140076  |No       |
|binlog.000004|1139166  |No       |
|binlog.000005|156      |No       |
+-------------+---------+---------+