mysql 利用binlog增量备份，还原实例

最新推荐文章于 2023-05-09 19:24:52 发布
最新推荐文章于 2023-05-09 19:24:52 发布
阅读量660 收藏
点赞数
文章标签： mysql server 数据库 thread insert query
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/bjtang/article/details/6565563
版权

mysql 利用binlog增量备份，还原实例

张映 发表于 2010-09-29

分类目录： mysql

一，什么是增量备份

增量备份，就是将新增加的数据进行备份。假如你一个数据库，有10G的数据，每天会增加10M的数据，数据库每天都要备份一次，这么多数据是不是都要备份呢？还是只要备份增加的数据呢，很显然，我只要备份增加的数据。这样减少服务器的负担。


二，启用binlog

vi my.cnf

log-bin=/var/lib/mysql/mysql-bin.log，如果是这样的话log-bin=mysql-bin.log默认在datadir目录下面

[root@BlackGhost mysql]# ls |grep mysql-bin
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005
mysql-bin.000006
mysql-bin.index

启动后会产生mysql-bin这样的文件，每启动一次，就会增加一个或者多个。

mysql-bin.000002这样文件存放的是数据库每天增加的数据，所有数据库的数据增量都在这里面。

三，查看mysql-bin.000002这样的文件里面到底是什么东西

[root@BlackGhost mysql]# mysqlbinlog   /var/lib/mysql/mysql-bin.000002 > /tmp/add.sql

查看 复制 打印 ?
  1. [root@BlackGhost mysql]# cat /tmp/add.sql   // 下面是根据mysql-bin生成的文件（部分内容）   
  2. /*!40019 SET @@session.max_insert_delayed_threads=0*/;   
  3. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;   
  4. DELIMITER /*!*/;   
  5. # at 4   
  6. #100929 21:23:52 server id 1  end_log_pos 106     Start: binlog v 4, server v 5.1.50-log created 100929 21:23:52 at startup   
  7. # Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.   
  8. ROLLBACK/*!*/;   
  9. BINLOG '  
  10. 6D2jTA8BAAAAZgAAAGoAAAABAAQANS4xLjUwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA  
  11. AAAAAAAAAAAAAAAAAADoPaNMEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC  
  12. '/*!*/;   
  13. # at 106   
  14. #100929 21:29:35 server id 1  end_log_pos 134     Intvar   
  15. SET INSERT_ID=16/*!*/;   
  16. # at 134   
  17. #100929 21:29:35 server id 1  end_log_pos 237     Query    thread_id=1    exec_time=0    error_code=0   
  18. use test/*!*/;           //这里是test数据库   
  19. SET TIMESTAMP=1285766975/*!*/;   
  20. SET @@session.pseudo_thread_id=1/*!*/;   
  21. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;   
  22. SET @@session.sql_mode=0/*!*/;   
  23. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;   
  24. /*!/C utf8 *//*!*/;   
  25. SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;   
  26. SET @@session.lc_time_names=0/*!*/;   
  27. SET @@session.collation_database=DEFAULT/*!*/;   
  28. insert into aa(name)values('cccccccccc')   
  29. /*!*/;   
  30. # at 237   
  31. #100929 21:32:21 server id 1  end_log_pos 265     Intvar   
  32. SET INSERT_ID=12/*!*/;   
  33. # at 265   
  34. #100929 21:32:21 server id 1  end_log_pos 370     Query    thread_id=1    exec_time=0    error_code=0   
  35. SET TIMESTAMP=1285767141/*!*/;   
  36. insert into user(name)values('cccccccccc')   
  37. /*!*/;   
  38. # at 370   
  39. #100929 21:35:25 server id 1  end_log_pos 440     Query    thread_id=1    exec_time=0    error_code=0   
  40. SET TIMESTAMP=1285767325/*!*/;   
  41. BEGIN   
  42. /*!*/;   
  43. # at 440   
  44. #100929 21:35:25 server id 1  end_log_pos 468     Intvar   
  45. SET INSERT_ID=45/*!*/;   
  46. # at 468   
  47. #100929 21:35:25 server id 1  end_log_pos 573     Query    thread_id=1    exec_time=0    error_code=0   
  48. use blog/*!*/;             //这里是blog数据库   
  49. SET TIMESTAMP=1285767325/*!*/;   
  50. insert into city(CityName)values('asdf')   
  51. /*!*/;   
  52. # at 573   
  53. #100929 21:35:25 server id 1  end_log_pos 600     Xid = 205   
  54. COMMIT/*!*/;   
  55. DELIMITER ;   
  56. # End of log file   
  57. ROLLBACK /* added by mysqlbinlog */;   
  58. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;  
