首页 > > 网络编程 > Mysql >

基于mysqldump备份集来恢复某个误操作的表（drop…

2018-07-27 06:11:08来源：博客园阅读 ()

Preface

How to rescue a dropped or truncated table online?Dropping or truncating is ddl operation which cannot be flashed back by the populare flashback tools like MyFlash,binlog2mysql,mysqldump_backup,etc.Therefore,the conventional method is restoring the database to a newly initialized instance on another server with backup(physical or logical).Whatif the backup set is rather huge for example the mysqldump backup is more than 200G?It will cost a long time to rescue the dropped table back.Is there an effective way to accomplish the issue?Let's see the tests below.

Framework

Hostname	IP/Port	Identity	OS Version	MySQL Version	GTID Mode	Binlog Format
zlm2	192.168.1.101/3306	master	CentOS 7.0	5.7.21	on	row
zlm3	192.168.1.102/3306	slave	CentOS 7.0	5.7.21	on	row

Precedure

Test1:Rescue a table after dropping it based on a new mysqldump backup.

Generate the test data with sysbench.

 1 [root@zlm2 07:30:58 ~/sysbench-1.0/src/lua]
 2 #sysbench oltp_read_write.lua --mysql-host=192.168.1.101 --mysql-port=3306 --mysql-user=zlm --mysql-password=zlmzlm --mysql-db=sysbench --tables=10 --table-size=10000 --mysql-storage-engine=innodb prepare
 3 sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
 4 
 5 Creating table 'sbtest1'...
 6 Inserting 10000 records into 'sbtest1'
 7 Creating a secondary index on 'sbtest1'...
 8 Creating table 'sbtest2'...
 9 Inserting 10000 records into 'sbtest2'
10 Creating a secondary index on 'sbtest2'...
11 Creating table 'sbtest3'...
12 Inserting 10000 records into 'sbtest3'
13 Creating a secondary index on 'sbtest3'...
14 Creating table 'sbtest4'...
15 Inserting 10000 records into 'sbtest4'
16 Creating a secondary index on 'sbtest4'...
17 Creating table 'sbtest5'...
18 Inserting 10000 records into 'sbtest5'
19 Creating a secondary index on 'sbtest5'...
20 Creating table 'sbtest6'...
21 Inserting 10000 records into 'sbtest6'
22 Creating a secondary index on 'sbtest6'...
23 Creating table 'sbtest7'...
24 Inserting 10000 records into 'sbtest7'
25 Creating a secondary index on 'sbtest7'...
26 Creating table 'sbtest8'...
27 Inserting 10000 records into 'sbtest8'
28 Creating a secondary index on 'sbtest8'...
29 Creating table 'sbtest9'...
30 Inserting 10000 records into 'sbtest9'
31 Creating a secondary index on 'sbtest9'...
32 Creating table 'sbtest10'...
33 Inserting 10000 records into 'sbtest10'
34 Creating a secondary index on 'sbtest10'...
35 
36 (zlm@192.168.1.101 3306)[sysbench]>show tables;
37 +--------------------+
38 | Tables_in_sysbench |
39 +--------------------+
40 | sbtest1            |
41 | sbtest10           |
42 | sbtest2            |
43 | sbtest3            |
44 | sbtest4            |
45 | sbtest5            |
46 | sbtest6            |
47 | sbtest7            |
48 | sbtest8            |
49 | sbtest9            |
50 +--------------------+
51 10 rows in set (0.00 sec)

Backup the database sysbench with mysqldump.

 1 [root@zlm2 07:32:01 ~]
 2 #mysqldump --single-transaction --master-data=2 -A > db3306_`date +%Y%m%d`.sql
 3 Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
 4 
 5 [root@zlm2 07:32:09 ~]
 6 #ls -l
 7 total 34744
 8 drwxr-xr-x   2 root root     4096 Jul 23 10:10 20180723
 9 -rw-------.  1 root root     1431 Jul 16  2015 anaconda-ks.cfg
