而mysql 二进制日志点_MySQL二进制日志-CSDN博客

本文链接：https://blog.csdn.net/weixin_39681486/article/details/113161293

二进制日志简介

二进制日志记录数据库的变化，如创建表操作，表数据增删修改，以及可能会造成数据修改的事件(如没有匹配行的DELETE语句，除非使用ROW-base日志格式)，及更新数据消耗的时长。二进制文件不记录SELECT、SHOW等不会修改数据的语句。

二进制日志的两个重要作用：

用于复制，主库提供数据更改的日志记录将被发送到从库，从库执行同样的操作保证主从数据一致。

根据二进制日志进行数据恢复，在备份恢复后，可从备份点重新执行二进制日志恢复到最新时间。

切换二进制日志文件：

启动MySQL服务器

使用flush logs刷新日志

当前二进制日志文件大小大于max_binlog_size，当事务比较大时，同一事务只能写入到同一个文件中，不能分割文件写入，因此可能出现二进制日志文件比max_binlog_size大。

二进制日志格式

STATEMENT

默认的MySQL的二进制日志格式，基于此复制称为statement-based replication(SBR)

ROW

基于此复制称为row-based replication(RBR)

MIXED

基于此复制称为mixed-based replication(MBR)

mysql> show variables like 'binlog_format';

+---------------+-----------+

| Variable_name | Value |

+---------------+-----------+

| binlog_format | STATEMENT |

+---------------+-----------+

1 row in set (0.04 sec)

修改binlog_format需要SUPER权限

mysql> SET GLOBAL binlog_format = 'ROW';

除了手动切换日志格式，Slave也可能自动切换。当主库使用ROW格式时，备库使用STATEMENT或MIXED，

备库将临时对该事务切换到ROW格式，复制完成将重新切换回之前格式。

会话修改二进制日志格式的情况：

会话中大量更新较小的语句可能使用基于ROW的记录

执行匹配许多行的WHERE子句的更新适合使用基于STATEMENT的记录

更新语句执行时间较长，但只有少数行记录被更新适合使用基于ROW的记录

下列情况将不可运行时修改二进制日志格式，如修改将报错：

在存储函数或者触发器中修改

NDB存储引擎启用

当前会话使用 RBR 模式，并且已打开了临时表

当使用InnoDB表且事务隔离级别是READ COMMITTED 或 READ UNCOMMITTED时，只能使用ROW格式。

如果将运行时修改日志格式为STATEMENT，将会报错因为InnoDB表不能插入操作。

当存在temporary表时，运行时不建议修改复制格式，因为临时表只会当SBR是才会写入日志，RBR不会

写入，MBR也会写入。

当二进制格式为ROW时，记录大量修改的语句仍可使用STATEMENT格式,如DDL语句CREATE TABLE、ALTER

TABLE、DROP TABLE。

当使用MIXED，下列情况将会自动从STATEMENT切换ROW:

函数中包含UUID()

表中AUTO_INCREMENT列被更新，并在触发器或存储函数中调用。

当视图创建需要依赖于RBR，如创建视图语句包含UUID()函数

调用UDF

非事务表执行INSERT DELAYED语句

如果一个会话存在临时表且语句使用ROW格式，则后续执行语句都将使用ROW(除访问临时表)，除非该会话中的临时表被删除。

当FOUND_ROWS() 或 ROW_COUNT()函数使用

当USER(), CURRENT_USER() 或 CURRENT_USER使用

当语句是指一个或多个系统变量

当LOAD_FILE()函数使用

mysql库的日志格式

直接修改mysql数据库表中数据的语句的日志格式根据binlog_format设置值，如INSERT, UPDATE, DELETE, REPLACE, DO, LOAD DATA INFILE, SELECT, TRUNCATE TABLE。

直接修改mysql数据库表中数据的语句的日志格式使用STATEMENT而忽略binlog_format设置的值。如GRANT, REVOKE, SET PASSWORD, RENAME USER, CREATE (all forms except CREATE TABLE ... SELECT), ALTER (all forms), and DROP (all forms)。

CREATE TABLE ... SELECT是DDL与DML的组合。CREATE TABLE使用STATEMENT格式而SELECT部分根据binlog_format设置的值

两者模式的优缺点

SBR 的优点：

从3.23已开始存在，历史悠久，技能成熟

binlog文件较小，当DELETE或UPDATE影响多行，日志文件占用较小的存储空间。

binlog中包含了所有数据库修改信息，可以据此来审核数据库

主从版本可以不一样，从服务器版本可以比主服务器版本高

SBR 的缺点：

SBR中不安全的语句

并非所有更新数据的语句都会在SBR中复制，尤其是包含不确定操作的时候。

如下列DML语句：

调用具有不确定因素的 UDF 或存储过程，如程序的返回值取决于多种因素而不仅仅包含入参