MySQL binlog相关

最新推荐文章于 2023-11-16 20:14:57 发布
最新推荐文章于 2023-11-16 20:14:57 发布
阅读量1.6k 收藏
点赞数
分类专栏： MySQL 备份还原
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Hehuyi_In/article/details/100712899
版权

一、 简介

  • 二进制日志记录了数据库的所有变化，常用于复制、审计、基于时间点恢复。
  • 二进制日志=一组binlog文件+binlog index文件
  • binlog index文件每一行都包含一个binlog文件名 
  • active binlog file：当前正在写入的binlog文件
  • 基于语句的二进制日志过滤器 
在my.cnf中添加过滤选项
binlog-ignore-db 忽略指定db
binlog-do-db 仅保留该db信息

[mysqld]
binlog-ignore-db=one_db
binlog-ignore-db=two_db
或
[mysqld]
binlog-do-db=one_db
binlog-do=two_db

注意它使用current database(如use testdb;)来决定是否需要过滤语句，而不是依据表所在数据库

例：若设置binlog-ignore-db=bad，执行：
1. use bad; insert into t1 values(1);
2. use bad; insert into good.t2 values(1);
3. use bad; update good.t1,ugly.t2 set a=b;

以上三条语句都会被过滤掉不记入binlog，因为current database均为bad

二、 常用命令

#查看所有的二进制日志列表
SHOW MASTER LOGS;

#根据文件或时间点来删除二进制日志
PURGE { BINARY | MASTER } LOGS {TO 'log_name' | BEFORE datetime_expr }
#删除mysql-bin.000002之前的日志（不包括mysql-bin.000002）
PURGE BINARY LOGS TO 'mysql-bin.000002';
#删除2014-04-28 23:59:59时间点之前的日志（如果删除主库binlog，一定要注意从库读到主库的哪个binlog，不要删多了）
PURGE BINARY LOGS BEFORE '2014-04-28 23:59:59';

#删除所有的二进制日志并清空二进制日志索引文件（生产环境谨慎操作）
reset master; 

#删除slave复制所用的所有文件（生产环境谨慎操作）
reset slave;

#触发过期日志清除操作(需设置expire_logs_days参数)
flush logs

#查看二进制日志 mysql-bin.000001，从pos 32开始，偏移2行，查询10条
show binlog events in 'mysql-bin.000001' from 32 limit 2,10\G

三、 mysqlbinlog

1. 常用参数

  • --database=db_name或-d db_name：只列出指定数据库相关内容
  • --base64-output=decode-rows ：将 BINLOG 语句中事件以 base-64 的编码显示，屏蔽一些二进制内容
  • -v：仅解释行信息
  • -vv：不但解释行信息，还将 SQL 列类型的注释信息也解析出来
  • -r：重定向到指定文件，与>作用相同
  • --start-position、--stop-position：按照指定位置解析binlog日志（精确），不指定--stop-positiion则一直到该日志结尾
  • --start-datetime、--stop-datetime：按照指定时间解析binlog日志（不准确），不指定--stop-datetime则一直到该日志结尾
  • -D --disable-log-bin：禁止恢复过程产生日志。指定-D时使用mysqlbinlog解析binlog时，会加上sql_log_bin=0，应用日志不会再记录二进制日志
  • --read-from-remote-server：解析远端服务器binlog
  • --raw  和--read-from-remote-server一起使用，输出原始的二进制日志格式，而不是SQL格式

2. 使用示例

  • 按时间解析
mysqlbinlog  --base64-output=decode-rows -v  --start-datetime='2018-03-18 15:46:30'  --stop-datetime='2018-03-18 15:47:40'  mysql-bin.000001 > /tmp/binlog.sql
  • 按pos解析
mysqlbinlog   --base64-output=decode-rows -v  --start-position=100049070 –-stop-position=100049079 mysql-bin.000001  > /tmp/tmp.sql
  • 远程获取binlog（阿里云和AWS RDS 都适用）
