如何刪庫以後不跑路

oliver-l發表於2020-10-20

我們都知道事務的基本特性ACID，事務具有永續性，一個事務一旦被提交了，那麼對資料庫中的資料的改變就是永久性的，即便是在資料庫系統遇到故障的情況下也不會丟失提交事務的操作。

經常會聽到哪個哪個程式設計師，刪庫跑路了，如果真的把資料庫刪了，有什麼辦法可以拯救一下這些操作或者在此事件發生前，有什麼途徑預防一下呢。

首先我們要先了解二進位制日誌

參考MySQL二進位制日誌總結

MySQL的二進位制日誌（binary log）是一個二進位制檔案，主要用於記錄修改資料或有可能引起資料變更的MySQL語句。二進位制日誌（binary log）中記錄了對MySQL資料庫執行更改的所有操作，並且記錄了語句發生時間、執行時長、運算元據等其它額外資訊，但是它不記錄SELECT、SHOW等那些不修改資料的SQL語句。二進位制日誌（binary log）主要用於資料庫恢復和主從複製，以及審計（audit）操作。

二進位制日誌是以事件形式記錄的，不是事務日誌(但可能是基於事務來記錄二進位制日誌)，不代表它只記錄innodb日誌，myisam表也一樣有二進位制日誌。

對於事務表的操作，二進位制日誌只在事務提交的時候一次性寫入(基於事務的innodb二進位制日誌)，提交前的每個二進位制日誌記錄都先cache，提交時寫入。

所以，對於事務表來說，一個事務中可能包含多條二進位制日誌事件，它們會在提交時一次性寫入。而對於非事務表的操作，每次執行完語句就直接寫入。

可通過命令show variables like '%log_bin%';檢視二進位制日誌是否開啟，可以檢視到目前我的二進位制日誌是開啟的

如何刪庫以後不跑路

MariaDB/MySQL預設沒有啟動二進位制日誌，需要在配置檔案中的[mysqld]部分設定log-bin也可以。注意：對於mysql 5.7，直接啟動binlog可能會導致mysql服務啟動失敗，這時需要在配置檔案中的mysqld為mysql例項分配server_id。

新增配置如下幾個引數

server-id=1
log-bin=[on|filename]
log_bin_index=filename.index 
#配置資訊為log-bin的filename加上.index,如我這邊log-bin配置為mysql-bin,這邊配置就為mysql-bin.index
binlog_format=[mixed|row|statement]
#STATEMENT模式（SBR）
#每一條會修改資料的sql語句會記錄到binlog中。優點是並不需要記錄每一條sql語句和每一行的資料變化，減少了binlog日誌量，節約IO，提高效能。缺點是在某些情況下會導致master-slave中的資料不一致(如sleep()函式， last_insert_id()，以及user-defined functions(udf)等會出現問題)

#ROW模式（RBR）
#不記錄每條sql語句的上下文資訊，僅需記錄哪條資料被修改了，修改成什麼樣了。而且不會出現某些特定情況下的儲存過程、或function、或trigger的呼叫和觸發無法被正確複製的問題。缺點是會產生大量的日誌，尤其是alter table的時候會讓日誌暴漲。

# MIXED模式（MBR）
#以上兩種模式的混合使用，一般的複製使用STATEMENT模式儲存binlog，對於STATEMENT模式無法複製的操作使用ROW模式儲存binlog，MySQL會根據執行的SQL語句選擇日誌儲存方式。

檢視二進位制日誌

通過命令show binary logs;可檢視二進位制日誌檔案

如何刪庫以後不跑路

在檢視二進位制日誌之前，我們先使用命令flush logs;清空一下二進位制日誌內容

再使用show master status檢視當前正在執行的二進位制日誌檔案，可以檢視到當前正在執行的二進位制日誌檔案為mysql-bin.000056;

如何刪庫以後不跑路

再通過命令show binlog events in 'mysql-bin.000056';檢視二進位制檔案事件內容，如下圖所示

如何刪庫以後不跑路

其中Log_name為當前二進位制檔名，Pos為當前事件開始偏移量，Event_type為當前事件型別，Server_id為配置檔案中配置的server-id值，End_log_pos為當前事件結束偏移量，Info為對事件的相關描述（如事務開始結束，執行sql語句等）

當然我們還可以使用mysqlbinlog工具

要檢視二進位制檔案具體內容，可執行mysqlbinlog [option] log-file1 log-file2...讀取解析二進位制檔案

以下是常用的幾個選項：

-d,--database=name：只檢視指定資料庫的日誌操作

-o,--offset= #：忽略掉日誌中的前n個操作命令

-r,--result-file=name：將輸出的日誌資訊輸出到指定的檔案中，使用重定向也一樣可以。

