场景一：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库。
023-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)
转载原文：
您可以选择一种方式赞助本站
支付宝扫一扫赞助
微信钱包扫描赞助
赏