思路
1.停止旧库,避免二次伤害
2.创建新库
3.导入上一次旧库的全备
4.通过binlog找到新增的数据
5.导入新数据,检查数据是否恢复
6.恢复业务
a.直接使用'临时库顶替原生产库',前端应用割接到新库('数据量特别大的时候')
b.将误删除的'表单独导出',然后导入到原生产环境('数据量小的时候')
模拟旧库环境
1.连接库
[root@db02 ~]
mysql> create database linux9;
mysql> use linux9
mysql> create table student(id int,name varchar(10),gender enum('m','f'),age tinyint)
mysql> desc student;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gender | enum('m','f') | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
mysql> insert student values(1,'aaa','m',10),(2,'bbb','m',20),(3,'ccc','m',30);
mysql> select * from student;
+------+------+--------+------+
| id | name | gender | age |
+------+------+--------+------+
| 1 | aaa | m | 10 |
| 2 | bbb | m | 20 |
| 3 | ccc | m | 30 |
+------+------+--------+------+
mysql> select database();
+------------+
| database() |
+------------+
| linux9 |
+------------+
[root@db02 ~]
Enter password:
[root@db02 ~]
-rw-r--r-- 1 root root 2305 Jul 23 04:17 /tmp/linux9.sql
[root@db02 ~]
Enter password:
mysql> use linux9
mysql> insert student values(10,'zzz','f',99);
mysql> select * from student;
+------+------+--------+------+
| id | name | gender | age |
+------+------+--------+------+
| 1 | aaa | m | 10 |
| 2 | bbb | m | 20 |
| 3 | ccc | m | 30 |
| 10 | zzz | f | 99 |
+------+------+--------+------+
mysql> drop database linux9;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
解决方法
[root@db02 ~]
Shutting down MySQL.. SUCCESS!
[root@db02 ~]
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=775;
[root@db02 ~]
[root@db02 data]
total 112132
-rw-rw---- 1 mysql mysql 56 Jul 23 04:50 auto.cnf
-rw-rw---- 1 mysql mysql 12582912 Jul 23 05:25 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Jul 23 05:25 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Jul 23 04:50 ib_logfile1
drwx------ 2 mysql mysql 4096 Jul 23 04:50 mysql
-rw-rw---- 1 mysql mysql 69408 Jul 23 04:50 mysql-bin.000001
-rw-rw---- 1 mysql mysql 1485584 Jul 23 04:50 mysql-bin.000002
-rw-rw---- 1 mysql mysql 1132 Jul 23 05:25 mysql-bin.000003
-rw-rw---- 1 mysql mysql 117 Jul 23 04:50 mysql-bin.index
drwx------ 2 mysql mysql 4096 Jul 23 04:50 performance_schema
drwx------ 2 mysql mysql 6 Jul 23 04:50 test
[root@db02 data]
...
COMMIT/*!*/;
SET TIMESTAMP=1595452650/*!*/;
SET @@session.pseudo_thread_id=6/*!*/;
drop database linux9
/*!*/;
...
[root@db02 data]
[root@db02 data]
root@10.53's password:
linux9.sql 100% 2305 2.1MB/s 00:00
new.sql 100% 693 496.8KB/s 00:00
#进入
mysql> use linux9
#导入全备
[root@db03 ~]# mysql -uroot -p < /tmp/linux9.sql
Enter password:
#查看数据
mysql> select * from student;
+------+------+--------+------+
| id | name | gender | age |
+------+------+--------+------+
| 1 | a | m | 10 |
| 2 | b | m | 20 |
| 3 | c | m | 30 |
+------+------+--------+------+
#导入新数据
mysql> \. /tmp/new.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
#查看总数据
mysql> select * from student;
+------+------+--------+------+
| id | name | gender | age |
+------+------+--------+------+
| 1 | a | m | 10 |
| 2 | b | m | 20 |
| 3 | c | m | 30 |
| 100 | z | m | 99 |
+------+------+--------+------+
#导出总数据
[root@db02 ~]# mysqldump -uroot -p123 linux9 > /tmp/linux9.sql
#拷贝到旧库
[root@db02 ~]# scp /tmp/linux9.sql 172.16.1.51:/tmp/
#启动旧库
[root@db01 ~]# systemctl start mysqld
#创建删除的库
mysql> create database linux9;
#进入库
mysql> use linux9;
#导入总数据
mysql> source /tmp/linux9.sql
#查看
mysql> select * from student;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步