當刪庫時如何避免跑路

延時節(jié)點解決方案

刪庫跑路也是個老梗了，可見在運維數(shù)據(jù)庫的過程中誤刪除數(shù)據(jù)，或者開發(fā)的代碼有bug，造成數(shù)據(jù)的誤刪除屢見不鮮。不過現(xiàn)在也有許多用于恢復或預防誤刪除的方案，例如SQL管理系統(tǒng)，將要執(zhí)行的SQL先交由管理員審核，然后由管理員備份一個鏡像數(shù)據(jù)庫，在鏡像上執(zhí)行該SQL，并在執(zhí)行后還原鏡像。這樣經(jīng)過層層把關就可以大大減小出現(xiàn)誤操作的幾率。

另外，利用binlog日志也可以恢復誤操作的數(shù)據(jù)，所以線上運行的數(shù)據(jù)庫都會開啟binlog日志功能。還有就是本小節(jié)要介紹的延時節(jié)點：在Replication集群中，可以設置一個延時節(jié)點，該節(jié)點的數(shù)據(jù)同步時間要慢于集群中的其他節(jié)點，當其他節(jié)點出現(xiàn)誤操作后，若延時節(jié)點的數(shù)據(jù)還沒有被影響就可以從延時節(jié)點進行恢復。

但如果現(xiàn)有的數(shù)據(jù)庫組建的都是PXC集群，沒有Replication集群可以采用該方案嗎？也是可以的，PXC集群與Replication集群并非是互斥的，我們可以將PXC集群中的某個節(jié)點設置為Master，然后增加一個延時節(jié)點設置為Slave，讓這兩個節(jié)點構成Replication集群進行數(shù)據(jù)同步即可。如下所示：

本小節(jié)就簡單演示一下如何搭建這種異構集群下的延時節(jié)點，我這里已經(jīng)事先準備好了一個PXC集群和一個用作延時節(jié)點的數(shù)據(jù)庫：

這里使用PXC集群中的PXC-Node3作為Master，讓其與DelayNode組成主從，而DelayNode自然就是作為延時節(jié)點了。

關于PXC集群和Replication集群的搭建可以參考如下文章，這里由于篇幅有限就不進行說明了：

• 在CentOS8下搭建PXC集群
• 搭建高可用的Replication集群歸檔大量的冷數(shù)據(jù)

為PXC節(jié)點配置延時節(jié)點

接下來開始動手實踐，首先需要將這兩個節(jié)點上的MySQL服務都給停掉：

systemctl stop mysqld

主從節(jié)點的配置文件都要開啟GTID，否則無法利用延時節(jié)點找回數(shù)據(jù)。主節(jié)點需要增加的配置如下：

[root@PXC-Node3 ~]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
[mysqld]
...

# 設置節(jié)點的id
server_id=3
# 開啟binlog
log_bin=mysql_bin
# 開啟GTID
gtid_mode=ON
enforce_gtid_consistency=1

從節(jié)點需要增加的配置如下：

[root@delay-node ~]# vim /etc/my.cnf
[mysqld]
...

server_id=102
log_bin=mysql_bin
# 從節(jié)點需要開啟relay_log
relay_log=relay_bin
gtid_mode=ON
enforce_gtid_consistency=1

完成配置文件的配置后，啟動這兩個節(jié)點：

systemctl start mysqld

接著配置Slave對Master的主從關系，進入Master的MySQL命令行終端，通過如下語句查詢Master當前正在使用的二進制日志及當前執(zhí)行二進制日志位置：

mysql> flush logs;  -- 刷新日志
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| PXC-Node3-bin.000003 |      154 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

記錄下以上執(zhí)行結果后，進入Slave的MySQL命令行終端，分別執(zhí)行如下語句：

mysql> stop slave;  -- 停止主從同步
mysql> change master to master_log_file='PXC-Node3-bin.000003', master_log_pos=154, master_host='192.168.190.134', master_port=3306, master_user='admin', master_password='Abc_123456';  -- 配置Master節(jié)點的連接信息，以及從Master二進制日志的哪個位置開始復制
mysql> start slave;  -- 啟動主從同步