10 -rw-r--r--   1 root root 20390934 Jul 26 07:32 db3306_20180726.sql
11 -rw-r--r--   1 root root  7333548 Jul 24 02:48 db.sql
12 -rwxr-xr-x   1 root root       54 Jun 13 04:16 mysqld.sh
13 -rwxr-xr-x   1 root root  7829340 Jul 24 10:02 percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
14 drwxr-xr-x  13 root root     4096 Jul  4 03:21 sysbench-1.0
15 
16 [root@zlm2 07:32:11 ~]
17 #scp db3306_20180726.sql zlm3:/data/backup
18 root@zlm3's password: 
19 db3306_20180726.sql                                                                                                100%   19MB  19.5MB/s   00:00    
20 
21 [root@zlm2 07:33:35 ~]

Drop one table in database "sysbench".

 1 (zlm@192.168.1.101 3306)[sysbench]>drop table sbtest10; 
 2 Query OK, 0 rows affected (0.01 sec)
 3 
 4 (zlm@192.168.1.101 3306)[sysbench]>show tables;
 5 +--------------------+
 6 | Tables_in_sysbench |
 7 +--------------------+
 8 | sbtest1            |
 9 | sbtest2            |
10 | sbtest3            |
11 | sbtest4            |
12 | sbtest5            |
13 | sbtest6            |
14 | sbtest7            |
15 | sbtest8            |
16 | sbtest9            |
17 +--------------------+
18 9 rows in set (0.00 sec)
19 
20 (zlm@192.168.1.101 3306)[sysbench]>

Create a rescue environment in an initialized instance on zlm3.

 1 (zlm@192.168.1.102 3306)[(none)]>show databases;
 2 +--------------------+
 3 | Database           |
 4 +--------------------+
 5 | information_schema |
 6 | mysql              |
 7 | performance_schema |
 8 | sys                |
 9 +--------------------+
10 4 rows in set (0.00 sec)
11 
12 (zlm@192.168.1.102 3306)[(none)]>create database sysbench; //Create a same name database.
13 Query OK, 1 row affected (0.00 sec)
14 
15 (zlm@192.168.1.102 3306)[(none)]>show databases;
16 +--------------------+
17 | Database           |
18 +--------------------+
19 | information_schema |
20 | mysql              |
21 | performance_schema |
22 | sys                |
23 | sysbench           |
24 +--------------------+
25 5 rows in set (0.00 sec)
26 
27 (zlm@192.168.1.102 3306)[(none)]>create user rescue@'192.168.1.%' identified by 'rescue'; //Create a rescue user called "rescue".
28 Query OK, 0 rows affected (0.00 sec)
29 
30 (zlm@192.168.1.102 3306)[(none)]>grant all privileges on sysbench.sbtest10 to rescue@'192.168.1.%'; //Grant privileges to user ""rescue.
31 ERROR 1142 (42000): GRANT command denied to user 'zlm'@'zlm3' for table 'sbtest10' //It seems current user does not has the privilege to grant.
32 (zlm@192.168.1.102 3306)[(none)]>exit
33 Bye
34 
35 [root@zlm3 07:49:50 ~]
36 #mysql -uroot -pPassw0rd -hlocalhost -S /tmp/mysql3306.sock //Login with root user.
37 mysql: [Warning] Using a password on the command line interface can be insecure.
38 Welcome to the MySQL monitor.  Commands end with ; or \g.
39 Your MySQL connection id is 6
40 Server version: 5.7.21-log MySQL Community Server (GPL)
41 
42 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
43 
44 Oracle is a registered trademark of Oracle Corporation and/or its
45 affiliates. Other names may be trademarks of their respective
46 owners.
47 
48 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
49 
50 (root@localhost mysql3306.sock)[(none)]>grant all privileges on sysbench.sbtest10 to rescue@'192.168.1.%'; //Grant privileges again.It works.
51 Query OK, 0 rows affected (0.00 sec)

