MySQL資料庫表誤刪除恢復(一)

chenoracle發表於2023-05-14

場景一:MySQL從庫單張表誤刪除恢復
場景二:MySQL恢復指定表結構
場景三:MySQL恢復指定表資料
場景四:MySQL恢復指定庫
場景五:MySQL恢復所有庫資料
場景六:MySQL恢復指定表到指定時間點

場景一:MySQL 從庫單張表誤刪除恢復

適用於:

MySQL 主從架構,不適用雙主架構。
測試環境:
OS:Redhat 7.9
DB:MySQL 8.0.20
主庫:172.16.11.137
從庫:172.16.11.138

測試資料:

主庫:

use cjc;
create table t2(id int,time time);
insert into t2 values(1,now());
insert into t2 values(2,now());
insert into t2 values(3,now());

模擬誤刪除操作

從庫:

use cjc;
select * from cjc.t2;
drop table t2;

主庫繼續插入資料:

insert into t2 values(4,now());
insert into t2 values(5,now());
insert into t2 values(6,now());

此時從庫同步報錯:

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.11.137
                  Master_User: repl
                  Master_Port: 13309
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000010
          Read_Master_Log_Pos: 2662
               Relay_Log_File: mysql-relay-bin.000021
                Relay_Log_Pos: 1848
        Relay_Master_Log_File: mysql-bin.000010
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1146
                   Last_Error: Error executing row event: 'Table 'cjc.t2' doesn't exist'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1633
              Relay_Log_Space: 3173
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1146
               Last_SQL_Error: Error executing row event: 'Table 'cjc.t2' doesn't exist'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1137
                  Master_UUID: b64d7590-ebb6-11ed-b20e-0800272944a2
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 230510 16:44:48
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: b64d7590-ebb6-11ed-b20e-0800272944a2:1-465543
            Executed_Gtid_Set: b64d7590-ebb6-11ed-b20e-0800272944a2:1-465540
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)
ERROR: 
No query specified

檢查t2對應的儲存過程還在

select routine_schema,routine_name from information_schema.routines where routine_schema='cjc';
+----------------+-------------------+
| ROUTINE_SCHEMA | ROUTINE_NAME      |
+----------------+-------------------+
| cjc            | proc_batch_insert |
+----------------+-------------------+
1 row in set (0.01 sec)
show create procedure proc_batch_insert\G;

解決方案:

一.從庫上忽略該表的同步:

STOP SLAVE SQL_THREAD;
CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE =  ('cjc.t2');
start SLAVE SQL_THREAD;

此時並不影響其他表資料的同步。

檢視主從同步狀態:

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.11.137
                  Master_User: repl
                  Master_Port: 13309
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000010
          Read_Master_Log_Pos: 2662
               Relay_Log_File: mysql-relay-bin.000021
                Relay_Log_Pos: 2877
        Relay_Master_Log_File: mysql-bin.000010
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: cjc.t2
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2662
              Relay_Log_Space: 3173
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1137
                  Master_UUID: b64d7590-ebb6-11ed-b20e-0800272944a2
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: b64d7590-ebb6-11ed-b20e-0800272944a2:1-465543
            Executed_Gtid_Set: b64d7590-ebb6-11ed-b20e-0800272944a2:1-465543
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)
ERROR: 
No query specified

此時SQL執行緒恢復正常

主庫繼續插入資料:

insert into t2 values(7,now());
insert into t2 values(8,now());

二:恢復從庫t2表資料

0).關閉從庫同步

stop slave;

1).主庫新增讀鎖

use cjc;
LOCK TABLES `t2` READ;

讀鎖和寫鎖說明:

讀鎖

LOCK TABLES `t2` READ;

當前會話可以讀取t2表資料,無法寫入資料,立即返回錯誤ERROR 1099。

mysql> insert into t2 values(10,now());
ERROR 1099 (HY000): Table 't2' was locked with a READ lock and can't be updated

其他會話可以讀取t2表資料,無法寫入資料,寫入時會卡住,Waiting for table metadata lock。

