MySQL通过binlog日志恢复数据--误删除的表

2021/4/10 19:19:19

编程Tag： mysql id rows binlog 误删除 -- sec t8 steven

本文主要是介绍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: 0mysql> 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: 0mysql> 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: 0mysql> update t8 set id=3 where id=4;Query OK, 2 rows affected (0.03 sec)Rows matched: 2 Changed: 2 Warnings: 0mysql> 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

四、恢复数据

拷贝生产库前一天晚上的备份文件以及备份到事故期间的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 = 1661COMMIT/*!*/;# 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=noSET @@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=0SET 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 changedmysql> 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日志恢复数据--误删除的表

相关编程文章