Check the backup set and import it.

 1 [root@zlm3 07:59:28 /data/backup]
 2 #ls -l|grep db3306
 3 -rw-r--r-- 1 root  root   20390934 Jul 26 07:33 db3306_20180726.sql
 4 
 5 [root@zlm3 07:59:42 /data/backup]
 6 #mysql -urescue -prescue -h192.168.1.102 -P3306 -f < db3306_20180726.sql 
 7 mysql: [Warning] Using a password on the command line interface can be insecure.
 8 ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
 9 ERROR 1227 (42000) at line 24: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
10 ERROR 1044 (42000) at line 36: Access denied for user 'rescue'@'192.168.1.%' to database 'mysql'
11 ERROR 1044 (42000) at line 38: Access denied for user 'rescue'@'192.168.1.%' to database 'mysql'
12 ERROR 1046 (3D000) at line 44: No database selected
13 
14 //A bundle of "No database seelcted" message has been omitted.
15 
16 ERROR 1046 (3D000) at line 915: No database selected
17 ERROR 1044 (42000) at line 935: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
18 ERROR 1142 (42000) at line 943: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest1'
19 ERROR 1142 (42000) at line 946: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest1'
20 ERROR 1044 (42000) at line 960: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
21 ERROR 1142 (42000) at line 961: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest1'
22 ERROR 1142 (42000) at line 962: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest1'
23 ERROR 1142 (42000) at line 963: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest1'
24 ERROR 1142 (42000) at line 964: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest1'
25 ERROR 1044 (42000) at line 988: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
26 ERROR 1142 (42000) at line 999: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest2'
27 ERROR 1142 (42000) at line 1002: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest2'
28 ERROR 1044 (42000) at line 1016: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
29 ERROR 1142 (42000) at line 1017: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest2'
30 ERROR 1142 (42000) at line 1018: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest2'
31 ERROR 1142 (42000) at line 1019: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest2'
32 ERROR 1142 (42000) at line 1020: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest2'
33 ERROR 1142 (42000) at line 1027: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest3'
34 ERROR 1142 (42000) at line 1030: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest3'
35 ERROR 1044 (42000) at line 1044: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
36 ERROR 1142 (42000) at line 1045: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest3'
37 ERROR 1142 (42000) at line 1046: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest3'
38 ERROR 1142 (42000) at line 1047: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest3'
39 ERROR 1142 (42000) at line 1048: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest3'
40 ERROR 1142 (42000) at line 1055: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest4'
41 ERROR 1142 (42000) at line 1058: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest4'
42 ERROR 1044 (42000) at line 1072: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
43 ERROR 1142 (42000) at line 1073: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest4'
44 ERROR 1142 (42000) at line 1074: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest4'
45 ERROR 1142 (42000) at line 1075: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest4'
46 ERROR 1142 (42000) at line 1076: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest4'
47 ERROR 1142 (42000) at line 1083: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest5'
48 ERROR 1142 (42000) at line 1086: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest5'
49 ERROR 1044 (42000) at line 1100: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
50 ERROR 1142 (42000) at line 1101: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest5'
51 ERROR 1142 (42000) at line 1102: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest5'
52 ERROR 1142 (42000) at line 1103: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest5'
53 ERROR 1142 (42000) at line 1104: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest5'
54 ERROR 1142 (42000) at line 1111: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest6'
55 ERROR 1142 (42000) at line 1114: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest6'
56 ERROR 1044 (42000) at line 1128: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
57 ERROR 1142 (42000) at line 1129: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest6'
58 ERROR 1142 (42000) at line 1130: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest6'
59 ERROR 1142 (42000) at line 1131: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest6'
60 ERROR 1142 (42000) at line 1132: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest6'
61 ERROR 1142 (42000) at line 1139: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest7'
62 ERROR 1142 (42000) at line 1142: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest7'
63 ERROR 1044 (42000) at line 1156: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
64 ERROR 1142 (42000) at line 1157: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest7'
65 ERROR 1142 (42000) at line 1158: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest7'
66 ERROR 1142 (42000) at line 1159: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest7'
67 ERROR 1142 (42000) at line 1160: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest7'
68 ERROR 1142 (42000) at line 1167: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest8'
69 ERROR 1142 (42000) at line 1170: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest8'
70 ERROR 1044 (42000) at line 1184: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
71 ERROR 1142 (42000) at line 1185: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest8'
72 ERROR 1142 (42000) at line 1186: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest8'
73 ERROR 1142 (42000) at line 1187: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest8'
74 ERROR 1142 (42000) at line 1188: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest8'
75 ERROR 1142 (42000) at line 1195: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest9'
76 ERROR 1142 (42000) at line 1198: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest9'
77 ERROR 1044 (42000) at line 1212: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
78 ERROR 1142 (42000) at line 1213: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest9'
79 ERROR 1142 (42000) at line 1214: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest9'
80 ERROR 1142 (42000) at line 1215: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest9'
81 ERROR 1142 (42000) at line 1216: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest9'
82 ERROR 1044 (42000) at line 1223: Access denied for user 'rescue'@'192.168.1.%' to database 'zlm'
83 ERROR 1044 (42000) at line 1225: Access denied for user 'rescue'@'192.168.1.%' to database 'zlm'
84 ERROR 1227 (42000) at line 1226: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
85 
86 //The other tables in backup set will be skipped except for table "sbtest10".

