用mysqldump备份的数据结合binlog解决drop表的故障二

文章目录

• 第1章 当前生产环境和故障说明
• 第2章 测试数据准备及故障模拟
• 第3章 数据恢复详细步骤

第1章 当前生产环境和故障说明

1.1 生产业务库数

01：当前生产环境的MySQL中存在多个业务库（名称分别为:  chenliang 和 lili ）

02：web帐号1：www@'172.16.1.%'   chenliang.*    select,insert,update,delete

03：web帐号2：lili@'172.16.1.%'  lili.*         select,insert,update,delete

04：MySQL版本为5.7.28，开了Gtid的,开了binlog的,模式为row模式;

1.2 我的环境说明

3306实例   当作是生产环境    只有多个业务库chenliang 和 lili;

3307实例   是我的测试环境    我先搭好了,用于后面先把数据恢复到测试环境；

1.3 数据定期备份

这个主要是针对3306实例(生产环境嘛)的哈,数据量不是很大(7个G左右),所以数据的定
时备份策略是：每天凌晨2点整对chenliang库进行了分库分表备份，采用mysqldump工
具+shell+crond来实现。

1.4 生产故障说明

1.5 解决思路说明

"#### 第一阶段"
01：找到被drop掉了的表的全备数据文件;
    A：复制一份到其它目录下,防止损坏备份数据;
    B：解压数据文件,找到备份时记录的binlog信息(例如记录的是：21_mysql_bin.000002)

02：找到增量数据文件(binlog文件);
    A：登录mysql实例,查看当前使用的是哪个binlog文件（例如是：21_mysql_bin.000005）
       记录下来,然后重新生成binlog文件(那最新的就binlog文件就是：21_mysql_bin.000006)
       然后退出mysql实例的登录;
    B：在操作系统下复制相应的binlog文件到其它目录下,复制的文件有哪些呢?如下所示：
       21_mysql_bin.000002 至 21_mysql_bin.000005
    C：对复制的21_mysql_bin.000002 至 21_mysql_bin.000005 文件做处理;
      (1)：因我生产有多个业务库,所以在处理时得用mysqlbinlog工具的-d参数了；
      (2)：因为我生产中开了GTID功能,所以在处理时要用mysqlbinlog工具的--skip-gtids参数
      (3)：因为mysqlbinlog工具不支持过滤出某一张表的相关binlog信息,所以放弃这个想法,
           网上说是用grep来过过滤出某张表的相关binlog,不实用,因为我的mysql的binlog
           模式是row模式,而不是Statement模式。
      (4)：所以我最终的处理这些文件的命令如下所示：
           mysqlbinlog -d chenliang --skip-gtids 21_mysql_bin.* >a.sql
      (5)：因为产生故障的命令是 drop table 表名;所以得在a.sql文件中注释或删除这条语句


"#### 第二阶段：在自己的测试环境上操作    <== 注意是在测试环境下哈"
01：创建一个测试库,名称要和你的生产业务库名称一样,我这里就是chenliang
    create database chenliang; show databases like "chenliang";

02：将"第一阶段"找到的某张表的全备数据文件恢复到chenliang库下;
    mysql -uroot -pchenliang -S /data/mysql/3307/run/mysql.sock chenliang <表的全备数据文件

03：将"第一阶段"处理的增量数据文件a.sql下载到你的桌面上;然后在测试环境导入sql脚本,
    将"发生错误时退出"选项去掉,然后点击执行,即使报错,你也点击"确定"。

04：查看测试环境下chenliang库下表中的数据

05：将测试环境(mysql 3307)实例中的chenliang.test1表(表结构+表数据)进行单独的逻辑数据备份


"#### 第三阶段：向生产环境恢复数据"
将"第二阶段"的备份数据恢复到生产环境，恢复命令如下所示：
mysql -uroot -pchenliang -S /data/mysql/3306/run/mysql.sock chenliang <"第二阶段"的备份数据

第2章 测试数据准备及故障模拟

