MySQL数据库表误删除恢复(转)

avatar 2023年5月19日18:16:19 评论 141 次浏览
场景一: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库。

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)

转载原文:http://blog.itpub.net/29785807/viewspace-2951942/

avatar

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: