MySQL資料庫表誤刪除恢復(一)
場景一：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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【北亞資料恢復】誤刪除oracle表和誤刪除oracle表資料的資料恢復方法資料恢復Oracle
- 寶塔資料庫恢復 mysql資料庫丟失恢復 mysql資料庫刪除庫恢復 寶塔mysql資料庫恢復資料庫MySql
- mysql資料庫誤刪除後的資料恢復操作說明MySql資料庫資料恢復
- MySQL 資料庫誤刪除後的資料恢復操作說明MySql資料庫資料恢復
- mysql誤刪資料恢復MySql資料恢復
- oracle恢復誤刪除資料Oracle
- Sybase ASE資料庫恢復，Sybase資料恢復，資料誤刪除恢復工具READSYBDEVICE資料庫資料恢復dev
- mysql 誤刪除表內資料，透過binlog日誌恢復MySql
- 【oracle資料庫資料恢復】誤操作導致的資料庫誤刪除的資料恢復案例Oracle資料庫資料恢復
- 恢復Oracle資料庫誤刪除資料的語句Oracle資料庫
- 【EM】資料表誤刪除故障模擬及恢復
- Oracle閃回刪除恢復誤刪資料Oracle
- SQL Server資料庫恢復，SQL Server資料恢復，SQL Server資料誤刪除恢復工具SQLRescueSQLServer資料庫資料恢復
- 【北亞資料庫資料恢復】使用delete未加where子句刪除全表資料的Mysql資料庫資料恢復資料庫資料恢復deleteMySql
- 【資料庫資料恢復】LINUX環境下ORACLE資料庫誤刪除的資料恢復資料庫資料恢復LinuxOracle
- Mysql資料庫delete刪除後資料恢復報告MySql資料庫delete資料恢復
- Sybase SQL Anywhere（ASA）資料庫恢復，ASA資料恢復，資料誤刪除恢復工具ReadASADBSQL資料庫資料恢復
- Mysql 誤刪資料進行恢復MySql
- 【資料庫資料恢復】HP-UX系統ORACLE資料庫被誤刪除的資料恢復資料庫資料恢復UXOracle
- Flashback Query恢復誤刪除資料(轉）
- 【北亞資料恢復】輸入錯誤命令導致MySQL資料庫資料被刪除的資料恢復案例資料恢復MySql資料庫
- 詳解：如何恢復MySQL資料庫下誤刪的資料MySql資料庫
- 誤刪除資料了怎麼辦？小編交易誤刪除資料的恢復方法
- Oracle恢復誤刪除的資料檔案Oracle
- truncate table 誤刪除資料後的恢復
- DB2 恢復誤刪除的表DB2
- Mongodb資料庫誤刪後的恢復MongoDB資料庫
- 【伺服器資料恢復】linux ext3檔案系統下誤刪除mysql資料庫的資料恢復案例伺服器資料恢復LinuxMySql資料庫
- 【伺服器資料恢復】伺服器誤刪除lun如何恢復資料？伺服器資料恢復
- flashback database 恢復誤刪除的表空間。Database
- 非歸檔庫誤刪表空間後的資料恢復資料恢復
- 【伺服器資料恢復】LINUX誤刪除、誤格式化怎麼恢復資料？伺服器資料恢復Linux
- Oracle恢復誤刪資料Oracle
- 14、MySQL Case-線上表誤刪除恢復MySql
- Oracle11g資料庫引數檔案誤刪除恢復Oracle資料庫
- mysql資料庫誤刪除操作說明MySql資料庫
- Oracle資料恢復 - Linux / Unix 誤刪除的檔案恢復(轉）Oracle資料恢復Linux
- 利用日誌挖掘 oracle 不完全恢復 恢復誤刪除的表/資料/檢視等Oracle