2.1 chenliang业务库模拟

基础数据

#### 创建chenliang库(字符集为utf8字符集),并进入到chenliang库下面
create database if not exists chenliang character set utf8 collate utf8_general_ci;
use chenliang;
select database();


#### 创建test1表,并插入几条数据
create table if not exists test1(
  id int unsigned not null auto_increment comment"序列号",
  name varchar(20) not null comment"姓名",
  primary key(id)
)engine=innodb character set utf8 collate utf8_general_ci comment"测试表1";

insert into test1(name) values
("chenliang01"),("chenliang02"),("chenliang03"),("chenliang04"),("chenliang05"),
("chenliang06"),("chenliang07"),("chenliang08"),("chenliang09"),("chenliang10");
commit;

select * from test1;


#### 创建test2表,并插入几条数据
create table if not exists test2(
  id int unsigned not null auto_increment comment"序列号",
  age tinyint not null comment"年龄",
  primary key(id)
)engine=innodb character set utf8 collate utf8_general_ci comment"测试表2";

insert into test2(age) values
 (11), (12), (13), (14), (15),(16), (17), (18), (19), (20);
commit;

select * from test2;

web帐号的创建

#### 用root用户进行创建
[root@node21 ~]# mysql -uroot -pchenliang -S /data/mysql/3306/run/mysql.sock
mysql>
mysql>
mysql>create user 'www'@'172.16.1.%' identified by "www123";
mysql>
mysql>grant select,update,delete,insert on chenliang.* to 'www'@'172.16.1.%';
mysql>
mysql>
mysql>exit
Bye


#### 用www用户连接后,查看所拥有的库
[root@node21 3306]# mysql -uwww -pwww123 -h172.16.1.21 -P3306 -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| chenliang          |
+--------------------+

2.2 lili业务库的基础模拟

基础数据

#### 创建lili库(字符集为utf8字符集),并进入到lili库下面
create database if not exists lili character set utf8 collate utf8_general_ci;
use lili;
select database();


#### 创建li1表,并插入几条数据
create table if not exists li1(
  id int unsigned not null auto_increment comment"序列号",
  name varchar(20) not null comment"姓名",
  primary key(id)
)engine=innodb character set utf8 collate utf8_general_ci comment"测试表1";

insert into li1(name) values
("lili01"),("lili02"),("lili03"),("lili04"),("lili05"),
("lili06"),("lili07"),("lili08"),("lili09"),("lili09");
commit;

select * from li1;


#### 创建li2表,并插入几条数据
create table if not exists li2(
  id int unsigned not null auto_increment comment"序列号",
  age tinyint not null comment"年龄",
  primary key(id)
)engine=innodb character set utf8 collate utf8_general_ci comment"测试表2";

insert into li2(age) values
(11), (12), (13), (14), (15),(16), (17), (18), (19), (20);
commit;

select * from li2;

web帐号的创建

#### 用root用户进行创建
[root@node21 ~]# mysql -uroot -pchenliang -S /data/mysql/3306/run/mysql.sock
mysql>
mysql>
mysql>create user 'lili'@'172.16.1.%' identified by "lili123";
mysql>
mysql>grant select,update,delete,insert on lili.* to 'lili'@'172.16.1.%';
mysql>
mysql>
mysql>exit
Bye


#### 用lili@172.16.1.%用户连接后,查看所拥有的库
[root@node21 3306]# mysql -ulili -plili123 -h172.16.1.21 -P3306 -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lili               |
+--------------------+

2.3 常规/平时的数据备份

我平时对数据的备份都是对数据做分库分表逻辑全备。我这里是手动备份，生产肯定是自动进行备份。

到了凌晨两点整了

#### 注释掉定时任务中定时更新系统时间的任务
[root@node21 ~]# crontab -l|head -2
# Crond update os time. USER:chenliang TIME:2020-01-01
#*/05 * * * * /bin/sh /server/scripts/update_os_time.sh >/dev/null 2>&1

