MySQL 企业案例：误删核心业务表

问题描述：

1.正在运行的网站系统，MySQL 数据库，数据量 25G，日业务增量 10 - 15M

2.备份策略：每天 23：00，计划任务调用 mysqldump 执行全备脚本

3.故障时间点：上午 10：00 开发人员误删除一个核心业务表，如何恢复？

解决思路：

1.暂时停止数据库服务，避免出现更多问题

2.在新的服务器环境上安装新的 MySQL 数据库

3.在新的服务器数据库上，导入前一天的全量备份数据

4.通过故障服务器上的 binlog 找到前一天 23：00 到第二天 10：00 之间的数据

5.导入找到的新数据

6.恢复业务

1） 直接使用临时库顶替原生产库，前端应用割接到新库（数据量特别大的时候）

​2） 将误删除的表单独导出，然后导入到原生产环境（数据量小的时候）

模拟案例场景#

模拟生产数据#

Copy
mysql> create database business;
Query OK, 1 row affected (0.00 sec)

mysql> use business;
Database changed
mysql> create table business(
    -> id int primary key auto_increment,
    -> detail varchar(100));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into business(detail) values("投资阿里巴巴 100000000元"),("进账 20000000元"),("ZZZZZZZ"),("QQQQQQQQ");
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM business;
+----+---------------------------------+
| id | detail                          |
+----+---------------------------------+
|  1 | 投资阿里巴巴 100000000元          |
|  2 | 进账 20000000元                  |
|  3 | ZZZZZZZ                         |
|  4 | QQQQQQQQ                        |
+----+---------------------------------+
4 rows in set (0.00 sec)

模拟 23:00 全量备份（定时任务）#

Copy
# 全量备份命令，可以用 Crontab 做定时任务
[root@dbtest01 mysql]# mysqldump -uroot -p12345 -A -R --triggers --master-data=2 --single-transaction > /tmp/data_backup.sql
Warning: Using a password on the command line interface can be insecure.

模拟 23：00 到 10:00 的数据修改#

Copy
# 全量备份后，增加或修改了表数据
mysql> use business;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into business(detail) values("进账 500000000元");
Query OK, 1 row affected (0.16 sec)

mysql> insert into business(detail) values("出账 100000000元");
Query OK, 1 row affected (0.05 sec)

# 新插入的表数据（ID=5,6）并没有做全量备份（没有备份到 /tmp/data_backup.sql）
mysql> select * from business;
+----+---------------------------------+
| id | detail                          |
+----+---------------------------------+
|  1 | 投资阿里巴巴 100000000元          |
|  2 | 进账 20000000元                  |
|  3 | ZZZZZZZ                         |
|  4 | QQQQQQQQ                        |
|  5 | 进账 500000000元                 |
|  6 | 出账 100000000元                 |
+----+---------------------------------+
6 rows in set (0.00 sec)

模拟误删数据库#

Copy
# 模拟删库
mysql> drop database business;
Query OK, 1 row affected (0.07 sec)

恢复数据方案#

暂停数据库服务#

Copy
[root@dbtest01 ~]# systemctl stop mysqld

在新服务器上安装新的 MySQL 数据库#

Copy
# 在一台新的服务器上，安装一个新的 MySQL 数据库
# 如果之前安装过 MySQL， 可以将 $basedir/data/ 下的数据全部删除
[root@dbtest02 ~]# rm -rf /usr/local/mysql/data/*

# 再重新初始化，该数据库就又变成 "新数据库" 了
[root@dbtest02 ~]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

导入全量备份数据#

Copy
# 将全量备份的数据库数据，传输到新的数据库服务器
[root@dbtest01 ~]# scp /tmp/data_backup.sql 172.16.1.122:/tmp
 
# 新数据库，导入全备数据
[root@dbtest02 ~]# mysql < /tmp/data_backup.sql

Binlog 记录着23：00 到 10:00 的数据修改事件，导出数据#

Copy
# 通过 binlog 找到前一天 23：00 到第二天 10:00 之间的数据

# 在全量备份中，找到 bin-log 的起始位置点（start-position）
[root@dbtest02 ~]# head -22 /tmp/data_backup.sql | tail -1
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=825;


# 在故障数据库中，找到 Binlog，并找到结束位置点（stop-position）
[root@dbtest01 ~]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000005 | grep -C 20 'drop'
# 或者使用 vim 查找
[root@dbtest01 ~]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000005 >/tmp/binlog.sql
过滤 drop database business 取出命令上面的位置点
# at 1405
......
drop database business

# 取出位置点之间的数据（Binlog 事件）
[root@dbtest01 ~]# mysqlbinlog -d business --start-position=825 --stop-position=1405 mysql-bin.000005 > /tmp/additional.sql

导入数据#

Copy
# 故障数据库，将 binlog 传到新数据库
[root@dbtest01 data]# scp /tmp/additional.sql  172.16.1.122:/tmp/

# 新数据库导入新的数据
[root@db02 ~]# mysql < /tmp/additional.sql
# 在 Mysql命令行中导入数据
mysql> source /tmp/additional.sql

确认数据#

Copy
mysql> select * from business.business;
+----+---------------------------------+
| id | detail                          |
+----+---------------------------------+
|  1 | 投资阿里巴巴 100000000元        |
|  2 | 进账 20000000元                 |
|  3 | ZZZZZZZ                         |
|  4 | QQQQQQQQ                        |
|  5 | 进账 500000000元                |
|  6 | 出账 100000000元                |
+----+---------------------------------+
6 rows in set (0.00 sec)

恢复业务#

1.直接使用临时库顶替原生产库，前端应用割接到新库（数据量特别大的时候）

2.将误删除的表单独导出，然后导入到原生产环境（数据量小的时候）

Copy
# 第二种恢复业务的操作
# 1）新库导出指定业务库，若不使用 -B 选项，就没有建库语句
[root@dbtest02 ~]# mysqldump -B business > /tmp/business.sql
# 2）新库将数据推送回老库
[root@dbtest02 ~]# scp /tmp/business.sql 172.16.1.121:/tmp
# 3）将恢复的数据导入老库
mysql> source  /tmp/business.sql