Mysql学习3-binlog

Mysql数据库的日志可以分为服务层和存储引擎层日志。服务层日志主要包含二进制日志（binlog）、慢查询日志和通用日志；存储引擎层日志（以InnoDB为例）包含重做日志（redolog）和回滚日志（undolog）。

binlog中只存储执行成功的日志。

binlog的三种格式

设置binlog日志格式的参数：

set binlog_format=[statement|row|mixed];

查看binlog文件：

show binary logs;

刷新log文件：

flush logs;

查看binlog内容：

mysqlbinlog -vv binlog.000154

一、STATEMENT

按段存储，mysql 5.7 之前的版本默认使用的日志格式。binlog中只记录数据库修改时使用的sql语句。

优点：

日志较小，节省磁盘和网络IO，只记录sql语句

缺点：

必须记录上下文信息，以保证在从服务器上能得到相同的结果

特定函数无法复制，如UUID(), user()，可能造成主从服务器数据不一致

BEGIN
/*!*/;
# at 1351
# at 1383
#210216 23:44:22 server id 1  end_log_pos 1383 CRC32 0x3a8b5df0 	Intvar
SET INSERT_ID=3/*!*/;
#210216 23:44:22 server id 1  end_log_pos 1492 CRC32 0x2503c540 	Query	thread_id=15	exec_time=0	error_code=0
SET TIMESTAMP=1613490262/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
insert into t (num) values (3)
/*!*/;
# at 1492
#210216 23:44:22 server id 1  end_log_pos 1523 CRC32 0xe14ff0b0 	Xid = 297
COMMIT/*!*/;

二、ROW

基于行的binlog日志格式，Mysql 5.7 版本之后默认的binlog日志格式

优点：

主从复制更加安全

对每一行的复制效率高于基于段的复制效率

如果对数据进行了误操作，可以通过ROW格式的binlog进行反向处理，恢复数据

缺点：

记录的日志量较大，尤其是对于批量处理数据，每一行变动都会记录

优化：

mysql通过binlog_row_image参数对ROW格式的binlog进行了优化

set binlog_row_image=[FULL|MANIMAL|NOBLOG]

**FULL：**默认，记录被修改行的数据的所有列的内容

### UPDATE `test`.`t`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2=4 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2=5 /* INT meta=0 nullable=1 is_null=0 */

**MINIMAL：**只记录被修改的列的变化

### UPDATE `test`.`t`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
### SET
###   @2=4 /* INT meta=0 nullable=1 is_null=0 */
# at 1035
#210217  0:14:33 server id 1  end_log_pos 1066 CRC32 0x37eac0bb 	Xid = 309
COMMIT/*!*/;

**NOBLOG：**与FULL类似，但是对于blog和text的值没有修改的话，就不记录（这里我先在表中添加了一个text字段）

### UPDATE `test`.`t`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=3 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=4 /* INT meta=0 nullable=1 is_null=0 */
# at 1522
#210217  0:22:11 server id 1  end_log_pos 1553 CRC32 0x853a53a6 	Xid = 315
COMMIT/*!*/;

上面的日志中省略了text字段：

mysql> select * from t;
+----+------+------+
| id | num  | tt   |
+----+------+------+
|  1 |    1 | NULL |
|  3 |    4 | NULL |
+----+------+------+

三、MIXED

mixed 格式由系统来决定使用哪种格式

• 系统根据sql语句来决定使用STATEMENT还是ROW。一般使用statement，如果sql中含有不确定函数，则会使用row格式

• 日志的大小由sql和事务的隔离级别来决定。如果隔离级别设置为读已提交，则会使用row格式

binlog日志格式对复制的影响

基于sql语句的复制（SBR）

主库binlog采用statement格式的时候。

优点：

日志量比较少，节约网络IO

不强制要求主从数据库的表定义完全相同（列的顺序可以不同）。

复制方式更加灵活

缺点：

无法保证非缺点函数的结果，如UUID()

对存储过程、触发器、自定义函数进行修改可能导致数据不一致

可能需要锁定更多的行

基于行的复制（RBR）

主库binlog采用row格式的时候。

优点：

能够保证主从库的数据一致

可以减少从服务器上行锁的使用

缺点：

要求主从服务器上的表结构完全一致，否则可能会中断复制

无法在从服务器上执行触发器