[root@BlackGhost mysql]# cat /tmp/add.sql   // 下面是根据mysql-bin生成的文件（部分内容）
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#100929 21:23:52 server id 1  end_log_pos 106     Start: binlog v 4, server v 5.1.50-log created 100929 21:23:52 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ROLLBACK/*!*/;
BINLOG '
6D2jTA8BAAAAZgAAAGoAAAABAAQANS4xLjUwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADoPaNMEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#100929 21:29:35 server id 1  end_log_pos 134     Intvar
SET INSERT_ID=16/*!*/;
# at 134
#100929 21:29:35 server id 1  end_log_pos 237     Query    thread_id=1    exec_time=0    error_code=0
use test/*!*/;           //这里是test数据库
SET TIMESTAMP=1285766975/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!/C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
insert into aa(name)values('cccccccccc')
/*!*/;
# at 237
#100929 21:32:21 server id 1  end_log_pos 265     Intvar
SET INSERT_ID=12/*!*/;
# at 265
#100929 21:32:21 server id 1  end_log_pos 370     Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1285767141/*!*/;
insert into user(name)values('cccccccccc')
/*!*/;
# at 370
#100929 21:35:25 server id 1  end_log_pos 440     Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1285767325/*!*/;
BEGIN
/*!*/;
# at 440
#100929 21:35:25 server id 1  end_log_pos 468     Intvar
SET INSERT_ID=45/*!*/;
# at 468
#100929 21:35:25 server id 1  end_log_pos 573     Query    thread_id=1    exec_time=0    error_code=0
use blog/*!*/;             //这里是blog数据库
SET TIMESTAMP=1285767325/*!*/;
insert into city(CityName)values('asdf')
/*!*/;
# at 573
#100929 21:35:25 server id 1  end_log_pos 600     Xid = 205
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

下面还有一个重要索引文件就是mysql-bin.index

  1. [root@BlackGhost mysql]# cat mysql-bin.index   
  2. ./mysql-bin.000001   
  3. ./mysql-bin.000002   
  4. ./mysql-bin.000003   
  5. ./mysql-bin.000004   
  6. ./mysql-bin.000005   
  7. ./mysql-bin.000006  
[root@BlackGhost mysql]# cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006

四，增量备份和增量还原

1，增量备份

既然我们知道了，mysql里面新增加的数据在mysql-bin这样的文件里面，我们只要把mysql-bin这样的文件进行备份就可以了。

cp /var/lib/mysql/mysql-bin* /data/mysql_newbak/

2，增量还原，讲几个常用的，比较有用的

a），根据时间来还原 --start-date,--stop-date

[root@BlackGhost mysql]# /usr/local/mysql/bin/mysqlbinlog --start-date="2010-09-29 18:00:00" --stop-date="2010-09-29 23:00:00" /var/lib/mysql/mysql-bin.000002 |mysql -u root -p

根据条件看一下数据

