[Mysql] mysql勿操作drop table之后，利用mysqldump备份和binlog恢复


本文总阅读量次

1、查看备份表数据：

1
2
3
4
5
6
7
8
root@db 07:30:  [test1]> select * from test;
+----+
| ID |
+----+
|  1 |
|  2 |
|  3 |
+----+

2、查看当前binlog位置

1
2
3
4
5
6
7
8
9
10
show master status;
+---------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                                                                                                                       |
+---------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-binlog.000036 |    80147 |              |                  | 8182e5ae-af54-11e8-af0e-000d3a801ae2:1-13253,
c42e3372-ba21-11e8-99ed-000d3a800ed3:1-2,
d240752c-b809-11e8-8947-000d3a800ed3:1,
dd412cc2-ba1f-11e8-9ba2-000d3a801ae2:1-195:1000036-1000040 |
+---------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3、开始备份test1数据库下的test数据表

1
mysqldump -uroot -p12345678 test1 test --master-data=2 --single-transaction > /data/test.dump

4、查看备份数据文件：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
 more /data/test.dump
-- MySQL dump 10.13  Distrib 5.7.22, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: test1
-- ------------------------------------------------------
-- Server version	5.7.22-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

--
-- GTID state at the beginning of the backup 
--

SET @@GLOBAL.GTID_PURGED='8182e5ae-af54-11e8-af0e-000d3a801ae2:1-13253,
c42e3372-ba21-11e8-99ed-000d3a800ed3:1-2,
d240752c-b809-11e8-8947-000d3a800ed3:1,
dd412cc2-ba1f-11e8-9ba2-000d3a801ae2:1-195:1000036-1000040';

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-binlog.000036', MASTER_LOG_POS=80147;

--
-- Table structure for table `test`
--

DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `ID` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test`
--

LOCK TABLES `test` WRITE;
/*!40000 ALTER TABLE `test` DISABLE KEYS */;
INSERT INTO `test` VALUES (1),(2),(3);
/*!40000 ALTER TABLE `test` ENABLE KEYS */;
UNLOCK TABLES;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-09-25  7:32:05

5、备份完成之后插入新的数据

1
2
root@db 07:34:  [test1]> insert into test values(4);
Query OK, 1 row affected (0.01 sec)

6、刷新binlog文件

1
2
root@db 07:44:  [test1]> flush binary logs;
Query OK, 0 rows affected (0.04 sec)

7、查看binlog文件

1
2
3
4
5
6
7
8
9
10
root@db 07:44:  [test1]> show master status;
+---------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                                                                                                                          |
+---------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-binlog.000037 |      326 |              |                  | 8182e5ae-af54-11e8-af0e-000d3a801ae2:1-13253,
c42e3372-ba21-11e8-99ed-000d3a800ed3:1-2,
d240752c-b809-11e8-8947-000d3a800ed3:1,
dd412cc2-ba1f-11e8-9ba2-000d3a801ae2:1-196:1000036-1000040 |
+---------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

8、在新的binlog文件里面再次插入一条数据：

1
2
root@db 07:44:  [test1]> insert into test values(5);
Query OK, 1 row affected (0.01 sec)

9、查看当前测试的数据

1
2
3
4
5
6
7
8
9
10
11
root@db 07:45:  [test1]> select * from test;
+----+
| ID |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

10、再次刷新binlog文件

1
2
3
4
5
6
7
8
9
10
11
12
13
root@db 07:45:  [test1]> flush binary logs;
Query OK, 0 rows affected (0.04 sec)

root@db 07:49:  [test1]> show master status;
+---------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                                                                                                                          |
+---------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-binlog.000038 |      326 |              |                  | 8182e5ae-af54-11e8-af0e-000d3a801ae2:1-13253,
c42e3372-ba21-11e8-99ed-000d3a800ed3:1-2,
d240752c-b809-11e8-8947-000d3a800ed3:1,
dd412cc2-ba1f-11e8-9ba2-000d3a801ae2:1-197:1000036-1000040 |
+---------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

11、删除测试表

1
2
root@db 07:49:  [test1]> drop table test;
Query OK, 0 rows affected (0.04 sec)

12、根据备份文件获取测试表test的创建语句

1
sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `test`/!d;q' /data/test.dump
1
2
3
4
5
6
7
8
DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `ID` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

13、获取测试表test的数据，并指定到数据文件

1
grep 'INSERT INTO `test`' /data/test.dump > insert.sql

cat insert.sql

1
INSERT INTO `test` VALUES (1),(2),(3);

14、根据test.dump备份文件记录的log-file文件位置和log-pos参数，去获取未备份的关于test表的增删改数据：
从mysql-binlog.000036文件开始，–start-position=80147：

1
mysqlbinlog  -v --base64-output=decode-rows --set-charset=UTF-8 --database=test1 --start-position=80147  mysql-binlog.000036  > restore.sql

15、获取test表被删除是的pos，文件为mysql-binlog.000038，位置为507：

1
mysqlbinlog -v --base64-output=DECODE-ROWS --set-charset=UTF-8  /data/mysql/log/mysql-binlog.000038 |grep DROP  -A15 -B15
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# at 326
#180925  7:49:39 server id 3306101  end_log_pos 387 	GTID	last_committed=0	sequence_number=1	rbr_only=no
SET @@SESSION.GTID_NEXT= 'dd412cc2-ba1f-11e8-9ba2-000d3a801ae2:198'/*!*/;
# at 387
#180925  7:49:39 server id 3306101  end_log_pos 507 	Query	thread_id=267	exec_time=0	error_code=0
use `test1`/*!*/;
SET TIMESTAMP=1537861779/*!*/;
SET @@session.pseudo_thread_id=267/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=29301/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DROP TABLE `test` /* generated by server */
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

16、在备份开始binlog文件和记录drop操作的binlog文件之间还存在一个mysql-binlog.000037文件，需要将该文件内记录的信息都导出：

1
mysqlbinlog -v --base64-output=DECODE-ROWS --set-charset=UTF-8 mysql-binlog.000037 >> restore.sql

17：将记录drop操作的binlog文件里面drop之前的信息导出：

1
mysqlbinlog -v --base64-output=DECODE-ROWS --set-charset=UTF-8 --stop-position=507 mysql-binlog.000038 >> restore.sql

18:对导出的文件进行筛选，过滤出test表的相关信息：

1
more restore.sql |grep  --ignore-case -E 'insert|update|delete' -A3|grep '`test1`.`test`' -A2
1
2
3
4
5
6
7
### INSERT INTO `test1`.`test`
### SET
###   @1=4
--
### INSERT INTO `test1`.`test`
### SET
###   @1=5

将过滤出来的内容进行编辑，@1为第一列的列名，建议使用sublime或者vim进行批量编辑 ：
cat test_insert.sql

1
2
INSERT INTO `test1`.`test` SET   id=4  ;
INSERT INTO `test1`.`test` SET   id=5  ;

19、执行create table语句，并引用insert.sql和test_insert.sql文件，至此drop掉的test表被恢复。

mysql
目录

Proudly powered by Hexo and Theme by Lap
本站访客数人次
© 2020 zeven0707's blog