複習: 在昨天講解了如何使用mysqldump備份是數據和binlog相關過濾方法，今天來實際模擬一下流程進行。

資料復原準備

使用binlog復原資料的流程，假設我們今天因為異動操作誤刪了表中資料要如何復原資料？

• DB:user

• TABLE:user_powers

• --DATA SQL資料參考--

 (資料庫)
 CREATE DATABASE user;
 ----------------------------------------------------
 (資料表)
 CREATE TABLE `user_powers` (
  `user_id` varchar(50) NOT NULL COMMENT '會員ID',
  `nick_name` varchar(20) NOT NULL COMMENT '會員名稱',
  `group_id` int(11) DEFAULT NULL COMMENT '會員所屬平台',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP    COMMENT '異動時間',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 ----------------------------------------------------
 (資料)
  INSERT INTO `user_powers` VALUES ('1021211','ken',119201,'2021-08-10 08:33:56'),('1232123','siang',19200,'2021-08-10 08:33:51'),('18675543','kitty',19200,'2021-08-10 08:33:51'),('23122141','kiki',1242000,'2021-08-12 09:01:54'),('24122441','snow',119201,'2021-08-10 08:33:51'),('4356231','lisa',19200,'2021-08-10 08:33:56'),('64121211','kevin',389110,'2021-08-10 08:33:51'),('7622441','ben',213123,'2021-08-10 08:33:56'),('91311233','alex',389110,'2021-08-10 08:33:56'),('91675543','kay',123321,'2021-08-10 08:33:56'),('98212321','sara',119201,'2021-08-10 08:33:51');
 ----------------------------------------------------
 (完成後數據)
mysql> select * from user_powers;
+----------+-----------+----------+---------------------+
| user_id  | nick_name | group_id | updated_at          |
+----------+-----------+----------+---------------------+
| 1021211  | ken       |   119201 | 2021-08-10 08:33:56 |
| 1232123  | siang     |    19200 | 2021-08-10 08:33:51 |
| 18675543 | kitty     |    19200 | 2021-08-10 08:33:51 |
| 23122141 | kiki      |  1242000 | 2021-08-12 09:01:54 |
| 24122441 | snow      |   119201 | 2021-08-10 08:33:51 |
| 4356231  | lisa      |    19200 | 2021-08-10 08:33:56 |
| 64121211 | kevin     |   389110 | 2021-08-10 08:33:51 |
| 7622441  | ben       |   213123 | 2021-08-10 08:33:56 |
| 91311233 | alex      |   389110 | 2021-08-10 08:33:56 |
| 91675543 | kay       |   123321 | 2021-08-10 08:33:56 |
| 98212321 | sara      |   119201 | 2021-08-10 08:33:51 |
+----------+-----------+----------+---------------------+
11 rows in set (0.00 sec)
 

1.假設上面數據為今天備份完的內容

重要*3
在下面的指令中加上 --master-data=2 參數作用會紀錄此備份內容是到哪一個binlog(POS點&檔名)，方便後續復原動作。

• 備份DB: user (數據含相關SQL排成文件 procedure,events...這是 後面會做介紹~)

# mysqldump -uroot -p1234  --routines --events --single-transaction --master-data=2 --flush-logs user > user_backup_20210817.sql

• 檢視此備份檔執行的binlog紀錄

# grep -i "CHANGE MASTER TO MASTER_LOG_FILE" user_backup_20210817.sql

• 在備份完後我們未做任何資料上更動，查看mysql目前的binlog紀錄位置，可以看到確實為備份檔案的紀錄位置。

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000011 |       201 |
| mysql-bin.000012 |      3068 |
| mysql-bin.000013 |       154 |   <-目前位置
+------------------+-----------+
3 rows in set (0.00 sec)

--補充--

環境備份:基本上生產環境下備份都是以全庫備為主，而有些重要的表資訊可能會有備份單庫or表的需求。

mysqldump還原注意: 備份時如果不是用指定到表的方式備份 ex.mysqldump -u root -p db_name tbl_name ... 的話在恢復時是沒辦法直接以恢復單一TABLE的方式還原資料倒回去的，可採用過濾備份資料把表相關資料語法另外撈出來做復原。

保險操作: 將備份檔和binlog資料在臨時庫上做復原動作，根據備份檔紀錄的binlog(POS&檔名)找後續執行的binlog內容進行復原，確認資料沒問題後再將表倒回生產庫中。
避免出錯造成二次傷害的狀況!!!

2.由於正常服務是持續運行的，備份完當然會有資料持續的更新寫入。

• 準備備份完後異動的資料，產新binlog檔。 (ex.有多個binlog紀錄檔的資料要恢復)

mysql> INSERT INTO `user_powers` VALUES ('98112111','leo',19201,'2021-08-13 12:33:56'),('38921112','shawn',19200,'2021-08-12 10:33:51');

mysql> INSERT INTO `user_powers` VALUES ('72197123','eric',19200,'2021-08-15 01:33:51');

mysql> FLUSH LOGS;

mysql> UPDATE user_powers SET nick_name = 'siangx' WHERE user_id = 1232123;

mysql> select * from user_powers;
+----------+-----------+----------+---------------------+
| user_id  | nick_name | group_id | updated_at          |
+----------+-----------+----------+---------------------+
| 1021211  | ken       |   119201 | 2021-08-10 08:33:56 |
| 1232123  | siangx    |    19200 | 2021-08-17 02:08:05 |
| 18675543 | kitty     |    19200 | 2021-08-10 08:33:51 |
| 23122141 | kiki      |  1242000 | 2021-08-12 09:01:54 |
| 24122441 | snow      |   119201 | 2021-08-10 08:33:51 |
| 38921112 | shawn     |    19200 | 2021-08-12 10:33:51 |
| 4356231  | lisa      |    19200 | 2021-08-10 08:33:56 |
| 64121211 | kevin     |   389110 | 2021-08-10 08:33:51 |
| 72197123 | eric      |    19200 | 2021-08-15 01:33:51 |
| 7622441  | ben       |   213123 | 2021-08-10 08:33:56 |
| 91311233 | alex      |   389110 | 2021-08-10 08:33:56 |
| 91675543 | kay       |   123321 | 2021-08-10 08:33:56 |
| 98112111 | leo       |    19201 | 2021-08-13 12:33:56 |
| 98212321 | sara      |   119201 | 2021-08-10 08:33:51 |
+----------+-----------+----------+---------------------+
14 rows in set (0.00 sec)

3.執行誤刪數據動作

mysql> delete from user_powers where group_id = 19200;

mysql> select * from user_powers;
+----------+-----------+----------+---------------------+
| user_id  | nick_name | group_id | updated_at          |
+----------+-----------+----------+---------------------+
| 1021211  | ken       |   119201 | 2021-08-10 08:33:56 |
| 23122141 | kiki      |  1242000 | 2021-08-12 09:01:54 |
| 24122441 | snow      |   119201 | 2021-08-10 08:33:51 |
| 64121211 | kevin     |   389110 | 2021-08-10 08:33:51 |
| 7622441  | ben       |   213123 | 2021-08-10 08:33:56 |
| 91311233 | alex      |   389110 | 2021-08-10 08:33:56 |
| 91675543 | kay       |   123321 | 2021-08-10 08:33:56 |
| 98112111 | leo       |    19201 | 2021-08-13 12:33:56 |
| 98212321 | sara      |   119201 | 2021-08-10 08:33:51 |
+----------+-----------+----------+---------------------+
9 rows in set (0.00 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000011 |       201 |
| mysql-bin.000012 |      3068 |
| mysql-bin.000013 |       816 |
| mysql-bin.000014 |       849 |
+------------------+-----------+
4 rows in set (0.00 sec)

總結-(備份完時位置在mysql-bin.000013-(154)的地方，所以異動過後的資料紀錄於 mysql-bin.000013 & mysql-bin.000014)

下集預告: 完成以上測試資料準備後，明天開始進行後續復原數據流程