#### 调整系统时间为凌晨2点整
[root@node21 ~]# date                            # 当前时间
2020年 09月 24日 星期四 00:38:07 CST 
[root@node21 ~]# date -s "2020-09-24 02:00:00"   # 修改系统时间为第二天的凌晨2点
2020年 03月 26日 星期四 02:00:00 CST

对chenliang业务库做分表备份

#### 对chenliang库下的每张表进行逻辑全备（这里是手动,生产肯定就是自动的了）
mysqldump --no-defaults -uroot -pchenliang -S /data/mysql/3306/run/mysql.sock \
--set-gtid-purged=off -F --master-data=2 -x \
--skip-add-drop-table --skip-extended-insert --skip-triggers \
chenliang test1|gzip >/tmp/chenliang-library-test1-table-info-data.sql.gz

mysqldump --no-defaults -uroot -pchenliang -S /data/mysql/3306/run/mysql.sock \
--set-gtid-purged=off -F --master-data=2 -x \
--skip-add-drop-table --skip-extended-insert --skip-triggers \
chenliang test2|gzip >/tmp/chenliang-library-test2-table-info-data.sql.gz

[root@node21 ~]# ll /tmp/chenliang*
-rw-r--r-- 1 root root 922 9月  24 02:00 /tmp/chenliang-library-test1-table-info-data.sql.gz
-rw-r--r-- 1 root root 914 9月  24 02:00 /tmp/chenliang-library-test2-table-info-data.sql.gz

对lili业务库做分表备份

#### 对lili库下的每张表进行逻辑全备（这里是手动,生产肯定就是自动的了）
mysqldump --no-defaults -uroot -pchenliang -S /data/mysql/3306/run/mysql.sock \
--set-gtid-purged=off -F --master-data=2 -x \
--skip-add-drop-table --skip-extended-insert --skip-triggers \
lili li1|gzip >/tmp/lili-library-li1-table-info-data.sql.gz

mysqldump --no-defaults -uroot -pchenliang -S /data/mysql/3306/run/mysql.sock \
--set-gtid-purged=off -F --master-data=2 -x \
--skip-add-drop-table --skip-extended-insert --skip-triggers \
lili li2|gzip >/tmp/lili-library-li2-table-info-data.sql.gz

[root@node21 ~]# ll /tmp/lili*
-rw-r--r-- 1 root root 909 9月  24 02:00 /tmp/lili-library-li1-table-info-data.sql.gz
-rw-r--r-- 1 root root 904 9月  24 02:01 /tmp/lili-library-li2-table-info-data.sql.gz

2.4 期间还有数据在产生

注意：我这里模拟的是insert操作,生产中可能还有delete、update操作，可能管理员还会有DDL语句的操作。

## 以下是对两个业务库下的每张表进行了insert操作,都记录到了一个binlog文件中
insert into chenliang.test1(name) values
("chenliang11"),("chenliang12"),("chenliang13"),("chenliang14"),("chenliang15");
commit;

insert into chenliang.test2(age) values
(21), (22), (23), (24), (25);
commit;

insert into lili.li1(name) values
("lili11"),("lili12"),("lili13"),("lili14"),("lili15");
commit;

insert into lili.li2(age) values
(21),(22),(23),(24),(25);
commit;

flush logs;


## 以下是对两个业务库下的每张表进行了insert操作,都记录到了一个binlog文件中
insert into chenliang. test1(name) values
("chenliang16"),("chenliang17"),("chenliang18"),("chenliang19"),("chenliang20");
commit;

insert into chenliang.test2(age) values
(26),(27),(28),(29),(30);
commit;


insert into lili.li1(name) values
("lili16"),("lili17"),("lili18"),("lili19"),("lili20");
commit;

insert into lili.li2(age) values
(26),(27),(28),(29),(30);
commit;

flush logs;

2.5 某个库下的某张表表被drop掉了

注意：这里是chenliang业务库下的test1表被drop掉了，在binlog中只有一句明文sql语
句，不会记录表被drop前，表中有哪些数据。

