误删除表，如何还原

最新推荐文章于 2024-03-22 11:28:38 发布

猪中梁朝伟

最新推荐文章于 2024-03-22 11:28:38 发布

阅读量429

点赞数 1

本文链接：https://blog.csdn.net/xiaochenwj1995/article/details/104699558

版权

前提：数据库的二进制日志功能得开启log_bin=ON
1、对数据库进行完全备份。
查看数据库信息

MariaDB [hellodb_innodb]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb_innodb     |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [hellodb_innodb]> show tables;
+--------------------------+
| Tables_in_hellodb_innodb |
+--------------------------+
| classes                  |
| coc                      |
| courses                  |
| scores                   |
| students                 |
| teachers                 |
| toc                      |
+--------------------------+
7 rows in set (0.00 sec)

[root@centos7 data]# mysqldump -A -F --single-transaction --master-data=2 > /data/backup/full_`date +%F`.sql
[root@centos7 data]# ls ./backup/
full_2020-03-07.sql

2、修改数据，表中插入数据。

ariaDB [hellodb_innodb]> insert students (stuid,name,age,gender,classid,teacherid) values (26,'Wei Xiaobao',26,2,4,4);
Query OK, 1 row affected, 1 warning (0.05 sec)

3、模拟误删除表。

MariaDB [hellodb_innodb]> drop table students;
Query OK, 0 rows affected (0.05 sec)

MariaDB [hellodb_innodb]> show tables;
+--------------------------+
| Tables_in_hellodb_innodb |
+--------------------------+
| classes                  |
| coc                      |
| courses                  |
| scores                   |
| teachers                 |
| toc                      |
+--------------------------+
6 rows in set (0.00 sec)

4、对其他表进行操作。

MariaDB [hellodb_innodb]> insert teachers  values (5,'Feng qingyang',80,1);
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [hellodb_innodb]> update teachers set Gender=2 where TID=5;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [hellodb_innodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Feng qingyang |  80 | M      |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)

此时发现数据库中的表被删除，要进行还原。
还原过程
5、对数据库添加读锁，禁止其他用户对数据库写操作。

MariaDB [hellodb_innodb]> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

6、刷新二进制日志，然后查看二进制日志信息。

MariaDB [hellodb_innodb]> show master logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| bin_log.000001 |     30361 |
| bin_log.000002 |   1038814 |
| bin_log.000003 |      7990 |
| bin_log.000004 |       245 |
| bin_log.000005 |       264 |
| bin_log.000006 |       286 |
| bin_log.000007 |      1079 |
+----------------+-----------+
7 rows in set (0.01 sec)

MariaDB [hellodb_innodb]> flush logs;
Query OK, 0 rows affected (0.05 sec)

MariaDB [hellodb_innodb]> show master logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| bin_log.000001 |     30361 |
| bin_log.000002 |   1038814 |
| bin_log.000003 |      7990 |
| bin_log.000004 |       245 |
| bin_log.000005 |       264 |
| bin_log.000006 |       286 |
| bin_log.000007 |      1120 |
| bin_log.000008 |       245 |
+----------------+-----------+
8 rows in set (0.00 sec)

7、查看全备份时的二进制日志position，确定全备份到目前的所有二进制日志文件和position。

[root@centos7 data]# head -n 25 /data/backup/full_2020-03-07.sql 
-- MySQL dump 10.14  Distrib 5.5.56-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version	5.5.56-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='bin_log.000007', MASTER_LOG_POS=245;

--
-- Current Database: `hellodb_innodb`

经查看，完全备份时的二进制日志及position是bin_log.000007, MASTER_LOG_POS=245。

8、导出二进制日志信息。

[root@centos7 data]# mysqlbinlog --start-position=245 /data/lvm/mysql_binlog/bin_log.000007 > /data/backup/bin_`date +%F`.sql
[root@centos7 data]# ls ./backup/
bin_2020-03-07.sql    full_2020-03-07.sql

9、找到误删除表的语句，然后删除此语句。

[root@centos7 data]# vim /data/backup/bin_2020-03-07.sql 

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200307 10:23:40 server id 1  end_log_pos 245   Start: binlog v 4, server v 5.5.56-MariaDB created 200307 10:23:40
BINLOG '
rAVjXg8BAAAA8QAAAPUAAAAAAAQANS41LjU2LU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAEbaPFw==
'/*!*/;
# at 245
#200307 10:29:37 server id 1  end_log_pos 323   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1583548177/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 323
#200307 10:29:37 server id 1  end_log_pos 488   Query   thread_id=2     exec_time=0     error_code=0
use `hellodb_innodb`/*!*/;
SET TIMESTAMP=1583548177/*!*/;
insert students (stuid,name,age,gender,classid,teacherid) values (26,'Wei Xiaobao',26,0,4,4)
/*!*/;
# at 488
#200307 10:29:37 server id 1  end_log_pos 515   Xid = 410
COMMIT/*!*/;
# at 515
#200307 10:43:59 server id 1  end_log_pos 635   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1583549039/*!*/;
DROP TABLE `students` /* generated by server */   ##删除此语句

10、关闭数据库，还原备份。

[root@centos7 ~]# systemctl stop mariadb

还原之前先清空之前的数据库内容

[root@centos7 lvm]# rm -rf /data/lvm/mysql/*
[root@centos7 lvm]# rm -rf /data/lvm/mysql_binlog/*
[root@centos7 lvm]# ls 
mysql  mysql_binlog
[root@centos7 lvm]# ls ./mysql
[root@centos7 lvm]# ls ./mysql_binlog/

还原数据库

[root@centos7 ~]# systemctl stop mariadb
[root@centos7 ~]# systemctl start mariadb
[root@centos7 ~]# ss -ntl
State      Recv-Q Send-Q                                        Local Address:Port                                                       Peer Address:Port              
LISTEN     0      128                                                       *:111                                                                   *:*                  
LISTEN     0      5                                             192.168.122.1:53                                                                    *:*                  
LISTEN     0      128                                                       *:22                                                                    *:*                  
LISTEN     0      128                                               127.0.0.1:631                                                                   *:*                  
LISTEN     0      100                                               127.0.0.1:25                                                                    *:*                  
LISTEN     0      128                                               127.0.0.1:6010                                                                  *:*                  
LISTEN     0      128                                               127.0.0.1:6013                                                                  *:*                  
LISTEN     0      50                                                        *:3306                                                                  *:*                  
LISTEN     0      128                                                      :::111                                                                  :::*                  
LISTEN     0      128                                                      :::22                                                                   :::*                  
LISTEN     0      128                                                     ::1:631                                                                  :::*                  
LISTEN     0      100                                                     ::1:25                                                                   :::*                  
LISTEN     0      128                                                     ::1:6010                                                                 :::*                  
LISTEN     0      128                                                     ::1:6013                                                                 :::*                  
[root@centos7 ~]# mysql < /data/backup/full_2020-03-07.sql 
[root@centos7 ~]# mysql < /data/backup/bin_2020-03-07.sql

查看还原后的数据库状态及数据。

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb_innodb     |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> select * from hellodb_innodb.students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|    26 | Wei Xiaobao   |  26 |        |       4 |         4 |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)

MariaDB [(none)]> select * from hellodb_innodb.teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | Feng qingyang |  80 | M      |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)

猪中梁朝伟

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
误删除表，如何还原

前提：数据库的二进制日志功能得开启log_bin=ON1、对数据库进行完全备份。查看数据库信息MariaDB [hellodb_innodb]> show databases;+--------------------+| Database |+--------------------+| information_schema || hellodb_inn...
复制链接

扫一扫