• Tips：通常配置主從同步會單獨創(chuàng)建一個用于同步賬戶，這里為了簡單起見就直接使用了現(xiàn)有的賬戶。另外，如果不想設置主庫的binlog偏移量，則使用master_auto_position=1參數(shù)即可

配置完主從關系后，使用show slave status\G;語句查看主從同步狀態(tài)，Slave_IO_Running和Slave_SQL_Running的值均為Yes才能表示主從同步狀態(tài)是正常的：

主從關系配置完成后，接著測試一下主從的數(shù)據(jù)同步是否正常。在Master上執(zhí)行一些SQL語句，如下：

mysql> create database test_db;
mysql> use test_db;
mysql> CREATE TABLE `student` (
          `id` int(11) NOT NULL,
          `name` varchar(20) NOT NULL,
          PRIMARY KEY (`id`)
       ); 
mysql> INSERT INTO `test_db`.`student`(`id`, `name`) VALUES (1, 'Jack');

執(zhí)行完成后，看看Slave上是否有正常同步：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test_db;
mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | Jack |
+----+------+
1 row in set (0.00 sec)

mysql> 

驗證了主從節(jié)點能正常同步數(shù)據(jù)后，我們就可以設置Slave節(jié)點的同步延時了。在Slave節(jié)點上分別執(zhí)行如下語句：

mysql> stop slave;
mysql> change master to master_delay=1200;  -- 設置同步延時為1200秒
mysql> start slave;

同樣，重新配置了主從關系后，需要確認主從同步狀態(tài)是正常的：

模擬誤刪除數(shù)據(jù)

接著演示下延時節(jié)點的作用，首先到Master節(jié)點上，將student表中的數(shù)據(jù)給刪除掉，模擬誤刪除的情況：

mysql> use test_db;
mysql> delete from student;  -- 刪除student表中的所有數(shù)據(jù)
mysql> select * from student;  -- Master上已經(jīng)查詢不到數(shù)據(jù)了
Empty set (0.00 sec)

mysql> 

此時，因為延時同步的原因，在Slave節(jié)點上依舊能夠正常查詢到被刪除的數(shù)據(jù)：

mysql> use test_db;
mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | Jack |
+----+------+
1 row in set (0.00 sec)

mysql> 

現(xiàn)在就輪到GTID上場了，我們得先讓Slave節(jié)點跳過刪除操作的GTID，后續(xù)才能讓Master從Slave上恢復數(shù)據(jù)。否則Slave同步了該GTID的話，Slave節(jié)點上的數(shù)據(jù)也會被刪除，即便在同步之前恢復了Master的數(shù)據(jù)也會造成主從數(shù)據(jù)不一致的問題。

GTID是記錄在binlog中的，由于誤刪除操作是在Master上進行的，所以首先在Master節(jié)點上使用show master logs;語句查詢binlog日志名稱：

接下來我們需要在binlog文件中找到誤刪除操作的記錄及其GTID，因為binlog文件的序號是遞增的，所以最近的操作一般記錄在序號最大的binlog文件中。因此執(zhí)行show binlog events in 'PXC-Node3-bin.000003';語句，并從結果集中查找誤刪除操作的記錄及其GTID。如下圖所示：

在Master節(jié)點上找到誤刪除操作的GTID后，復制該GTID。然后到Slave節(jié)點上分別執(zhí)行如下語句：

mysql> stop slave;  -- 停止主從同步
mysql> set gtid_next='d36eaafb-c653-ee15-4458-5d6bc793bd7a:4';  -- 設置需要跳過的GTID
mysql> begin; commit;  -- 開啟并提交事務，即模擬Slave同步了該GTID，后續(xù)就不會再進行同步，從而達到了跳過的效果
mysql> set gtid_next='automatic';  -- 恢復gtid的設置
mysql> change master to master_delay=0;  -- 設置同步延時為0是為了馬上進行同步跳過該GTID
mysql> start slave;