Check the rescued table "sbtest10".

 1 (root@localhost mysql3306.sock)[(none)]>use sysbench
 2 Reading table information for completion of table and column names
 3 You can turn off this feature to get a quicker startup with -A
 4 
 5 Database changed
 6 (root@localhost mysql3306.sock)[sysbench]>show tables;
 7 +--------------------+
 8 | Tables_in_sysbench |
 9 +--------------------+
10 | sbtest10           |
11 +--------------------+
12 1 row in set (0.00 sec)
13 
14 (root@localhost mysql3306.sock)[sysbench]>select count(*) from sbtest10;
15 +----------+
16 | count(*) |
17 +----------+
18 |    10000 |
19 +----------+
20 1 row in set (0.00 sec)
21 
22 //Because the dropping operation is just happened after my backing up with mysqldump.There's no need to backup the incremental data in the dropped table.
23 //Therefore,we can simply copy the table back with transportable tablespace method,which can be referred to my previous blog.

Test2:Rescue a table after truncating it based on a old mysqldump backup plus binlog.

Execute several normal dml operations in table "sbtest9".

 1 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest9;
 2 +----------+
 3 | count(*) |
 4 +----------+
 5 |    10000 |
 6 +----------+
 7 1 row in set (0.00 sec)
 8 
 9 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest9 limit 5000;
10 Query OK, 5000 rows affected (0.07 sec)
11 
12 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest9;
13 +----------+
14 | count(*) |
15 +----------+
16 |     5000 |
17 +----------+
18 1 row in set (0.00 sec)
19 
20 (zlm@192.168.1.101 3306)[sysbench]>flush logs;
21 Query OK, 0 rows affected (0.04 sec)
22 
23 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest9 limit 2500;
24 Query OK, 2500 rows affected (0.04 sec)
25 
26 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest9;
27 +----------+
28 | count(*) |
29 +----------+
30 |     2500 |
31 +----------+
32 1 row in set (0.00 sec)
33 
34 (zlm@192.168.1.101 3306)[sysbench]>flush logs;
35 Query OK, 0 rows affected (0.02 sec)

Truncate the table to mimic the miss operation.

 1 (zlm@192.168.1.101 3306)[sysbench]>truncate table sbtest9;
 2 Query OK, 0 rows affected (0.02 sec)
 3 
 4 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest9;
 5 +----------+
 6 | count(*) |
 7 +----------+
 8 |        0 |
 9 +----------+
10 1 row in set (0.00 sec)
11 
12 (zlm@192.168.1.101 3306)[sysbench]>show master status;
13 +------------------+----------+--------------+------------------+------------------------------------------------+
14 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
15 +------------------+----------+--------------+------------------+------------------------------------------------+
16 | mysql-bin.000033 |      340 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730214 |
17 +------------------+----------+--------------+------------------+------------------------------------------------+
18 1 row in set (0.00 sec)

