이 글은 MySQL의 binlog 로그 파일에 대한 자세한 소개를 제공합니다. 이는 특정 참조 값을 가지고 있습니다. 도움이 필요한 친구들이 참고할 수 있기를 바랍니다.
MySQL의 binlog 로그 파일은 데이터베이스 테이블의 모든 수정 작업을 기록합니다. 이 기사에서는 MySQL binlog와 관련된 지식과 binlog를 사용하여 데이터베이스 데이터를 복원하거나 플래시백하는 방법을 간략하게 요약합니다.
STATEMENT 형식 binlog
binlog를 활성화하려면 MySQL을 시작할 때 --log-bin 매개변수를 전달해야 합니다. 또는 MySQL 구성 파일 /etc/my.cnf에서 log_bin을 설정하여 binlog를 활성화할 수 있습니다. MySQL 5.7부터 binlog를 활성화한 후 --server-id 매개변수도 지정해야 합니다. 그렇지 않으면 MySQL 서버가 시작되지 않습니다.
binlog_format은 STATEMENT, ROW 및 MIXED의 세 가지 형식을 지원합니다. MySQL 5.5 및 5.6은 기본적으로 STATEMENT로 시작되고 MySQL 5.7.7은 기본적으로 ROW로 시작됩니다. 좋다 SQL은 UUID(), RAND(), VERSION() 및 기타 함수를 사용하거나 STATEMENT를 기반으로 저장 프로시저, 사용자 정의 함수를 사용합니다. 마스터-슬레이브가 복제되면 안전하지 않습니다. (많은 사람들이 NOW(), CURRENT_TIMESTAMP 및 이러한 함수도 안전하지 않다고 생각할 수 있지만 실제로는 안전합니다.) [doc1, 문서 2]. ROW 기반의 마스터-슬레이브 복제는 가장 안전한 복제 방법입니다.
이제 STATEMENT 형식의 binlog를 살펴보겠습니다. /etc/my.cnf 파일의 수정된 내용은 다음과 같습니다.
server_id = 1 log_bin = mysql-bin binlog_format = STATEMENT binlog_row_image=FULL
MySQL을 다시 시작한 후 데이터 디렉토리 datadir에 /var/lib/ mysql/, 해당 binlog 파일, mysql-bin.index 및 mysql-bin.000001. .index 접미사가 붙은 파일은 모든 binlog 파일 이름을 저장합니다. mysql-bin.000001 파일은 binlog 내용을 기록합니다. MySQL이 시작되거나 로그를 플러시할 때마다 시퀀스 번호에 따라 새 로그 파일이 생성됩니다. 또한 로그 파일 크기가 max_binlog_size를 초과하면 새 로그 파일도 생성됩니다.
이제 binlog 기능을 사용해 보겠습니다. testdb 라이브러리에 hello 테이블이 있고 그 안의 행에 수정 작업이 수행되었다고 가정합니다.
mysql> select * from hello; +----+-------+ | id | name | +----+-------+ | 1 | Andy | | 2 | Bill | | 3 | Candy | +----+-------+ 4 rows in set (0.00 sec) mysql> update hello set name = 'Will' where id = 3; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
binlog는 바이너리 파일입니다.
$ sudo mysqlbinlog /var/lib/mysql/mysql-bin.000001 # 直接在 mysql 服务器上读取 binlog 文件 $ mysqlbinlog -R -h192.168.2.107 -uroot -p123456 mysql-bin.000001 # 或者，远程读取 binlog 文件
업데이트 실행 후 새로 추가된 binlog 파일 내용 :
# at 154 #180617 22:47:49 server id 1 end_log_pos 219 CRC32 0x4bd9d69b Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #180617 22:47:49 server id 1 end_log_pos 302 CRC32 0x476fafc9 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1529246869/*!*/; SET @@session.pseudo_thread_id=2/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1075838976/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 302 #180617 22:47:49 server id 1 end_log_pos 423 CRC32 0x7f2c2c7a Query thread_id=2 exec_time=0 error_code=0 use `testdb`/*!*/; SET TIMESTAMP=1529246869/*!*/; update hello set name = 'Will' where id = 3 /*!*/; # at 423 #180617 22:47:49 server id 1 end_log_pos 454 CRC32 0x68da744a Xid = 12 COMMIT/*!*/;
ROW 형식 binlog
/etc/my.cnf의 binlog_format을 ROW로 수정한 후 MySQL을 다시 시작하세요. 형식이 수정되면 새로운 binlog 파일 mysql-bin.000002가 생성됩니다.
mysql> show create table hello; +-------+-------------------------------------------------------------------------+ | Table | Create Table +-------+-------------------------------------------------------------------------+ | hello | CREATE TABLE `hello` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 | +-------+-------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from hello where id; +----+------+ | id | name | +----+------+ | 1 | Andy | | 2 | Lily | | 3 | Will | +----+------+ 1 row in set (0.00 sec) mysql> update hello set name = 'David' where id = 3; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
binlog를 ROW 형식으로 보려면 sudo mysqlbinlog -v --base64-output=DECODE-ROWS /var/lib/mysql/mysql-bin.000002 명령을 사용해야 합니다. 업데이트 실행 후 새로 추가된 해당 binlog 콘텐츠:
# at 154 #180617 22:54:13 server id 1 end_log_pos 219 CRC32 0x2ce70d4d Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #180617 22:54:13 server id 1 end_log_pos 293 CRC32 0x8183fddf Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1529247253/*!*/; SET @@session.pseudo_thread_id=2/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1075838976/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 293 #180617 22:54:13 server id 1 end_log_pos 346 CRC32 0x0fc7e1a4 Table_map: `testdb`.`hello` mapped to number 110 # at 346 #180617 22:54:13 server id 1 end_log_pos 411 CRC32 0xb58e729d Update_rows: table id 110 flags: STMT_END_F ### UPDATE `testdb`.`hello` ### WHERE ### @1=3 ### @2='Will' ### SET ### @1=3 ### @2='David' # at 411 #180617 22:54:13 server id 1 end_log_pos 442 CRC32 0xef964db8 Xid = 13 COMMIT/*!*/;
다음 SQL이 실행된 경우:
mysql> insert hello (name) values ('Frank');
Query OK, 1 row affected (0.02 sec)해당 생성된 binlog 콘텐츠:
# at 442 #180617 22:55:47 server id 1 end_log_pos 507 CRC32 0x79de08a7 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 507 #180617 22:55:47 server id 1 end_log_pos 581 CRC32 0x56f9eb6a Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1529247347/*!*/; BEGIN /*!*/; # at 581 #180617 22:55:47 server id 1 end_log_pos 634 CRC32 0xedb73620 Table_map: `testdb`.`hello` mapped to number 110 # at 634 #180617 22:55:47 server id 1 end_log_pos 684 CRC32 0x525a6a70 Write_rows: table id 110 flags: STMT_END_F ### INSERT INTO `testdb`.`hello` ### SET ### @1=4 ### @2='Frank' # at 684 #180617 22:55:47 server id 1 end_log_pos 715 CRC32 0x09a0d4de Xid = 14 COMMIT/*!*/;
다음 SQL이 실행된 경우:
mysql> delete from hello where id = 2; Query OK, 1 row affected (0.02 sec)
해당 생성된 binlog 콘텐츠:
# at 715 #180617 22:56:44 server id 1 end_log_pos 780 CRC32 0x9f52450e Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 780 #180617 22:56:44 server id 1 end_log_pos 854 CRC32 0x0959bc8d Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1529247404/*!*/; BEGIN /*!*/; # at 854 #180617 22:56:44 server id 1 end_log_pos 907 CRC32 0x2945260f Table_map: `testdb`.`hello` mapped to number 110 # at 907 #180617 22:56:44 server id 1 end_log_pos 956 CRC32 0xc70df255 Delete_rows: table id 110 flags: STMT_END_F ### DELETE FROM `testdb`.`hello` ### WHERE ### @1=2 ### @2='Bill' # at 956 #180617 22:56:44 server id 1 end_log_pos 987 CRC32 0x0c98f18e Xid = 15 COMMIT/*!*/;
binlog 증분 복구 사용
MySQL 논리적 백업은 일반적으로 전체 백업과 증분 백업을 결합합니다. mysqldump를 사용하여 정기적으로 데이터베이스를 전체 백업한 다음 binlog를 사용하여 증분 데이터를 저장합니다. 데이터 복원 시 mysqldump에 의해 백업된 데이터는 백업 시점으로 복원된다. 백업 시점부터 현재 시점까지 데이터베이스를 증분 수정하면, binlog에 있는 증분 데이터는 mysqlbinlog를 통해 데이터베이스에 복원된다. 이제 mysqldump를 사용하여 데이터베이스를 다음 위치로 복원했다고 가정합니다.
mysql> select * from hello; +----+------+ | id | name | +----+------+ | 1 | Andy | | 2 | Lily | | 3 | Will | +----+------+ 3 rows in set (0.00 sec)
나중에 실행된 SQL:
update hello set name = 'David' where id = 3;
insert hello (name) values ('Frank');
delete from hello where id = 2;STATEMENT를 사용하든 ROW를 사용하든 mysqlbinlog 명령은 binlog를 데이터베이스 [doc]에 점진적으로 복원할 수 있습니다.
binlog를 관찰하면 초기 업데이트 hello set name = 'David'(id = 3)부터 id = 2;의 hello에서 최종 삭제까지 시간은 "2018-06-17 22: 54:13" ~ "2018-06-17 22:56:44"이므로 시점 복구 기준으로 명령은
$ sudo mysqlbinlog --start-datetime="2018-06-17 22:54:13" --stop-datetime="2018-06-17 22:56:44" mysql-bin.000002 | mysql -uroot -p123456
binlog 的事件位置号是从 "154" 到 "956"，但需要注意的是 用 --start-position 和 --stop-position 指定位置点范围，逻辑上对应的是 start <= position < stop이므로 시점 복구 기준으로 명령은 다음과 같습니다.
$ sudo mysqlbinlog --start-position=154 --stop-position=957 mysql-bin.000002 | mysql -uroot -p123456
두 방법 모두 어떤 방식으로든 실행할 수 있습니다.
mysql> select * from hello; +----+-------+ | id | name | +----+-------+ | 1 | Andy | | 3 | David | | 4 | Frank | +----+-------+ 3 rows in set (0.00 sec)<p><strong>binlog2sql을 사용하여 플래시백</strong></p> <p>binlog2sql, Dianping.com의 DBA인 Cao Shanfeng이 작성함. binlog2sql은 MySQL binlog에서 원하는 SQL을 구문 분석합니다. 옵션에 따라 원본 SQL, 롤백 SQL, 기본 키가 제거된 INSERT SQL 등을 얻을 수 있습니다. binlog2sql, 기본 구현은 MySQL 복제 프로토콜 및 binlog 형식의 구문 분석을 완료하는 python-mysql-replication을 사용합니다. </p> <pre class="brush:php;toolbar:false">$ python binlog2sql/binlog2sql.py -h192.168.2.107 -uroot -p123456 --start-position=154 --stop-position=957 --start-file='mysql-bin.000002' UPDATE `testdb`.`hello` SET `id`=3, `name`='David' WHERE `id`=3 AND `name`='Will' LIMIT 1; #start 4 end 411 time 2018-06-17 22:54:13 INSERT INTO `testdb`.`hello`(`id`, `name`) VALUES (4, 'Frank'); #start 442 end 684 time 2018-06-17 22:55:47 DELETE FROM `testdb`.`hello` WHERE `id`=2 AND `name`='Bill' LIMIT 1; #start 715 end 956 time 2018-06-17 22:56:44
롤백 sql 생성:
$ python binlog2sql/binlog2sql.py --flashback -h192.168.2.107 -uroot -p123456 --start-position=154 --stop-position=956 --start-file='mysql-bin.000002' INSERT INTO `testdb`.`hello`(`id`, `name`) VALUES (2, 'Bill'); #start 715 end 956 time 2018-06-17 22:56:44 DELETE FROM `testdb`.`hello` WHERE `id`=4 AND `name`='Frank' LIMIT 1; #start 442 end 684 time 2018-06-17 22:55:47 UPDATE `testdb`.`hello` SET `id`=3, `name`='Will' WHERE `id`=3 AND `name`='David' LIMIT 1; #start 154 end 411 time 2018-06-17 22:54:13
플래시백의 실제 원리는 매우 간단합니다. 먼저 MySQL 복제 프로토콜의 com-binlog-dump 명령을 통해 binlog를 덤프한 다음 binlog를 따릅니다. 형식 사양에 따라 binlog를 구문 분석하고, binlog를 SQL로 변환하고, 이러한 SQL을 역논리 SQL로 변환한 후 최종적으로 역순으로 실행합니다.
Java 구문 분석 binlog
上文中的 binlog2sql 其实底层依赖 python-mysql-replication 库，这是 Python 库。如果想使用 Java 解析 binlog 可以使用 mysql-binlog-connector-java（github）库。目前开源的 CDC 工具，如 Zendesk maxwell、Redhat debezium、LinkedIn Databus 等都底层依赖 mysql-binlog-connector-java 或者其前身 open-replicator。使用 mysql-binlog-connector-java 的示例代码如下：
BinaryLogClient client = new BinaryLogClient("192.168.2.107", 3306, "root", "123456");
client.setBinlogFilename("mysql-bin.000001");
client.setBinlogPosition(4);
client.setBlocking(false);
client.registerEventListener(event -> {
System.out.println(event);
});
client.connect();输出（省略部分内容）：
...
Event{header=EventHeaderV4{timestamp=1529247253000, eventType=TABLE_MAP, serverId=1, headerLength=19, dataLength=34, nextPosition=346, flags=0}, data=TableMapEventData{tableId=110, database='testdb', table='hello', columnTypes=8, 15, columnMetadata=0, 40, columnNullability={1}}}
Event{header=EventHeaderV4{timestamp=1529247253000, eventType=EXT_UPDATE_ROWS, serverId=1, headerLength=19, dataLength=46, nextPosition=411, flags=0}, data=UpdateRowsEventData{tableId=110, includedColumnsBeforeUpdate={0, 1}, includedColumns={0, 1}, rows=[
{before=[3, Will], after=[3, David]}
]}}
...
Event{header=EventHeaderV4{timestamp=1529247347000, eventType=TABLE_MAP, serverId=1, headerLength=19, dataLength=34, nextPosition=634, flags=0}, data=TableMapEventData{tableId=110, database='testdb', table='hello', columnTypes=8, 15, columnMetadata=0, 40, columnNullability={1}}}
Event{header=EventHeaderV4{timestamp=1529247347000, eventType=EXT_WRITE_ROWS, serverId=1, headerLength=19, dataLength=31, nextPosition=684, flags=0}, data=WriteRowsEventData{tableId=110, includedColumns={0, 1}, rows=[
[4, Frank]
]}}
...
Event{header=EventHeaderV4{timestamp=1529247404000, eventType=TABLE_MAP, serverId=1, headerLength=19, dataLength=34, nextPosition=907, flags=0}, data=TableMapEventData{tableId=110, database='testdb', table='hello', columnTypes=8, 15, columnMetadata=0, 40, columnNullability={1}}}
Event{header=EventHeaderV4{timestamp=1529247404000, eventType=EXT_DELETE_ROWS, serverId=1, headerLength=19, dataLength=30, nextPosition=956, flags=0}, data=DeleteRowsEventData{tableId=110, includedColumns={0, 1}, rows=[
[2, Bill]
]}}위 내용은 MySQL의 binlog 로그 파일에 대한 자세한 소개의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