完成以上操作后，此時Slave上依舊存在著誤刪除的數(shù)據(jù)：

而Master上的student表依舊為空：

完成以上的操作后，恢復同步延時的設置：

mysql> stop slave;
mysql> change master to master_delay=1200;  -- 設置同步延時為1200秒
mysql> start slave;

恢復Master節(jié)點誤刪除的數(shù)據(jù)

讓Slave節(jié)點跳過誤刪除操作的GTID后，就可以開始恢復Master節(jié)點的數(shù)據(jù)了。首先停止業(yè)務系統(tǒng)對Master節(jié)點所在的PXC集群的讀寫操作，避免還原的過程中造成數(shù)據(jù)混亂。然后導出Slave節(jié)點的數(shù)據(jù)：

在Master節(jié)點上創(chuàng)建臨時庫，這是為了先在臨時庫驗證了數(shù)據(jù)的正確性之后再導入到業(yè)務庫中，避免出現(xiàn)意外：

create database temp_db;

然后導入數(shù)據(jù)：

把Master節(jié)點上的數(shù)據(jù)表重命名：

rename table test_db.student to test_db.student_bak;

把臨時庫的數(shù)據(jù)表遷移到業(yè)務庫中：

rename table temp_db.student to test_db.student;

此時就成功恢復了Master節(jié)點上誤刪除的數(shù)據(jù)：

日志閃回方案

之前也提到了除延時節(jié)點這種解決方案外，使用binlog日志也是可以實現(xiàn)數(shù)據(jù)恢復的，這種恢復數(shù)據(jù)的方式通常被稱為日志閃回。這里之所以還要介紹這種方案，是因為延時節(jié)點方案存在著一定的局限性：一旦在延時階段沒有發(fā)現(xiàn)問題并解決問題的話，那么當主從數(shù)據(jù)同步后，也無法利用從節(jié)點去實現(xiàn)誤刪除的恢復。

日志閃回方案相對于延時節(jié)點方案來說要簡單一些，不需要增加額外的節(jié)點，利用當前節(jié)點就可以恢復數(shù)據(jù)。但該方案也并非全能，例如binlog日志不會記錄drop table、truncate table等操作所刪除的數(shù)據(jù)，那么也就無法通過日志恢復了。不過這兩種方案并不沖突，可以同時使用以提高數(shù)據(jù)恢復的可能性。

日志閃回的前提是要開啟binlog日志，然后通過一些閃回工具將binlog日志解析成SQL，接著將SQL中的delete語句轉換成insert語句，或者找到被誤刪除的數(shù)據(jù)的insert語句。最后將這些insert語句重新在數(shù)據(jù)庫中執(zhí)行一遍，這樣就實現(xiàn)了數(shù)據(jù)的恢復：

閃回工具有很多，本文中采用的是binlog2sql，它是大眾點評開源的基于Python編寫的MySQL日志閃回工具。

安裝binlog2sql

該工具的安裝步驟如下：

# 安裝前置工具
[root@PXC-Node3 ~]# yum install -y epel-release
[root@PXC-Node3 ~]# yum install -y git python3-pip

# 克隆binlog2sql的源碼庫，并進入源碼目錄
[root@PXC-Node3 ~]# git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql

# 安裝binlog2sql所依賴的Python庫
[root@PXC-Node3 ~/binlog2sql]# pip3 install -r requirements.txt

在MySQL配置文件中配置如下參數(shù)，因為binlog2sql是基于row格式的binlog進行解析的：

[mysqld]
...

binlog_format = row
binlog_row_image = full

模擬誤刪除數(shù)據(jù)

我這里有一張商品表，該表中有如下數(shù)據(jù)：

使用delete語句刪除該表中的數(shù)據(jù)來模擬誤刪除：

delete from flash.goods;

然后再插入一些數(shù)據(jù)，模擬誤刪除后新增的數(shù)據(jù)：

