MySQL通过binlog日志恢复数据--误删除的表
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL通过binlog日志恢复数据--误删除的表,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4909字,纯文字阅读大概需要8分钟。
内容图文
![MySQL通过binlog日志恢复数据--误删除的表](/upload/InfoBanner/zyjiaocheng/860/12f96837d11a4c8d8b3e35a907a80f59.jpg)
本实验模拟生产环境误操作删除表,可利用逻辑备份和binlog恢复所删除的表。
生产库:192.168.8.31
临时库:192.168.8.32
一、数据备份
操作的前一天晚上进行了日常逻辑备份
1 | mysqldump?-uroot?-pmysql?-P3306?--all-databases?>? /mysql/backup/dump/alldb_bak .sql |
二、模拟事故
模拟事故发生前后的业务情况
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869 | mysql>?show?tables; + ----------------+ |?Tables_in_test?| + ----------------+ |?kk?????????????| |?t1?????????????| |?t2?????????????| |?t3?????????????| |?t4?????????????| |?t5?????????????| |?t6?????????????| |?t7?????????????| + ----------------+ 8? rows in set (0.00?sec) mysql>? desc t7; + -------+-------------+------+-----+---------+----------------+ |?Field?|?Type????????|? Null |? Key |? Default |?Extra??????????| + -------+-------------+------+-----+---------+----------------+ |?id????|? int (11)?????|? NO |?PRI?|? NULL |?auto_increment?| |? name |? varchar (30)?|?YES??|?????|? NULL |????????????????| + -------+-------------+------+-----+---------+----------------+ 2? rows in set (0.00?sec) mysql>? create table t8? as select *? from t7; Query?OK,?3? rows affected?(0.17?sec) Records:?3??Duplicates:?0??Warnings:?0 mysql>? select *? from t8; + ----+--------+ |?id?|? name | + ----+--------+ |??1?|?steven?| |??3?|?steven?| |??4?|?steven?| + ----+--------+ 3? rows in set (0.00?sec) mysql>? insert into t8? select *? from t7; Query?OK,?3? rows affected?(0.04?sec) Records:?3??Duplicates:?0??Warnings:?0 mysql>? select *? from t8; + ----+--------+ |?id?|? name | + ----+--------+ |??1?|?steven?| |??3?|?steven?| |??4?|?steven?| |??1?|?steven?| |??3?|?steven?| |??4?|?steven?| + ----+--------+ 6? rows in set (0.00?sec) mysql>? update t8? set id=2? where id=3; Query?OK,?2? rows affected?(0.33?sec) Rows matched:?2??Changed:?2??Warnings:?0 mysql>? update t8? set id=3? where id=4; Query?OK,?2? rows affected?(0.03?sec) Rows matched:?2??Changed:?2??Warnings:?0 mysql>? select *? from t8; + ----+--------+ |?id?|? name | + ----+--------+ |??1?|?steven?| |??2?|?steven?| |??3?|?steven?| |??1?|?steven?| |??2?|?steven?| |??3?|?steven?| + ----+--------+ 6? rows in set (0.00?sec) mysql>? drop table t8; Query?OK,?0? rows affected?(0.10?sec) |
三、查看当前binlog
1234567 | mysql>?show?master?status?; +------------------+----------+--------------+------------------+-------------------+ |?File?????????????|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?|?Executed_Gtid_Set?| +------------------+----------+--------------+------------------+-------------------+ |?mysql-bin.000001?|?????1344?|??????????????|??????????????????|???????????????????| +------------------+----------+--------------+------------------+-------------------+ 1?row? in set (0.00?sec) |
四、恢复数据
拷贝生产库前一天晚上的备份文件以及备份到事故期间的binlog至临时库
1 | scp alldb_bak.sql?192.168.8.32: /mysql/backup/dump/ |
1 | scp /mysql/data/mysql-bin .000001?192.168.8.32: /mysql/backup/dump/ |
在临时库创建出现事故的database
12345678910111213141516171819202122232425 | mysql>? create database test; Query?OK,?1?row?affected?(0.03?sec) mysql>?show?databases; + --------------------+ |? Database | + --------------------+ |?information_schema?| |?mysql??????????????| |?performance_schema?| |?sys????????????????| |?test???????????????| |?testdb13???????????| |?testdb14???????????| |?testdb15???????????| |?testdb16???????????| |?testdb17???????????| |?testdb18???????????| |?testdb19???????????| |?testdb20???????????| |?testdb21???????????| |?testdb22???????????| |?testdb23???????????| |?testdb24???????????| + --------------------+ 17? rows in set (0.00?sec) |
从备份中恢复test数据库
1 | mysql?-uroot?-pmysql?-P3306?-o? test <?alldb_bak.sql |
-o是指单独恢复test库,忽略其他数据库
从mysql-bin.000001中查看到drop table t8之前的pos是1164
12345678910111213 | update?t8? set id =3?where? id =4 /*!*/; #?at?1133 #181127?14:12:41?server?id?330631??end_log_pos?1164?CRC32?0x1203751c? Xid?=?1661 COMMIT/*!*/; #?at?1164 #181127?14:12:53?server?id?330631??end_log_pos?1229?CRC32?0x48fad728? Anonymous_GTID? last_committed=4??? sequence_number=5?? rbr_only=no SET?@@SESSION.GTID_NEXT=? 'ANONYMOUS' /*!*/; #?at?1229 #181127?14:12:53?server?id?330631??end_log_pos?1344?CRC32?0x2a7eb0d7? Query?? thread_id=3 exec_time=1 error_code=0 SET?TIMESTAMP=1543299173/*!*/; DROP?TABLE?`t8`?/*?generated?by?server?*/ /*!*/; |
1 | mysqlbinlog?--no-defaults?--stop-position=1164?--database= test mysql-bin.000001?|mysql?-uroot?-p? test |
五、根据临时库的数据,将该表恢复至生产库
六、数据验证
1234567891011121314151617181920212223242526272829 | mysql>?use?test; Database changed mysql>?show?tables; + ----------------+ |?Tables_in_test?| + ----------------+ |?kk?????????????| |?t1?????????????| |?t2?????????????| |?t3?????????????| |?t4?????????????| |?t5?????????????| |?t6?????????????| |?t7?????????????| |?t8?????????????| + ----------------+ 9? rows in set (0.01?sec) mysql>? select *? from t8; + ----+--------+ |?id?|? name | + ----+--------+ |??1?|?steven?| |??2?|?steven?| |??3?|?steven?| |??1?|?steven?| |??2?|?steven?| |??3?|?steven?| + ----+--------+ 6? rows in set (0.00?sec) |
数据恢复完毕。
内容总结
以上是互联网集市为您收集整理的MySQL通过binlog日志恢复数据--误删除的表全部内容,希望文章能够帮你解决MySQL通过binlog日志恢复数据--误删除的表所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。