| 实例 | 状态 | 角色 |
|---|---|---|
| 3308 | 未宕机,单实例 | 发生误操作实例 |
| 3312 | 新实例 | 作为恢复实例 |
版本：Server version: 5.7.19-log MySQL Community Server (GPL)
关键参数：
binlog_gtid_simple_recovery =ON
binlog_format=row
gtid_mode=on
实验思路：
单实例无主从的3308，存有某一时刻得mysqldump的逻辑备份，和后续时间点的所有binlog日志。
在之后的操作中发生drop table sbtest1 误操作
恢复办法：
概述：使用mysqldump创建一个3308实例的从库（3312），在复制的时候只针对表sbtest1 的复制并停在drop
table事物前时刻。之后再将3312上恢复的表恢复到3308上
操作记录
1 3308数据状态
3308数据库现状，图中没有sbtest1实验表因为，该图为完成drop table候后截取因此show tables没有sbtest1表
sbtest1数据
2进行逻辑全备
mysqldump -S /data/mysql/mysql3308/mysql3308.sock -uroot -p —single-transaction —master-data=2 -A > full_back.sql
3进行数据破坏
drop table sbtest1；
发现该现象后及时进行flush logs；(本实验中进行了两次flush操作)
查看此时binlog 状态，确定为mysql-bin.000042中进行了drop table 操作
4解析binlog
mysqlbinlog -v --base64-output=decode-rows mysql-bin.000042 >42.sql
从解析文件中找到drop table 操作并找到对应的gtid 或者log-pos;
结论：
gtid位置为：set @@session.GTID_NETX=’e2b6….dea:111’
log_pos=1648(在使用非GTID时使用)
特别注意：
图中关于log_pos两个值 1617 和1648 必须选择1648为恢复终点
如果使用解析分别到两个值的结果是1617 最后会生成一个rollback。1648 会是正确的commit
1648的解析
1617解析
5创建3312实例
如果创建实例再次不做复述
但是需要特别注意的是需要创建特殊需要导入备份的用户
因为本次的3312 实例上只针对问题表sbtest1 进行恢复不对其他表数据恢复，复制以便缩短时间，因此该用户只针对sytest.sbtest1 有权限
先在3312上创建systes.sbtest1 表
创建用户并赋权
create user 'user1'@'%' identified by 'user1';
grant select,insert,update,delete on systest.sbtest1 to 'user1'@'%';
6将备份导入到3312中
注意需要实用-f强制导入
mysql -S /data/mysql/mysql3312/mysql3312.sock -uuser1 -puser1 -f < full_bk.sql
期间会因为权限不足会报错
此时只恢复了sbtest1的数据，且此时增长的是3312的gtid
7在进行创建主从前进行修改execute_gtid_set
从备份中获取gtid
设置executed_gtid_set
"root@localhost:mysql3312.sock [(none)]>reset master;
Query OK, 0 rows affected (0.06 sec)
"root@localhost:mysql3312.sock [(none)]>SET @@GLOBAL.GTID_PURGED='83bc1292-48fe-11e8-b4e2-000c29f1c412:1,
8f6c5d66-d12a-11e7-a0ff-000c29817dea:1-61,
ab5e28fb-bea0-11e7-aea5-000c29817dea:1-8,
c4a1c232-bef2-11e7-a9c0-000c29f1c412:1-5,
e2b6a2cf-2e3c-11e8-a9cd-000c29817dea:1-104,
fc94fc0c-d1d4-11e7-96ad-000c29817dea:120424-120454';
Query OK, 0 rows affected (0.01 sec)
8搭建主从
"root@localhost:mysql3312.sock [(none)]>change master to
-> master_host='172.0.0.51',
-> master_port=3308,
-> master_user='repl3307',
-> master_password='repl',
-> master_auto_position=1;
9更改过滤规则
只针对systest.sbtest1进行复制
"root@localhost:mysql3312.sock [(none)]>change replication filter replicate_do_table=(systest.sbtest1);
Query OK, 0 rows affected (0.00 sec)
10开始同步
注意点：将sql_thread 停止在drop 操作前
无法控制io_thread 停止在哪个位置
"root@localhost:mysql3312.sock [systest]>start slave sql_thread until sql_before_gtids='e2b6a2cf-2e3c-11e8-a9cd-000c29817dea:110';
如果没有开io_htread 需要将io_thread 开启
start slave io_thread;
检查结果
11.将3312表恢复到3308上
在3312 表数据恢复后，就需要将表数据导回3308中
注意：如果直接把表恢复到3308，业务会直接写如该表，造成数据混乱。在未判断出直接将表恢复进去没有影响前应先将表rename：
判断的情况：1如果是drop table：可能还好直接把表恢复好继续业务就行
2.如果是truncate table。在发生错误时应对的方法可能是把表rename ，禁止写入。也有可能只是将主键跳跃大一个非常的的值以便区分发生错误的截点。这样错误表会依然会写入，如果用表传输可能会出问题。mysqldump也需要分析后在导入
11.1rename table
"root@localhost:mysql3312.sock [systest]>rename table sbtest1 to sbtest_bk
-> ;
Query OK, 0 rows affected (0.05 sec)
"root@localhost:mysql3312.sock [systest]>show tables;
+-------------------+
| Tables_in_systest |
+-------------------+
| sbtest_bk |
+-------------------+
1 row in set (0.01 sec)
11.2在3308上创建sbtest_bk
"root@localhost:mysql3312.sock [systest]>show create table sbtest_bk;
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest_bk | CREATE TABLE `sbtest_bk` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10021 DEFAULT CHARSET=utf8 |
11.3将3308 表进行discard 一定要注意主机不然就完蛋了
"root@localhost:mysql3308.sock [systest]>alter table sbtest_bk discard tablespace;
Query OK, 0 rows affected (0.03 sec)
11.4 在3312 上将表锁住并export
"root@localhost:mysql3312.sock [systest]>flush table sbtest_bk for export;
Query OK, 0 rows affected (0.00 sec)
11.5拷贝cfg 和ibd文件（cfg 是flush 后才有的）到3308库
[root@zst2 mysql3312]# cd data/systest/
[root@zst2 systest]# ls
db.opt sbtest1.frm sbtest1.ibd
[root@zst2 systest]# ls
db.opt sbtest_bk.cfg sbtest_bk.frm sbtest_bk.ibd
[root@zst2 systest]# cp -p sbtest_bk.cfg sbtest_bk.ibd /data/mysql/mysql3308/data/systest/
11.6解锁3312库表
"root@localhost:mysql3312.sock [systest]>unlock tables;
Query OK, 0 rows affected (0.00 sec)
11.7在3308上将表信息导入
"root@localhost:mysql3308.sock [systest]>alter table sbtest_bk import tablespace;
Query OK, 0 rows affected (0.07 sec)
检查
"root@localhost:mysql3308.sock [systest]>select * from sbtest_bk;
+-------+----+---+-----+
| id | k | c | pad |
+-------+----+---+-----+
| 10001 | 30 | | |
| 10002 | 40 | | |
| 10003 | 30 | | |
| 10004 | 40 | | |
| 10006 | 30 | | |
| 10007 | 30 | | |
| 10008 | 40 | | |
| 10009 | 40 | | |
| 10013 | 30 | | |
| 10014 | 30 | | |
| 10015 | 30 | | |
| 10016 | 30 | | |
| 10017 | 40 | | |
| 10018 | 40 | | |
| 10019 | 40 | | |
| 10020 | 40 | | |
+-------+----+---+-----+
16 rows in set (0.00 sec)
将表改为原名即可（也可以使用mysqldump恢复）
优缺点:
表空间传输如果在主从的环境中在主做了的话复制会中断，如果表多则会非常麻烦
mysqldump：
大表100G会很慢，但是不会影响复制
注意点：一定要对表进行rename后在进行操作，避免冲突二次污染
若有收获，就点个赞吧
0 人点赞