-s,--short-form：顯示簡單格式的日誌，只記錄一些普通的語句，會省略掉一些額外的資訊如位置資訊和時間資訊以及基於行的日誌。可以用來除錯，生產環境千萬不可使用

--set-charset=char_name：在輸出日誌資訊到檔案中時，在檔案第一行加上``set` `names char_name`

--start-datetime,--stop-datetime：指定輸出開始時間和結束時間內的所有日誌資訊

--start-position= #,--stop-position=#：指定輸出開始位置和結束位置內的所有日誌資訊

-v,-vv：顯示更詳細資訊，基於row的日誌預設不會顯示出來，此時使用-v或-vv可以檢視

我通過執行mysqlbinlog /www/server/data/mysql-bin.000056 > /root/binlog.txt將輸出內容重定向到binlog.txt檔案中

以下為二進位制檔案輸出的相關內容

# at 52746
#201019 14:33:53 server id 1  end_log_pos 52843 CRC32 0xfa71c68d     Query    thread_id=41332    exec_time=0    error_code=0
SET TIMESTAMP=1603089233/*!*/;
BEGIN
/*!*/;
# at 52843
#201019 14:33:53 server id 1  end_log_pos 53228 CRC32 0xac4385c3     Query    thread_id=41332    exec_time=0    error_code=0
SET TIMESTAMP=1603089233/*!*/;
update `goods` set `min_price_attr_id` = 66938, `max_price_attr_id` = 66938, `goods`.`updated_at` = '2020-10-18 23:33:53' where `id` = 666 and `goods`.`deleted_at` is null and (`store_id` = 3) and (`is_store` = 1 and `max_price` is not null and `min_price` is not null) order by `position` asc
/*!*/;
# at 53228
#201019 14:33:53 server id 1  end_log_pos 53259 CRC32 0x75346610     Xid = 1227571
COMMIT/*!*/;
# at 53259
#201019 14:36:25 server id 1  end_log_pos 53324 CRC32 0x19d0106c     Anonymous_GTID    last_committed=17    sequence_number=18    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 53324
#201019 14:36:25 server id 1  end_log_pos 53421 CRC32 0xb8303b88     Query    thread_id=41341    exec_time=0    error_code=0
SET TIMESTAMP=1603089385/*!*/;
BEGIN
/*!*/;
# at 53421
#201019 14:36:25 server id 1  end_log_pos 53806 CRC32 0xe0a65551     Query    thread_id=41341    exec_time=0    error_code=0
SET TIMESTAMP=1603089385/*!*/;
update `goods` set `min_price_attr_id` = 66938, `max_price_attr_id` = 66938, `goods`.`updated_at` = '2020-10-18 23:36:25' where `id` = 666 and `goods`.`deleted_at` is null and (`store_id` = 3) and (`is_store` = 1 and `max_price` is not null and `min_price` is not null) order by `position` asc
/*!*/;
# at 53806
#201019 14:36:25 server id 1  end_log_pos 53837 CRC32 0x79445d95     Xid = 1227807
COMMIT/*!*/;

內容說明參考mysql binlog之一 binlog輸出內容的說明

  • 以上資訊中有許多# at開頭的行，用於標示每個事件在binlog中的偏移量，例如# at 52746表示該事件在binlog的位置是52746位元組之後。兩個# at之間標示了mysql中的一個事件，而且在事件開始時也會通過end_log_pos標示事件結束的位置

  • server id 1表示產生該事務伺服器id號為1，這是在my.cnf檔案中定義的，該id在叢集內不能重複。一來是對事務產生的server做標識，二來是在類似雙主模式下的叢集環境裡用於中斷binlog迴圈複製重做的問題

  • CRC32用於校驗binlog檔案的完整性

  • GTID表示啟動了GTID屬性

  • last_committed=17事務組提交標識、同一組提交的事務具備相同的last_committed值，可以在從庫並行重放，以減少同步延遲。

  • sequence_number=18，事務對應的順序號，該值單調遞增，同時也標識了同一組提交事務的順序，在從庫設定slave_preserve_commit_order=1時，依賴該值模擬主庫的提交順序，在從庫提交。以達到資料落盤過程完全一致

  • SET @@SESSION.GTID_NEXT=’ANONYMOUS’,標識該事務使用的GTID序列號

  • SET TIMESTAMP=1603089385/!/;標識了該事務產生的時間，在一些特殊情況下保證資料一致性。比如使用了now函式的sql，可能延遲了很長時間才傳到備庫，此時now函式取該值插入，保證了與主庫執行時間的一致。

  • begin開始執行事務

  • Table_map: test.testxxxx mapped to number 185，標識要操作哪個庫的哪張表

  • update goods set min_price_attr_id = 66938, max_price_attr_id = 66938, goods.updated_at = ‘2020-10-18 23:36:25’ where id = 666 and goods.deleted_at is null and (store_id = 3) and (is_store = 1 and max_price is not null and min_price is not null) order by position asc為具體執行的sql語句

  • Xid = 1227807是保證資料完整性的一個標識，每個DML事務提交併完全寫入binlog中結尾處一定會有Xid（DDL是沒有的）。在redolog中也會記錄該Xid值。在2PC提交機制中用此標識在redolog和binlog進行對比，崩潰時如果redolog沒有commit，在redolog和binlog都有該值時，則事務重做，如果binlog沒有找到該值時，則事務回滾。如果redolog也沒有該值時，無需查詢binlog，事務直接回滾

  • 最後由COMMIT標識提交事務

刪庫前的準備工作

開啟二進位制日誌

在配置檔案中啟動相應的二進位制日誌

定期備份資料庫

可編寫資料備份指令碼，定期執行定時任務儲存備份

#! /bin/bash

user="root"
passwd="123456"
database="test"

BCK_DIR="/root/mysql-backups"  #備份檔案目錄
DATE=`date +%F`

# 建立檔案
mkdir -p $BCK_DIR/$DATE

mysqldump -u$user -p$passwd --databases $database > $BCK_DIR/$DATE/$database.sql
#crontab -e
#設定每天凌晨一點備份 
0 1 * * * /home/sh/dump.sh

實踐刪庫

在資料庫中建立名字為binlog的資料庫

如何刪庫以後不跑路

其中資料庫存在一張名為test的表

如何刪庫以後不跑路

在刪除資料庫之前，我們首先要對資料庫全庫進行全量備份mysqldump -uroot -p --all-databases > /backup/mysqldump/all.db

因為在執行二進位制日誌檔案撤回前，資料庫需要存在相關資料，比如相應的表結構資料等。使我們可以恢復到上一次備份前的資料，再通過增量日誌，使我們可以恢復到誤刪之前的環境

然後我們執行命令drop database binlog命令刪除該資料庫

首先我們通過備份的資料，回退到上一次備份的資料中，使用命令cat /backup/mysqldump/all.db > mysql -uroot -p

執行命令mysqlbinlog /www/server/data/mysql-bin.000058 > /root/mysql-bin.000058.txt,將二進位制日誌檔案內容輸出到txt檔案中

查詢二進位制檔案內容，並定位到drop database binlog語句的位置中

# at 331017
# at 331049
#201020 16:58:58 server id 1  end_log_pos 331049 CRC32 0x639270f4     Intvar
SET INSERT_ID=1603/*!*/;
#201020 16:58:58 server id 1  end_log_pos 331552 CRC32 0xf87e2911     Query    thread_id=51712    exec_time=0    error_code=0
SET TIMESTAMP=1603184338/*!*/;
insert into `trackers` (`type`, `type_name`, `value`, `currency`, `content`, `uuid`, `store_id`, `time`, `from_host`, `from_path`, `url_host`, `url_path`, `updated_at`, `created_at`) values (102, 'VIEW_GOODS_CONTENT', '{{$selectAttr[\'price\']}}', 'USD', NULL, '034DF7A7E1E3E1590C5FCF28BBB0A975', 3, 1603184338, 'test.belment.cn', '/', 'test.belment.cn', '/product/ODQ2', '2020-10-20 01:58:58', '2020-10-20 01:58:58')
/*!*/;
# at 331552
#201020 16:58:58 server id 1  end_log_pos 331583 CRC32 0xca2c5c6a     Xid = 1495527
COMMIT/*!*/;
# at 331583
#201020 16:59:35 server id 1  end_log_pos 331648 CRC32 0x57c85e3d     Anonymous_GTID    last_committed=319    sequence_number=320    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 331648
#201020 16:59:35 server id 1  end_log_pos 331746 CRC32 0xd22d3ecb     Query    thread_id=51675    exec_time=0    error_code=0
SET TIMESTAMP=1603184375/*!*/;
SET @@session.sql_mode=1342177280/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
drop database binlog
/*!*/;
# at 331746
#201020 16:59:48 server id 1  end_log_pos 331811 CRC32 0xf21596d0     Anonymous_GTID    last_committed=320    sequence_number=321    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 331811
#201020 16:59:48 server id 1  end_log_pos 331908 CRC32 0x8acc8fd4     Query    thread_id=51719    exec_time=0    error_code=0
SET TIMESTAMP=1603184388/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=224,@@session.collation_connection=224,@@session.collation_server=45/*!*/;
BEGIN
/*!*/;

由此可見，我們應該將二進位制檔案偏移量重置到331583

mysqlbinlog /www/server/data/mysql-bin.000058 --stop-position=331583 | mysql -u root -p

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章