MySQL二进制日志管理和数据恢复

一、二进制日志的作用

• 备份恢复
• 主从复制

二、启用二进制日志

查看当前MySQL是否启用二进制日志：

mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+

通过配置开启二进制日志：

server_id=1             #设定server_id
log_bin=mysql-bin       #开启二进制日志，并设置文件名称前缀为mysql-bin
binlog_format=row       #二进制日志格式

修改之后重启MySQL。在MySQL的数据目录下可以看到两个文件：

[root@server01 ~]# ls -l /usr/local/mysql/data/mysql-bin.*
-rw-rw---- 1 mysql mysql 120 12月 12 19:35 /usr/local/mysql/data/mysql-bin.000001
-rw-rw---- 1 mysql mysql  19 12月 12 19:35 /usr/local/mysql/data/mysql-bin.index

mysql-bin.index文件中记录了当前正在使用的二进制日志文件。
mysql-bin.000001是记录二进制日志内容的文件。

三、二进制日志文件记录格式

二进制日志是SQL层的功能，记录的是数据变更类的SQL语句，DCL、DDL和已经提交事务的DML语句。
二进制日志文件的格式：

• statement：语句模式。是MySQL5.6的默认格式，会将DML语句原封不动的记录；
• row：行模式。是MySQL5.7的默认格式，记录数据行的变化；
• mixed：以上两种模式的混合模式。

statement和row的对比：

• statement可读性很高，因为是将DML语句原封不动的记录。日志量少，但不够严谨。
• row的日志量很大，可读性低。但足够严谨。

四、event事件

4.1、概念

event事件是二进制日志记录的最小单元。一个DCL或DDL是一个事件，而一个DML语句则被分成四个events：

BEGIN;
DML1;
DML2;
COMMIT;

4.2、查看event

4.2.1、查看有效的二进制日志文件

查看当前正在使用的二进制日志文件：SHOW MASTER STATUS;

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

• File：当前使用的二进制日志文件的名称；
• Position：最后一个事件的结束位置。

查看所有有效的二进制日志文件：SHOW BINARY LOGS;

mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+--------