先来聊聊MySQL的binlog文件解析

看腻了文章就来听听视频讲解吧：https://www.bilibili.com/video/BV1F94y1s7xe/

简介

MySQL的binlog日志是用来记录MySQL对数据库有变更操作的记录，包括DDL和DML，除了select或show等查询类语句不会被binlog日志记录，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。

binlog文件类型：

• 二进制索引文件(后缀名为.index)
• 日志文件(后缀名为.00000*),记录数据库所有的DDL和DML(除了查询语句select)语句事件

用途：
主要用于数据库的主从复制和数据恢复

• 主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
• 数据恢复：通过使用mysqlbinlog工具来恢复数据。

MySQL binlog的三种工作模式

• statement格式，生产环境不建议使用
  优点：不需要记录每一行的变化，减少了binlog日志量，节约I/O，提高性能
  缺点：使用特殊函数或跨库操作时容易丢失数据
• row格式，生产环境建议使用
  优点：记录每行数据被修改的情况，安全性高
  缺点：会产生大量binlog，网络开销也较大
• mixed格式，生产环境不建议使用
  MySQL5.1的一个过渡版本，DDL语句会记录成statement，DML会记录成row

binlog相关参数

-- binlog开关
mysql> show variables like 'log_bin%';
+---------------------------------+-------------------------------+
| Variable_name                   | Value                          
+---------------------------------+-------------------------------+
| log_bin                         | ON                              
| log_bin_basename                | /mysqldata/data/mysql-bin      
| log_bin_index                   | /mysqldata/data/mysql-bin.index
| log_bin_trust_function_creators | OFF                            
| log_bin_use_v1_row_events       | OFF                             
+---------------------------------+-------------------------------+
5 rows in set (0.00 sec)
-- binlog工作模式
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)
-- binlog文件大小，1073741824/1024/1024/1024=1GB
mysql> show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)
-- 安全参数 ：binlog过期天数自动清理，建议全备周期2倍
mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
-- 查看biglog日志文件所在点，最后一个为当前binlog日志文件
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000012 |      8736 |
| mysql-bin.000013 |      3087 |
+------------------+-----------+
2 rows in set (0.00 sec)

-- 重新生成一个binlog日志
mysql> flush logs;
-- 重置(清空)所有binlog日志(慎用)
mysql> reset master;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

mysqlbinlog解析工具

mysqlbinlog解析工具是安装MySQL时自带的，一款用于将MySQL的binlog日志转换成“SQL语句”，默认情况下binlog日志是二进制文件，无法直接查看。

# 解析binlog文件，打印误操作SQL前后各10行，选择要恢复的结束位点 # at xxoo
mysqlbinlog -v -v mysql-bin.000002 | grep "DROP xxoo" -A 10 -B 10

# 统计DML操作频繁的表（ROW模式统计的是行数，statement模式统计的是次数）
[root@db01 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-bin.000012 | awk '/###/{if($0~/UPDATE|INSERT|DELETE/)count[$2" "$NF]++}END{for(i in count)print i,"\t",count[i]}' | column -t | sort -k3nr | more
INSERT  `mdb`.`t12`  9
UPDATE  `mdb`.`t12`  5
DELETE  `mdb`.`t12`  3
1
2
3
4
5
6
7
8

MySQL的binlog系列和奇技操作：

先来聊聊MySQL的binlog文件解析
接着说说mysqlbinlog解析工具如何做数据恢复
再来谈谈如何从binlog文件恢复误update的数据，模拟Oracle的闪回功能
接着聊聊如何从binlog文件恢复误delete的数据，模拟Oracle的闪回功能
借用binlog2sql工具轻松解析MySQL的binlog文件，再现Oracle的闪回功能
再来介绍另一个binlog文件解析的第三方工具my2sql
顺带来聊聊MySQL误删ibdata数据文件的恢复
MySQL大表直接复制文件的copy方式