查看 复制 打印 ?
  1. [root@BlackGhost mysql]# /usr/local/mysql/bin/mysqlbinlog  --start-date="2010-09-29 18:00:00"  
  2. --stop-date="2010-09-29 23:00:00"  /var/lib/mysql/mysql-bin.000002   
  3. //下面是部分内容，其实也就是一些对数据进行操作的sql语句   
  4. # at 237   
  5. #100929 21:32:21 server id 1  end_log_pos 265     Intvar   
  6. SET INSERT_ID=12/*!*/;   
  7. # at 265   
  8. #100929 21:32:21 server id 1  end_log_pos 370     Query    thread_id=1    exec_time=0    error_code=0   
  9. SET TIMESTAMP=1285767141/*!*/;   
  10. insert into user(name)values('cccccccccc')   
  11. /*!*/;   
  12. # at 370   
  13. #100929 21:35:25 server id 1  end_log_pos 440     Query    thread_id=1    exec_time=0    error_code=0   
  14. SET TIMESTAMP=1285767325/*!*/;   
  15. BEGIN   
  16. /*!*/;   
  17. # at 440   
  18. #100929 21:35:25 server id 1  end_log_pos 468     Intvar   
  19. SET INSERT_ID=45/*!*/;   
  20. # at 468   
  21. #100929 21:35:25 server id 1  end_log_pos 573     Query    thread_id=1    exec_time=0    error_code=0  
[root@BlackGhost mysql]# /usr/local/mysql/bin/mysqlbinlog  --start-date="2010-09-29 18:00:00"
--stop-date="2010-09-29 23:00:00"  /var/lib/mysql/mysql-bin.000002
//下面是部分内容，其实也就是一些对数据进行操作的sql语句
# at 237
#100929 21:32:21 server id 1  end_log_pos 265     Intvar
SET INSERT_ID=12/*!*/;
# at 265
#100929 21:32:21 server id 1  end_log_pos 370     Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1285767141/*!*/;
insert into user(name)values('cccccccccc')
/*!*/;
# at 370
#100929 21:35:25 server id 1  end_log_pos 440     Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1285767325/*!*/;
BEGIN
/*!*/;
# at 440
#100929 21:35:25 server id 1  end_log_pos 468     Intvar
SET INSERT_ID=45/*!*/;
# at 468
#100929 21:35:25 server id 1  end_log_pos 573     Query    thread_id=1    exec_time=0    error_code=0

b），根据起始位置来还原，--start-position,--stop-position

[root@BlackGhost mysql]# /usr/local/mysql/bin/mysqlbinlog --start-position=370 --stop-position=440  /var/lib/mysql/mysql-bin.000002 |mysql -u root -p

//查看插入的内容，根a）中是一样的

[root@BlackGhost mysql]# /usr/local/mysql/bin/mysqlbinlog --start-position=370 --stop-position=440  /var/lib/mysql/mysql-bin.000002

--start-position=370 --stop-position=440 这里面数字从哪儿来的呢？
# at 370
#100929 21:35:25 server id 1  end_log_pos 440 Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1285767325/*!*/;

上面的红色加粗的就是，一个是start-position，一个是stop-position

c），根据数据库名来进行还原 -d

在这里是小写的d，请不要把它和mysqldump中的－D搞混了。哈哈。

[root@BlackGhost mysql]# /usr/local/mysql/bin/mysqlbinlog -d test  /var/lib/mysql/mysql-bin.000002

查看内容，请参考a）

d），根据数据库所在IP来分－h

[root@BlackGhost mysql]# /usr/local/mysql/bin/mysqlbinlog -h 192.1681.102  /var/lib/mysql/mysql-bin.000002

查看内容，请参考a）

e），根据数据库所占用的端口来分－P

有的时候，我们的mysql用的不一定是3306端口，注意是大写的P

[root@BlackGhost mysql]# /usr/local/mysql/bin/mysqlbinlog -p 13306  /var/lib/mysql/mysql-bin.000002

查看内容，请参考a）

f），根据数据库serverid来还原--server-id

在数据库的配置文件中，都有一个serverid并且同一集群中serverid是不能相同的。

[root@BlackGhost mysql]# /usr/local/mysql/bin/mysqlbinlog --server-id=1  /var/lib/mysql/mysql-bin.000002

查看内容，请参考a）

注意：上面的几个例子，我都是一个一个说的，其实可以排列组合的。例如

[root@BlackGhost mysql]# /usr/local/mysql/bin/mysqlbinlog --start-position="2010-09-29 18:00:00" -d test -h 127.0.0.1 /var/lib/mysql/mysql-bin.000002 ｜mysql -u root -p