寫鎖

LOCK TABLES `t2` WRITE;

鎖定後,當前會話仍然可以讀、寫t2表,其他會話對t2表不可讀、不可寫。

2).主庫備份

注意:

mysqldump中如果新增了--master-data=2或--flush-logs引數會導致備份時執行:

FLUSH TABLES;
FLUSH TABLES WITH READ LOCK;

由於主庫已經將t2表改只讀狀態,導致mysqldump時FLUSH TABLES操作卡住,無法正常備份表。

所以備份時需要去掉--master-data=2和--flush-logs引數。

備份

mysqldump -uroot -p cjc t2 --hex-blob --single-transaction --set-gtid-purged=OFF --extended-insert=TRUE --net-buffer-length=16777216 --max-allowed-packet=67108864 > /mysqldata/bak/mysql_t2_20230510.sql

傳到備庫

scp mysql_t2_20230510.sql 172.16.11.138:/mysqldata/bak

3).匯入到從庫

mysql> source /mysqldata/bak/mysql_t2_20230510.sql

從庫

more /mysqldata/bak/mysql_t2_20230510.sql
tail -n 10 /mysqldata/bak/mysql_t2_20230510.sql
mysql -uroot -p  cjc  < /mysqldata/bak/mysql_t2_20230510.sql

4).忽略過濾

從庫檢查表資料

mysql> select count(*) from cjc.t2;

忽略過濾

CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE =  ();

注意:無單引號

檢查同步:

SHOW SLAVE STATUS\G;

5).從庫:啟動同步

start slave;

6).主、從:驗證資料量是否一致  

select count(*) from cjc.t2;

7).主庫:解鎖表

UNLOCK TABLES;

場景二:MySQL恢復指定表結構
場景三:MySQL恢復指定表資料
場景四:MySQL恢復指定庫
場景五:MySQL恢復所有庫資料
場景六:MySQL恢復指定表到指定時間點

適用於:

MySQL 主從、雙主、單機等
測試環境:
OS:Redhat 7.9
DB:MySQL 8.0.20
主庫:172.16.11.137
從庫:172.16.11.138

建立測試資料

use cjc;
mysql> select * from cjc.t1;
+------+----------+
| id   | time     |
+------+----------+
|    1 | 14:40:56 |
|    1 | 15:17:57 |
+------+----------+

先看一下mysqldump備份選項:

執行資料庫全備

mysqldump --help

全庫備份

mysqldump -uroot -p --all-databases --hex-blob --single-transaction --set-gtid-purged=OFF --master-data=2 --flush-logs --routines --triggers --events --extended-insert=TRUE --net-buffer-length=16777216 --max-allowed-packet=67108864 > /mysqldata/bak/mysql_bak1.sql

指定資料庫備份

mysqldump -uroot -p cjc --hex-blob --single-transaction --set-gtid-purged=OFF --master-data=2 --flush-logs --routines --triggers --events --extended-insert=TRUE --net-buffer-length=16777216 --max-allowed-packet=67108864 > /mysqldata/bak/mysql_bak2.sql

指定表備份

mysqldump -uroot -p cjc t2 t3 --hex-blob --single-transaction --set-gtid-purged=OFF --master-data=2 --flush-logs --extended-insert=TRUE --net-buffer-length=16777216 --max-allowed-packet=67108864 > /mysqldata/bak/mysql_bak3.sql

只備份表結構

mysqldump -uroot -p cjc t3 --no-data --hex-blob --single-transaction --set-gtid-purged=OFF --master-data=2 --flush-logs --extended-insert=TRUE --net-buffer-length=16777216 --max-allowed-packet=67108864 > /mysqldata/bak/mysql_bak4.sql

只備份表資料

mysqldump -uroot -p cjc t3 --no-create-info --hex-blob --single-transaction --set-gtid-purged=OFF --master-data=2 --flush-logs --extended-insert=TRUE --net-buffer-length=16777216 --max-allowed-packet=67108864 > /mysqldata/bak/mysql_bak4.sql