mysqlbinlog --read-from-remote-server --host='vg-*******.us-east-1.rds.amazonaws.com' --port=3306 --user root --password --raw --result-file=/tmp  mysql-bin-changelog.147158
  • 解析出来的日志是二进制的，需要再解析一下
mysqlbinlog  --base64-output=decode-rows -v -v  /tmp/mysql-bin-changelog.147158 > /tmp/mysql-bin.147158
  • 根据二进制日志恢复
mysqlbinlog mysql-bin.000009 --start-position 154 --stop-position 755 | mysql -uroot -p mytest
# 或者
mysqlbinlog --start-position=4 --stop-position=610 /var/lib/mysql/master-log.000042  > master-log.sql && source master-log.sql

 

binlog与数据恢复

0. 前提条件

基于binlog的恢复需要建立在全量备份恢复基础上（DB至少需要做过一次全备，建议安装完后先做一次）

1. 重做指定binlog内数据
mysqlbinlog binlog_files | mysql -u root -p

多个binlog还原最好使用一个连接完成，使用不同连接有时会导致不安全

例如：
[root@localhost /]# mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
[root@localhost /]# mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!
如果第一个日志包含创建临时表语句CREATE TEMPORARY TABLE，第二个日志要使用该临时表，第一个导入binlog日志的进程退出后临时表会被删除，执行第二个日志文件要使用临时表时会因找不到而报 “unknown table.”

建议的方法

方法1：
所有二进制文件放在单个连接里
[root@localhost /]# mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

方法2：
将所有二进制文件写在一个文件里执行
[root@localhost /]# mysqlbinlog binlog.000001 > ?/tmp/statements.sql
[root@localhost /]# mysqlbinlog binlog.000002 >> /tmp/statements.sql
[root@localhost /]# mysql -u root -p -e "source /tmp/statements.sql"

方法2需要过滤掉二进制文件里包含的GTID信息
[root@localhost /]# mysqlbinlog --skip-gtids binlog.000001 > ?/tmp/dump.sql
[root@localhost /]# mysqlbinlog --skip-gtids binlog.000002 >> /tmp/dump.sql
[root@localhost /]# mysql -u root -p -e "source /tmp/dump.sql"

 

2. 基于时间点的恢复
可以通过参数--start-datetime 和 --stop-datetime指定恢复binlog的起止时间点，时间使用DATETIME格式。


比如在时间点2005-04-20 10:00:00删除掉一个库，要恢复该时间点前的所有日志

mysqlbinlog --stop-datetime="2005-04-20 9:59:59" /usr/local/mysql/data/binlog.123456 | mysql -u root -p

也几个小时后才发现该错误，后面又有一系列的增删查改等操作，还需要恢复后续的binlog

可以指定起始时间

mysqlbinlog --start-datetime="2005-04-20 10:01:00" /usr/local/mysql/data/binlog.123456 | mysql -u root -p

如果不知道误操作的具体时间点，可以通过查看binlog内容定位

mysqlbinlog /usr/local/mysql/data/binlog.123456 > /tmp/mysql_restore.sql

3. 基于位置的恢复
可以通过参数--start-position 和 --stop-position指定恢复binlog的起止位置点

通过位置的恢复需要我们有更加精细的操作，例如在某个时间点我们执行了错误的语句，且这个时间点前后都有大并发操作，要确定破坏性sql的时间点，可以先导出大致的时间段的日志到文件以缩小查找范围，再去分析和确定

mysqlbinlog --start-datetime="2005-04-20 9:55:00" ?--stop-datetime="2005-04-20 10:05:00" /usr/local/mysql/data/binlog.123456 > /tmp/mysql_restore.sql

确定好需要跳过的位置之后，就可以进行恢复了

mysqlbinlog --stop-position=368312 /usr/local/mysql/data/binlog.123456 | mysql -u root -p
mysqlbinlog --start-position=368315 /usr/local/mysql/data/binlog.123456 | mysql -u root -p

