7.3 故障时间点：

周四上午10点，开发人员误删除了一个表，如何恢复？

7.4 思路：

1、停业务，避免数据的二次伤害

2、找一个临时库，恢复周三23：00全备

3、截取周三23：00  --- 周四10点误删除之间的binlog，恢复到临时库

4、测试可用性和完整性

5、

5.1 方法一：直接使用临时库顶替原生产库，前端应用割接到新库

5.2 方法二：将误删除的表导出，导入到原生产库

6、开启业务

 #-------------------------------------------------------------------------------

 #

 # 企业案例恢复操作

 # 1.1 全备+恢复部分误删除的表（全备期间删除或者全备完成后删除）

 # Author：nod

 # Date:18-08-05

 #-------------------------------------------------------------------------------

 #-------------------------------------------------------------------------------

 # 启动实例模拟备用数据库

 #-------------------------------------------------------------------------------

 mysqld_safe --defaults-file=/data/3307/my.cnf &

 [root@db01 tmp]# netstat -lnp | grep 330*

 tcp        0      0 :::3306                     :::*                        LISTEN      45894/mysqld

 tcp        0      0 :::3307                     :::*                        LISTEN      46940/mysqld

 unix  2      [ ACC ]     STREAM     LISTENING     146100 46940/mysqld        /data/3307/mysql.sock

 #-------------------------------------------------------------------------------

 # 主库模拟数据

 #-------------------------------------------------------------------------------

 [root@db01 tmp]# mysql -uroot -p123 -A

 mysql> flush logs;

 mysql> show master status;

 +------------------+----------+--------------+------------------+-------------------+

 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

 +------------------+----------+--------------+------------------+-------------------+

 | mysql-bin.000012 |      120 |              |                  |                   |

 +------------------+----------+--------------+------------------+-------------------+

 1 row in set (0.00 sec)

 mysql> create database backup;

 mysql> create table backup.full select * from world.city;

 mysql> create table  backup.full_1 select * from mysql.user;

 #-------------------------------------------------------------------------------

 # 模拟主库每天定时进行全备

 #-------------------------------------------------------------------------------

 [root@db01 backup]# mysqldump -uroot -p123 -A  -R  --triggers --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz

 Warning: Using a password on the command line interface can be insecure.

 [root@db01 backup]# ll

 total 328

 -rw-r--r-- 1 root root 334770 Aug  5 22:19 full_2018-08-05.sql.gz

 #-------------------------------------------------------------------------------

 # 模拟主库数据发生变化

 # 故障前thur数据为239rows

 #-------------------------------------------------------------------------------

 mysql> create table backup.thur select * from world.country;

 Query OK, 239 rows affected (0.11 sec)

 Records: 239  Duplicates: 0  Warnings: 0

 mysql> update backup.full set countrycode='CHN';

 Query OK, 3716 rows affected (0.04 sec)

 Rows matched: 4079  Changed: 3716  Warnings: 0

 mysql> delete from backup.full where id>200;

 Query OK, 3879 rows affected (0.05 sec)

 #-------------------------------------------------------------------------------

 # 模拟主库数据故障 删除thur表

 #-------------------------------------------------------------------------------

 mysql> drop table thur;

 #-------------------------------------------------------------------------------

 # 开始恢复数据部分

 #-------------------------------------------------------------------------------

 #-------------------------------------------------------------------------------

 # 通过全备进行数据分析

 #-------------------------------------------------------------------------------

 [root@db01 backup]# gzip -d full_2018-08-05.sql.gz

 [root@db01 backup]# ll

 total 1072

 -rw-r--r-- 1 root root 1096129 Aug  5 22:19 full_2018-08-05.sql

 #-------------------------------------------------------------------------------

 # 2 分析full_2018-08-05.sql得出

 # CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=137208;

 # 说明是从mysql-bin.000012开始记录 start-position为137208

 # 3 通过show binlog events in 'mysql-bin.000012' 得出drop开始的位置为 536332

 # 因而语句写为：

 # mysqlbinlog --start-position=137208 --stop-position=536332   /data/mysql/mysql-bin.000012 >/backup/inc.sql

 #-------------------------------------------------------------------------------

 [root@db01 backup]# mysqlbinlog --start-position=137208 --stop-position=536332   /data/mysql/mysql-bin.000012 >/backup/inc.sql

 [root@db01 backup]# ll

 total 1608

 -rw-r--r-- 1 root root 1096129 Aug  5 22:19 full_2018-08-05.sql

 -rw-r--r-- 1 root root  546397 Aug  5 22:27 inc.sql

 #-------------------------------------------------------------------------------

 # 进入备用数据库 恢复数据

 #-------------------------------------------------------------------------------

 [root@db01 tmp]# mysql -S /data/3307/mysql.sock 

 set sql_log_bin=0;

 source /backup/full_2018-08-05.sql

 source /backup/inc.sql

 #-------------------------------------------------------------------------------

 # 检查恢复后的数据

 #-------------------------------------------------------------------------------

 mysql> select count(*) from thur;

 +----------+

 | count(*) |

 +----------+

 |      239 |

 +----------+

 1 row in set (0.00 sec)

 #-------------------------------------------------------------------------------

 # 将故障表导出

 #-------------------------------------------------------------------------------

 [root@db01 backup]# mysqldump -S /data/3307/mysql.sock backup thur >/backup/thur.sql

 #-------------------------------------------------------------------------------

 # 登录主库 导入数据thur.sql

 # 因为是导出的单表，一定要进入数据库后恢复 use backup

 #-------------------------------------------------------------------------------

 [root@db01 backup]# mysql -uroot -p123 -A

 mysql> use backup;

 Database changed

 mysql> source /backup/thur.sql;

 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 count(*) from thur;

 +----------+

 | count(*) |

 +----------+

 |      239 |

 +----------+

 1 row in set (0.01 sec)