其中:

--single-transaction 引數會新增下面額外執行:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */

--master-data=2和--flush-logs都會新增下面額外操作,注意鎖表:

FLUSH /*!40101 LOCAL */ TABLES
FLUSH TABLES WITH READ LOCK

繼續插入資料

insert into t1 values(2,now());
insert into t1 values(3,now());
insert into t1 values(2,now());

檢視資料

mysql> select * from cjc.t1;
+------+----------+
| id   | time     |
+------+----------+
|    1 | 14:40:56 |
|    1 | 15:17:57 |
|    2 | 10:39:06 |
|    3 | 10:39:06 |
|    2 | 10:39:15 |
+------+----------+
5 rows in set (0.00 sec)

場景二:MySQL恢復指定表結構


通常定時備份是備份所有資料庫--all-databases,如何透過備份檔案恢復所需的資料?

mysqldump -uroot -p --all-databases --hex-blob --single-transaction --set-gtid-purged=OFF --master-data=2 --flush-logs --routines --triggers --events --extended-insert=TRUE --net-buffer-length=16777216 --max-allowed-packet=67108864 > /mysqldata/bak/mysql_bak1.sql

1:恢復指定表結構

例如:cjc庫t2表

全備備份檔案

/mysqldata/bak/mysql_bak1.sql

恢復表結構

方式1:全備資料量很小時,直接透過vi進行查詢

