MySQL 二进制日志

1. 二进制日志

Binlog 在 MySQL 的 Server 层实现（引擎共用）
Binlog 为逻辑日志，记录的是一条语句的原始逻辑
Binlog 不限大小，追加写入，不会覆盖以前的日志

他和 redo log 的区别就在于，他是逻辑日志，而redo log 是物理日志

1.1 概述

二进制日志（BINLOG）记录了所有 DDL（数据定义语言）和 DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL 的主从复制，就是通过该 binlog 实现的。

1.2 日志格式

1.2.1 STATEMENT

该日志格式在日志文件中记录的都是 SQL 语句（statement），每一条对数据进行修改的 SQL 都会记录在日志文件中，通过 MySQL 提供的 mysqlbinlog 工具，可以清晰的查看到每条语句的文本。主从复制的时候，从库（slave）会将日志文件解析为原文本，并在从库重新执行一次。

1.2.2 ROW

该日志格式在日志文件中记录的是 每一行的数据变更 ，而不是记录 SQL 语句。比如，执行 SQL 语句：update tb_book set status=‘1’，如果是 statement 日志格式，在日志中会记录一行 SQL 文件；而如果是 row ，由于是对全表进行更新，也就是每一行记录都会发生变更，row 格式的日志中会记录每一行数据变更。

statement 记录的是SQL语句，row 记录的是数据库中所有变化的行

1.2.3 MIXED

这是目前 MySQL 默认的日志格式，即混合了 STATEMENT 和 ROW 两种格式。默认情况下采用 STATEMENT 。但是在一些特殊情况下采用 ROW 来进行记录。MIXED 格式能尽量利用两种模式的优点，而避开他们的缺点

1.3 bin-log 开启

bin-log 默认是关闭的，先用 sql 查询自己的 bin-log 是否是开启的

show variables like 'log_%';

查询结果如下，这里一般是 OFF ，但是我的已经打开了。

如果是 OFF 的话，先找到自己的 my.ini 文件（路径一般是：C:\ProgramData\MySQL\MySQL Server 5.6）

在 my.ini 文件下添加如下两句话：

# 开启二进制文件
log_bin=mysql-binlog
# 二进制文件的格式，有 statement 、row 、mixed 可选
binlog_format=STATEMENT

然后重启 mysql 的服务就好了

binlog 开启成功之后，你可以看到以下两个文件（C:\ProgramData\MySQL\MySQL Server 5.6\data）

• mysql-binlog.000001：日志文件
• mysql-binlog.index： 该文件是日志索引文件，记录日志的文件名

查看 binlog 文件的两种方式：

• 导出成文本文件：
  D:\AppServ\MySQL\bin>mysqlbinlog >d:/test.txt
  确保在bin目录下有mysqlbinlog.exe 没有的话搜索下载
• mysql 使用mysqlbinlog查看日志的问题
  mysqlbinlog mysql-bin.00005 --startdatetime=‘时间’ --stopdatetime=‘时间’ --database=DB_name | grep -i -A 6 ‘id=111’

用grep…

2. 主从复制

2.1 复制概述

复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步

MySQL 支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制

2.2 复制原理

MySQL 的主从复制原理如下：

Binary log：二进制日志
Relay log：中继日志

从上层来看，复制分成三步：

• Master 主库在事务提交时，会把数据变更作为时间 Events 记录在二进制日志文件 Binlog 中
• 主库推送二进制日志文件 Binlog 中日志事件到从库的中继日志 Relay log
• Slave 重做中继日志中的事件，将改变反映它自己的数据

2.3 复制优势

• 主库出现问题，可以快速切换到从库提供服务
• 可以在从库上执行查询操作，从主库中更新，实现读写分离，降低主库的访问压力
• 可以在从库中执行备份，以避免备份期间影响主库的服务