[MySQL]-删库后恢复

[MySQL]-删库后恢复

sen格 | 2022年11月

本文旨在记录个人在数据库的删库恢复演练过程中的一些总结，如有不足，欢迎指正。

---

一、恢复场景

1）假设生产实例MySQL端口为：3306

2）本地实例MySQL端口为：3307

在这里的两个端口分别模拟生产和本地数据库

步骤一

在3306上创建数据库tpcc

mysql3306>create database tpcc;
1

查看当前数据库

mysql3306>show databases;
1

创建表

在tpcc数据库上创建9张表，使用tpcc-mysql自带的建表语句

mysql3306>use tpcc;
mysql3306>source /usr/local/tpcc-mysql/create_table.sql
1
2

create_table.sql 文件内容

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

drop table if exists warehouse;

create table warehouse (
w_id smallint not null,
w_name varchar(10), 
w_street_1 varchar(20), 
w_street_2 varchar(20), 
w_city varchar(20), 
w_state char(2), 
w_zip char(9), 
w_tax decimal(4,2), 
w_ytd decimal(12,2),
primary key (w_id) ) Engine=InnoDB;

drop table if exists district;

create table district (
d_id tinyint not null, 
d_w_id smallint not null, 
d_name varchar(10), 
d_street_1 varchar(20), 
d_street_2 varchar(20), 
d_city varchar(20), 
d_state char(2), 
d_zip char(9), 
d_tax decimal(4,2), 
d_ytd decimal(12,2), 
d_next_o_id int,
primary key (d_w_id, d_id) ) Engine=InnoDB;

drop table if exists customer;

create table customer (
c_id int not null, 
c_d_id tinyint not null,
c_w_id smallint not null, 
c_first varchar(16), 
c_middle char(2), 
c_last varchar(16), 
c_street_1 varchar(20), 
c_street_2 varchar(20), 
c_city varchar(20), 
c_state char(2), 
c_zip char(9), 
c_phone char(16), 
c_since datetime, 
c_credit char(2), 
c_credit_lim bigint, 
c_discount decimal(4,2), 
c_balance decimal(12,2), 
c_ytd_payment decimal(12,2), 
c_payment_cnt smallint, 
c_delivery_cnt smallint, 
c_data text,
PRIMARY KEY(c_w_id, c_d_id, c_id) ) Engine=InnoDB;

drop table if exists history;

create table history (
h_c_id int, 
h_c_d_id tinyint, 
h_c_w_id smallint,
h_d_id tinyint,
h_w_id smallint,
h_date datetime,
h_amount decimal(6,2), 
h_data varchar(24) ) Engine=InnoDB;

drop table if exists new_orders;

create table new_orders (
no_o_id int not null,
no_d_id tinyint not null,
no_w_id smallint not null,
PRIMARY KEY(no_w_id, no_d_id, no_o_id)) Engine=InnoDB;

drop table if exists orders;

create table orders (
o_id int not null, 
o_d_id tinyint not null, 
o_w_id smallint not null,
o_c_id int,
o_entry_d datetime,
o_carrier_id tinyint,
o_ol_cnt tinyint, 
o_all_local tinyint,
PRIMARY KEY(o_w_id, o_d_id, o_id) ) Engine=InnoDB ;

drop table if exists order_line;

create table order_line ( 
ol_o_id int not null, 
ol_d_id tinyint not null,
ol_w_id smallint not null,
ol_number tinyint not null,
ol_i_id int, 
ol_supply_w_id smallint,
ol_delivery_d datetime, 
ol_quantity tinyint, 
ol_amount decimal(6,2), 
ol_dist_info char(24),
PRIMARY KEY(ol_w_id, ol_d_id, ol_o_id, ol_number) ) Engine=InnoDB ;

drop table if exists item;

create table item (
i_id int not null, 
i_im_id int, 
i_name varchar(24), 
i_price decimal(5,2), 
i_data varchar(50),
PRIMARY KEY(i_id) ) Engine=InnoDB;

drop table if exists stock;

create table stock (
s_i_id int not null, 
s_w_id smallint not null, 
s_quantity smallint, 
s_dist_01 char(24), 
s_dist_02 char(24),
s_dist_03 char(24),
s_dist_04 char(24), 
s_dist_05 char(24), 
s_dist_06 char(24), 
s_dist_07 char(24), 
s_dist_08 char(24), 
s_dist_09 char(24), 
s_dist_10 char(24), 
s_ytd decimal(8,0), 
s_order_cnt smallint, 
s_remote_cnt smallint,
s_data varchar(50),
PRIMARY KEY(s_w_id, s_i_id) ) Engine=InnoDB ;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141

步骤二

删除数据库tpcc

mysql3306>drop database tpcc;
1

步骤三

库都删除了，那下面就是恢复了

二、恢复！

在恢复之前，我们肯定要查看现在使用的binlog，因为场景比较简单，所以创建库和删除库大差不差的在一个binlog里。

查看当前正在写入的binlog文件：

mysql3306>show master status;         
1

由上图，我们得知当前正在写mysql-bin.000017这个文件

还可以查看binlog文件列表：

mysql3306>show binary logs;
1

既然如此了，我们先拷贝出来一份到别的目录，不然生产的文件被搞坏，那就准备跑路吧~

centos7>cp /usr/local/mysql/data/binlog/mysql-bin.000017 /data/bakcup/mysql-bin.000017
1

简单查看下是否有数据库创建和删除语句

centos7>cd /data/bakcup/

backup>mysqlbinlog mysql-bin.000017 | grep -n  "create database"

backup>mysqlbinlog mysql-bin.000017 | grep -n  "drop database"
1
2
3
4
5

因为场景比较单一，所以我们这里可以使用 --start-position 和 --stop-position 两个参数来恢复数据

查看 mysql-bin.000017，binlog为二进制类型不能直接查看，借助mysqlbinlog工具导致查看

backup>mysqlbinlog --set-charset=utf8mb4 mysql-bin.000017 > tmp.sql
1

然后我们可以在vim里查找

backup>vim tmp.sql
1

按 / ，输入create database tpcc，查看开始位置（由图的开始位置6162）

按 / ，输入drop database tpcc，查看结束位置（有图得结束位置为11841）

根据这个binlog的翻译文件，查看完开始和结束位置后，我们就可以对binlog进行截取

backup>mysqlbinlog -v mysql-bin.000017 --start-position=6162 --stop-position=11841 > tmp_tpcc.sql
1

在本地实例上执行

backup>mysql -P3307 0-uroot -p < tmp_tpcc.sql
1

查看后，我们发现3307端口的数据库已经有了tpcc数据库和创建表了

下面我们用mysqldump来把tpcc这个库拉出来

backup>mysqldump -uroot -p -h 127.0.0.1 -P3307 --single-transaction --skip-lock-tables --default-character-set=utf8mb4 --set-gtid-purged=OFF > back_tpcc_3307.sql
1

最后就是通知业务不要对生产库做操作，以免影响业务写入数据被刷

然后开始恢复

backup>mysql -P3306 -uroot -p -h 127.0.0.1 < back_tpcc_3307.sql
1

最后结果：

三、总结

该案例比较简单，在实际生产过程中还是要根据场景具体分析，但是恢复过程以及原理和本文大致相似。

在这里积极欢迎各位大佬指出不足之处。