Clear the environment and grant the right privileges.

1 (root@localhost mysql3306.sock)[sysbench]>drop table sbtest10;
2 Query OK, 0 rows affected (0.03 sec)
3 
4 (root@localhost mysql3306.sock)[sysbench]>revoke all privileges on sysbench.sbtest10 from rescue@'192.168.1.%';
5 Query OK, 0 rows affected (0.00 sec)
6 
7 (root@localhost mysql3306.sock)[sysbench]>grant all privileges on sysbench.sbtest9 to rescue@'192.168.1.%';
8 Query OK, 0 rows affected (0.00 sec)

Restore the table "sbtest9" from mysqldump backup.

 1 [root@zlm3 09:19:39 /data/backup]
 2 #mysql -urescue -prescue -h192.168.1.102 -P3306 -f < db3306_20180726.sql
 3 
 4 ... //Omitted.
 5 
 6 (root@localhost mysql3306.sock)[sysbench]>show tables;
 7 +--------------------+
 8 | Tables_in_sysbench |
 9 +--------------------+
10 | sbtest9            |
11 +--------------------+
12 1 row in set (0.00 sec)
13 
14 (root@localhost mysql3306.sock)[sysbench]>select count(*) from sbtest9;
15 +----------+
16 | count(*) |
17 +----------+
18 |    10000 |
19 +----------+
20 1 row in set (0.00 sec)
21 
22 //On account of restoring from an old mysqldump backup,we cannot rescue the incremental data in the table "sbtest9".
23 //What can we do next step?Those incremental data are all in the binlog,so we need to implement a slave first.

Implement a slave filter replication on zlm3.

  1 //Fetch the gtid_purged infomation from mysqldump backup.
  2 [root@zlm3 09:39:19 /data/backup]
  3 #grep "SET @@GLOBAL.GTID_PURGED" db3306_20180726.sql 
  4 SET @@GLOBAL.GTID_PURGED='1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730210';
  5 
  6 (root@localhost mysql3306.sock)[sysbench]>reset master;
  7 Query OK, 0 rows affected (0.01 sec)
  8 
  9 (root@localhost mysql3306.sock)[sysbench]>reset slave;
 10 Query OK, 0 rows affected (0.02 sec)
 11 
 12 (root@localhost mysql3306.sock)[sysbench]>show slave status\G
 13 *************************** 1. row ***************************
 14                Slave_IO_State: 
 15                   Master_Host: 192.168.1.101
 16                   Master_User: repl
 17                   Master_Port: 3306
 18                 Connect_Retry: 60
 19               Master_Log_File: 
 20           Read_Master_Log_Pos: 4
 21                Relay_Log_File: relay-bin.000001
 22                 Relay_Log_Pos: 4
 23         Relay_Master_Log_File: 
 24              Slave_IO_Running: No
 25             Slave_SQL_Running: No
 26               Replicate_Do_DB: 
 27           Replicate_Ignore_DB: 
 28            Replicate_Do_Table: 
 29        Replicate_Ignore_Table: 
 30       Replicate_Wild_Do_Table: 
 31   Replicate_Wild_Ignore_Table: 
 32                    Last_Errno: 0
 33                    Last_Error: 
 34                  Skip_Counter: 0
 35           Exec_Master_Log_Pos: 0
 36               Relay_Log_Space: 169
 37               Until_Condition: None
 38                Until_Log_File: 
 39                 Until_Log_Pos: 0
 40            Master_SSL_Allowed: No
 41            Master_SSL_CA_File: 
 42            Master_SSL_CA_Path: 
 43               Master_SSL_Cert: 
 44             Master_SSL_Cipher: 
 45                Master_SSL_Key: 
 46         Seconds_Behind_Master: NULL
 47 Master_SSL_Verify_Server_Cert: No
 48                 Last_IO_Errno: 0
 49                 Last_IO_Error: 
 50                Last_SQL_Errno: 0
 51                Last_SQL_Error: 
 52   Replicate_Ignore_Server_Ids: 
 53              Master_Server_Id: 0
 54                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
 55              Master_Info_File: mysql.slave_master_info
 56                     SQL_Delay: 0
 57           SQL_Remaining_Delay: NULL
 58       Slave_SQL_Running_State: 
 59            Master_Retry_Count: 86400
 60                   Master_Bind: 
 61       Last_IO_Error_Timestamp: 
 62      Last_SQL_Error_Timestamp: 
 63                Master_SSL_Crl: 
 64            Master_SSL_Crlpath: 
 65            Retrieved_Gtid_Set: 
 66             Executed_Gtid_Set: 
 67                 Auto_Position: 1
 68          Replicate_Rewrite_DB: 
 69                  Channel_Name: 
 70            Master_TLS_Version: 
 71 1 row in set (0.00 sec)
 72 
 73 //Set gtid_purged variable.
 74 (root@localhost mysql3306.sock)[sysbench]>SET @@GLOBAL.GTID_PURGED='1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730210';
 75 Query OK, 0 rows affected (0.00 sec)
 76 
 77 (root@localhost mysql3306.sock)[sysbench]>show slave status\G
 78 *************************** 1. row ***************************
 79                Slave_IO_State: 
 80                   Master_Host: 192.168.1.101
 81                   Master_User: repl
 82                   Master_Port: 3306
 83                 Connect_Retry: 60
 84               Master_Log_File: 
 85           Read_Master_Log_Pos: 4
 86                Relay_Log_File: relay-bin.000001
 87                 Relay_Log_Pos: 4
 88         Relay_Master_Log_File: 
 89              Slave_IO_Running: No
 90             Slave_SQL_Running: No
 91               Replicate_Do_DB: 
 92           Replicate_Ignore_DB: 
 93            Replicate_Do_Table: 
 94        Replicate_Ignore_Table: 
 95       Replicate_Wild_Do_Table: 
 96   Replicate_Wild_Ignore_Table: 
 97                    Last_Errno: 0
 98                    Last_Error: 
 99                  Skip_Counter: 0
