实验：误删除正在运行的数据库表然后恢复

将误删除了的某个表进行还原

• 做一个完全备份

[root@Centos7 ~]# mysqldump -A -F --data-master=2 --single-trancsaction > /tmp/backup/all`date +%T-%F`.sql
[root@Centos7 ~]# ll /tmp/backup/
total 508
-rw-r--r-- 1 root root 516216 Nov 28 09:12 all2019-11-28-09:12:28

• 完全备份后更新数据

MariaDB [hellodb]> INSERT INTO teachers (name,age)VALUES('DA Bai','33');
Query OK, 1 row affected (0.01 sec)

MariaDB [hellodb]> INSERT INTO teachers (name,age)VALUES('DA HEI','33');
Query OK, 1 row affected (0.01 sec)
/ 用来模拟生产环境中数据继续更新，时间点往前走

• 某个时间点 (10:00) 误删除了一个表

MariaDB [hellodb]> DROP TABLE teachers;
Query OK, 0 rows affected (0.01 sec)

• 后续其他表继续有数据更新，这个误删除的表无法访问

MariaDB [hellodb]> INSERT INTO classes (class)VALUES('Linghunchuqiao');
Query OK, 1 row affected (0.01 sec)

MariaDB [hellodb]> INSERT INTO classes (class)VALUES('Jiuyangshengong');
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> SELECT * FROM classes;
+---------+--------------------+----------+
| ClassID | Class              | NumOfStu |
+---------+--------------------+----------+
|       1 | Shaolin Pai        |       10 |
|       2 | Emei Pai           |        7 |
|       3 | QingCheng Pai      |       11 |
|       4 | Wudang Pai         |       12 |
|       5 | Riyue Shenjiao     |       31 |
|       6 | Lianshan Pai       |       27 |
|       7 | Ming Jiao          |       27 |
|       8 | Xiaoyao Pai        |       15 |
|       9 | Baijian Shanzhuang |        6 |
|      10 | 1                  |        1 |
|      11 | 1                  |        1 |
|      12 | Linghunchuqiao     |     NULL |
|      13 | Jiuyangshengong    |     NULL |
+---------+--------------------+----------+
13 rows in set (0.00 sec)

此时(10:30)发现表删除

• 立刻停止数据库服务

/ 先加全局读锁
MariaDB [hellodb]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)
/ 查看当前正在运行的事务
MariaDB [hellodb]> SHOW PROCESSLIST;
+----+-------------+-------------------+---------+---------+------+--------------------------+------------------+----------+
| Id | User        | Host              | db      | Command | Time | State                    | Info             | Progress |
+----+-------------+-------------------+---------+---------+------+--------------------------+------------------+----------+
|  1 | system user |                   | NULL    | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  2 | system user |                   | NULL    | Daemon  | NULL | InnoDB purge coordinator | NULL             |    0.000 |
|  3 | system user |                   | NULL    | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  4 | system user |                   | NULL    | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  5 | system user |                   | NULL    | Daemon  | NULL | InnoDB shutdown handler  | NULL             |    0.000 |
| 19 | root        | localhost         | hellodb | Query   |    0 | init                     | SHOW PROCESSLIST |    0.000 |
| 21 | root        | 172.20.54.1:55330 | NULL    | Sleep   |    3 |                          | NULL             |    0.000 |
+----+-------------+-------------------+---------+---------+------+--------------------------+------------------+----------+
7 rows in set (0.00 sec)
/ 杀掉未完成的事务
MariaDB [hellodb]> kill 21;
Query OK, 0 rows affected (0.00 sec)
/ 停止MYSQL服务
[root@Centos7 ~]# systemctl  stop mysqld

• 找到完全备份二进制日志的检查点

[root@Centos7 ~]# grep    '\-\- CHANGE MASTER TO' < /tmp/backup/all2019-11-28-09\:12\:28 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=371;

• 备份完全备份后续的全部二进制日志

[root@Centos7 ~]# mysqlbinlog  /var/mysqllog/mysql-bin.000003 --start-position=371 > /tmp/backup/inc1.sql

• 找到删除teahcers表的语句注释掉

vim /tmp/backup/inc1
/ 搜索语句
:/ DROP TABLE
# at 867
#191128  9:22:19 server id 1  end_log_pos 984 CRC32 0x5f1c9510  Query   thread_id=19    exec_time=0     error_code=0
SET TIMESTAMP=1574904139/*!*/;
DROP TABLE `teachers` /* generated by server */
/*!*/;
/ 已经找到注释掉即可

开始还原

/  启动禁止远程用户登录参数
vim /etc/my.cnf
[mysqld]
skip-networking
/ 重启mysqld服务
systemctl start mysqld
/ 临时关闭二进制日志
MariaDB [(none)]> SET @@sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
/ 开始恢复
MariaDB [hellodb]> source /tmp/backup/all2019-11-28-09:12:28.sql
MariaDB [hellodb]> source /tmp/backup/inc1.sql
/ 查看删除的表是否已经恢复
MariaDB [hellodb]> SHOW TABLES;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| employees         |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
8 rows in set (0.00 sec)
/ 查询数据
MariaDB [hellodb]> 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 | 100 | NULL   |
|   6 | Huang Rong    |  18 | NULL   |
|   7 | Guo Jing      |  88 | NULL   |
|   8 | Bai Cai       |  22 | NULL   |
|  11 | Hu Yidao      |  33 | NULL   |
|  12 | Lao Miao      |  34 | NULL   |
|  13 | Lao HEI       |  35 | NULL   |
|  14 | Miao Renfeng  |  40 | NULL   |
|  15 | DA Bai        |  33 | NULL   |
|  16 | DA HEI        |  33 | NULL   |
+-----+---------------+-----+--------+
/ 查看10:00以后新增的数据
MariaDB [hellodb]> SELECT * FROM classes;
+---------+--------------------+----------+
| ClassID | Class              | NumOfStu |
+---------+--------------------+----------+
|       1 | Shaolin Pai        |       10 |
|       2 | Emei Pai           |        7 |
|       3 | QingCheng Pai      |       11 |
|       4 | Wudang Pai         |       12 |
|       5 | Riyue Shenjiao     |       31 |
|       6 | Lianshan Pai       |       27 |
|       7 | Ming Jiao          |       27 |
|       8 | Xiaoyao Pai        |       15 |
|       9 | Baijian Shanzhuang |        6 |
|      10 | 1                  |        1 |
|      11 | 1                  |        1 |
|      12 | Linghunchuqiao     |     NULL |
|      13 | Jiuyangshengong    |     NULL |
+---------+--------------------+----------+
/ 直接退出数据库开始修改配置文件，准备启动数据库
exit

• 重启数据库

vim /etc/my.cnf 
#skip-networking
[root@Centos7 backup]# systemctl restart mysqld

• 此时恢复数据库结束可以正常访问，最好后续直接再做一次完全备份