mysql刪除表資料如何恢復

2022-11-11 14:02:12

實驗模擬

該實驗必須是mysql開啟了binlog的情況。

binlog紀錄檔，即binary log，是二進位制紀錄檔檔案。它有兩個作用，

一是增量備份，即只備份新增的內容；
二是用於主從複製等，即主節點維護了一個binlog紀錄檔檔案，從節點從binlog中同步資料。

我們可以通過binlog紀錄檔恢復資料

檢視mysql是否開啟binlog語句

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

如果是OFF，則說明沒有開啟binlog。可以使用如下方法開啟binlog

編輯/etc/my.cnf檔案，在檔案後面增加如下兩行程式碼即可

server-id=1
log-bin=/var/lib/mysql/mysql-bin

server-id表示單個結點的id，這裡由於只有一個結點，所以可以把id隨機指定為一個數，這裡將id設定成1。若叢集中有多個結點，則id不能相同

第二句是指定binlog紀錄檔檔案的名字為mysql-bin，以及其儲存路徑

重啟讓設定生效。

[root@localhost ~]# systemctl stop mysqld.service
[root@localhost ~]# systemctl start mysqld.service

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

建立資料庫，即相關表

CREATE DATABASE `hbk`  DEFAULT CHARACTER SET utf8 ;
use hbk;
CREATE TABLE `t_delete_test` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入相關測試資料，在這裡，我使用使用者端工具插入的，也可以使用手工執行insert into語句。

模擬資料庫誤刪除操作，把id從1-4的資料不小心刪除，只留唐僧師徒4人，然後如何恢復所有資料。

mysql> delete from t_delete_test where id<=4;
Query OK, 4 rows affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

恢復實戰

由於在/etc/my.cnf設定了binlog，這個紀錄檔會儲存插入，刪除，修改的紀錄檔資訊

mysql > show binlog events in 'mysql-bin.000001'G

在3101的時刻進行了delete操作，從704建表之後的操作開始恢復

[root@localhost mysql]# /usr/bin/mysqlbinlog  /var/lib/mysql/mysql-bin.000001 --start-position=704 --stop-position=3101 |mysql -uroot -p hbk

執行之後，成功恢復了之前刪除的資料。

常用引數選項解釋

--start-position=875 起始pos點
--stop-position=954 結束pos點
--start-datetime="2016-9-25 22:01:08" 起始時間點
--stop-datetime="2019-9-25 22:09:46" 結束時間點
--database=zyyshop 指定只恢復zyyshop資料庫(一臺主機上往往有多個資料庫，只限本地log紀錄檔)

不常用選項

-u --user=name 連線到遠端主機的使用者名稱
-p --password[=name] 連線到遠端主機的密碼
-h --host=name 從遠端主機上獲取binlog紀錄檔
--read-from-remote-server 從某個MySQL伺服器上讀取binlog紀錄檔

a）查詢第一個(最早)的binlog紀錄檔：

mysql> show binlog eventsG;

b）指定查詢 mysql-bin.000002這個檔案：

mysql> show binlog events in 'mysql-bin.000002'G;

c）指定查詢 mysql-bin.000002這個檔案，從pos點:624開始查起：

mysql> show binlog events in 'mysql-bin.000002' from 624G;

d）指定查詢 mysql-bin.000002這個檔案，從pos點:624開始查起，查詢10條（即10條語句）

mysql> show binlog events in 'mysql-bin.000002' from 624 limit 10G;

e）指定查詢 mysql-bin.000002這個檔案，從pos點:624開始查起，偏移2行（即中間跳過2個），查詢10條

mysql> show binlog events in 'mysql-bin.000002' from 624 limit 2,10G;

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。

mysql刪除表資料如何恢復

目錄

實驗模擬

恢復實戰

常用引數選項解釋

不常用選項

熱門文章