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

 
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.
 
posted @ 2018-07-25 23:09  aaron8219  阅读(532)  评论(0)  编辑  收藏  举报