MySQL binlog format

binlog를 저장하는 format은 3가지가 있다.

• STATEMENT format
• ROW format
• MIXED format

global 및 각 세션마다 binlog format을 설정할 수 있다.

 

mysql> SET GLOBAL binlog_format = 'ROW';
 
mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';

1. ROW format

• 변경된 row를 binary format으로 binlog에 저장한다.
• Each row may consist of a Before Image (BI) and/or an After Image (AI). The BI identifies the row to modify and the AI describes the row after the change. There are three types of log_events:
  • Write_rows_log_event: adds a new row to a table. Has only AI.
  • Update_rows_log_event: modifies an existing row in a table. Has both BI and AI.
  • Delete_rows_log_event: removes an existing row from a table. Has only BI.

# at 4065723
#220608  2:28:45 server id 11  end_log_pos 4065800 CRC32 0x00246769     Query   thread_id=22647 exec_time=0     error_code=0
SET TIMESTAMP=1654655325/*!*/;
BEGIN
/*!*/;
# at 4065800
#220608  2:28:45 server id 11  end_log_pos 4065853 CRC32 0x63dccbf0     Table_map: `employees`.`test1` mapped to number 131
# at 4065853
#220608  2:28:45 server id 11  end_log_pos 4065893 CRC32 0xe117cd0d     Write_rows: table id 131 flags: STMT_END_F

BINLOG '
XQmgYhMLAAAANQAAAD0KPgAAAIMAAAAAAAEACWVtcGxveWVlcwAFdGVzdDEAAQMAAfDL3GM=
XQmgYh4LAAAAKAAAAGUKPgAAAIMAAAAAAAEAAgAB//4NAAAADc0X4Q==
'/*!*/;
# at 4065893
#220608  2:28:45 server id 11  end_log_pos 4065924 CRC32 0xc0c5bbdd     Xid = 68260
COMMIT/*!*/;

 

2. STATEMENT format

SQL Query 형태로 binlog에 기록된다.

# at 4065989
#220608  2:50:56 server id 11  end_log_pos 4066078 CRC32 0x48501759     Query   thread_id=22648 exec_time=0     error_code=0
SET TIMESTAMP=1654656656/*!*/;
BEGIN
/*!*/;
# at 4066078
#220608  2:50:56 server id 11  end_log_pos 4066190 CRC32 0xde336322     Query   thread_id=22648 exec_time=0     error_code=0
SET TIMESTAMP=1654656656/*!*/;
insert into test1 values(14)
/*!*/;
# at 4066190
#220608  2:50:56 server id 11  end_log_pos 4066221 CRC32 0xa54cf5de     Xid = 68279
COMMIT/*!*/;

3. MIXED format

MIXED format을 사용하면 기본적으로 STATEMENT format으로 생성되며, ROW fomat을 사용해야 데이터 일관성을 보장하는 경우에만 ROW format을 사용한다 (UUID()함수 / sysdate 함수 등 사용시)

STATEMENT format 장점

• A session that performs updates that match many rows in the WHERE clause might want to use statement-based logging because it is more efficient to log a few statements than many rows.

ROW format 장점

• Some statements require a lot of execution time on the source, but result in just a few rows being modified. It might therefore be beneficial to replicate them using row-based logging.

주의 사항

• InnoDB 테이블을 사용 중이고 트랜잭션 격리 수준이 READ COMMITTED 또는 READ UNCOMMITTED인 경우 STATEMENT format은 사용할 수 없고, ROW format만 사용할 수 있다.→ 하지만 READ-COMMITED 환경에서는 다른세션에서 commit 발생시 트랜잭션에서 조회하는 데이터가 달라지기 때문에 원본 DB와 동일한 결과값을 보장할 수 없다.
• → REPEATABLE READ 환경에서는 트랜잭션 내에서 읽기 일관성을 보장하기 때문에, statement 단위로 로깅해도 원본 DB와 동일한 결과를 보장한다.
• source 서버의 binlog format변경은 replica에 반영되지 않는다
• → This means that changing the logging format on a replication source server does not cause a replica to change its logging format to match.
• replica는 row format binlog를 statment format으로 변환해서 사용할수 없다. 따라서 source서버가 row format을 사용하면 replica도 row format을 사용해야 한다.
• → A replica is not able to convert binary log entries received in ROW logging format to STATEMENT format for use in its own binary log.

참고 :

MySQL :: MySQL 5.7 Reference Manual :: 5.4.4.2 Setting The Binary Log Format

MySQL :: MySQL Internals Manual :: 19.3.1 Binlog Formats

MySQL :: MySQL 5.7 Reference Manual :: 16.1.6.4 Binary Logging Options and Variables

 

 

MSR 환경에서 binlog 설정

 

1. replication 구성시 statement / row format의 장단점

MySQL :: MySQL 5.7 Reference Manual :: 16.2.1.1 Advantages and Disadvantages of Statement-Based and Row-Based Replication

2. MSR 환경에서는 binlog format = ROW 를 사용해야 한다.

예를들어 shard1,2가 employee 테이블을 emp_no 값을 기준으로 모듈러 연산으로 값을 나누어서 저장하고 있고, slave가 데이터를 통합해서 가지고 있을 경우,

• shard1 : emp_no = 1,3,5,7 인 사원 정보
• shard2 : emp_no = 2,4,6,8인 사원 정보
• slave : emp_no 1,2,3,4,5,6,7,8 사원 정보

2.1 Row format일 경우

shard1에서

delete from employee where emp_no > 3​

을 수행하면 emp_no = 5,7 인 row가 삭제되는 binlog가 기록되고

slave에서도 emp_no = 5,7인 row만 삭제 된다.

 

2.2 Statement format일 경우

shard1에서

delete from employee where emp_no > 3

을 수행하면 ‘delete from employee where emp_no > 3 ‘ 해당 SQL 쿼리가 binlog가 기록되고

slave에서도 동일 쿼리가 수행되어 slave에서 emp_no = 4,5,6,7,8 인 row가 모두 삭제된다.

또한 order by 가 없는 delete … limit .. 도 비슷한 상황을 초래할 수 있다.

 

따라서 MSR 환경에서는 binlog format으로 ROW format을 사용해야 한다.