到了下午14:00:00

[root@node21 ~]# date                         # 当前时间
2020年 09月 24日 星期四 02:07:16 CST

[root@node21 ~]# date -s "2020-09-24 14:00:00"     # 到了14:00
2020年 03月 26日 星期四 14:00:00 CST

[root@node21 ~]# date                          
2020年 09月 24日 星期四 14:00:04 CST

chenliang业务库下的test1表被drop掉了

#### chenliang业务库下的test1表被drop掉了
[root@node21 ~]# mysql -uroot -pchenliang -S /data/mysql/3306/run/mysql.sock
mysql>
mysql> use chenliang;
Database changed
mysql>
mysql> show tables like "test1";
+-----------------------------+
| Tables_in_chenliang (test1) |
+-----------------------------+
| test1                       |
+-----------------------------+
1 row in set (0.00 sec)
mysql>
mysql> drop table test1;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> show tables like "test1";
Empty set (0.00 sec)


#### 用chenliang库的web帐号连接后查看test1表中的数据,肯定是查询不到的
[root@node21 3306]# mysql -uwww -pwww123 -h172.16.1.21 -P3306 -e "select * from chenliang.test1;"
ERROR 1146 (42S02) at line 1: Table 'chenliang.test1' doesn't exist

第3章 数据恢复详细步骤

3.1 找到全备数据文件

这里是找到chenliang业务库test1表的全备数据文件,将其复制一份到其它目录下进行操
作(解压,找到备份时记录的binlog信息)

#### 对chenliang库下的test1表的全备数据文件做备份,防止损坏源文件
[root@node21 ~]# cp -a /tmp/chenliang-library-test1-table-info-data.sql.gz /mnt/
[root@node21 ~]# ll /mnt/chenliang-library-test1-table-info-data.sql.gz 
-rw-r--r-- 1 root root 922 9月  24 02:00 /mnt/chenliang-library-test1-table-info-data.sql.gz

#### 解压备份文件,找到binlog文件信息
[root@node21 ~]# cd /mnt/
[root@node21 mnt]# gzip -d chenliang-library-test1-table-info-data.sql.gz 
[root@node21 mnt]# grep -i "change master to" chenliang-library-test1-table-info-data.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='21_mysql_bin.000002', MASTER_LOG_POS=194;
 
  最终的结果是：文件是：21_mysql_bin.000002  POS点是：154

3.2 找到增量数据文件

#### 查看当前mysql 3306实例所在的binlog文件
[root@node21 ~]# mysql -uroot -pchenliang -S /data/mysql/3306/run/mysql.sock 
mysql>
mysql> show master status\G
*************************** 1. row ***************************
             File: 21_mysql_bin.000007    # 当前的binlog文件
         Position: 387
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 6f108d1f-f133-11ea-93a4-000c29e5fcf9:1-35
1 row in set (0.00 sec)

mysql> flush logs;                        # 重新生成一个binlog文件
Query OK, 0 rows affected (0.01 sec)

mysql> show master status\G               # 查看重新成生的binlog文件
*************************** 1. row ***************************
             File: 21_mysql_bin.000008
         Position: 194
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 6f108d1f-f133-11ea-93a4-000c29e5fcf9:1-35
1 row in set (0.00 sec)


#### 当前mysql中所有的binlog文件
[root@node21 ~]# ll /data/mysql/3306/logs/binlog/
总用量 44
-rw-r----- 1 mysql mysql 10525 9月  24 02:00 21_mysql_bin.000001
-rw-r----- 1 mysql mysql   244 9月  24 02:00 21_mysql_bin.000002
-rw-r----- 1 mysql mysql   244 9月  24 02:00 21_mysql_bin.000003
-rw-r----- 1 mysql mysql   244 9月  24 02:01 21_mysql_bin.000004
-rw-r----- 1 mysql mysql  2000 9月  24 02:05 21_mysql_bin.000005
-rw-r----- 1 mysql mysql  2001 9月  24 02:05 21_mysql_bin.000006
-rw-r----- 1 mysql mysql   437 9月  24 14:05 21_mysql_bin.000007
-rw-r----- 1 mysql mysql   194 9月  24 14:05 21_mysql_bin.000008
-rw-r----- 1 mysql mysql   392 9月  24 14:05 21_mysql_bin.index
   PS：后面要把21_mysql_bin.000002 至 21_mysql_bin.000007 文件拷贝走


