mysql binlog 学习笔记

【摘要】mysql 的 binlog 日志（二进制日志）记录着所有更新、插入、建表，改表等等出了查询之外的所有 SQL 语句，当我们某表、某库被删时，通过 binlog 日志是可以找回数据的。 mysql 默认是不开启 binlog 日志的，需要手动开启。

开启 binlog 日志其实就相当于将用户的每一个操作都记录下来，当我们需要的时候可以回顾查询，往往 binlog 日志是非常大的，记录着不同数据库、不同表、不同 SQL 操作，想要找回所有原始数据是需要很大耐心去做的一份工作。

一、开启 mysql binlog 日志，打开配置文件 my.cnf，在 [mysqld] 字段下面添加：

1. [mysqld]

2. log-bin = mafutian	#	bin-log = 日志名，即 mafutian 指的是日志名称，这里是可以自定义的

3. binlog_format = MIXED # 修改 binlog 日志的保存方式，以便出现类似存储过程、存储函数的保存时出错

保存后重启 mysql 服务即生效，开启之后，会在 mysql 的 data 目录下生成两个文件：mafutian.index 和 mafutian.000001。

二、创建若干表和相关 SQL 操作：

首先，可以自行创建一些表，进行删减操作，这里不是核心问题，核心问题是如何通过 binlog 日志找回想要的数据。

第 1 步，创建测试数据：

1. # 创建表：

2. CREATE TABLE `mafutian` (

3.   `id` int(11) NOT NULL AUTO_INCREMENT,

4.   `name` varchar(30) DEFAULT NULL,

5.   PRIMARY KEY (`id`)

6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

8. # 插入数据：

9. INSERT INTO mafutian(name) VALUES("aa");

10. INSERT INTO mafutian(name) VALUES("bb");

11. INSERT INTO mafutian(name) VALUES("cc");

13. # 修改数据：

14. UPDATE mafutian SET name = "dd" WHERE id = 3;

16. # 删除数据：

17. DELETE FROM mafutian;

19. # 删除库表：

20. DROP TABLE mafutian;

22. # 若日志文件过大了，则需要生产新的日志文件

23. # 生产新的日志，一般第一个文件是 *.000001 ，然后依次自增 1

24. flush logs;

第 2 步，找回数据：

将最新的 binlog 日志文件拷贝到指定的文件中，这样不会影响网站当前的运行，不会造成数据的混乱。

打开 dos ，进入该日志所在位置即可，我这里已经是 mafutian.000005 了。

1. # 将 binlog 文件转成可阅读的 txt 文件，命令为

2. mysqlbinlog mafutian.000005 > 0005.txt

然后就会在该目录下生成一个 0005.txt 文件，打开之后，可以进行阅读，仔细看的话，第一步操作的所有 SQL 语句都能够看到，我们这里最主要的是看 # at m、# at n,看 m,n 是多少，然后通过 m,n 的范围来回复指定的数据。

仔细分析文件，可以看出从创建表开始的行数为：1236 ,至删除数据之前的行数为：3068，我们只要重新执行这区间的 SQL ，就能够找回所有数据。执行方式如下：

1. mysqlbinlog mafutian.000005 --start-position=1236 --stop-position=3068 | mysql -uroot -p

最后就恢复了数据，具体我就不截图啦~

三、修改 binlog_format，解决 binlog 为 row 格式时增长量过大问题

开启 binlog 之后，如果不做任何修改，一些大的 DML 操作，会导致 binlog 量增加很大，消耗额外的 IO 、网络资源，会发现在创建存储函数的时候，特别容易出现如下错误：

MySQL：Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT...

binlog_format = MIXED 的作用就是解决这一问题。以下命令可以查看 binlog 相关信息：

1. SHOW VARIABLES LIKE "binlog_%"

最后，数据恢复应该是一个细心活，为了不要在数据丢失上花费很多心思，一定要养成定期备份数据库的习惯。

我要打赏