INSERT INTO `flash`.`goods`(`id`, `name`, `desc`, `status`) VALUES (6, '蘋果', 'xxxx', '1');
INSERT INTO `flash`.`goods`(`id`, `name`, `desc`, `status`) VALUES (7, '香蕉', 'xxxx', '1');

通過binlog2sql恢復數(shù)據(jù)

恢復前的準備工作：

• 停止應用對數(shù)據(jù)庫的讀寫操作，避免還原后覆蓋新寫入的數(shù)據(jù)
• 熱備份數(shù)據(jù)庫，以保證還原工作萬無一失，關于備份相關內(nèi)容可以參考：關于數(shù)據(jù)庫的各種備份與還原姿勢詳解
• 清空需要恢復數(shù)據(jù)的數(shù)據(jù)表的全部記錄，避免主鍵、唯一鍵約束的沖突

因為要恢復的是商品表，所以清空商品表的全部記錄：

delete from flash.goods;

之前也提到了最近的操作一般記錄在序號最大的binlog文件中，所以得查詢數(shù)據(jù)庫中的binlog文件名：

然后使用binlog2sql解析指定的binlog日志，具體命令如下：

[root@PXC-Node3 ~/binlog2sql]# python3 binlog2sql/binlog2sql.py -uadmin -p'Abc_123456' -dflash -tgoods --start-file='PXC-Node3-bin.000003' > /home/PXC-Node3-bin.000003.sql

• binlog2sql/binlog2sql.py：被執(zhí)行的Python文件
• -u：用于連接數(shù)據(jù)庫的賬戶
• -p：數(shù)據(jù)庫賬戶的密碼
• -d：指定邏輯庫的名稱
• -t：指定數(shù)據(jù)表的名稱
• --start-file：指定需要解析的binlog的文件名
• /home/PXC-Node3-bin.000003.sql：指定將解析生成的SQL寫到哪個文件

接著查看解析出來的SQL內(nèi)容：cat /home/PXC-Node3-bin.000003.sql。這里截取了有用的部分，如下圖，可以看到delete語句和insert語句都有我們要恢復的數(shù)據(jù)：

能得到這些語句接下來就簡單了，要么將delete語句轉換成insert語句，要么直接復制insert部分的SQL語句到數(shù)據(jù)庫上執(zhí)行即可。我這里就直接復制insert語句了：

INSERT INTO `flash`.`goods`(`id`, `name`, `desc`, `status`) VALUES (1, '蛋糕', '好吃', '1'); #start 3170 end 3363 time 2020-01-27 18:00:11
INSERT INTO `flash`.`goods`(`id`, `name`, `desc`, `status`) VALUES (2, '檸檬茶', '爽過吸大麻', '1'); #start 3459 end 3664 time 2020-01-27 18:00:56
INSERT INTO `flash`.`goods`(`id`, `name`, `desc`, `status`) VALUES (3, '豆奶', '好喝', '0'); #start 3760 end 3953 time 2020-01-27 18:01:10
INSERT INTO `flash`.`goods`(`id`, `name`, `desc`, `status`) VALUES (4, '窩窩頭', '一塊錢四個', '1'); #start 4049 end 4254 time 2020-01-27 18:01:37
INSERT INTO `flash`.`goods`(`id`, `name`, `desc`, `status`) VALUES (5, '雞腿', '雞你太美', '0'); #start 4350 end 4549 time 2020-01-27 18:02:08
INSERT INTO `flash`.`goods`(`id`, `name`, `desc`, `status`) VALUES (6, '蘋果', 'xxxx', '1'); #start 5052 end 5243 time 2020-01-27 18:06:24
INSERT INTO `flash`.`goods`(`id`, `name`, `desc`, `status`) VALUES (7, '香蕉', 'xxxx', '1'); #start 5339 end 5530 time 2020-01-27 18:06:24

執(zhí)行完以上SQL后，可以看到成功恢復了商品表中被刪除的數(shù)據(jù)：