#### 复制相应的binlog文件,到/tmp目录下
[root@node21 ~]# cp -a /data/mysql/3306/logs/binlog/21_mysql_bin.00000{2..7} /tmp/
[root@node21 ~]# ll /tmp/21_mysql_bin.*
-rw-r----- 1 mysql mysql  244 9月  24 02:00 /tmp/21_mysql_bin.000002
-rw-r----- 1 mysql mysql  244 9月  24 02:00 /tmp/21_mysql_bin.000003
-rw-r----- 1 mysql mysql  244 9月  24 02:01 /tmp/21_mysql_bin.000004
-rw-r----- 1 mysql mysql 2000 9月  24 02:05 /tmp/21_mysql_bin.000005
-rw-r----- 1 mysql mysql 2001 9月  24 02:05 /tmp/21_mysql_bin.000006
-rw-r----- 1 mysql mysql  437 9月  24 14:05 /tmp/21_mysql_bin.000007


#### 对/tmp目录下的binlog文件进行处理,处理成sql文件
[root@node21 ~]# cd /tmp/
[root@node21 tmp]# mysqlbinlog -d chenliang --skip-gtids 21_mysql_bin.* >a.sql 
[root@node21 tmp]# ll a.sql 
-rw-r--r-- 1 root root 8143 9月  24 14:07 a.sql


##### 将a.sql文件中drop table test1 这个语句给注释掉 
[root@node21 tmp]# grep -ni "drop table \`test1\`" a.sql   # 查看drop table `test1`语句在多少行
212:DROP TABLE `test1` /* generated by server */

[root@node21 tmp]# tail a.sql                         # 将其注释后的结果
#SET TIMESTAMP=1600927311/*!*/; 
#SET @@session.pseudo_thread_id=18/*!*/;
#DROP TABLE `test1` /* generated by server */            <== 可看到已被注释掉了
/*!*/;
# at 387
#200924 14:05:27 server id 21  end_log_pos 437 CRC32 0x4b9502f8 	Rotate to 21_mysql_bin.000008  pos: 4
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;


#### 将a.sql文件复制到/mnt目录下
[root@node21 tmp]# cp -a a.sql /mnt/
[root@node21 tmp]# ll /tmp/a.sql 
-rw-r--r-- 1 root root 8147 9月  24 14:13 /tmp/a.sql


#### 目前准备的数据如下所示
[root@node21 tmp]# ll /mnt/*
-rw-r--r-- 1 root root 8147 9月  24 14:13 /mnt/a.sql
-rw-r--r-- 1 root root 2499 9月  24 02:00 /mnt/chenliang-library-test1-table-info-data.sql

注意：不要直接拿着这些数据往生产环境冲，先在测试环境(3307 实例)里面过一到;
01：先将/mnt/chenliang-library-test1-table-info-data.sql恢复到测试环境
02：再将/mnt/a.sql恢复到测试环境

3.3 测试环境恢复数据

我这里的测试环境就是3307实例了哈，我先创建一个库，这个库名一定要和你生产的业
务库名称一样。因为我不小心把3306实例(生产环境)业务库chenliang下的test1表给drop
掉了，所以我在测试环境也创建chenliang业务库。

mysql -uroot -pchenliang -S /data/mysql/3307/run/mysql.sock
  create database chenliang;
  show databases like "chenliang";
exit

将chenliang库的test1表的全备数据文件先恢复到测试环境的chenliang库下去。

mysql -uroot -pchenliang -S /data/mysql/3307/run/mysql.sock chenliang </mnt/chenliang-library-test1-table-info-data.sql                                    # 指定了库名的哈

