年終故障演練案例(一)
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);
[[email protected] ~]# 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;
思路
- 恢複全備份
- 恢複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 |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------+
| mysql-bin.000018 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |
| mysql-bin.000018 | 123 | Previous_gtids | 6 | 234 | 39dad7bc-602f-11e9-a236-000c29422f63:1-309,
c3972b9a-55bf-11e9-8abc-000c29422f63:13-25 |
| mysql-bin.000018 | 234 | Gtid | 6 | 299 | SET @@SESSION.GTID_NEXT= '39dad7bc-602f-11e9-a236-000c29422f63:310' |
| mysql-bin.000018 | 299 | Query | 6 | 370 | BEGIN |
| mysql-bin.000018 | 370 | Table_map | 6 | 414 | table_id: 381 (app.t1) |
| mysql-bin.000018 | 414 | Write_rows | 6 | 474 | table_id: 381 flags: STMT_END_F |
| mysql-bin.000018 | 474 | Xid | 6 | 505 | COMMIT /* xid=2400 */ |
| mysql-bin.000018 | 505 | Gtid | 6 | 570 | SET @@SESSION.GTID_NEXT= '39dad7bc-602f-11e9-a236-000c29422f63:311' |
| mysql-bin.000018 | 570 | Query | 6 | 659 | drop database app |
| mysql-bin.000018 | 659 | Gtid | 6 | 724 | SET @@SESSION.GTID_NEXT= '39dad7bc-602f-11e9-a236-000c29422f63:312' |
| mysql-bin.000018 | 724 | Query | 6 | 815 | create database app |
| mysql-bin.000018 | 815 | Gtid | 6 | 880 | SET @@SESSION.GTID_NEXT= '39dad7bc-602f-11e9-a236-000c29422f63:313' |
| mysql-bin.000018 | 880 | Query | 6 | 975 | use `app`; create table t1(id int) |
| mysql-bin.000018 | 975 | Gtid | 6 | 1040 | SET @@SESSION.GTID_NEXT= '39dad7bc-602f-11e9-a236-000c29422f63:314' |
| mysql-bin.000018 | 1040 | Query | 6 | 1111 | BEGIN |
| mysql-bin.000018 | 1111 | Table_map | 6 | 1155 | table_id: 383 (app.t1) |
| mysql-bin.000018 | 1155 | Write_rows | 6 | 1215 | table_id: 383 flags: STMT_END_F |
| mysql-bin.000018 | 1215 | Xid | 6 | 1246 | COMMIT /* xid=2413 */ |
| mysql-bin.000018 | 1246 | Gtid | 6 | 1311 | SET @@SESSION.GTID_NEXT= '39dad7bc-602f-11e9-a236-000c29422f63:315' |
| mysql-bin.000018 | 1311 | Query | 6 | 1382 | BEGIN |
| mysql-bin.000018 | 1382 | Table_map | 6 | 1426 | table_id: 385 (app.t1) |
| mysql-bin.000018 | 1426 | Write_rows | 6 | 1471 | table_id: 385 flags: STMT_END_F |
| mysql-bin.000018 | 1471 | Xid | 6 | 1502 | COMMIT /* xid=3195 */ |
| mysql-bin.000018 | 1502 | Gtid | 6 | 1567 | SET @@SESSION.GTID_NEXT= '39dad7bc-602f-11e9-a236-000c29422f63:316' |
| mysql-bin.000018 | 1567 | Query | 6 | 1638 | BEGIN |
| mysql-bin.000018 | 1638 | Table_map | 6 | 1682 | table_id: 385 (app.t1) |
| mysql-bin.000018 | 1682 | Update_rows | 6 | 1728 | table_id: 385 flags: STMT_END_F |
| mysql-bin.000018 | 1728 | Xid | 6 | 1759 | COMMIT /* xid=3196 */ |
| mysql-bin.000018 | 1759 | Gtid | 6 | 1824 | SET @@SESSION.GTID_NEXT= '39dad7bc-602f-11e9-a236-000c29422f63:317' |
| mysql-bin.000018 | 1824 | Query | 6 | 1895 | BEGIN |
| mysql-bin.000018 | 1895 | Table_map | 6 | 1939 | table_id: 385 (app.t1) |
| mysql-bin.000018 | 1939 | Delete_rows | 6 | 1979 | table_id: 385 flags: STMT_END_F |
| mysql-bin.000018 | 1979 | Xid | 6 | 2010 | COMMIT /* xid=3197 */ |
| mysql-bin.000018 | 2010 | Gtid | 6 | 2075 | SET @@SESSION.GTID_NEXT= '39dad7bc-602f-11e9-a236-000c29422f63:318' |
| mysql-bin.000018 | 2075 | Query | 6 | 2146 | BEGIN |
| mysql-bin.000018 | 2146 | Table_map | 6 | 2190 | table_id: 385 (app.t1) |
| mysql-bin.000018 | 2190 | Delete_rows | 6 | 2255 | table_id: 385 flags: STMT_END_F |
| mysql-bin.000018 | 2255 | Xid | 6 | 2286 | COMMIT /* xid=3198 */ |
| mysql-bin.000018 | 2286 | Gtid | 6 | 2351 | SET @@SESSION.GTID_NEXT= '39dad7bc-602f-11e9-a236-000c29422f63:319' |
| mysql-bin.000018 | 2351 | Query | 6 | 2422 | BEGIN |
| mysql-bin.000018 | 2422 | Table_map | 6 | 2466 | table_id: 385 (app.t1) |
| mysql-bin.000018 | 2466 | Write_rows | 6 | 2526 | table_id: 385 flags: STMT_END_F |
| mysql-bin.000018 | 2526 | Xid | 6 | 2557 | COMMIT /* xid=3200 */ |
| mysql-bin.000018 | 2557 | Gtid | 6 | 2622 | SET @@SESSION.GTID_NEXT= '39dad7bc-602f-11e9-a236-000c29422f63:320' |
| mysql-bin.000018 | 2622 | Query | 6 | 2693 | BEGIN |
| mysql-bin.000018 | 2693 | Table_map | 6 | 2737 | table_id: 385 (app.t1) |
| mysql-bin.000018 | 2737 | Update_rows | 6 | 2783 | table_id: 385 flags: STMT_END_F |
| mysql-bin.000018 | 2783 | Xid | 6 | 2814 | COMMIT /* xid=3201 */ |
| mysql-bin.000018 | 2814 | Gtid | 6 | 2879 | SET @@SESSION.GTID_NEXT= '39dad7bc-602f-11e9-a236-000c29422f63:321' |
| mysql-bin.000018 | 2879 | Query | 6 | 2950 | BEGIN |
| mysql-bin.000018 | 2950 | Table_map | 6 | 2994 | table_id: 385 (app.t1) |
| mysql-bin.000018 | 2994 | Update_rows | 6 | 3040 | table_id: 385 flags: STMT_END_F |
| mysql-bin.000018 | 3040 | Xid | 6 | 3071 | COMMIT /* xid=3202 */ |
| mysql-bin.000018 | 3071 | Gtid | 6 | 3136 | SET @@SESSION.GTID_NEXT= '39dad7bc-602f-11e9-a236-000c29422f63:322' |
| mysql-bin.000018 | 3136 | Query | 6 | 3207 | BEGIN |
| mysql-bin.000018 | 3207 | Table_map | 6 | 3251 | table_id: 385 (app.t1) |
| mysql-bin.000018 | 3251 | Update_rows | 6 | 3297 | table_id: 385 flags: STMT_END_F |
| mysql-bin.000018 | 3297 | Xid | 6 | 3328 | COMMIT /* xid=3203 */ |
+------------------+------+----------------+-----------+-------------+----------------------------------------------------------------------------------------+
58 rows in set (0.00 sec)
FBI [(none)]>
mysqlbinlog --skip-gtids --include-gtids='39dad7bc-602f-11e9-a236-000c29422f63:315-322' --exclude-gtids='39dad7bc-602f-11e9-a236-000c29422f63:317' /data/binlog/mysql-bin.000018 >/data/backup/appbin.sql
全備以及binlog日志
[[email protected] backup]# ll app*
-rw-r--r-- 1 root root 7191 Apr 16 21:47 appbin.sql
-rw-r--r-- 1 root root 50823577 Apr 16 21:35 appfull.sql
恢複
set sql_log_bin=0;
source /data/backup/appfull.sql;
source /data/backup/appbin.sql
set sql_log_bin=1;
![資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)