[mysql@cjc-db-01 bak]$ ls -lrth mysql_bak1.sql
-rw-rw-r-- 1 mysql mysql 1.0M May 12 10:34 mysql_bak1.sql
[mysql@cjc-db-01 bak]$ vi /mysqldata/bak/mysql_bak1.sql 
---DROP TABLE IF EXISTS `t2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t2` (
  `id` int DEFAULT NULL,
  `time` time DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

方式2:透過sed方式查詢中備份檔案中備份t2表結構部分

sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `t2`/!d;q'  /mysqldata/bak/mysql_bak1.sql

示例如下:

---DROP TABLE IF EXISTS `t2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t2` (
  `id` int DEFAULT NULL,
  `time` time DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

場景三:MySQL恢復指定表資料

例如:cjc庫t1表

方式1:全備資料量很小時,直接透過vi進行查詢

[mysql@cjc-db-01 bak]$ ls -lrth mysql_bak1.sql
-rw-rw-r-- 1 mysql mysql 1.0M May 12 10:34 mysql_bak1.sql
[mysql@cjc-db-01 bak]$ vi /mysqldata/bak/mysql_bak1.sql 
INSERT INTO `t1` VALUES (1,'14:40:56'),(1,'15:17:57');

方式2:透過grep方式查詢中備份檔案中備份t1表資料

grep -i 'INSERT INTO `t1`' /mysqldata/bak/mysql_bak1.sql

示例如下:

[mysql@cjc-db-01 bak]$ grep -i 'INSERT INTO `t1`' /mysqldata/bak/mysql_bak1.sql 
INSERT INTO `t1` VALUES (1,'14:40:56'),(1,'15:17:57');


場景四:MySQL恢復指定庫

透過--all-databases方式備份檔案恢復指定資料庫資料。

例如:cjc庫資料


方式1:使用引數--one-database 

[mysql@cjc-db-01 bak]$ mysql --help|grep database
Usage: mysql [OPTIONS] [database]
  -D, --database=name Database to use.
  -o, --one-database  Ignore statements except those that occur while the default database is the one named at the command line.

還原:

mysql -uroot -p --one-database cjc < /mysqldata/bak/mysql_bak1.sql

透過general_log可以看到,只還原了cjc庫。

2023-05-12T11:22:55.954721+08:00   16 QueryCREATE DATABASE /*!32312 IF NOT EXISTS*/ `cjc` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
2023-05-12T11:22:56.183843+08:00   16 QueryUSE `cjc`
2023-05-12T11:22:56.184529+08:00   16 QuerySELECT DATABASE()
2023-05-12T11:22:56.186302+08:00   16 Init DBcjc
2023-05-12T11:22:56.186739+08:00   16 Init DBcjc
2023-05-12T11:22:56.187086+08:00   16 QueryDROP TABLE IF EXISTS `t1`
2023-05-12T11:22:56.398024+08:00   16 Query/*!40101 SET @saved_cs_client     = @@character_set_client */
2023-05-12T11:22:56.398445+08:00   16 Query/*!50503 SET character_set_client = utf8mb4 */
2023-05-12T11:22:56.399049+08:00   16 QueryCREATE TABLE `t1` (

方式2:

sed -n '/^-- Current Database: `cjc`/,/^-- Current Database: `/p'  /mysqldata/bak/mysql_bak1.sql > createdb_cjc.sql

檢視檔案

ls -lrth createdb_cjc.sql
vi createdb_cjc.sql

示例如下:

-- Current Database: `cjc`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `cjc` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `cjc`;
......

場景五:MySQL恢復所有庫資料

還原--all-databases備份的所有資料。

mysql -uroot -p < /mysqldata/bak/mysql_bak1.sql

由於恢復了mysql庫,還原完資料庫後需要執行flush privileges;操作,或在備份是指定--flush-privileges。

引數說明如下:

[mysql@cjc-db-01 bak]$ mysqldump --help|grep privilege
  --flush-privileges  Emit a FLUSH PRIVILEGES statement after dumping the mysql
  
flush privileges;

場景六:MySQL恢復指定表到指定時間點

生成測試資料

use cjc;
insert into t1 values(1,now());
insert into t1 values(2,now());
insert into t1 values(3,now());

mysql> select * from t1;
+------+----------+
| id   | time     |
+------+----------+
|    1 | 13:41:51 |
|    2 | 13:41:56 |
|    3 | 13:42:04 |
+------+----------+
3 rows in set (0.00 sec)

備份表

mysqldump -uroot -p cjc t1 --hex-blob --single-transaction --set-gtid-purged=OFF --master-data=2 --flush-logs --extended-insert=TRUE --net-buffer-length=16777216 --max-allowed-packet=67108864 > /mysqldata/bak/mysql_t1_bak.sql

繼續插入資料庫

insert into t1 values(4,now());
insert into t1 values(5,now());

更新資料

update t1 set id=500 where id=5;

檢視當前資料

mysql> select * from t1;
+------+----------+
| id   | time     |
+------+----------+
|    1 | 13:41:51 |
|    2 | 13:41:56 |
|    3 | 13:42:04 |
|    4 | 13:45:49 |
|  500 | 13:46:07 |
+------+----------+
5 rows in set (0.00 sec)

刪除資料,模擬誤刪除

delete t1 where 1=1;

如何將資料恢復到刪除前時刻,恢復誤刪除的資料?

主庫檢視資訊

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000019
         Position: 1594
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: b64d7590-ebb6-11ed-b20e-0800272944a2:1-465835
1 row in set (0.00 sec)
ERROR: 
No query specified

檢視binlog資訊

mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 219625469 | No        |
| mysql-bin.000002 |       219 | No        |
| mysql-bin.000003 |      1272 | No        |
| mysql-bin.000004 |       219 | No        |
| mysql-bin.000005 |      4211 | No        |
| mysql-bin.000006 |     93888 | No        |
| mysql-bin.000007 |       403 | No        |
| mysql-bin.000008 |       243 | No        |
| mysql-bin.000009 |      1939 | No        |
| mysql-bin.000010 |      5796 | No        |
| mysql-bin.000011 |       219 | No        |
| mysql-bin.000012 |     46818 | No        |
| mysql-bin.000013 |       243 | No        |
| mysql-bin.000014 |       243 | No        |
| mysql-bin.000015 |       243 | No        |
| mysql-bin.000016 |       243 | No        |
| mysql-bin.000017 |   2041790 | No        |
| mysql-bin.000018 |       243 | No        |
| mysql-bin.000019 |      1594 | No        |
+------------------+-----------+-----------+
19 rows in set (0.15 sec)

檢視event資訊

mysql> show binlog events in 'mysql-bin.000019' limit 30;
+------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                   |
+------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------+
| mysql-bin.000019 |    4 | Format_desc    |      1137 |         125 | Server ver: 8.0.20, Binlog ver: 4                                      |
| mysql-bin.000019 |  125 | Previous_gtids |      1137 |         196 | b64d7590-ebb6-11ed-b20e-0800272944a2:1-465831                          |
| mysql-bin.000019 |  196 | Gtid           |      1137 |         275 | SET @@SESSION.GTID_NEXT= 'b64d7590-ebb6-11ed-b20e-0800272944a2:465832' |
| mysql-bin.000019 |  275 | Query          |      1137 |         362 | BEGIN                                                                  |
| mysql-bin.000019 |  362 | Rows_query     |      1137 |         416 | # insert into t1 values(4,now())                                       |
| mysql-bin.000019 |  416 | Table_map      |      1137 |         465 | table_id: 413 (cjc.t1)                                                 |
| mysql-bin.000019 |  465 | Write_rows     |      1137 |         508 | table_id: 413 flags: STMT_END_F                                        |
| mysql-bin.000019 |  508 | Xid            |      1137 |         539 | COMMIT /* xid=1942 */                                                  |
| mysql-bin.000019 |  539 | Gtid           |      1137 |         618 | SET @@SESSION.GTID_NEXT= 'b64d7590-ebb6-11ed-b20e-0800272944a2:465833' |
| mysql-bin.000019 |  618 | Query          |      1137 |         705 | BEGIN                                                                  |
| mysql-bin.000019 |  705 | Rows_query     |      1137 |         759 | # insert into t1 values(5,now())                                       |
| mysql-bin.000019 |  759 | Table_map      |      1137 |         808 | table_id: 413 (cjc.t1)                                                 |
| mysql-bin.000019 |  808 | Write_rows     |      1137 |         851 | table_id: 413 flags: STMT_END_F                                        |
| mysql-bin.000019 |  851 | Xid            |      1137 |         882 | COMMIT /* xid=1943 */                                                  |
| mysql-bin.000019 |  882 | Gtid           |      1137 |         961 | SET @@SESSION.GTID_NEXT= 'b64d7590-ebb6-11ed-b20e-0800272944a2:465834' |
| mysql-bin.000019 |  961 | Query          |      1137 |        1049 | BEGIN                                                                  |
| mysql-bin.000019 | 1049 | Rows_query     |      1137 |        1104 | # update t1 set id=500 where id=5                                      |
| mysql-bin.000019 | 1104 | Table_map      |      1137 |        1153 | table_id: 413 (cjc.t1)                                                 |
| mysql-bin.000019 | 1153 | Update_rows    |      1137 |        1202 | table_id: 413 flags: STMT_END_F                                        |
| mysql-bin.000019 | 1202 | Xid            |      1137 |        1233 | COMMIT /* xid=1944 */                                                  |
| mysql-bin.000019 | 1233 | Gtid           |      1137 |        1312 | SET @@SESSION.GTID_NEXT= 'b64d7590-ebb6-11ed-b20e-0800272944a2:465835' |
| mysql-bin.000019 | 1312 | Query          |      1137 |        1391 | BEGIN                                                                  |
| mysql-bin.000019 | 1391 | Rows_query     |      1137 |        1439 | # delete from t1 where 1=1                                             |
| mysql-bin.000019 | 1439 | Table_map      |      1137 |        1488 | table_id: 413 (cjc.t1)                                                 |
| mysql-bin.000019 | 1488 | Delete_rows    |      1137 |        1563 | table_id: 413 flags: STMT_END_F                                        |
| mysql-bin.000019 | 1563 | Xid            |      1137 |        1594 | COMMIT /* xid=1947 */                                                  |
+------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------+
26 rows in set (0.00 sec)

解析binlog資料

mysql -uroot -p -e"show binlog events in 'mysql-bin.000019';" |grep -i t1 > t1_binlog.sql

資訊如下:

[mysql@cjc-db-01 binlog]$ mysql -uroot -p -e"show binlog events in 'mysql-bin.000019';" |grep -i t1 
Enter password: 
mysql-bin.000019362Rows_query1137416# insert into t1 values(4,now())
mysql-bin.000019416Table_map1137465table_id: 413 (cjc.t1)
mysql-bin.000019705Rows_query1137759# insert into t1 values(5,now())
mysql-bin.000019759Table_map1137808table_id: 413 (cjc.t1)
mysql-bin.0000191049Rows_query11371104# update t1 set id=500 where id=5
mysql-bin.0000191104Table_map11371153table_id: 413 (cjc.t1)
mysql-bin.0000191391Rows_query11371439# delete from t1 where 1=1
mysql-bin.0000191439Table_map11371488table_id: 413 (cjc.t1)
mysql-bin.0000191671Query11371811use `cjc`; DROP TABLE IF EXISTS `t1` /* generated by server */ /* xid=1969 */
mysql-bin.0000191890Query11372119use `cjc`; CREATE TABLE `t1` (\n  `id` int DEFAULT NULL,\n  `time` time DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci /* xid=1972 */
mysql-bin.0000192196Query11372328use `cjc`; /*!40000 ALTER TABLE `t1` DISABLE KEYS */ /* xid=1975 */
mysql-bin.0000192486Rows_query11372578# INSERT INTO `t1` VALUES (1,'13:41:51'),(2,'13:41:56'),(3,'13:42:04')
mysql-bin.0000192578Table_map11372627table_id: 431 (cjc.t1)
mysql-bin.0000192794Query11372925use `cjc`; /*!40000 ALTER TABLE `t1` ENABLE KEYS */ /* xid=1977 */

或者

mysql -uroot -p -e"show binlog events in 'mysql-bin.000019';"  > t1a_binlog.sql

備份資料還原

mysql -uroot -p cjc < /mysqldata/bak/mysql_t1_bak.sql

查詢資料

mysql> select * from cjc.t1;
+------+----------+
| id   | time     |
+------+----------+
|    1 | 13:41:51 |
|    2 | 13:41:56 |
|    3 | 13:42:04 |
+------+----------+
3 rows in set (0.00 sec)

已將t1表還原到備份時間點資料。

需要透過binlog將資料推進到誤刪除資料前一時刻。

檢視備份檔案位置資訊

[mysql@cjc-db-01 bak]$ cat mysql_t1_bak.sql |grep CHANGE
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=196;

查詢delete位置資訊

前面查到的

| mysql-bin.000019 | 1049 | Rows_query     |      1137 |        1104 | # update t1 set id=500 where id=5                                      |
| mysql-bin.000019 | 1104 | Table_map      |      1137 |        1153 | table_id: 413 (cjc.t1)                                                 |
| mysql-bin.000019 | 1153 | Update_rows    |      1137 |        1202 | table_id: 413 flags: STMT_END_F                                        |
| mysql-bin.000019 | 1202 | Xid            |      1137 |        1233 | COMMIT /* xid=1944 */                                                  |
| mysql-bin.000019 | 1233 | Gtid           |      1137 |        1312 | SET @@SESSION.GTID_NEXT= 'b64d7590-ebb6-11ed-b20e-0800272944a2:465835' |
| mysql-bin.000019 | 1312 | Query          |      1137 |        1391 | BEGIN                                                                  |
| mysql-bin.000019 | 1391 | Rows_query     |      1137 |        1439 | # delete from t1 where 1=1

或者

mysqlbinlog --no-defaults --base64-output=decode-rows --verbose -vvv  /mysqldata/13309/binlog/mysql-bin.000019 > 19.log 
vi 19.log

......
COMMIT/*!*/;
# at 1233
#230512 13:47:23 server id 1137  end_log_pos 1312 CRC32 0xfaa0e159      GTID    last_committed=3        sequence_number=4       rbr_only=yes    original_committed_timestamp=1683870443366610   immediate_commit_timestamp=1683870443366610     transaction_length=361
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1683870443366610 (2023-05-12 13:47:23.366610 CST)
# immediate_commit_timestamp=1683870443366610 (2023-05-12 13:47:23.366610 CST)
/*!80001 SET @@session.original_commit_timestamp=1683870443366610*//*!*/;
/*!80014 SET @@session.original_server_version=80020*//*!*/;
/*!80014 SET @@session.immediate_server_version=80020*//*!*/;
SET @@SESSION.GTID_NEXT= 'b64d7590-ebb6-11ed-b20e-0800272944a2:465835'/*!*/;
# at 1312
#230512 13:47:23 server id 1137  end_log_pos 1391 CRC32 0xbc6d1505      Query   thread_id=19    exec_time=0     error_code=0
SET TIMESTAMP=1683870443/*!*/;
BEGIN
/*!*/;
# at 1391
#230512 13:47:23 server id 1137  end_log_pos 1439 CRC32 0x75c2251d      Rows_query
# delete from t1 where 1=1
# at 1439
#230512 13:47:23 server id 1137  end_log_pos 1488 CRC32 0x6117e485      Table_map: `cjc`.`t1` mapped to number 413
# at 1488

誤刪除的位置  1312

這個位置應該在delete的上一個事務COMMIT下面的位置,也就是delete前面SET @@SESSION.GTID_NEXT對應的位置資訊。

生成還原語句

先生成可讀的檔案,透過vi等校驗是否有問題

mysqlbinlog -vvv --base64-output=decode-rows --skip-gtids --start-position=196 --stop-position=1312 -d cjc /mysqldata/13309/binlog/mysql-bin.000019 > t1_del_1_binlog.sql

在去掉-vvv --base64-output=decode-rows引數,生成最終恢復檔案

mysqlbinlog -vvv --base64-output=decode-rows --skip-gtids --start-position=196 --stop-position=1312 -d cjc /mysqldata/13309/binlog/mysql-bin.000019 > t1_del_binlog.sql

注意:

這裡必須選擇上一條語句commit之後的position,不能是刪除語句開始的position 1439,否則會有這個警告。
WARNING: The range of printed events ends with a row event or a table map event that does not have the STMT_END_F flag set. This might be because the last statement was not fully written to the log, or because you are using a --stop-position or --stop-datetime that refers to an event in the middle of a statement. The event(s) from the partial statement have not been written to output.
警告:列印的事件範圍以未設定STMT_END_F標誌的行事件或表對映事件結束。這可能是因為最後一條語句沒有完全寫入日誌,或者是因為您使用了--stop-position或--stop-datetime來引用語句中間的事件。分部語句中的事件尚未寫入輸出。
使用 --skip-gtids=true 引數
如果要恢復資料到源資料庫或者和源資料庫有相同 GTID 資訊的例項,那麼就要使用該引數,否則無法恢復成功的。
因為相同的GTID事務已經在源資料庫執行過了,根據 GTID 特性,一個 GTID 資訊在一個資料庫只能執行一次,所以預設不會恢復成功。

檢視日誌內容

[mysql@cjc-db-01 bak]$ cat t1_del_binlog.sql 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 125
#230512 13:44:00 server id 1137  end_log_pos 125 CRC32 0x7886bfc5 Start: binlog v 4, server v 8.0.20 created 230512 13:44:00
BINLOG '
INJdZA9xBAAAeQAAAH0AAAAAAAQAOC4wLjIwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigBxb+GeA==
'/*!*/;
/*!50616 SET @@SESSION.GTID_NEXT='AUTOMATIC'*//*!*/;
# at 196
# at 275
#230512 13:45:49 server id 1137  end_log_pos 362 CRC32 0x14c766fd Querythread_id=19exec_time=0error_code=0
SET TIMESTAMP=1683870349/*!*/;
SET @@session.pseudo_thread_id=19/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1306525696/*!*/;
SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=45/*!*/;
SET @@session.time_zone='+08:00'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 362
# at 416
#230512 13:45:49 server id 1137  end_log_pos 465 CRC32 0x406f74e5 Table_map: `cjc`.`t1` mapped to number 413
# at 465
#230512 13:45:49 server id 1137  end_log_pos 508 CRC32 0x3ebb25d0 Write_rows: table id 413 flags: STMT_END_F
BINLOG '
jdJdZBNxBAAAMQAAANEBAAAAAJ0BAAAAAAEAA2NqYwACdDEAAgMTAQADAQEA5XRvQA==
jdJdZB5xBAAAKwAAAPwBAAAAAJ0BAAAAAAEAAgAC/wAEAAAAgNtx0CW7Pg==
'/*!*/;
# at 508
#230512 13:45:49 server id 1137  end_log_pos 539 CRC32 0x12c8a010 Xid = 1942
COMMIT/*!*/;
# at 539
# at 618
#230512 13:46:07 server id 1137  end_log_pos 705 CRC32 0x0b291193 Querythread_id=19exec_time=0error_code=0
SET TIMESTAMP=1683870367/*!*/;
BEGIN
/*!*/;
# at 705
# at 759
#230512 13:46:07 server id 1137  end_log_pos 808 CRC32 0x52ba1ef9 Table_map: `cjc`.`t1` mapped to number 413
# at 808
#230512 13:46:07 server id 1137  end_log_pos 851 CRC32 0xa22ac978 Write_rows: table id 413 flags: STMT_END_F
BINLOG '
n9JdZBNxBAAAMQAAACgDAAAAAJ0BAAAAAAEAA2NqYwACdDEAAgMTAQADAQEA+R66Ug==
n9JdZB5xBAAAKwAAAFMDAAAAAJ0BAAAAAAEAAgAC/wAFAAAAgNuHeMkqog==
'/*!*/;
# at 851
#230512 13:46:07 server id 1137  end_log_pos 882 CRC32 0xa21394b5 Xid = 1943
COMMIT/*!*/;
# at 882
# at 961
#230512 13:46:40 server id 1137  end_log_pos 1049 CRC32 0xd9685b01 Querythread_id=19exec_time=0error_code=0
SET TIMESTAMP=1683870400/*!*/;
BEGIN
/*!*/;
# at 1049
# at 1104
#230512 13:46:40 server id 1137  end_log_pos 1153 CRC32 0xa6a2b51d Table_map: `cjc`.`t1` mapped to number 413
# at 1153
#230512 13:46:40 server id 1137  end_log_pos 1202 CRC32 0xd4562c93 Update_rows: table id 413 flags: STMT_END_F
BINLOG '
wNJdZBNxBAAAMQAAAIEEAAAAAJ0BAAAAAAEAA2NqYwACdDEAAgMTAQADAQEAHbWipg==
wNJdZB9xBAAAMQAAALIEAAAAAJ0BAAAAAAEAAgAC/wEABQAAAIDbhwD0AQAAkyxW1A==
'/*!*/;
# at 1202
#230512 13:46:40 server id 1137  end_log_pos 1233 CRC32 0xd8b6a2ec Xid = 1944
COMMIT/*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

重新整理binlog

mysql> flush logs;

還原誤刪除的資料:

通常會先將資料還原到測試庫裡,確保沒問題以後,在匯出匯入到生產庫。

mysql -uroot -p < t1_del_binlog.sql

檢視資料

已恢復到誤刪除前的資料

mysql> select * from cjc.t1;
+------+----------+
| id   | time     |
+------+----------+
|    1 | 13:41:51 |
|    2 | 13:41:56 |
|    3 | 13:42:04 |
|    4 | 13:45:49 |
|  500 | 13:46:07 |
+------+----------+
5 rows in set (0.00 sec)



###chenjuchao 20230512###



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29785807/viewspace-2951942/,如需轉載,請註明出處,否則將追究法律責任。

相關文章