[root@node21 ~]# mysql -uroot -pchenliang -S /data/mysql/3307/run/mysql.sock -e "select * from chenliang.test1;"
+----+-------------+
| id | name        |
+----+-------------+
|  1 | chenliang01 |
|  2 | chenliang02 |
|  3 | chenliang03 |
|  4 | chenliang04 |
|  5 | chenliang05 |
|  6 | chenliang06 |
|  7 | chenliang07 |
|  8 | chenliang08 |
|  9 | chenliang09 |
| 10 | chenliang10 |
+----+-------------+

将/mnt/a.sql文件下载到个人PC桌面上，我用的是sz命令,我已经下载了,如下所示：

用客户端工具(我这用的是sqlyog)连接mysql 3307实例。

选择"chenliang"库—>右键—>导入---->执行SQL脚本。后面的操作如图所示：





将mysql 3307 实例中的chenliang库下的test1表(表结构+表数据)进行逻辑备份。如下：

mysqldump --no-defaults -uroot -pchenliang -S /data/mysql/3307/run/mysql.sock \
--set-gtid-purged=off -F --master-data=2 -x     \
--skip-add-drop-table --skip-extended-insert --skip-triggers   \
chenliang test1|gzip >/chenliang-library-test1-table-info-all-data.sql.gz 

[root@node21 ~]# ll /chenliang-library-test1-table-info-all-data.sql.gz 
-rw-r--r-- 1 root root 983 9月  24 14:31 /chenliang-library-test1-table-info-all-data.sql.gz

 "PS：这个文件里面的数据后面就直接恢复到生产环境（mysql 3306实例的chenliang库下）"

3.4 生产环境恢复数据

复制从测试环境备份的数据,并进行解压；

[root@node21 ~]# mkdir tools
[root@node21 ~]# cd tools/
[root@node21 tools]# cp -a /chenliang-library-test1-table-info-all-data.sql.gz ./
[root@node21 tools]# ll chenliang-library-test1-table-info-all-data.sql.gz 
-rw-r--r-- 1 root root 983 9月  24 14:31 chenliang-library-test1-table-info-all-data.sql.gz

[root@node21 tools]# gzip -d chenliang-library-test1-table-info-all-data.sql.gz 
[root@node21 tools]# ll chenliang-library-test1-table-info-all-data.sql 
-rw-r--r-- 1 root root 2969 9月  24 14:31 chenliang-library-test1-table-info-all-data.sql

我们看一看/root/tools/chenliang-library-test1-table-info-all-data.sql文件内容。

把/root/tools/chenliang-library-test1-table-info-all-data.sql文件中的数据恢复到生产环境
(mysql 3306实例)，要指定生产业务库哈。

[root@node21 ~]# mysql -uroot -pchenliang -S /data/mysql/3306/run/mysql.sock  chenliang  </root/tools/chenliang-library-test1-table-info-all-data.sql 
[root@node21 ~]#

[root@node21 ~]# mysql -uroot -pchenliang -S /data/mysql/3306/run/mysql.sock  -e "select * from chenliang.test1;"
+----+-------------+
| id | name        |
+----+-------------+
|  1 | chenliang01 |
|  2 | chenliang02 |
|  3 | chenliang03 |
|  4 | chenliang04 |
|  5 | chenliang05 |
|  6 | chenliang06 |
|  7 | chenliang07 |
|  8 | chenliang08 |
|  9 | chenliang09 |
| 10 | chenliang10 |
| 11 | chenliang11 |
| 12 | chenliang12 |
| 13 | chenliang13 |
| 14 | chenliang14 |
| 15 | chenliang15 |
| 16 | chenliang16 |
| 17 | chenliang17 |
| 18 | chenliang18 |
| 19 | chenliang19 |
| 20 | chenliang20 |
+----+-------------+

PS：到止,被drop掉的test1表的数据已成功恢复到生产环境。

本文地址：https://blog.****.net/weixin_43733154/article/details/108764517