# 记录生产事故数据库被删
## 查看二进制日志列表，确定要恢复的日志文件和位置
```bash
mysql> SHOW BINARY LOGS;
+------------------+------------+
| Log_name | File_size |
+------------------+------------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 177 |
| mysql-bin.000003 | 1073909821 |
| mysql-bin.000004 | 1074007257 |
| mysql-bin.000005 | 1074271245 |
| mysql-bin.000006 | 1073742064 |
| mysql-bin.000007 | 1073746424 |
| mysql-bin.000008 | 1073748114 |
| mysql-bin.000009 | 1073741947 |
| mysql-bin.000010 | 1074430565 | # 通过查看文件属性，得出文件是今天之前的数据
| mysql-bin.000011 | 1074338387 |
| mysql-bin.000012 | 1074603345 |
| mysql-bin.000013 | 1074268173 |
| mysql-bin.000014 | 1073930080 |
| mysql-bin.000015 | 1074431863 |
| mysql-bin.000016 | 1074027152 |
| mysql-bin.000017 | 1074314262 |
+------------------+------------+
33 rows in set (0.01 sec)
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000010' LIMIT 10; # 数据太长，这里就不演示了
```
* 第一步恢复今天之前的数据,数据量过大，分批全量导入,
```bash
# 如果只是恢复其中一个数据库, 请指定数据库名字
mysqlbinlog --no-defaults mysql-bin.000001 mysql-bin.000002 >/tmp/01-02.sql
mysqlbinlog --no-defaults mysql-bin.000003 mysql-bin.000004 >/tmp/03-04.sql
mysqlbinlog --no-defaults mysql-bin.000005 mysql-bin.000006 >/tmp/05-06.sql
mysqlbinlog --no-defaults mysql-bin.000007 mysql-bin.000008 >/tmp/07-08.sql
mysqlbinlog --no-defaults mysql-bin.000009 >/tmp/09.sql
```
* 干净数据库，导入上面sql，导完之后，恢复到今天之前的数据
```bash
mysql> source /tmp/01-02.sql;
mysql> source /tmp/03-04.sql;
mysql> source /tmp/05-06.sql;
mysql> source /tmp/07-08.sql;
mysql> source /tmp/09.sql;
# 稳健为主，导出今天数据之前数据，防止最后一步出现异常错误
mysqdump -uroot -p --all-databases > /tmp/before_last_all.sql
```
* 分析今天的binlog日志
```bash
# 可读 SQL 预计
mysqlbinlog --no-defaults --database=You_db --base64-output=decode-rows /data/mysql/mysql-bin.000010 > /tmp/10.sql
```
grep -i -n drop 10.sql
sed -n '5121344, 546546p' 10.sql # 比如第一个 drop 是在 546546 行,那么我们就恢复 546546 行之前的 end_log_pos 位置数据
# 例如确定 10.sql 开始时间是 123， 结束时间是 546546 , 小数据采用这个方法
mysqlbinlog --no-defaults \
--start-position=123 --stop-position=398718561 \
/data/mysql/mysql-bin.000010 | mysql -u root -p You_db # 恢复 You_db 数据
# 如果最后一步恢复出现其他错误，则导出sql语句，再次全量恢复 123-398718561.sql； 大数据采用方法
mysqlbinlog --no-defaults --start-position=123 --stop-position=398718561 /data/mysql/mysql-bin.000010 > /tmp/123-398718561.sql
# 恢复最后事故之前的数据
mysql> source /tmp/123-398718561.sql;
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://close.gitbook.io/yun-wei-bi-ji/centos/mysql/ji-lu-sheng-chan-shi-gu-shu-ju-ku-bei-shan.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.