MySQL에서 binlog를 사용할 때 binlog 형식 선택

mysql tutorial 칼럼에서는 binlog를 사용할 때 binlog 형식을 선택하는 방법을 소개합니다.

1. binlog의 세 가지 모드

1.statement level 모드

데이터를 수정하는 모든 SQL은 마스터의 bin-log에 기록됩니다. 슬레이브가 복사하면 SQL 프로세스는 이를 원래 마스터 측에서 실행된 것과 동일한 SQL로 구문 분석하고 다시 실행합니다. 장점: 명령문 수준의 장점은 행 수준의 단점을 먼저 해결하고, 데이터의 각 행에 대한 변경 사항을 기록할 필요가 없으며, bin-log 로그 양을 줄이고, IO를 절약하며 성능을 향상시킨다는 것입니다. 왜냐하면 마스터에서 실행된 문의 내용과 해당 문이 실행될 때의 컨텍스트 정보만 기록하면 되기 때문입니다. 단점: 기록된 실행 명령문이기 때문에 이러한 명령문이 슬레이브 측에서 올바르게 실행되기 위해서는 각 명령문이 실행될 때 관련 정보, 즉 모든 명령문이 실행되도록 하는 컨텍스트 정보도 기록해야 합니다. 슬레이브 측에서 실행하면 마스터 측에서 실행할 때와 동일한 결과를 얻을 수 있습니다. 또한, 현재 mysql이 빠르게 발전하고 있기 때문에 많은 새로운 기능이 추가되어 mysql 복제에 많은 어려움이 따릅니다. 당연히 복제에 관련된 내용이 복잡할수록 버그가 나타나기 쉽습니다. 명령문 수준에서는 주로 데이터를 수정할 때 특정 함수나 함수를 사용할 때 MySQL 복제 문제가 발생하는 경우가 많은 것으로 나타났습니다. 예를 들어 일부 버전에서는 sleep()을 올바르게 사용할 수 없습니다.

2.rowlevel 모드

로그는 각 데이터 행의 수정된 형태를 기록하며, 슬레이브 측에서도 동일한 데이터가 수정됩니다. 장점: bin-log는 실행된 SQL 문의 컨텍스트 관련 정보를 기록할 필요가 없으며 어떤 레코드가 수정되었는지, 어떤 내용이 수정되었는지만 기록하면 됩니다. 따라서 행 수준 로그의 내용에는 데이터 수정의 각 행에 대한 세부 정보가 명확하게 기록됩니다. 그리고 특정 상황에서 저장 프로시저, 함수, 트리거 호출 및 트리거가 올바르게 복사되지 않는 문제가 없습니다. 단점: 행 수준에서 실행된 모든 명령문이 로그에 기록되면 각 행에 기록된 수정 사항으로 기록되므로 많은 양의 로그 내용이 생성될 수 있습니다. 예를 들어 update product set과 같은 업데이트 명령문이 있습니다. owner_member_id= 'd' 여기서 owner_member_id='a', 실행 후 로그에 기록되는 것은 이 업데이트 명령문에 해당하는 이벤트가 아니라(mysql은 bin-log 로그를 이벤트 형태로 기록함), 업데이트된 모든 이벤트는 이 문은 하나의 레코드의 변경 사항이 여러 레코드가 업데이트되는 이벤트로 기록됩니다. 당연히 bin-log 로그의 양이 많아집니다.

3. 혼합 모드

는 실제로 처음 두 모드의 조합입니다. 혼합 모드에서는 mysql이 실행된 각 특정 SQL 문, 즉 명령문과 행을 기준으로 기록할 로그 형식을 구분합니다. 새 버전의 명령문 수준은 이전과 동일하며 실행된 명령문만 기록됩니다. 새로운 버전의 mysql에서는 행 수준 모드가 최적화되었습니다. 예를 들어, 테이블 구조 변경이 발생하면 SQL 문이 실제로 업데이트 또는 명령문 모드로 기록됩니다. 데이터를 수정하는 삭제와 같은 명령문의 경우 모든 행 변경 사항이 계속 기록됩니다.

2. binlog를 사용할 때 어떤 형식을 선택해야 합니까?

위의 소개를 통해 우리는 binlog_format이 일부 시나리오에서 IO를 절약하고 동기화 속도를 높일 수 있다는 것을 알고 있습니다. COMMITTED, READ-UNCOMMITTED 격리 수준 또는 innodb_locks_unsafe_for_binlog 매개변수가 ON인 경우 binlog_format=statement 아래에 쓰기가 금지됩니다. 동시에 비트랜잭션 엔진 및 기타 격리 수준의 기본 쓰기 문 형식인 binlog_format=mixed, 행 형식만 기록합니다.

> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+

> create table t(c1 int) engine=innodb;

> set binlog_format=statement;

> insert into t values(1);
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

> set binlog_format='mixed';

> show binlog events in 'mysql-bin.000004'\G
*************************** 3. row ***************************
   Log_name: mysql-bin.000002
        Pos: 287
 Event_type: Gtid
  Server_id: 3258621899
End_log_pos: 335
       Info: SET @@SESSION.GTID_NEXT= 'ed0eab2f-dfb0-11e7-8ad8-a0d3c1f20ae4:9375'
