mysql binlog 日志详解及恢复

这篇具有很好参考价值的文章主要介绍了mysql binlog 日志详解及恢复。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一、binlog概述

binlog是Mysql sever层维护的一种二进制日志,与innodb引擎中的redo/undolog是完全不同的日志;

其主要是用来记录对mysql数据更新或潜在发生更新的SQL语句,并以"事务"的形式保存在磁盘中;

作用主要有:

复制:MySQL Replication在Master端开启binlog,Master把它的二进制日志传递给slaves并回放来达到master-slave数据一致的目的

数据恢复:通过mysqlbinlog工具恢复数据

增量备份:

二、开启binlog日志:

  vi编辑打开mysql配置文件

  # vi /etc/my.cnf

  在[mysqld] 区块

  设置/添加 log-bin=mysql-bin  确认是打开状态(值 mysql-bin 是日志的基本 名或前缀名);

  重启mysqld服务使配置生效

  #pkill mysqld

  #mysqld_safe --defaults-file=/etc/my.cnf &

  mysql> show variables like 'log_bin%';

  +---------------------------------+---------------------------------------+

  | Variable_name                   | Value                                 |

  +---------------------------------+---------------------------------------+

  | log_bin                         | ON                                    |

  | log_bin_basename                | /u01/mysql3308/binlog/mysql-bin       |

  | log_bin_index                   | /u01/mysql3308/binlog/mysql-bin.index |

  | log_bin_trust_function_creators | OFF                                   |

  | log_bin_use_v1_row_events       | OFF                                   |

  +---------------------------------+---------------------------------------+

三、常用binlog日志操作命令

   1.查看所有binlog日志列表

     mysql> show master logs;

   2.查看master状态,即最后(最新)一个binlog日志的编号名称,及其最后一个操作事件pos结束点(Position)值

     mysql> show master status;

   3.刷新log日志,自此刻开始产生一个新编号的binlog日志文件

     mysql> flush logs;

     注:每当mysqld服务重启时,会自动执行此命令,刷新binlog日志;在mysqldump备份数据时加 -F 选项也会刷新binlog日志;

   4.删除二进制日志

     a.mysql> reset master;  重置(清空)所有binlog日志

     b.purge master logs to 'mysql-bin.000006'; (删除mysql-bin.000006之前的二进制日志文件)

     c.purge master logs before '2019-08-10 04:07:00'(删除该日期之前的日志)

     d.在my.cnf 配置文件中[mysqld]中添加:

       expire_logs_day=3设置日志的过期天数,过了指定的天数,会自动删除

四、mysqlbinlog工具介绍