五，后续

增量备份的时候，有一点让人不爽，就是mysql-bin这样的文件，每启动一次mysql就会增加一些，如果你不去管他的话，时间长了，他会把你的磁盘占满。

./mysqldump --flush-logs -u root  myblog > /tmp/myblog.sql

备份myblog数据库，清除增量备份里面的有关myblog的数据

./mysqldump --flush-logs -u root  --all-databases > /tmp/alldatabase.sql

备份所有数据库，清除增量备份

mysql-bin.index的起索引作用，因为增量的数据不一定在一个mysql-bin000这样的文件里面，这个时候，我们就要根据mysql-bin.index来找mysql-bin这样的增量文件了。

如果mysql里面做了这样的配置binlog-do-db=test1，增量备份里面只会有test1这个数据库的数据

bjtang
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
binglog日志追踪：数据备份并备份追踪
Miracle_ze的博客
07-17 1516
binglog日志的备份与追踪
MySQL的全量备份和增量备份以及利用binlog日志实现恢复增量数据；
qq_44930876的博客
12-08 2943
MySQL的全量备份和增量备份以及利用binlog日志实现恢复增量数据;
binglog备份数据
C_LCH_2000的博客
07-19 528
在日常运维工作中，对于mysql数据库的备份是至关重要的！数据库对于网站的重要性使得我们对mysql数据的管理不容有失！ 然后，是人总难免会犯错误，说不定哪天大脑短路了来个误操作把数据库给删除了，怎么办？？？...
MySQL数据库使用binlog备份数据
u014158954的博客
10-20 747
navicat误删除数据恢复
mysql-bin文件删除与产生的原因
houxian1103的博客
05-12 7147
问题背景 随着 MySQL 数据库的使用，/usr/local/mysql/data目录下会生成很多mysql-bin.XXXXXX文件，会占用很多磁盘空间。 这些文件是用于记录 MySQL 数据库的操作行为，用于数据恢复和主从数据同步。 在机器的磁盘空间不足，而又不考虑 MySQL 历史数据恢复的情况下，是 处理方式 1、停止 MySQL 服务（service mysqld stop） 2、删除mysql-bin打头的文件（mysql-bin.index除外） 3、修改mysql-bin....
MySQL binlog 远程备份方法详解
09-10
以前备份binlog时，都是先在本地进行备份压缩，然后发送到远程服务器中，但是这种备份方式非常的不安全，下面通过本文给大家介绍MySQL binlog 远程备份的方法，需要的朋友一起学习吧
mysql全量备份、增量备份实现方法
01-19
mysql全量备份、增量备份。开启mysql的logbin日志功能。在/etc/my.cnf文件中加入以下代码： [mysqld] log-bin = /home/mysql/logbin.log binlog-format = ROW log-bin-index = /home/mysql/logindex binlog_cache_...
利用binlog进行数据库的还原
01-30
前言：在学习mysql备份的时候，深深的感受到mysql的备份还原功能没有oracle强大；比如一个很常见的恢复场景：基于时间点的恢复，oracle通过rman工具就能够很快的实现数据库的恢复，但是mysql在进行不完全恢复的时候...
MySQL Binlog Digger 4.28 + Mysql Binlog分析 + 数据库
08-19
MySQL Binlog Digger是一款免费的，且基于图形界面的binlog挖掘分析工具与sql审计工具。当发生误删、误增、误改时，它可以帮助我们从binlog中快速定位到误操作的重做语句(redo sql)，同时推理出回滚语句(undo sql)。...
MySQL利用binlog恢复库表
11-18
利用binlog恢复库表，要求如下： 1. 启用binlog日志 2. 创建db1库tb1表，插入3条记录 3. 删除tb1表中刚插入的3条记录 4. 使用mysqlbinlog恢复删除的3条记录
binlog实现增量备份
ikta的博客
07-17 631
记录对数据库做出修改的语句所影响到的数据行以及这些行的修改，比如，updateAsettest=’test’，如果使用row模式，那么这条update语句所影响到的行所对应的修改，将会记录到binlog中，比如，A表中有1000条数据，那么当执行这条update语句以后，由于1000条数据都会被修改，所以会有1000行数据被记录到二进制日志中，以及它们是怎样被修改的，使用row模式时，优点是能够完全的还原或者复制日志被记录时的操作，缺点是记录日志量较大，IO压力大，性能消耗较大。...
用binlog做mysql备份_用Mysqlbinlog备份BinLog文件
weixin_39761573的博客
01-20 254
默认情况下, mysqlbinlog读取二进制文件【BinLog】并以文本的方式呈现【text format】.mysqlbinlog可以直接地从本地读取Log，也可以读取远程的Log【--read-from-remote-server】,默认是直接输出，当然也可以保存到指定的输出文件中。如：1.显示有哪些Log文件：show binary logs;如：2.Load指定的文件，如 mysqlbi...
讲一讲 MySQL 数据备份杀手锏 binlog
IT学习小镇
01-06 367
我们都知道，数据非常重要网上也经常看到一些段子，某公司程序员对工作不满，删库跑路，老板损失惨重，欲哭无泪。那么有没有什么解决方案？即使数据库真的被删了，也有备份数据，能快速恢复。甚至可以做到实时热备，即使内部炸掉外部用户也感知不到，一片风平浪静。MySQL 作为当下流行数据库，在数据备份、高可用方面非常有竞争力，今天，我们就重点来讲下什么是 MySQL 主备情况一：客户端的业务操作，读、写访问的是...
MySQL 主从复制
qq_60271706的博客
04-22 145
主服务器主动通知从服务器来拿二进制日志。
MYSQL增量备份
hdxx2022的博客
01-16 218
这么做的目的是在备份时让 MySQL Server 进行 logrotate，这样子日后要辨别 "最后一次备份时间点" 之后的 Binary Log 会比较方便，因为若你没有主动(或通过设置)去删除 Binary Log，则只要你的硬盘空间够大，MySQL 会无限期的保存 Binary Log，也就是说你的 Binary Log 里面所记载的数据有可能包含 "最后一次备份时间点" 之前的数据。其中--stop-position和--start-position的值为文件中的end_log_pos。
MySQL数据库的备份与恢复
Yuzhang2046的博客
05-09 3892
完全备份是对整个数据库、数据库结构和文件结构的备份保存的是备份完成时刻的数据库是差异备份与增量备份的基础使用mysqldump进行完全备份存在的问题备份数据中有重复数据备份时间与恢复时间过长是自上一次备份后增加/变化的文件或者内容增量备份的特点没有重复数据，备份量不大，时间短恢复需要上次完全备份及完全备份之后所有的增量备份才 能恢复，而且要对所有增量备份进行逐个反推恢复。
手工删除mysql-bin.000002等文件导致mysql无法启动问题
qq591840685的专栏
11-28 2306
问题：由于磁盘满的原因，手工直接将mysql-bin.000002，mysql-bin.000003，mysql-bin.000005等这类文件删除了。启动mysql,报如下错误： Starting MySQL... ERROR! The server quit without updating PID file (/usr/local/mysql/var/iZuf6**************
mysqlbinlog增量备份还原简介
博客
11-27 356
使用二进制日志的实时(增量)恢复 使用事件时间的实时恢复 使用事件位置的实时恢复 时间点恢复是指自给定时间点以来所做的数据更改的恢复. 通常，这种类型的恢复是在还原到备份时使服务器处于完整备份之后的状态执行的。(完全备份可以通过多种方式进行)然后，实时恢复将服务器从完整备份的时间逐步更新到最近的时间。 实时恢复基于以下原则： 实时恢复的信息源是在完全备份操作之后生成的二进制日志文件表...
mysql如果进行增量备份及快速恢复
最新发布
05-31
MySQL进行增量备份的常用方法是使用binlog日志。binlog日志记录了MySQL中所有的数据修改操作，包括INSERT、UPDATE和DELETE等操作，可以通过将binlog日志备份到另一台服务器上，来实现增量备份。 具体的步骤如下： ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值