MySQL通过binlog日志恢复数据--误删除的表
2021/4/10 19:19:19
本文主要是介绍MySQL通过binlog日志恢复数据--误删除的表,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
本实验模拟生产环境误操作删除表,可利用逻辑备份和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日志恢复数据--误删除的表的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-10-23BinLog入门:新手必读的MySQL二进制日志指南
- 2024-10-23Binlog入门:MySQL数据库的日志管理指南
- 2024-10-22MySQL数据库入门教程:从安装到基本操作
- 2024-10-22MySQL读写分离入门教程:轻松实现数据库性能提升
- 2024-10-22MySQL分库分表入门教程
- 2024-10-22MySQL慢查询的诊断与优化指南
- 2024-10-22MySQL索引入门教程:快速理解与应用指南
- 2024-10-22MySQL基础入门教程:从安装到基本操作
- 2024-10-22MySQL数据库中的Binlog详解与操作教程
- 2024-10-12部署MySQL集群项目实战:新手入门教程