场景一：MySQL从库单张表误删除恢复场景二：MySQL恢复指定表结构场景三：MySQL恢复指定表数据场景四：MySQL恢复指定库场景五：MySQL恢复所有库数据场景六：MySQL恢复指定表到指定时间点复制
场景一：MySQL 从库单张表误删除恢复
适用于：
MySQL 主从架构，不适用双主架构。复制
测试环境：
OS:Redhat 7.9DB: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());复制
此时从库同步报错：
Last_Errno: 1146Last_Error: Error executing row event: 'Table 'cjc.t2' doesn't exist'复制
详细信息如下：
mysql> show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.16.11.137Master_User: replMaster_Port: 13309Connect_Retry: 60Master_Log_File: mysql-bin.000010Read_Master_Log_Pos: 2662Relay_Log_File: mysql-relay-bin.000021Relay_Log_Pos: 1848Relay_Master_Log_File: mysql-bin.000010Slave_IO_Running: YesSlave_SQL_Running: NoReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 1146Last_Error: Error executing row event: 'Table 'cjc.t2' doesn't exist'Skip_Counter: 0Exec_Master_Log_Pos: 1633Relay_Log_Space: 3173Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 1146Last_SQL_Error: Error executing row event: 'Table 'cjc.t2' doesn't exist'Replicate_Ignore_Server_Ids:Master_Server_Id: 1137Master_UUID: b64d7590-ebb6-11ed-b20e-0800272944a2Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State:Master_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp: 230510 16:44:48Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set: b64d7590-ebb6-11ed-b20e-0800272944a2:1-465543Executed_Gtid_Set: b64d7590-ebb6-11ed-b20e-0800272944a2:1-465540Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:Master_public_key_path:Get_master_public_key: 0Network_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;复制
此时并不影响其他表数据的同步。
查看主从同步状态：
Replicate_Wild_Ignore_Table: cjc.t2
详细信息如下：
mysql> show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.16.11.137Master_User: replMaster_Port: 13309Connect_Retry: 60Master_Log_File: mysql-bin.000010Read_Master_Log_Pos: 2662Relay_Log_File: mysql-relay-bin.000021Relay_Log_Pos: 2877Relay_Master_Log_File: mysql-bin.000010Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table: cjc.t2Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 2662Relay_Log_Space: 3173Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1137Master_UUID: b64d7590-ebb6-11ed-b20e-0800272944a2Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set: b64d7590-ebb6-11ed-b20e-0800272944a2:1-465543Executed_Gtid_Set: b64d7590-ebb6-11ed-b20e-0800272944a2:1-465543Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:Master_public_key_path:Get_master_public_key: 0Network_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).导入到从库
从库
more /mysqldata/bak/mysql_t2_20230510.sqltail -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.9DB: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 READSTART TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */复制
--master-data=2和--flush-logs都会添加下面额外操作，注意锁表：
FLUSH /*!40101 LOCAL */ TABLESFLUSH 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复制
例如：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.sqlINSERT 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.sqlINSERT 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 databaseUsage: 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 DBcjc2023-05-12T11:22:56.186739+08:00 16 Init DBcjc2023-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
sed -n '/^-- Current Database: `cjc`/,/^-- Current Database: `/p' mysqldata/bak/mysql_bak1.sql > createdb_cjc.sql复制
查看文件
ls -lrth createdb_cjc.sqlvi 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 mysqlflush 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.000019Position: 1594Binlog_Do_DB:Binlog_Ignore_DB:Executed_Gtid_Set: b64d7590-ebb6-11ed-b20e-0800272944a2:1-4658351 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 t1Enter 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=5mysql-bin.0000191104Table_map11371153table_id: 413 (cjc.t1)mysql-bin.0000191391Rows_query11371439# delete from t1 where 1=1mysql-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=0SET 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:00BINLOG 'INJdZA9xBAAAeQAAAH0AAAAAAAQAOC4wLjIwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQACigBxb+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=0SET 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_FBINLOG 'jdJdZBNxBAAAMQAAANEBAAAAAJ0BAAAAAAEAA2NqYwACdDEAAgMTAQADAQEA5XRvQA==jdJdZB5xBAAAKwAAAPwBAAAAAJ0BAAAAAAEAAgAC/wAEAAAAgNtx0CW7Pg=='/*!*/;# at 508#230512 13:45:49 server id 1137 end_log_pos 539 CRC32 0x12c8a010 Xid = 1942COMMIT/*!*/;# at 539# at 618#230512 13:46:07 server id 1137 end_log_pos 705 CRC32 0x0b291193 Querythread_id=19exec_time=0error_code=0SET 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_FBINLOG 'n9JdZBNxBAAAMQAAACgDAAAAAJ0BAAAAAAEAA2NqYwACdDEAAgMTAQADAQEA+R66Ug==n9JdZB5xBAAAKwAAAFMDAAAAAJ0BAAAAAAEAAgAC/wAFAAAAgNuHeMkqog=='/*!*/;# at 851#230512 13:46:07 server id 1137 end_log_pos 882 CRC32 0xa21394b5 Xid = 1943COMMIT/*!*/;# at 882# at 961#230512 13:46:40 server id 1137 end_log_pos 1049 CRC32 0xd9685b01 Querythread_id=19exec_time=0error_code=0SET 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_FBINLOG 'wNJdZBNxBAAAMQAAAIEEAAAAAJ0BAAAAAAEAA2NqYwACdDEAAgMTAQADAQEAHbWipg==wNJdZB9xBAAAMQAAALIEAAAAAJ0BAAAAAAEAAgAC/wEABQAAAIDbhwD0AQAAkyxW1A=='/*!*/;# at 1202#230512 13:46:40 server id 1137 end_log_pos 1233 CRC32 0xd8b6a2ec Xid = 1944COMMIT/*!*/;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###
文章转载自IT小Chen，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