*************************** 4. row ***************************
   Log_name: mysql-bin.000002
        Pos: 335
 Event_type: Query
  Server_id: 3258621899
End_log_pos: 407
       Info: BEGIN
*************************** 5. row ***************************
   Log_name: mysql-bin.000002
        Pos: 407
 Event_type: Table_map
  Server_id: 3258621899
End_log_pos: 452
       Info: table_id: 124 (test.t)
*************************** 6. row ***************************
   Log_name: mysql-bin.000002
        Pos: 452
 Event_type: Write_rows_v1
  Server_id: 3258621899
End_log_pos: 498
       Info: table_id: 124 flags: STMT_END_F
*************************** 7. row ***************************
   Log_name: mysql-bin.000002
        Pos: 498
 Event_type: Xid
  Server_id: 3258621899
End_log_pos: 529
       Info: COMMIT /* xid=18422 */复制代码

READ-COMMITTED(RC) 및 READ-UNCOMMITTED에서 명령문 형식 binlog를 사용할 수 없는 이유는 무엇입니까? 트랜잭션에서 문이 실행되면 다른 트랜잭션에서 제출되거나 작성된 데이터를 볼 수 있기 때문입니다. 트랜잭션이 커밋된 후 binlog가 기록되고 슬레이브 라이브러리에서 재생됩니다. 표시되는 데이터는 기본 라이브러리에 기록된 데이터와 일치하지 않습니다. 예를 들어: 테이블이 있습니다:

+------+------+
| a    | b    |
+------+------+
|   10 |    2 |
|   20 |    1 |
+------+------+复制代码

우리는 다음 작업을 수행합니다:

1. session1 트랜잭션의 업데이트, UPDATE t1 SET a=11 where b=2; 다음과 같은 행(10,2)이 있습니다. 조건을 충족합니다. 기록이 제출되지 않았습니다.
UPDATE t1 SET a=11 where b=2;满足条件的有行(10,2)的一条记录，并未提交。
2. session2也做update操作，将行(20,1)更新为(20,2)并提交。
3. 然后前面的sesssion1提交对行(10,2)的更新。

如果binlog中使用Statement格式记录，在slave回放的时候，session2中的更新由于先提交会先回放，将行(20,1)更新为（20,2)。随后回放session1的语句UPDATE t1 SET a=11 where b=2;session2도 업데이트 작업을 수행하고 행 (20,1)을 (20,2)로 업데이트한 후 제출합니다.

그런 다음 이전 session1은 행 (10,2)에 업데이트를 제출합니다. 🎜binlog 기록에 명령문 형식을 사용하는 경우 슬레이브 재생 중에 session2의 업데이트가 먼저 제출되었기 때문에 먼저 재생되고 라인 (20,1)이 ( 20,2). 그런 다음 session1 UPDATE t1 SET a=11 where b=2; 명령문을 재생하면 두 줄 (10,2)과 (20,2)가 (11,2)로 업데이트됩니다. 이로 인해 기본 라이브러리 동작은 (11, 2), (20,2)이고 슬레이브 쪽은 (11,2), (11, 2)입니다. 🎜

三、问题分析

上面是通过一个具体的例子说明。本质原因是RC事务隔离级别并不满足事务串行化执行要求，没有解决不可重复和幻象读。

对于Repetable-Read和Serializable隔离级别就没关系，Statement格式记录。这是因为对于RR和Serializable，会保证可重复读，在执行更新时候除了锁定对应行还会在可能插入满足条件行的时候加GAP Lock。上述case更新时，session1更新b =2的行时，会把所有行和范围都锁住，这样session2在更新的时候就需要等待。从隔离级别的角度看Serializable满足事务的串行化，因此binlog串行记录事务statement格式是可以的。同时InnoDB的RR隔离级别实际已经解决了不可重复读和幻象读，满足了ANSI SQL标准的事务隔离性要求。

READ-COMMITTED、READ-UNCOMMITTED的binlog_format限制可以说对于所有事务引擎都适用。

四、拓展内容

对于InnoDB RR和Serializable隔离级别下就一定能保证binlog记录Statement格式么？也不一定。在Innodb中存在参数innodb_locks_unsafe_for_binlog控制GAP Lock，该参数默认为OFF：

mysql> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF   |
+--------------------------------+-------+
1 row in set (0.01 sec)复制代码

即RR级别及以上除了行锁还会加GAP Lock。但如果该参数设置为ON，对于当前读就不会加GAP Lock，即在RR隔离级别下需要加Next-key lock的当前读蜕化为READ-COMMITTED。所以如果此参数设置为ON时即便使用的事务隔离级别为Repetable-Read也不能保证从库数据的正确性。

五、总结

对于线上业务，如果使用InnoDB等事务引擎，除非保证RR及以上隔离级别的写入，一定不要设置为binlog_format为STATEMENT，否则业务就无法写入了。而对于binlog_format为Mixed模式，RR隔离级别以下这些事务引擎也一定写入的是ROW event。

更多相关免费学习推荐：mysql教程(视频)

위 내용은 MySQL에서 binlog를 사용할 때 binlog 형식 선택의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!