100           Exec_Master_Log_Pos: 0
101               Relay_Log_Space: 169
102               Until_Condition: None
103                Until_Log_File: 
104                 Until_Log_Pos: 0
105            Master_SSL_Allowed: No
106            Master_SSL_CA_File: 
107            Master_SSL_CA_Path: 
108               Master_SSL_Cert: 
109             Master_SSL_Cipher: 
110                Master_SSL_Key: 
111         Seconds_Behind_Master: NULL
112 Master_SSL_Verify_Server_Cert: No
113                 Last_IO_Errno: 0
114                 Last_IO_Error: 
115                Last_SQL_Errno: 0
116                Last_SQL_Error: 
117   Replicate_Ignore_Server_Ids: 
118              Master_Server_Id: 0
119                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
120              Master_Info_File: mysql.slave_master_info
121                     SQL_Delay: 0
122           SQL_Remaining_Delay: NULL
123       Slave_SQL_Running_State: 
124            Master_Retry_Count: 86400
125                   Master_Bind: 
126       Last_IO_Error_Timestamp: 
127      Last_SQL_Error_Timestamp: 
128                Master_SSL_Crl: 
129            Master_SSL_Crlpath: 
130            Retrieved_Gtid_Set: 
131             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730210 //After set @@global.gtid_purged operation,Executed_Gitd_Set will contain it.
132                 Auto_Position: 1
133          Replicate_Rewrite_DB: 
134                  Channel_Name: 
135            Master_TLS_Version: 
136 1 row in set (0.00 sec)
137 
138 //Start IO Thread.
139 (root@localhost mysql3306.sock)[sysbench]>start slave io_thread;
140 Query OK, 0 rows affected (0.01 sec)
141 
142 (root@localhost mysql3306.sock)[sysbench]>show slave status\G
143 *************************** 1. row ***************************
144                Slave_IO_State: Waiting for master to send event
145                   Master_Host: 192.168.1.101
146                   Master_User: repl
147                   Master_Port: 3306
148                 Connect_Retry: 60
149               Master_Log_File: mysql-bin.000033 //The newly binlog has been pulled to local server.
150           Read_Master_Log_Pos: 190
151                Relay_Log_File: relay-bin.000001
152                 Relay_Log_Pos: 4
153         Relay_Master_Log_File: 
154              Slave_IO_Running: Yes //The IO Thread working normally.
155             Slave_SQL_Running: No
156               Replicate_Do_DB: 
157           Replicate_Ignore_DB: 
158            Replicate_Do_Table: 
159        Replicate_Ignore_Table: 
160       Replicate_Wild_Do_Table: 
161   Replicate_Wild_Ignore_Table: 
162                    Last_Errno: 0
163                    Last_Error: 
164                  Skip_Counter: 0
165           Exec_Master_Log_Pos: 0
166               Relay_Log_Space: 1433264
167               Until_Condition: None
168                Until_Log_File: 
169                 Until_Log_Pos: 0
170            Master_SSL_Allowed: No
171            Master_SSL_CA_File: 
172            Master_SSL_CA_Path: 
173               Master_SSL_Cert: 
174             Master_SSL_Cipher: 
175                Master_SSL_Key: 
176         Seconds_Behind_Master: NULL
177 Master_SSL_Verify_Server_Cert: No
178                 Last_IO_Errno: 0
179                 Last_IO_Error: 
180                Last_SQL_Errno: 0
181                Last_SQL_Error: 
182   Replicate_Ignore_Server_Ids: 
183              Master_Server_Id: 1013306
184                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
185              Master_Info_File: mysql.slave_master_info
186                     SQL_Delay: 0
187           SQL_Remaining_Delay: NULL
188       Slave_SQL_Running_State: 
189            Master_Retry_Count: 86400
190                   Master_Bind: 
191       Last_IO_Error_Timestamp: 
192      Last_SQL_Error_Timestamp: 
193                Master_SSL_Crl: 
194            Master_SSL_Crlpath: 
195            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730211-3730214 //The newest gtid information has been got(3730211-3730214).
196             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730210
197                 Auto_Position: 1
198          Replicate_Rewrite_DB: 
199                  Channel_Name: 
200            Master_TLS_Version: 
201 1 row in set (0.00 sec)
202 
203 //Specify the replication filter only for table "sbtest9".
204 (root@localhost mysql3306.sock)[sysbench]>CHANGE REPLICATION FILTER REPLICATE_DO_TABLE = (sysbench.sbtest9);
205 Query OK, 0 rows affected (0.00 sec)
206 
207 //Analyze the binlog on master to find out the right postion of gtid_set.
208 [root@zlm2 10:20:28 ~]
209 #mysqlbinlog -v --base64-output=decode-rows /data/mysql/mysql3306/logs/mysql-bin.000033 > 33.log
210 
211 [root@zlm2 10:20:36 ~]
212 #cat 33.log
213 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
214 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
215 DELIMITER /*!*/;
216 # at 4
217 #180726  9:13:04 server id 1013306  end_log_pos 123     Start: binlog v 4, server v 5.7.21-log created 180726  9:13:04
218 # Warning: this binlog is either in use or was not closed properly.
219 # at 123
220 #180726  9:13:04 server id 1013306  end_log_pos 190     Previous-GTIDs
221 # 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730213
222 # at 190
223 #180726 10:11:52 server id 1013306  end_log_pos 251     GTID    last_committed=0    sequence_number=1    rbr_only=no
224 SET @@SESSION.GTID_NEXT= '1b7181ee-6eaf-11e8-998e-080027de0e0e:3730214'/*!*/;
225 # at 251
226 #180726 10:11:52 server id 1013306  end_log_pos 340     Query    thread_id=16    exec_time=0    error_code=0
227 use `sysbench`/*!*/;
228 SET TIMESTAMP=1532592712/*!*/;
229 SET @@session.pseudo_thread_id=16/*!*/;
230 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
231 SET @@session.sql_mode=1436549152/*!*/;
232 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
233 /*!\C utf8 *//*!*/;
234 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
235 SET @@session.lc_time_names=0/*!*/;
236 SET @@session.collation_database=DEFAULT/*!*/;
237 truncate table sbtest9 //Here's the truncate operation,we are supposed the sql_thread just stop before this operation.
238 /*!*/;
239 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
240 DELIMITER ;
241 # End of log file
242 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
243 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
244 
245 //Start SQL Thread using until clause.
246 (root@localhost mysql3306.sock)[sysbench]>start slave sql_thread until SQL_BEFORE_GTIDS='1b7181ee-6eaf-11e8-998e-080027de0e0e:3730214';
247 Query OK, 0 rows affected (0.00 sec)
248 
249 (root@localhost mysql3306.sock)[sysbench]>show slave status\G
250 *************************** 1. row ***************************
251                Slave_IO_State: Waiting for master to send event
252                   Master_Host: 192.168.1.101
253                   Master_User: repl
254                   Master_Port: 3306
255                 Connect_Retry: 60
256               Master_Log_File: mysql-bin.000033
257           Read_Master_Log_Pos: 340
258                Relay_Log_File: relay-bin.000007
259                 Relay_Log_Pos: 395
260         Relay_Master_Log_File: mysql-bin.000033
261              Slave_IO_Running: Yes
262             Slave_SQL_Running: No
263               Replicate_Do_DB: 
264           Replicate_Ignore_DB: 
265            Replicate_Do_Table: sysbench.sbtest9 //Here's the "do table" option of replication filter.
266        Replicate_Ignore_Table: 
267       Replicate_Wild_Do_Table: 
268   Replicate_Wild_Ignore_Table: 
269                    Last_Errno: 0
270                    Last_Error: 
271                  Skip_Counter: 0
272           Exec_Master_Log_Pos: 190
273               Relay_Log_Space: 821
274               Until_Condition: SQL_BEFORE_GTIDS //Here's the option of until condition of start slave clause.
275                Until_Log_File: 
276                 Until_Log_Pos: 0
277            Master_SSL_Allowed: No
278            Master_SSL_CA_File: 
279            Master_SSL_CA_Path: 
280               Master_SSL_Cert: 
281             Master_SSL_Cipher: 
282                Master_SSL_Key: 
283         Seconds_Behind_Master: NULL
284 Master_SSL_Verify_Server_Cert: No
285                 Last_IO_Errno: 0
286                 Last_IO_Error: 
287                Last_SQL_Errno: 0
288                Last_SQL_Error: 
289   Replicate_Ignore_Server_Ids: 
290              Master_Server_Id: 1013306
291                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
292              Master_Info_File: mysql.slave_master_info
293                     SQL_Delay: 0
294           SQL_Remaining_Delay: NULL
295       Slave_SQL_Running_State: 
296            Master_Retry_Count: 86400
297                   Master_Bind: 
298       Last_IO_Error_Timestamp: 
299      Last_SQL_Error_Timestamp: 
300                Master_SSL_Crl: 
301            Master_SSL_Crlpath: 
302            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730211-3730214
303             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730213
304                 Auto_Position: 1
305          Replicate_Rewrite_DB: 
306                  Channel_Name: 
307            Master_TLS_Version: 
308 1 row in set (0.00 sec)
309 
310 //Check the contents of rescued table.
311 (root@localhost mysql3306.sock)[sysbench]>select count(*) from sbtest9;
312 +----------+
313 | count(*) |
314 +----------+
315 |     2500 | //This is the correct number of records before we truncate the table on master.
316 +----------+
317 1 row in set (0.00 sec)
318 
319 //Likewise,we can copy the rescued table back to master in a proper certain time by transportable tablespace tech(I'm not going to demonstrate here).

Summary

There always be some miss operations such as drop,truncate which cannot be flashed back easily by tools.We should be careful to avoid them.
Onlyif you have a full database backup(mysqldump or Xtraback) and vital binlog,the destroyed table could be rescued.
The portion of recovering imcremental data also can be used in Xtrabackup method when rescuing lost data.
It's recommend to rename the rescued table before copying it back to the product database with transportable tablespace.

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有