當删庫時如何避免跑路

延時節點解決方案

删庫跑路也是個老梗了，可見在運維資料庫的過程中誤删除資料，或者開發的代碼有bug，造成資料的誤删除屢見不鮮。不過現在也有許多用于恢複或預防誤删除的方案，例如SQL管理系統，将要執行的SQL先交由管理者稽核，然後由管理者備份一個鏡像資料庫，在鏡像上執行該SQL，并在執行後還原鏡像。這樣經過層層把關就可以大大減小出現誤操作的幾率。

另外，利用binlog日志也可以恢複誤操作的資料，是以線上運作的資料庫都會開啟binlog日志功能。還有就是本小節要介紹的延時節點：在Replication叢集中，可以設定一個延時節點，該節點的資料同步時間要慢于叢集中的其他節點，當其他節點出現誤操作後，若延時節點的資料還沒有被影響就可以從延時節點進行恢複。

但如果現有的資料庫組建的都是PXC叢集，沒有Replication叢集可以采用該方案嗎？也是可以的，PXC叢集與Replication叢集并非是互斥的，我們可以将PXC叢集中的某個節點設定為Master，然後增加一個延時節點設定為Slave，讓這兩個節點構成Replication叢集進行資料同步即可。如下所示：

本小節就簡單示範一下如何搭建這種異構叢集下的延時節點，我這裡已經事先準備好了一個PXC叢集和一個用作延時節點的資料庫：

這裡使用PXC叢集中的

PXC-Node3

作為Master，讓其與

DelayNode

組成主從，而

DelayNode

自然就是作為延時節點了。

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

在CentOS8下搭建PXC叢集
搭建高可用的Replication叢集歸檔大量的冷資料

為PXC節點配置延時節點

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

systemctl stop mysqld

主從節點的配置檔案都要開啟GTID，否則無法利用延時節點找回資料。主節點需要增加的配置如下：

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

# 設定節點的id
server_id=3
# 開啟binlog
log_bin=mysql_bin
# 開啟GTID
gtid_mode=ON
enforce_gtid_consistency=1

從節點需要增加的配置如下：

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

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

完成配置檔案的配置後，啟動這兩個節點：

systemctl start mysqld

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

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)

記錄下以上執行結果後，進入Slave的MySQL指令行終端，分别執行如下語句：

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節點的連接配接資訊，以及從Master二進制日志的哪個位置開始複制
mysql> start slave;  -- 啟動主從同步

Tips：通常配置主從同步會單獨建立一個用于同步賬戶，這裡為了簡單起見就直接使用了現有的賬戶。另外，如果不想設定主庫的binlog偏移量，則使用 master_auto_position=1 參數即可

配置完主從關系後，使用

show slave status\G;

語句檢視主從同步狀态，

Slave_IO_Running

和

Slave_SQL_Running

的值均為

Yes

才能表示主從同步狀态是正常的：

主從關系配置完成後，接着測試一下主從的資料同步是否正常。在Master上執行一些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');

執行完成後，看看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>

驗證了主從節點能正常同步資料後，我們就可以設定Slave節點的同步延時了。在Slave節點上分别執行如下語句：

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

同樣，重新配置了主從關系後，需要确認主從同步狀态是正常的：

模拟誤删除資料

接着示範下延時節點的作用，首先到Master節點上，将

student

表中的資料給删除掉，模拟誤删除的情況：

mysql> use test_db;
mysql> delete from student;  -- 删除student表中的所有資料
mysql> select * from student;  -- Master上已經查詢不到資料了
Empty set (0.00 sec)

mysql>

此時，因為延時同步的原因，在Slave節點上依舊能夠正常查詢到被删除的資料：

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

mysql>

現在就輪到GTID上場了，我們得先讓Slave節點跳過删除操作的GTID，後續才能讓Master從Slave上恢複資料。否則Slave同步了該GTID的話，Slave節點上的資料也會被删除，即便在同步之前恢複了Master的資料也會造成主從資料不一緻的問題。

GTID是記錄在binlog中的，由于誤删除操作是在Master上進行的，是以首先在Master節點上使用

show master logs;

語句查詢binlog日志名稱：

接下來我們需要在binlog檔案中找到誤删除操作的記錄及其GTID，因為binlog檔案的序号是遞增的，是以最近的操作一般記錄在序号最大的binlog檔案中。是以執行

show binlog events in 'PXC-Node3-bin.000003';

語句，并從結果集中查找誤删除操作的記錄及其GTID。如下圖所示：

在Master節點上找到誤删除操作的GTID後，複制該GTID。然後到Slave節點上分别執行如下語句：

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

