欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-11-15 15:20:21
文章目录第1章 当前生产环境和故障说明1.1 生产业务库数1.2 我的环境说明1.3 数据定期备份1.4 生产故障说明1.5 解决思路说明第1章 当前生产环境和故障说明1.1 生产业务库数01:当前生产环境的MySQL中存在多个业务库(名称分别为: chenliang 和 lili )02:web帐号1:www@'172.16.1.%' chenliang.* select,insert,update,delete03:web帐号2:lili@'172.16.1.%' lili....

第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 生产故障说明

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

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命令,我已经下载了,如下所示:
用mysqldump备份的数据结合binlog解决drop表的故障二
用客户端工具(我这用的是sqlyog)连接mysql 3307实例。
用mysqldump备份的数据结合binlog解决drop表的故障二
选择"chenliang"库—>右键—>导入---->执行SQL脚本。后面的操作如图所示:
用mysqldump备份的数据结合binlog解决drop表的故障二
用mysqldump备份的数据结合binlog解决drop表的故障二
用mysqldump备份的数据结合binlog解决drop表的故障二
用mysqldump备份的数据结合binlog解决drop表的故障二
用mysqldump备份的数据结合binlog解决drop表的故障二
将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文件内容。
用mysqldump备份的数据结合binlog解决drop表的故障二
把/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

相关标签: # mysql 5.7.X mysql