乐趣区

关于binlog:技术分享-误删表以及表中数据该如何恢复

jiezi

作者：杨小云

爱可生数据库工程师，负责 MySQL 日常保护及 DMP 产品反对。善于 mysql 故障解决。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

场景：

客户误删了一张表，所以 想复原某张表的数据，冀望复原到删除之前的数据。

前提：

数据库误删某表复原办法，上面介绍的的办法是针对每天有备份的数据库和开启 binlog 日志的。

阐明：本文中的测试库为 test 数据库，测试表是 test 中 student 表。

一、开启 binlog 日志，并备份数据

1. 查看数据库是否开启 binlog 日志

如果没有开启，须要上面的办法进行开启

（1）在 linux 零碎下，批改 /etc/my.cnf 文件

# 编辑模式进入 /etc/my.cnf
vi /etc/my.cnf
# i 开始进行编辑
# 在 #log bin 前面增加内容
server_id=2
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 30
log_bin_basename= / 数据库实例装置目录 /log/binlog/ 端口号 /mysql-bin       
log_bin_index=/ 数据库实例装置目录 /log/binlog/ 端口号 /mysql-bin.index
# esc 退出编辑，shift+: 保留

（2）重启 mysql 服务

systemctl restart mysqld

到这里曾经开启 binlog 了，能够用查看命令看一下是否开启

2. 查看一下数据表中的数据

3. 备份数据

备份命令格局：

mysqldump [选项] 数据库名 [表名] > 脚本名
mysqldump [选项] -- 数据库名 [选项 表名] > 脚本名
mysqldump [选项] --all-databases [选项] > 脚本名

（1）备份所有数据库

mysqldump -h10.186.63.4 -P4149 -uu1 -p1234567890q@ -all-databases > /test1.sql

（2）备份单库（多个库之间以空格距离）

mysqldump -h10.186.63.4 -P4149 -uu1 -p1234567890q@ database > test2.sql

（3）备份单表(多个表以空格距离)

mysqldump -h10.186.63.4 -P4149 -uu1 -p1234567890q@ database table  > test3.sql

（4）备份指定数据库排除某些表

mysqldump -h10.186.63.4 -P4149 -uu1 -p1234567890q@ database --ignore-table=db.tb --ignore-table=db.tb2 > /test4.sql

4. 插入数据后删除数据库

插入数据，生成 binlog 日志

mysql> insert into student values('201215130','张三','男',21,'IS');
Query OK, 1 row affected (0.03 sec)
mysql>  insert into student values('201215131','李四','女',20,'MA');
Query OK, 1 row affected (0.02 sec)

删除数据库

mysql> drop database test;
Query OK, 1 row affected (0.10 sec)

切记这个时候不要有任何的操作！！！

二、复原数据

1. 查看以后的 binlog

mysql>  show master status\G;
************************ 1. row ***************************
File: mysql-bin.000021
Position: 68403303
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 130df5fa-01c1-11ed-916a-02000aba3f04:1-446151
1 row in set (0.00 sec)
ERROR:
No query specified
注：mysql-bin.000021 文件将作为复原删除数据的起源之一

2. 拷贝 binlog 日志

将以后的 binlog 日志拷贝到其余目录，免得后续操作对 binlog 日志产生影响

cp  /test/data/mysql/log/binlog/4149/mysql-bin.000021  /root

3. 将转换 binlog 日志为 sql

命令格局为：mysqlbinlog -d database mysql-bin 文件 > xx.sql
如：

/data/mysql/base/5.7.25/bin/mysqlbinlog -d test mysql-bin.000021 > 0021bin.sql
[root@test2 4149]# /data/mysql/base/5.7.25/bin/mysqlbinlog -d test mysql-bin.000022 > 0022.sql
WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.

编辑转化后的 sql 文件

vi 0021bin.sql

将外面的 误操作命令 (DROP 命令) 全副删除

保留后开始进行复原数据

4. 复原备份文件

/data/mysql/base/5.7.25/bin/mysql -h10.186.63.4 -P4149 -uu1 -p1234567890q@  < test.sql

查看数据库备份文件是否复原

mysql> show databases;
mysql> use test;
mysql> show tables;
mysql>select * from table;

5. 复原备份之后被删除的数据

正文掉 binlog 转化后的 sql 文件中的这一行

SET @@GLOBAL.GTID_PURGED=
/*SET @@GLOBAL.GTID_PURGED=XXXX*/;

指定被删除表所在的数据库，导入数据

/data/mysql/base/5.7.25/bin/mysql -h10.186.63.4 -P4149 -uu1 -p1234567890q@  test  < 0021bin.sql

查看复原后的数据


mysql> select * from test.student;
+-----------+-----------+------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept |
+-----------+-----------+------+------+-------+
| 201215121 | 李勇      | 男   |   20 | CS    |
| 201215122 | 刘晨      | 女   |   19 | CS    |
| 201215123 | 王敏      | 女   |   18 | MA    |
| 201215125 | 张立      | 男   |   19 | IS    |
| 201215126 | 李一平    | 男   |   18 | IS    |
| 201215127 | 张琴      | 女   |   19 | CS    |
| 201215128 | 王方      | 女   |   20 | MA    |
| 201215129 | 黄林林    | 男   |   21 | IS    |
| 201215130 | 李四      | 女   |   20 | MA    |
| 201215131 | 张三      | 男   |   21 | IS    |
+-----------+-----------+------+------+-------+
10 rows in set (0.00 sec)
退出移动版