MySQL-年终故障演练

年终故障演练案例(一)

1、创建一个数据库 app
2、在app下创建一张表t1
3、插入5行任意数据
4、全备
5、插入两行数据，任意修改1行数据，删除1行数据
6、删除所有数据
7、再t1中又插入5行新数据，修改3行数据
需求，跳过第六步恢复表数据
写备份脚本和策略

环境模拟

#1、创建一个数据库 app
create database app;
#2、在app下创建一张表t1
use app;
create table t1(id int);
#3、插入5行任意数据
insert into  t1 values(1),(2),(3),(4),(5);
#4、全备
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/appfull.sql
#5、插入两行数据，任意修改1行数据，删除1行数据
insert into t1 values(6),(7);
update t1 set id='30' where id=1;
delete from t1 where id=2;
#6、删除所有数据
delete from t1 ;
#7、再t1中又插入5行新数据，修改3行数据
insert into  t1 values(11),(12),(13),(14),(15);
update t1 set id='31' where id=11;
update t1 set id='32' where id=12;
update t1 set id='33' where id=13;
select * from t1;

create database app;
use app;
create table t1(id int);
insert into  t1 values(1),(2),(3),(4),(5);


[root@db01 ~]# mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/appfull.sql

use app;
insert into t1 values(6),(7);
update t1 set id='30' where id=1;
delete from t1 where id=2;
delete from t1 ;
select * from t1;
insert into  t1 values(11),(12),(13),(14),(15);
update t1 set id='31' where id=11;
update t1 set id='32' where id=12;
update t1 set id='33' where id=13;
select * from t1;

思路

1. 恢复全备份
2. 恢复binlog

准备binlog日志
查看全背中的binlog信息
vim /data/backup/appfull.sql
大概在22行
– CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000018’, MASTER_LOG_POS=1246;
查找截取点

FBI [(none)]>show binlog events in 'mysql-bin.000018';
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                   |
+------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------