某天工作时间，一个二货犯晕登错生产当测试环境了，直接drop了一个数据库,需要紧急恢复!可利用备份的数据文件以及增量的 binlog 文件进行数据恢复。
具体思路归纳几点：
1、恢复条件为 MySQL 要开启 binlog 日志功能，并且要全备和增量的所有数据。
2、恢复时建议对外停止更新，即禁止更新数据库。（这点很重要）
3、先恢复全量，然后把全备时刻点以后的增量日志，按顺序恢复成 SQL 文件，
4、然后把文件中有问题的SQL语句删除(也可通过时间和位置点)，再恢复到数据库。
具体实例演示：
1、首先要确保MySQL开启了binlog日志功能，检查如下结果
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> show variables like
'%log_bin%'
;
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename |
/mysql_data/mysql-bin
|
| log_bin_index |
/mysql_data/mysql-bin
.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------+
6 rows
in
set
(0.01 sec)
|
2、查看当前测试表里面数据信息后面好做对比
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
mysql>
select
* from Student;
+-----------+-----------+------+------+-------+
| Sno | Sname | Ssex | Sage | Sdept |
+-----------+-----------+------+------+-------+
| 200215121 | 李勇 | 男 | 20 | CS |
| 200215122 | 刘晨 | 女 | 19 | CS |
| 200215123 | 王敏 | 女 | 18 | MA |
| 200215125 | 张立 | 女 | 19 | IS |
| 200215126 | 虎威 | 男 | 25 | CS |
| 200215127 | 魏大师 | 男 | 35 | IS |
| 200215128 | 老谢 | 男 | 33 | MA |
| 200215129 | 小贾 | 男 | 30 | CS |
| 200215130 | 会民 | 男 | 23 | CS |
| 200215131 | 陈兴 | 男 | 33 | MA |
| 200215132 | 阿帆 | 男 | 36 | IS |
| 200215133 | 国良 | 男 | 40 | IS |
| 200215134 | 老宋 | 男 | 40 | IS |
| 200215135 | 光光 | 男 | 35 | IS |
| 200215136 | 王老板 | 女 | 27 | IS |
+-----------+-----------+------+------+-------+
15 rows
in
set
(0.00 sec)
|
3、现在进行全备份
|
1
2
3
4
5
6
7
|
mysqldump -u root -p -B -F -R -x student|
gzip
>
/mysql_backup/student_
$(
date
+%Y%m%d_%H%M%S).sql.gz
参数说明：
-B：指定数据库
-F：刷新日志
-R：备份存储过程等
-x：锁表
|
4、再次插入新数据
|
1
2
3
|
INSERT INTO Student VALUES (
'200215137'
,
'程程'
,
'女'
,30,
'IS'
);
INSERT INTO Student VALUES (
'200215138'
,
'琪琪'
,
'男'
,29,
'MA'
);
INSERT INTO Student VALUES (
'200215139'
,
'龙龙'
,
'男'
,27,
'IS'
);
|
5、检查是否插入成功，如下可以看出已经插入成功
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
mysql>
select
* from Student;
+-----------+-----------+------+------+-------+
| Sno | Sname | Ssex | Sage | Sdept |
+-----------+-----------+------+------+-------+
| 200215121 | 李勇 | 男 | 20 | CS |
| 200215122 | 刘晨 | 女 | 19 | CS |
| 200215123 | 王敏 | 女 | 18 | MA |
| 200215125 | 张立 | 女 | 19 | IS |
| 200215126 | 虎威 | 男 | 25 | CS |
| 200215127 | 魏大师 | 男 | 35 | IS |
| 200215128 | 老谢 | 男 | 33 | MA |
| 200215129 | 小贾 | 男 | 30 | CS |
| 200215130 | 会民 | 男 | 23 | CS |
| 200215131 | 陈兴 | 男 | 33 | MA |
| 200215132 | 阿帆 | 男 | 36 | IS |
| 200215133 | 国良 | 男 | 40 | IS |
| 200215134 | 老宋 | 男 | 40 | IS |
| 200215135 | 光光 | 男 | 35 | IS |
| 200215136 | 王老板 | 女 | 27 | IS |
| 200215137 | 程程 | 女 | 30 | IS |
| 200215138 | 琪琪 | 男 | 29 | MA |
| 200215139 | 龙龙 | 男 | 27 | IS |
+-----------+-----------+------+------+-------+
18 rows
in
set
(0.00 sec)
|
6.此时误操作，删除了student数据库
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> drop database student;
Query OK, 3 rows affected (0.11 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows
in
set
(0.00 sec)
|
7.查看全备之后备份文件
|
1
2
3
4
5
|
[root@ocbsdb01 mysql_backup]
# cd /mysql_backup
[root@ocbsdb01 mysql_backup]
# gzip -d student_20170829_090319.sql.gz
[root@ocbsdb01 mysql_backup]
# ls
student_20170829_090319.sql
[root@ocbsdb01 mysql_backup]
# vim student_20170829_090319.sql
|
8.检查并移动binlog文件，并导出为 SQL 文件剔除其中的 drop 语句，查看 MySQL 的数据存放目录，
由下面可知是在/mysql_data下，将 binlog 文件导出SQL文件，并vim编辑它删除其中的 drop 语句。
/home/mysql/mysql5/bin/mysqlbinlog --no-defaults /tmp/mysql-bin.000004 > /tmp/04.sql
注意：在恢复全备数据之前必须将该 binlog文件移出，否则恢复过程中，会继续写入语句到 binlog，最终导致增量恢复数据部分变得比较混乱。
在转换sql的时候可能会报错，具体信息如下：
[root@ocbsdb01 tmp]# /home/mysql/mysql5/bin/mysqlbinlog /tmp/mysql-bin.000004 > /tmp/04.sql
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'
原因是mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8这个指令。
两个方法可以解决这个问题
-
在MySQL的配置/etc/my.cnf中将default-character-set=utf8 修改为 character-set-server = utf8，但是这需要重启MySQL服务，如果你的MySQL服务正在忙，那这样的代价会比较大。
-
用mysqlbinlog --no-defaults mysql-bin.000004 命令打开
9、开始恢复全备数据
|
1
2
|
[root@ocbsdb01 tmp]
# mysql -u root -p < /mysql_backup/student_20170829_090319.sql
Enter password:
|
查看数据看看，student在不在,可以看到已经在了
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| student |
| sys |
+--------------------+
5 rows
in
set
(0.00 sec)
|
11、开始恢复增量数据
使用04.sql文件恢复全备时刻到删除数据库之间新增的数据，编辑04bin.sql #删除里面的drop语句
[root@ocbsdb01 tmp]# vim 04.sql
|
1
2
3
4
5
6
7
|
将drop 操作下面的内容删除
drop database student
/*!*/;
SET @@SESSION.GTID_NEXT=
'AUTOMATIC'
/* added by mysqlbinlog */ /*!*/;
DELIMITER ;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
|
不然会报如下错误：
ERROR 1790 (HY000) at line 96: @@SESSION.GTID_NEXT cannot be changed by a client that owns a GTID.
The client owns ANONYMOUS. Ownership is released on COMMIT or ROLLBACK.
我的binlog文件具体内容如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170829 9:03:22 server id 201609 end_log_pos 123 CRC32 0x669b3a18 Start: binlog v 4, server v 5.7.18-log created 170829 9:03:22
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
Wr2kWQ+JEwMAdwAAAHsAAAABAAQANS43LjE4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ARg6m2Y=
'/*!*/;
# at 123
#170829 9:03:22 server id 201609 end_log_pos 154 CRC32 0xf909764e Previous-GTIDs
# [empty]
# at 154
#170829 9:04:42 server id 201609 end_log_pos 219 CRC32 0x754f68f2 Anonymous_GTIDlast_committed=0sequence_number=1
SET @@SESSION.GTID_NEXT=
'ANONYMOUS'
/*!*/;
# at 219
#170829 9:04:42 server id 201609 end_log_pos 294 CRC32 0xdef39415 Querythread_id=7exec_time=0error_code=0
SET TIMESTAMP=1503968682/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *
//
*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 294
#170829 9:04:42 server id 201609 end_log_pos 359 CRC32 0xad757652 Table_map: `student`.`Student` mapped to number 225
# at 359
#170829 9:04:42 server id 201609 end_log_pos 421 CRC32 0x5da9edf3 Write_rows: table id 225 flags: STMT_END_F
BINLOG '
qr2kWROJEwMAQQAAAGcBAAAAAOEAAAAAAAEAB3N0dWRlbnQAB1N0dWRlbnQABf7+
/gL
+CP4h
/jz
+
Bv48HlJ2da0=
qr2kWR6JEwMAPgAAAKUBAAAAAOEAAAAAAAEAAgAF/+AJMjAwMjE1MTM3Bueoi+eoiwPlpbMeAAJJ
U
/PtqV0
=
'/*!*/;
# at 421
#170829 9:04:42 server id 201609 end_log_pos 452 CRC32 0xfcd87186 Xid = 168
COMMIT/*!*/;
# at 452
#170829 9:04:42 server id 201609 end_log_pos 517 CRC32 0xe47a26ba Anonymous_GTIDlast_committed=1sequence_number=2
SET @@SESSION.GTID_NEXT=
'ANONYMOUS'
/*!*/;
# at 517
#170829 9:04:42 server id 201609 end_log_pos 592 CRC32 0x0d3e44d1 Querythread_id=7exec_time=0error_code=0
SET TIMESTAMP=1503968682/*!*/;
BEGIN
/*!*/;
# at 592
#170829 9:04:42 server id 201609 end_log_pos 657 CRC32 0x98d94728 Table_map: `student`.`Student` mapped to number 225
# at 657
#170829 9:04:42 server id 201609 end_log_pos 719 CRC32 0x32c7750e Write_rows: table id 225 flags: STMT_END_F
BINLOG '
qr2kWROJEwMAQQAAAJECAAAAAOEAAAAAAAEAB3N0dWRlbnQAB1N0dWRlbnQABf7+
/gL
+CP4h
/jz
+
Bv48HihH2Zg=
qr2kWR6JEwMAPgAAAM8CAAAAAOEAAAAAAAEAAgAF/+AJMjAwMjE1MTM4BueQqueQqgPnlLcdAAJN
QQ51xzI=
'/*!*/;
# at 719
#170829 9:04:42 server id 201609 end_log_pos 750 CRC32 0x92ebbf95 Xid = 169
COMMIT/*!*/;
# at 750
#170829 9:04:42 server id 201609 end_log_pos 815 CRC32 0xfe05a22e Anonymous_GTIDlast_committed=2sequence_number=3
SET @@SESSION.GTID_NEXT=
'ANONYMOUS'
/*!*/;
# at 815
#170829 9:04:42 server id 201609 end_log_pos 890 CRC32 0x551c26a4 Querythread_id=7exec_time=0error_code=0
SET TIMESTAMP=1503968682/*!*/;
BEGIN
/*!*/;
# at 890
#170829 9:04:42 server id 201609 end_log_pos 955 CRC32 0x67c477d0 Table_map: `student`.`Student` mapped to number 225
# at 955
#170829 9:04:42 server id 201609 end_log_pos 1017 CRC32 0x5d3f2503 Write_rows: table id 225 flags: STMT_END_F
BINLOG '
qr2kWROJEwMAQQAAALsDAAAAAOEAAAAAAAEAB3N0dWRlbnQAB1N0dWRlbnQABf7+
/gL
+CP4h
/jz
+
Bv48HtB3xGc=
qr2kWR6JEwMAPgAAAPkDAAAAAOEAAAAAAAEAAgAF/+AJMjAwMjE1MTM5Bum+mem+mQPnlLcbAAJJ
UwMlP10=
'/*!*/;
# at 1017
#170829 9:04:42 server id 201609 end_log_pos 1048 CRC32 0x2d5f70ba Xid = 170
COMMIT/*!*/;
# at 1048
#170829 9:06:30 server id 201609 end_log_pos 1113 CRC32 0xb8cdf9b6 Anonymous_GTIDlast_committed=3sequence_number=4
SET @@SESSION.GTID_NEXT=
'ANONYMOUS'
/*!*/;
# at 1113
#170829 9:06:30 server id 201609 end_log_pos 1214 CRC32 0x69d17a84 Querythread_id=7exec_time=0error_code=0
SET TIMESTAMP=1503968790/*!*/;
drop database student
/*!*/;
SET @@SESSION.GTID_NEXT=
'AUTOMATIC'
/* added by mysqlbinlog */ /*!*/;
DELIMITER ;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
|
调整好后开始恢复增量数据
[root@ocbsdb01 tmp]# mysql -u root -p < 04.sql
Enter password:
再次查看数据库，发现全备份到删除数据库之间的那三条数据也恢复了!!
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
mysql>
select
* from Student;
+-----------+-----------+------+------+-------+
| Sno | Sname | Ssex | Sage | Sdept |
+-----------+-----------+------+------+-------+
| 200215121 | 李勇 | 男 | 20 | CS |
| 200215122 | 刘晨 | 女 | 19 | CS |
| 200215123 | 王敏 | 女 | 18 | MA |
| 200215125 | 张立 | 女 | 19 | IS |
| 200215126 | 虎威 | 男 | 25 | CS |
| 200215127 | 魏大师 | 男 | 35 | IS |
| 200215128 | 老谢 | 男 | 33 | MA |
| 200215129 | 小贾 | 男 | 30 | CS |
| 200215130 | 会民 | 男 | 23 | CS |
| 200215131 | 陈兴 | 男 | 33 | MA |
| 200215132 | 阿帆 | 男 | 36 | IS |
| 200215133 | 国良 | 男 | 40 | IS |
| 200215134 | 老宋 | 男 | 40 | IS |
| 200215135 | 光光 | 男 | 35 | IS |
| 200215136 | 王老板 | 女 | 27 | IS |
| 200215137 | 程程 | 女 | 30 | IS |
| 200215138 | 琪琪 | 男 | 29 | MA |
| 200215139 | 龙龙 | 男 | 27 | IS |
+-----------+-----------+------+------+-------+
18 rows
in
set
(0.00 sec)
|
以上就是MySQL 数据库增量数据恢复的实例过程!如有不足还请指正
本文转自 yuri_cto 51CTO博客，原文链接：http://blog.51cto.com/laobaiv1/1960846，如需转载请自行联系原作者
