binlog介绍

最新推荐文章于 2024-04-23 08:15:00 发布

weixin_34211761

最新推荐文章于 2024-04-23 08:15:00 发布

阅读量155

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 python

原文链接：https://my.oschina.net/13426421702/blog/796373

2019独角兽企业重金招聘Python工程师标准>>>

什么是binlog

binlog是MySQL的二进制日志，可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间

binlog有什么用途

binlog实现主从复制，master开启binlog记录操作日志,slave通过master授予复制权限发那个访问主库，获取到binlog做并做解析，再做记录数据的追加
可以根据时间段、或者开始和结束位置查找对应的binlog，转换成对应的sql或者直接导入到数据库
开启binlog会对性能产生损耗，因为会增加IO读写，官方手册提供的数据是1%的性能损耗

开启和配置binlog

mysql默认配置是不会开启binlog，如果需要开启，需要在cnf的配置文件下，添加如下配置：log-bin=binlog(文件名前缀)
查看当前数据库设置的binlog的类型：show variables like 'binlog_format'
执行如下set命令或者cnf新增配置
binlog_format=STATEMENT、binlog_format=ROW、binlog_format=MIXED

binlog查看命令

./mysql/bin/mysqlbinlog --base64-output=decode-rows -v mysql-bin.000009 > mixed.txt

三种binlog类型比较

1、statement（statement-based replication基于语句）默认类型，具有如下特点：

基于语句的binlog示例：

SET @@session.collation_database=DEFAULT/*!*/;
create table user_info(name varchar(10),age int (1))
/*!*/;
# at 246
#161129 11:30:31 server id 1  end_log_pos 325 CRC32 0xc8d48f9f  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1480390231/*!*/;
BEGIN
/*!*/;
# at 325
#161129 11:30:31 server id 1  end_log_pos 437 CRC32 0xb30ee456  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1480390231/*!*/;
insert into user_info values('20',100)
/*!*/;
# at 437
#161129 11:30:31 server id 1  end_log_pos 468 CRC32 0xcbfecfe5  Xid = 30
COMMIT/*!*/;
# at 468
#161129 11:34:36 server id 1  end_log_pos 547 CRC32 0x15670894  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1480390476/*!*/;
BEGIN
/*!*/;
# at 547
#161129 11:34:36 server id 1  end_log_pos 664 CRC32 0x060b73a3  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1480390476/*!*/;
update user_info set age=99 where name='20'
/*!*/;
# at 664
#161129 11:34:36 server id 1  end_log_pos 695 CRC32 0xdeb0be1d  Xid = 32
COMMIT/*!*/;

2、row（row-based replication基于行记录）

基于行的binlog示例：

create table user_info (name varchar(10),age int(1))
/*!*/;
# at 246
#161129 14:22:43 server id 1  end_log_pos 318 CRC32 0xe64dfdd3  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1480400563/*!*/;
BEGIN
/*!*/;
# at 318
#161129 14:22:43 server id 1  end_log_pos 373 CRC32 0xdda86f16  Table_map: `test`.`user_info` mapped to number 70
# at 373
#161129 14:22:43 server id 1  end_log_pos 416 CRC32 0xfce7b3ee  Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `test`.`user_info`
### SET
###   @1='tt'
###   @2=100
# at 416
#161129 14:22:43 server id 1  end_log_pos 447 CRC32 0x5d2403c1  Xid = 21
COMMIT/*!*/;
# at 447
#161129 14:22:58 server id 1  end_log_pos 519 CRC32 0x699d2647  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1480400578/*!*/;
BEGIN
/*!*/;
# at 519
#161129 14:22:58 server id 1  end_log_pos 574 CRC32 0x65ea9b82  Table_map: `test`.`user_info` mapped to number 70
# at 574
#161129 14:22:58 server id 1  end_log_pos 626 CRC32 0x260fd57c  Update_rows: table id 70 flags: STMT_END_F
### UPDATE `test`.`user_info`
### WHERE
###   @1='tt'
###   @2=100
### SET
###   @1='tt'
###   @2=111
# at 626
#161129 14:22:58 server id 1  end_log_pos 657 CRC32 0x462c081f  Xid = 22
COMMIT/*!*/;

3、mixed(mixed-based replication混合模式)

混合模式的示例：

create table user_info (name varchar(100),age int(1),create_time timestamp)
/*!*/;
# at 391
#161129 15:29:21 server id 1  end_log_pos 478 CRC32 0x7919babc  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1480404561/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
BEGIN
/*!*/;
# at 478
#161129 15:29:21 server id 1  end_log_pos 608 CRC32 0xd5446943  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1480404561/*!*/;
insert into user_info  values ('test',100,now())
/*!*/;
# at 608
#161129 15:29:21 server id 1  end_log_pos 639 CRC32 0x5ac70783  Xid = 21
COMMIT/*!*/;
# at 639
#161129 15:29:43 server id 1  end_log_pos 719 CRC32 0x70e976ab  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1480404583/*!*/;
BEGIN
/*!*/;
# at 719
#161129 15:29:43 server id 1  end_log_pos 776 CRC32 0xf88ede22  Table_map: `test`.`user_info` mapped to number 71
# at 776
#161129 15:29:43 server id 1  end_log_pos 858 CRC32 0xe96068b2  Write_rows: table id 71 flags: STMT_END_F
### INSERT INTO `test`.`user_info`
### SET
###   @1='98aef934-b605-11e6-a7c5-7908bb90a27b'
###   @2=99
###   @3=1480404583
# at 858
#161129 15:29:43 server id 1  end_log_pos 889 CRC32 0xafd8326b  Xid = 22
COMMIT/*!*/;