注：mysqlbinlog工具的输出会在每条sql语句前增加 SET TIMESTAMP语句，恢复的数据及mysql日志反映当前时间。

 

参考

https://blog.csdn.net/huangliang0703/article/details/80923916

https://blog.csdn.net/JesseYoung/article/details/41211841

Hehuyi_In
关注
专栏目录
MySQL Binlog Digger 4.28 + Mysql Binlog分析 + 数据库
08-19
MySQL Binlog Digger是一款免费的，且基于图形界面的binlog挖掘分析工具与sql审计工具。当发生误删、误增、误改时，它可以帮助我们从binlog中快速定位到误操作的重做语句(redo sql)，同时推理出回滚语句(undo sql)。...
mysql-mysqlbinlog的使用详解
热门推荐
line_on_database的博客
04-17 1万+
一、背景 最近总在学习新知识，担心老的知识不用会忘，所以梳理一下，新欢旧爱都要宠着 二、介绍 1.参数简介 Option Name Description Introduced Deprecated --base64-output Print binary log entries using base-64 encoding --bind-address Use specified network interface to connec...
mysql binlog 日志详解
zll4859291的博客
03-20 3915
在下面你将看到 mysqlbinlog --stop-datetime="2019-04-20 9:59:59″ /u01/mysql3308/binlog/mysql-bin.000001 | mysql -u root -pmypwd 类似的语句，这是把mysql-bin.000001这个二进制文件里的内容转换成ASCII文件(也就是sql语句)，直接通过管道操作符”|”传输给 mysql这个程序，然后过滤掉其它数据库的语句，只在db_test里执行。在该行中，从上午10:01登录的SQL语句将运行。
Mysql读取binlog并分析 binlog
a80C51的博客
09-01 1783
配置文件中增加。
mysql 的binlog日志查看
学海无涯,我用JAVA
01-20 1253
mysqlbinlog.exe --no-defaults --base64-output=decode-rows -v D:\Users\sunhailong\log\mysql-bin.009788 > D:\Users\sunhailong\log\text1.txt 打开mysql服务的bin文件夹 然后执行上述命令 第一部分固定的 mysqlbinlog.exe --no-defa...
mysql bin日志转sql文件方法
zpf_07的博客
03-01 4454
用mysqlbinlog命令去执行。
MySQL Binlog Digger 4.8.0
12-14
MySQL Binlog Digger 4.8.0 是一个专为MySQL数据库设计的强大的日志挖掘和分析工具，尤其适用于数据恢复场景。它采用图形用户界面，使得操作更加直观易用。该工具支持在线和离线两种模式的binlog分析，能够帮助用户...
mysql binlog日志恢复【亲测有效，有例子实测】
03-22
MySQL的二进制日志（binlog）是数据库...在实际操作中，应根据具体业务需求调整binlog的相关配置，例如binlog_format、expire_logs_days和max_binlog_size等。同时，定期备份binlog文件也是防止数据丢失的重要手段。
Mysql binlog查看工具
07-29
MySQL的二进制日志（Binary Log，简称binlog）是数据库系统中非常重要的一个功能，主要用于数据恢复、数据同步以及审计。它记录了所有改变数据库状态的事务，包括INSERT、UPDATE、DELETE等操作，为数据库提供了事务...
MySQL Binlog Digger 4.10
02-14
MySQL Binlog Digger基于图形界面，免安装的日志分析工具，能对在线binlog与离线binlog进行分析，在选定在线binlog或离线binlog日志后，可对数据库、表、binlog开始时间、binlog结束时间、误操作的重做类型进行信息...
技术分享 | mysqlbinlog 技巧
ActionTech的博客
12-27 1358
作者：胡呈清 爱可生 DBA 团队成员，擅长故障分析、性能优化，个人博客：https://www.jianshu.com/u/a95ec11f67a8，欢迎讨论。 本文来源：原创投稿 *爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。 常用命令 1. 解析 binlog 排查问题 如果只是解析出来查看，可以加 --base64-output=decode-rows 不显示行格式的内容：mysqlbinlog --no-defaults -vv --base64-output.
2020-09-21
eagle89的专栏
09-21 356
MySQL 误操作后数据恢复（update,delete忘加where条件） 在数据库日常维护中，开发人员是最让人头痛的，很多时候都会由于SQL语句写的有问题导致服务器出问题，导致资源耗尽。最危险的操作就是在做DML操作的时候忘加where条件，导致全表更新，这是作为运维或者DBA的我们改如何处理呢？下面我分别针对update和delete操作忘加where条件导致全表更新的处理方法。 一. update 忘加where条件误操作恢复数据(binglog格式必须是ROW) 1.创建测试用的数...
Mysql使用binlog日志恢复数据
漠效的博客
08-31 1450
Mysql的binlog日志,以事件的形式记录了Mysql执行的除去 数据查询语句之外所有的DDL和DML语句，可以通过mysqlbinlog工具进行查看。模式特点优点缺点STATEMENT模式(默认)基于SQL语句的复制(statement-based replication, SBR),仅记录每一条会修改数据的sql语句binlog日志相对较小,减少了磁盘IO,提高了性能。
mysql如何利用binlog进行数据恢复详解
wangxuelei036的博客
04-29 1万+
mysqlbinlog 官方操作文档：https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html 前言 MySQL Binary Log也就是常说的bin-log, ,是mysql执行改动产生的二进制日志文件,其主要作用有两个: * 数据回复 * 主从数据库。用于slave端执行增删改，保持与master同步 如果线上误操作了一个数据...
mysqlbinlog -v -vv --base64-output参数的区别
chenliang
05-21 3797
文章目录1.1 涉及binlog的知识点带入1.2 -v -vv及--base64-output之间的区别1.3 测试环境说明1.4 测试数据准备1.4 找出insert语句的post点1.5 不用-v -vv --base64-output参数1.5 用-v参数来看1.6 用-vv参数来看1.7 用--base-64-output参数来看1.8 用-vv配合--base64-output来看 1.1 涉及binlog的知识点带入 01：binlog会记录DDL、DCL、DML(除select)类的
mysqlbinlog命令介绍（远程拉取binlog日志）
lgq2016的博客
07-20 3054
远程拉取binlog日志
MYSQL使用binlog日志恢复数据
好吃的油炸代码
08-23 4844
注意事项：本教程目的在于，你的MYSQL采用了binlog日志，且产生了binlog日志文件，使用日志文件恢复数据。
MysqlBinLog学习记录
最新发布
qq_38656758的博客
11-16 83
其中 --base64-output=decode-rows 是 指定按 588 和857 节点获取数据。每次操作保存受影响的行，以及怎么变化。但是会产生大量的日志，比较浪费存储空间。如果是OFF,则在my.conf文件的mysqld模块下添加一下参数。，但是如果语句中有随机数，会造成主从同步数据不一致。然后找到对应的事务的begin 和commit；等，就保存sql语句，否则保存为row格式。-vv 是更具体的解析成能明白的数据。查看和修改binlog文件格式。statement格式。
MySQL日志之binlog简介
lkforce
09-11 1245
MySQL中的日志类型： 1，Error Log，错误日志。 2，General Query Log，查询日志，记录了每条sql的信息，包括查询和修改等，对性能影响比较大。 3，Slow Query Log，慢查询日志，记录查询比较慢的sql。可以设置阈值。 4，Binary Log，也就是binlog，二进制日志，归档日志，记录了数据库改动。包括建表，删表，数据修改等。 5，Redo Log，重做日志。用于恢复提交后的物理数据页。 6，Undo Log，回滚日志。用于回滚行记录到...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Hehuyi_In

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值