利用binlog来恢复数据库
1.根据binlog解析出所有ring数据库的所有sql
[mysql@DEV_SNS1 log]$mysqlbinlog --no-defaults --database=ring --start-datetime="2005-04-20 9:55:00" --stop-datetim="2009-04-08 08:05:00" /u01/mysql/log/mysql-bin.000005 > /u01/mysql/log/mysql_restore5.sql
-rw-r--r-- 1 mysql dba 407 Apr 8 15:33 /u01/mysql/log/mysql_restore5.sql
--start-datetime=datetime 从哪个点开始解析
--stop-datetim=datetime 从哪个点停止解析
--database=ring 指定需要解析哪个数据库,只提取某个数据库的sql语句
2.重新在数据库中执行
[mysql@DEV_SNS1 log]$mysql -u root ERROR 1062 (23000) at line 2559580: Duplicate entry \'175754263-140\' for key 1 --恢复过程中出现主键冲突,导入失败
Welcome to the MySQL monitor. Commands end with ; or \\g.
Your MySQL connection id is 7647
Server version: 5.0.67-log Source distribution
Query OK, 8 rows affected (0.04 sec)
ERROR 1062 (23000) at line 2559580: Duplicate entry \'175754263-140\' for key 1
ERROR 1062 (23000) at line 2564671: Duplicate entry \'138\' for key 1
ERROR 1062 (23000) at line 2566216: Duplicate entry \'139\' for key 1
ERROR 1062 (23000) at line 2566224: Duplicate entry \'140\' for key 1
ERROR 1062 (23000) at line 2566232: Duplicate entry \'141\' for key 1
ERROR 1062 (23000) at line 2566240: Duplicate entry \'142\' for key 1
ERROR 1062 (23000) at line 2648410: Duplicate entry \'143\' for key 1
ERROR 1062 (23000) at line 2648418: Duplicate entry \'144\' for key 1
ERROR 1062 (23000) at line 2648581: Duplicate entry \'145\' for key 1
ERROR 1062 (23000) at line 2648589: Duplicate entry \'146\' for key 1
ERROR 1062 (23000) at line 2648597: Duplicate entry \'147\' for key 1
ERROR 1062 (23000) at line 2648605: Duplicate entry \'148\' for key 1
ERROR 1062 (23000) at line 2649279: Duplicate entry \'149\' for key 1
ERROR 1062 (23000) at line 2649287: Duplicate entry \'150\' for key 1
ERROR 1062 (23000) at line 2649295: Duplicate entry \'151\' for key 1
ERROR 1062 (23000) at line 2649303: Duplicate entry \'152\' for key 1
ERROR 1062 (23000) at line 2649311: Duplicate entry \'153\' for key 1
ERROR 1062 (23000) at line 2649319: Duplicate entry \'154\' for key 1
ERROR 1062 (23000) at line 2649327: Duplicate entry \'155\' for key 1
ERROR 1062 (23000) at line 2649335: Duplicate entry \'156\' for key 1
建议继续学习:
- 这样恢复 Linux 分区下误删的文件 (阅读:4103)
- 恢复删除的数据表,数据库 (阅读:3874)
- Oracle数据恢复专题 (阅读:3840)
- Mysql .frm损坏后如何恢复 (阅读:3492)
- Oracle数据恢复 - Linux / Unix 误删除的文件恢复 (阅读:3445)
- MySQL数据库InnoDB数据恢复工具使用总结 (阅读:3199)
- 说说使用mysqlbinlog按时间查询二进制日志时容易疏忽的地方 (阅读:2959)
- MySQL在切换binlog时会阻塞更新 (阅读:2946)
- linux上ext2文件系统中,用debugfs来恢复被删除的文件 (阅读:2892)
- 实例:Linux EXT3文件系统下成功恢复误删的文件 (阅读:2722)
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:Incessant 来源: Incessant
- 标签: binlog 数据恢复
- 发布时间:2009-10-11 22:39:20
- [66] Oracle MTS模式下 进程地址与会话信
- [64] Go Reflect 性能
- [64] 如何拿下简短的域名
- [58] 【社会化设计】自我(self)部分――欢迎区
- [58] 图书馆的世界纪录
- [58] android 开发入门
- [57] IOS安全–浅谈关于IOS加固的几种方法
- [53] 视觉调整-设计师 vs. 逻辑
- [47] 界面设计速成
- [46] 读书笔记-壹百度:百度十年千倍的29条法则