mysql binlog 学习笔记

学习笔记 马富天 2018-11-29 16:38:26 1 0

【摘要】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;
  7. # 插入数据:
  8. INSERT INTO mafutian(name) VALUES("aa");
  9. INSERT INTO mafutian(name) VALUES("bb");
  10. INSERT INTO mafutian(name) VALUES("cc");
  11. # 修改数据:
  12. UPDATE mafutian SET name = "dd" WHERE id = 3;
  13. # 删除数据:
  14. DELETE FROM mafutian;
  15. # 删除库表:
  16. DROP TABLE mafutian;
  17. # 若日志文件过大了,则需要生产新的日志文件
  18. # 生产新的日志,一般第一个文件是 *.000001 ,然后依次自增 1
  19. 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_%"

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

版权归 马富天PHP博客 所有

本文标题:《mysql binlog 学习笔记》

本文链接地址:http://www.mafutian.net/396.html

转载请务必注明出处,小生将不胜感激,谢谢! 喜欢本文或觉得本文对您有帮助,请分享给您的朋友 ^_^

0

0

上一篇《 Python 单个页面连接 MySQL 数据库,进行增删改实例 》 下一篇《 使用原生 javascript 编写 ajax 代码并封装成函数 》

暂无评论

评论审核未开启
表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情
验证码

TOP10

  • 浏览最多
  • 评论最多