详解使用MySQL的binlog(二进制日志)恢复数据的教程

SQL herman 932浏览 0评论
公告:“业余草”微信公众号提供免费CSDN下载服务(只下Java资源),关注业余草微信公众号,添加作者微信:xttblog,发送下载链接帮助你免费下载!
本博客日IP超过1800,PV 2600 左右,急需赞助商。
极客时间所有课程通过我的二维码购买后返现24元微信红包,请加博主新的微信号:xttblog,之前的微信号好友位已满,备注:返现
所有面试题(java、前端、数据库、springboot等)一网打尽,请关注文末小程序
视频教程免费领

连续3篇讲解binlog。恢复是binlog的两大主要作用之一,接下来通过实例演示如何利用binlog恢复数据:

首先,看下当前binlog的位置:

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 |     1847 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

接着想测试表tb_person中插入两条记录:

insert into tb_person  set name="person_1", address="beijing", sex="man", other="test-1";
insert into tb_person  set name="person_2", address="beijing", sex="man", other="test-2";

记录当前binlog位置:

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 |     2585 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

查询数据:

mysql> select *  from tb_person where name ="person_2" or name="person_1";
+----+----------+---------+-----+--------+
| id | name     | address | sex | other  |
+----+----------+---------+-----+--------+
|  6 | person_1 | beijing | man | test-1 |
|  7 | person_2 | beijing | man | test-2 |
+----+----------+---------+-----+--------+

删除一条数据。

mysql> select *  from tb_person where name ="person_2" or name="person_1";
+----+----------+---------+-----+--------+
| id | name     | address | sex | other  |
+----+----------+---------+-----+--------+
|  6 | person_1 | beijing | man | test-1 |
+----+----------+---------+-----+--------+

binlog恢复(指定pos点恢复/部分恢复)

mysqlbinlog   --start-position=1847  --stop-position=2585  mysql-bin.000008  > test.sql
mysql> source /var/lib/mysql/3306/test.sql

数据恢复完成后查询验证一下。

mysql> select *  from tb_person where name ="person_2" or name="person_1";
+----+----------+---------+-----+--------+
| id | name     | address | sex | other  |
+----+----------+---------+-----+--------+
|  6 | person_1 | beijing | man | test-1 |
|  7 | person_2 | beijing | man | test-2 |
+----+----------+---------+-----+--------+

binlog恢复数据,就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已。

业余草公众号

最后,欢迎关注我的个人微信公众号:业余草(yyucao)!可加作者微信号1:xmtxtt(5000人已满),微信号2:codedq(5000人已满),微信号3:xttblog(超2800)。备注:“xttblog”,添加博主微信拉你进微信群。备注错误不会同意好友申请。再次感谢您的关注!后续有精彩内容会第一时间发给您!原创文章投稿请发送至532009913@qq.com邮箱。商务合作也可添加作者微信进行联系!

本文原文出处:业余草: » 详解使用MySQL的binlog(二进制日志)恢复数据的教程