完成以上操作後，此時Slave上依舊存在着誤删除的資料：

而Master上的

student

表依舊為空：

完成以上的操作後，恢複同步延時的設定：

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

恢複Master節點誤删除的資料

讓Slave節點跳過誤删除操作的GTID後，就可以開始恢複Master節點的資料了。首先停止業務系統對Master節點所在的PXC叢集的讀寫操作，避免還原的過程中造成資料混亂。然後導出Slave節點的資料：

在Master節點上建立臨時庫，這是為了先在臨時庫驗證了資料的正确性之後再導入到業務庫中，避免出現意外：

create database temp_db;

然後導入資料：

把Master節點上的資料表重命名：

rename table test_db.student to test_db.student_bak;

把臨時庫的資料表遷移到業務庫中：

rename table temp_db.student to test_db.student;

此時就成功恢複了Master節點上誤删除的資料：

日志閃回方案

之前也提到了除延時節點這種解決方案外，使用binlog日志也是可以實作資料恢複的，這種恢複資料的方式通常被稱為日志閃回。這裡之是以還要介紹這種方案，是因為延時節點方案存在着一定的局限性：一旦在延時階段沒有發現問題并解決問題的話，那麼當主從資料同步後，也無法利用從節點去實作誤删除的恢複。

日志閃回方案相對于延時節點方案來說要簡單一些，不需要增加額外的節點，利用目前節點就可以恢複資料。但該方案也并非全能，例如binlog日志不會記錄

drop table

、

truncate table

等操作所删除的資料，那麼也就無法通過日志恢複了。不過這兩種方案并不沖突，可以同時使用以提高資料恢複的可能性。

日志閃回的前提是要開啟binlog日志，然後通過一些閃回工具将binlog日志解析成SQL，接着将SQL中的

delete

語句轉換成

insert

語句，或者找到被誤删除的資料的

insert

語句。最後将這些

insert

語句重新在資料庫中執行一遍，這樣就實作了資料的恢複：

閃回工具有很多，本文中采用的是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配置檔案中配置如下參數，因為binlog2sql是基于

row

格式的binlog進行解析的：

[mysqld]
...

binlog_format = row
binlog_row_image = full

我這裡有一張商品表，該表中有如下資料：

使用

delete

語句删除該表中的資料來模拟誤删除：

delete from flash.goods;

然後再插入一些資料，模拟誤删除後新增的資料：

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恢複資料

恢複前的準備工作：

停止應用對資料庫的讀寫操作，避免還原後覆寫新寫入的資料
熱備份資料庫，以保證還原工作萬無一失，關于備份相關内容可以參考：關于資料庫的各種備份與還原姿勢詳解
清空需要恢複資料的資料表的全部記錄，避免主鍵、唯一鍵限制的沖突

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

delete from flash.goods;

之前也提到了最近的操作一般記錄在序号最大的binlog檔案中，是以得查詢資料庫中的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 ：被執行的Python檔案
-u ：用于連接配接資料庫的賬戶
-p ：資料庫賬戶的密碼
-d ：指定邏輯庫的名稱
-t ：指定資料表的名稱
--start-file ：指定需要解析的binlog的檔案名
/home/PXC-Node3-bin.000003.sql ：指定将解析生成的SQL寫到哪個檔案

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

當删庫時如何避免跑路

延時節點解決方案

為PXC節點配置延時節點

模拟誤删除資料

恢複Master節點誤删除的資料

日志閃回方案

安裝binlog2sql

通過binlog2sql恢複資料

繼續閱讀

MariaDB資料庫主從架構和配置優化

一文帶你深入了解 redis 複制技術及主從架構主從環境搭建複制技術的原理主從拓撲架構

MySQL(三)：MHA實作MySQL主從架構中主伺服器的高可用，zabbix完成manager重新開機

Docker 搭建mysql pxc叢集 + haproxy + keepalived 高可用

cenos7 安裝 Percona-XtraDB-Cluster-57

mysql高可用-基于docker容器下的pxc叢集方案mysql單機性能測試docker 基礎Haproxy實作負載均衡keepalived實作雙機熱備

雲原生問題集中大讨論，主要集中在1、微服務如何上容器2、網絡選擇3、快速部署及主從架構規劃4、容器化與傳統項目如何共同提

【Redis主從架構】搭建一個一主一從的Redis主從架構5. 【Redis主從架構】搭建一個一主一從的Redis主從架構

CentOS 7.9，二分鐘搭建好MySQL8.0-PXC叢集(實操不說原理)