1、mybinlog常用参数介绍

  mysqlbinlog  

   --base64-output  控制解析日志文件编码输出参数如下:

     auto:默认常规输出

     never:不处理row格式日志

     decode-rows:解码处理,通常与-V 一起使用

  -v 重组伪SQL语句输出,指定两次-v,输出信息中还包括列的数据类型信息。

  -d 只处理与指定数据库相关的日志

  --start-datetime  指定分析起始的时间点

  --stop-datetime 指定分析结束的时间点  (可以做精确的时间点恢复)

  -j,--start-position:指定分析起始事件位置(#at 后面的值)

  --stopt-position:指定分析结束事件位置 (可以做基于位置点恢复)

  对于 mysqlbinlog的详细信息,参见mysql手册8.6节,“mysqlbinlog:用于处理二进制日志文件的实用工具”。

  要想从二进制日志恢复数据,你需要知道当前二进制日志文件的路径和文件名。一般可以从选项文 件(即my.cnf or my.ini,取决于你的系统)中找到路径。

  如果未包含在选项文件中,当服务器启动时,可以在命令行中以选项的形式给出。启用二进制日志的选项为--log-bin。

  要想确定当前的二进制日志文件的文件名,输入下面的MySQL语句:

  SHOW BINLOG EVENTS G (查看初始的二进制文件信息)

  你还可以从命令行输入下面的内容:

  mysql --user=root -pmy_pwd -e 'SHOW BINLOG EVENTS G',将密码my_pwd替换为服务器的root密码。

  指定恢复时间

  mysqlbinlog语句中通过--start-datetime和--stop-datetime选项指定DATETIME格式的起止时间

  举例说明,假设在今天上午10:00(今天是2019年4月20日),执行SQL语句来删除一个大表。要想恢复表和数据,你可以恢复前晚上的备份,并输入:

  mysqlbinlog --stop-datetime="2019-04-20 9:59:59″ /u01/mysql3308/binlog/mysql-bin.000001 | mysql -u root -pmypwd

  该命令将恢复截止到在--stop-datetime选项中以DATETIME格式给出的日期和时间的所有数据。如果你没有检测到几个小时后输入的错误的SQL语句,

  可能你想要恢复后面发生的活动。根据这些,你可以用起使日期和时间再次运行mysqlbinlog:

  mysqlbinlog –start-datetime=”2019-04-20 10:01:00″ /u01/mysql3308/binlog/mysql-bin.000001 | mysql -u root -pmypwd

  在该行中,从上午10:01登录的SQL语句将运行。组合执行前夜的转储文件和mysqlbinlog的两行可以将所有数据恢复到上午10:00前一秒钟。

  指定恢复位置

  也可以不指定日期和时间,而使用mysqlbinlog的选项–start-position和–stop-position来指定日志位置。它们的作用与起止日选项相同,不同的是给出了从日志起的位置号。

  使用日志位置是更准确的 恢复方法,特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号,可以运行mysqlbinlog寻找执行了不期望的事务的时间范围,

  但应将结果重新指向文本文件以便进行检查。操作方法为:

  mysqlbinlog --start-datetime="2019-04-20 9:55:00″ --stop-datetime="2005-04-20 10:05:00″/u01/mysql3308/binlog/mysql-bin.000001 > /tmp/mysql_restore.sql

  该命令将在/tmp目录创建小的文本文件,将显示执行了错误的SQL语句时的SQL语句。你可以用文本编辑器打开该文件,寻找你不要想重复的语句。

  如果二进制日志中的位置号用于停止和继续恢复操作,应进行注释。用log_pos加一个数字来标记位置。使用位置号恢复了以前的备份文件后,你应从命令行输入下面内容:

  mysqlbinlog –stop-position="368312″ /u01/mysql3308/binlog/mysql-bin.000001 | mysql -u root -pmypwd

  mysqlbinlog –start-position="368315″/u01/mysql3308/binlog/mysql-bin.000001 | mysql -u root -pmypwd

  上面的第1行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。

  因为mysqlbinlog的输出包括每个SQL语句记录之前的SET TIMESTAMP语句,恢复的数据和相关MySQL日志将反应事务执行的原时间

2、详细信息输出用例

  mysqlbinlog --base64-output=decode-rows -v -v mysql-bin.000009

  查看binlog文件中的内容(--no--defaults 参数是为解决my.cnf 无法识别default-character-set=utf8mb4)

  mysqlbinlog --no-defaults mysql-bin.000009  

  分析结果输出到sql文件

  mysqlbinlog  mysql-bin.000009  > /backup/binlog/inc_000009.sql

  mysqlbinlog --stop-datetime="2020-04-10 10:25:20" mysql-bin.000008

  从开头显示到20-10-26 10:25:20为止,此时间要在文件的损坏时间之前,提前1秒即可!

  mysqlbinlog --stop-datetime="2020-10-26 10:25:20" mysql-bin.000001 | mysql -uroot -p123  

  根据截至日期来恢复数据库

  mysqlbinlog --no-defaults --start-datetime="2020-04-07 16:50:01" --stop-datetime="2020-04-07 16:58:51"  mysql-bin.000002

  显示这个之间的日志

  mysqlbinlog --start-position=469 --stop-position=562 mysql-bin.000001 |mysql -uroot -p123

  根据位置点来恢复数据库,位置点建议取实际开始和结束的点而不是解析出来的位置点

3、mysqlbinlog 使用技巧

  技巧1 :

  在下面你将看到 mysqlbinlog --stop-datetime="2019-04-20 9:59:59″ /u01/mysql3308/binlog/mysql-bin.000001 | mysql -u root -pmypwd 类似的语句,

  但是它一次只能操作一个日志文件,如果你变通一下变成这样  

  mysqlbinlog --stop-date="2019-04-20 9:59:59″/u01/mysql3308/binlog/mysql-bin.0* | mysql -u root -pmypwd  

  那么它基本上就会表示出的所有的日志文件了,这样可解决你忘记在哪一个日志文件中的问题,当然你也可以用这种写法更完美,

  mysqlbinlog –stop-datetime="2019-04-20 9:59:59″/u01/mysql3308/binlog/mysql-bin.[0-9]* | mysql -u root -pmypwd  

  看到[0-9]这个东东了吧,它表示以数字开头的任何字符,方便吧!

  技巧2:

  你可以通过--one-database 参数选择性的恢复单个数据库,example在下面,爽吧。

  mysqlbinlog --stop-datetime="2019-04-20 9:59:59″ /u01/mysql3308/binlog/mysql-bin.000001 | mysql -u root -pmypwd --one-database db_test

  技巧3:

  mysqlbinlog --start-datetimetime="2019-04-20 9:55:00″/u01/mysql3308/binlog/mysql-bin.0 > /home/db/tt.sql  

  类似的语句将日志导成了ASCII文本文件,那么你就可以直接在phpmyadmin或者其它什么乱七糟八的的客户端里执行这个文件文件就行了[source *.sql],

  因为它本身就是一个标准的sql文件,比如想让文件里面的某些语句不执行,OK,it’s easy,找到它们删除即可,然后再放进去执行就OK滴啦!这个可是灰常灰常的爽哟。。。。。。

  技巧4:

  我来给大家讲一下,下面这条语句都做了什么

  mysqlbinlog --stop-datetime="2019-04-20 9:59:59″ /u01/mysql3308/binlog/mysql-bin.000001 | mysql -u root -pmypwd --one-database db_test

  这是把mysql-bin.000001这个二进制文件里的内容转换成ASCII文件(也就是sql语句),直接通过管道操作符”|”传输给 mysql这个程序,然后过滤掉其它数据库的语句,只在db_test里执行。

五、binlog 恢复案例

五、binlog 恢复案例

  1、库被删除

   drop database test;

   恢复方式:

   获取数据库全备份--恢复备份--使用二进制日志恢复

  1.1、恢复数据库全备份

   myloader -u root -p root -S /u01/mysql3308/data/mysql.sock -B test -o -d /u01/mysql3308/backup/mysqldumper_test

  1.2、获取删除数据库位置点

   mysqlbinlog --base64-output=decode-rows -v -v -d test /u01/mysql3308/binlog/mysql-bin.00000* | grep -i 'DROP DATABASE test' -A3 -B4

   ---------------

  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

  # at 14134

  #230208 10:45:58 server id 1  end_log_pos 14238 CRC32 0xad4dd6bf        Query   thread_id=27    exec_time=0     error_code=0       Xid = 767

  SET TIMESTAMP=1675824358/*!*/;

  drop database test

  /*!*/;

  SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

  DELIMITER ;

  --------------

  或者查看最近的binlog

  show binlog events in '/u01/mysql3308/binlog/mysql-bin.000002';

  | mysql-bin.000002 | 13736 | Write_rows     |         1 |       13817 | table_id: 234 flags: STMT_END_F                                                                                                                                                                                                                                                                       |

  | mysql-bin.000002 | 13817 | Xid            |         1 |       13848 | COMMIT /* xid=764 */                                                                                                                                                                                                                                                                                  |

  | mysql-bin.000002 | 13848 | Anonymous_Gtid |         1 |       13925 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                                                                                  |

  | mysql-bin.000002 | 13925 | Query          |         1 |       14057 | use `test`; DROP TABLE IF EXISTS `t_dept` /* generated by server */                                                                                                                                                                                                                                   |

  | mysql-bin.000002 | 14057 | Anonymous_Gtid |         1 |       14134 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                                                                                  |

  | mysql-bin.000002 | 14134 | Query          |         1 |       14238 | drop database test /* xid=767 */  

  1.3、获取备份数据库位置点

  more metadata  

  SHOW MASTER STATUS:

         Log: mysql-bin.000002

         Pos: 11625

         GTID:

  1.4、执行备份点到删除点恢复

  mysqlbinlog --start-position=11625 --stop-position=14057 /u01/mysql3308/binlog/mysql-bin.000002| mysql -uroot -proot -S /u01/mysql3308/data/mysql.sock --one-database test

 2、表被删除

  drop table tt9;

  恢复方式:

  获取备份-恢复备份-使用二进制日志恢复

 2.1、恢复表结构(从全备份里获取)

  sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `tt9`/!d;q' test_full.sql > tt9.sql

 2.2、获取表的数据:

  grep -i 'INSERT INTO ​​tt9​​' test_full.sql >> tt9.sql

 2.3、恢复:

  mysql -usystem -p -S /usr/local/mysql/data/mysql.sock test < tt9.sql

  如果是mydumper备份

  直接执行恢复,/u01/mysql/backup 下只包括要恢复的tt9 表文件

  myloader -u root -p root  -B test -o -d /u01/mysql/backup

  确认对象已经恢复

 2.4、从binlog里恢复备份点到当前点变化的数据

  获取删除点pos

  mysqlbinlog   --no-defaults --base64-output=decode-rows -v -v  -d test  /usr/local/mysql/binlog/mysql-bin.00000*   | grep  -i 'DROP TABLE `tt9`'  -A3 -B4

  -----------------------------------------------------------------------------------

  #200729 17:17:08 server id 1  end_log_pos 11778 CRC32 0xff8f558f        Anonymous_GTID  last_committed=18       sequence_number=19rbr_only=yes

  --

  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

  # at 13819

  #200729 17:19:56 server id 1  end_log_pos 13935 CRC32 0xe89e1574        Query   thread_id=1449  exec_time=0     error_code=0

  SET TIMESTAMP=1596014396/*!*/;

  DROP TABLE `tt9` /* generated by server */

  /*!*/;

  # at 13935

  #200729 17:25:03 server id 1  end_log_pos 14000 CRC32 0xb30bcfa7        Anonymous_GTID  last_committed=26       sequence_number=27rbr_only=no

  SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

  # at 14000

  ------------------------------------------------------------------------------------

  获取到删除点pos: 13819

 2.5、获取备份点到删除点的binlog日志

  假如从上次备份,到发现表被删除,共有两个binlog文件,分别是mysql-bin.000003、mysql-bin.000004

  则按照binlog序号从小到大的排列,恢复的顺序应该是:(执行两个v 获取伪列信息)

  mysqlbinlog   --no-defaults  --base64-output=decode-rows -v -v -d test   /usr/local/mysql/binlog/mysql-bin.000003  > recover_tt9.sql

  mysqlbinlog   --no-defaults  --base64-output=decode-rows -v -v -d test  --stop-position=13819  /usr/local/mysql/binlog/mysql-bin.000004  >> recover_tt9.sql

  注意:MySQL中binlog参数:binlog_rows_query_log_events 开启后才能记录完整的sql语句,不然就是变量形式。

  由于恢复的文件recover_tt9.sql中包含了整个test数据库的所有表,我们只要恢复指定的表tt9,还要对恢复出来的sql进行过滤。

  cat recover_tt9.sql | grep  -A2 -B2 -i -E 'insert|update|delete|replace|alter' | grep -A2 -B2 tt9  > tt9.sql

  ####cat recover_tt9.sql | grep --ignore-case -E 'insert|update|select|delete' -A2 -B2 | grep tt9 >tt9.sql

  过滤后,更改注释及不要的,保存sql执行恢复。

  注意:获取实际执行的语句,伪列信息需要删除

 3、对象被更新

  获取更新点

  分析binlog中关于test表的所有DML 操作输出到文本文件

  mysqlbinlog   --no-defaults  --base64-output=decode-rows -v -v -d test  --start-position=13910  /usr/local/mysql/binlog/mysql-bin.000007  >> recover_tt9.sql

  截取tt9 表的操作

  cat recover_tt9.sql | grep  -A2 -B2 -i  'update' | grep -A2 -B2 tt9  > tt9.sql

  编辑文件,做反向操作,然后执行恢复

 4、对象新增加

  分析binlog中关于test表的所有DML 操作输出到文本文件(也可以只查询insert操作)

  mysqlbinlog   --no-defaults  --base64-output=decode-rows -v -v -d test  --start-position=13910  /usr/local/mysql/binlog/mysql-bin.000007  >> recover_tt9.sql

  截取tt9 表的操作

  cat recover_tt9.sql | grep  -A2 -B2 -i  'insert' | grep -A2 -B2 tt9  > tt9.sql

  编辑文件,做反向(delete)操作,然后执行恢复

 5、对象被删除

  分析binlog中关于test表的所有DML 操作输出到文本文件(也可以只查询insert操作)

  mysqlbinlog   --no-defaults  --base64-output=decode-rows -v -v -d test  --start-position=13910  /usr/local/mysql/binlog/mysql-bin.000007  >> recover_tt9.sql

  截取tt9 表的操作

  cat recover_tt9.sql | grep  -A2 -B2 -i  'delete' | grep -A2 -B2 tt9  > tt9.sql文章来源地址https://www.toymoban.com/news/detail-651386.html

到了这里,关于mysql binlog 日志详解及恢复的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • MySQL-备份+日志:介质故障与数据库恢复

    本关任务: 备份数据库,然后再恢复它。 为了完成本关任务,你需要掌握: 1.MySQL的恢复机制; 2.MySQL提供的备份与恢复工具。 和大多数DBMS一样,MySQL利用备份、日志文件实现恢复。 具体理论知识在此不详细介绍。 MySQL提供了以下工具: 逻辑备份工具:mysqldump 物理备份工具

    2024年02月05日
    浏览(81)
  • mysql-DBA(1)-数据库备份恢复-导入导出-日志解释

    log: hdd data :ssd  ,备份和导出都慢,缓冲池有污染。 逻辑备份:把所有的命令转换成sql语句。 修改配置文件: -A 备份所有 -B 备份哪个数据库 --master-data=1 同步 内容: 备份参数: 1.备份成文件,里面就是sql语句 2.routine: 3.trigger 触发器 4.event: 定时任务 5.-B 数据库 1.有-B 表

    2024年03月09日
    浏览(65)
  • MySQL 开启配置binlog以及通过binlog恢复数据

       binlog是MySQL sever层维护的一种二进制日志,binlog是记录所有数据库表结构变更(例如CREATE、ALTER TABLE、DROP等)以及表数据修改(INSERT、UPDATE、DELETE、TRUNCATE等)的二进制日志。不会记录SELECT和SHOW这类操作,因为这类操作对数据本身并没有修改。 作用主要有: 主从复制:在

    2024年02月03日
    浏览(55)
  • MySQL——通过binlog恢复数据

    目录 1.binlog基本概念 2.MySQL开启binlog 3.使用binlog日志恢复数据 3.1.恢复前准备工作 3.2.数据恢复 3.2.1.通过mysqlbinlog将binlog转为sql,以方便查询具体位置 3.2.2.查看生成的backuptmp.sql,最终确定需要恢复的起始位置为219,结束位置为982 3.2.3.通过mysqlbinlog执行恢复操作 前言 : mysqldu

    2024年02月06日
    浏览(50)
  • MySQL三大日志——binlog、redoLog、undoLog详解

    日志是mysql数据库的重要组成部分,记录着数据库运行期间各种状态信息,能帮助我们进行很多容错及分析工作,其中有三大日志与我们这些开发者息息相关,本文将介绍binlog、redoLog、undoLog三种日志: 我们都知道,事务的四大特性里面有一个是持久性,具体来说就是只要事

    2024年02月02日
    浏览(37)
  • MySQL如何恢复不小心误删的数据记录(binlog)

    题主于今天(2022年11月27日) 在线上环境误操作删除了记录,且没有备份数据,通宵排查事故原因,终于没有酿成生产事故。谨以此文记录。 https://blog.csdn.net/qq_23543983/article/details/127298578 本文是对上文操作的实际补充说明。 首先确保你binlog日志是打开的。一般线上环境都会

    2024年02月07日
    浏览(67)
  • mysql关闭binlog日志,删除binlog数据(win和linux通用)

    打开 mysql 命令窗口,查询 binlog 是否开启   (ON)为开启状态 (OFF)为关闭状态 若开启状态则需要修改配置文件,反之不需要任何操作 在 C:ProgramDataMySQLMySQL Server 8.0 路径下打开 my.ini 并注释掉 bin-log 配置项然后在其后面加入skip-log-bin   重启mysql服务   打开 mysql 命令窗口,

    2024年02月07日
    浏览(38)
  • MySQL知识学习03(三大日志详解 binlog、redo log、undo log)

    前言 MySQL 日志 主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。其中,比较重要的还要属 二进制日志 binlog(归档日志) 和 事务日志 redo log(重做日志) 和 undo log(回滚日志) 。 1、redo log? redo log (重做日志)是 InnoDB 存储引擎独有的,它让

    2024年02月02日
    浏览(44)
  • mysql误删数据后,从binlog中进行恢复删除数据(拯救手残,不跑路)

    在一次数据维护过程中,对数据删除时没有提前备份数据,导致数据被删除后无法通过备份文件直接恢复。 数据如果在删除前提前备份好,那么直接从备份文件中恢复。 如果没有备份文件,则需要查看mysql数据库是否打开logbin日志。如果没有打开直接GG。如果恰好打开了的,

    2024年02月16日
    浏览(35)
  • Mysql 数据库开启 binlog

    在MySQL中,binlog指的是binary log,二进制日志文件。这个文件记录了MySQL所有的DML操作。通过binlog日志,我们可以做数据恢复,做主从复制等等。对于运维或架构人员来说,开启binlog日志功能非常重要。 (如何开启MySQL的binlog日志呢?下面将介绍两种方法) 2.1 方法一:在my.cn

    2024年02月13日
    浏览(42)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包