*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.cfg10 -rw-r--r-- 1 root root 20390934 Jul 26 07:32 db3306_20180726.sql11 -rw-r--r-- 1 root root 7333548 Jul 24 02:48 db.sql12 -rwxr-xr-x 1 root root  54 Jun 13 04:16 mysqld.sh13 -rwxr-xr-x 1 root root 7829340 Jul 24 10:02 percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm14 drwxr-xr-x 13 root root  4096 Jul 4 03:21 sysbench-1.015 16 [root@zlm2 07:32:11 ~]17 #scp db3306_20180726.sql zlm3:/data/backup18 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)]>exit33 Bye34 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 640 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 its45 affiliates. Other names may be trademarks of their respective46 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 operation10 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 selected13 14 //A bundle of "No database seelcted" message has been omitted.15 16 ERROR 1046 (3D000) at line 915: No database selected17 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 operation85 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 statusG 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 statusG 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: 0100   Exec_Master_Log_Pos: 0101    Relay_Log_Space: 169102     Until_Condition: None103     Until_Log_File: 104     Until_Log_Pos: 0105    Master_SSL_Allowed: No106    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: NULL112 Master_SSL_Verify_Server_Cert: No113     Last_IO_Errno: 0114     Last_IO_Error: 115    Last_SQL_Errno: 0116     Last_SQL_Error: 117  Replicate_Ignore_Server_Ids: 118    Master_Server_Id: 0119     Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e120     Master_Info_File: mysql.slave_master_info121      SQL_Delay: 0122    SQL_Remaining_Delay: NULL123   Slave_SQL_Running_State: 124   Master_Retry_Count: 86400125      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: 1133    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 statusG143 *************************** 1. row ***************************144    Slave_IO_State: Waiting for master to send event145     Master_Host: 192.168.1.101146      Master_User: repl147     Master_Port: 3306148     Connect_Retry: 60149    Master_Log_File: mysql-bin.000033 //The newly binlog has been pulled to local server.150   Read_Master_Log_Pos: 190151    Relay_Log_File: relay-bin.000001152     Relay_Log_Pos: 4153   Relay_Master_Log_File: 154    Slave_IO_Running: Yes //The IO Thread working normally.155    Slave_SQL_Running: No156     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: 0163      Last_Error: 164     Skip_Counter: 0165   Exec_Master_Log_Pos: 0166    Relay_Log_Space: 1433264167     Until_Condition: None168     Until_Log_File: 169     Until_Log_Pos: 0170    Master_SSL_Allowed: No171    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: NULL177 Master_SSL_Verify_Server_Cert: No178     Last_IO_Errno: 0179     Last_IO_Error: 180    Last_SQL_Errno: 0181     Last_SQL_Error: 182  Replicate_Ignore_Server_Ids: 183    Master_Server_Id: 1013306184     Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e185     Master_Info_File: mysql.slave_master_info186      SQL_Delay: 0187    SQL_Remaining_Delay: NULL188   Slave_SQL_Running_State: 189   Master_Retry_Count: 86400190      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-3730210197     Auto_Position: 1198    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.log210 211 [root@zlm2 10:20:36 ~]212 #cat 33.log213 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;214 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;215 DELIMITER /*!*/;216 # at 4217 #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:04218 # Warning: this binlog is either in use or was not closed properly.219 # at 123220 #180726 9:13:04 server id 1013306 end_log_pos 190  Previous-GTIDs221 # 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730213222 # at 190223 #180726 10:11:52 server id 1013306 end_log_pos 251  GTID last_committed=0 sequence_number=1 rbr_only=no224 SET @@SESSION.GTID_NEXT= '1b7181ee-6eaf-11e8-998e-080027de0e0e:3730214'/*!*/;225 # at 251226 #180726 10:11:52 server id 1013306 end_log_pos 340  Query thread_id=16 exec_time=0 error_code=0227 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 file242 /*!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 statusG250 *************************** 1. row ***************************251    Slave_IO_State: Waiting for master to send event252     Master_Host: 192.168.1.101253      Master_User: repl254     Master_Port: 3306255     Connect_Retry: 60256    Master_Log_File: mysql-bin.000033257   Read_Master_Log_Pos: 340258    Relay_Log_File: relay-bin.000007259     Relay_Log_Pos: 395260   Relay_Master_Log_File: mysql-bin.000033261     Slave_IO_Running: Yes262    Slave_SQL_Running: No263     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: 0270      Last_Error: 271     Skip_Counter: 0272   Exec_Master_Log_Pos: 190273    Relay_Log_Space: 821274    Until_Condition: SQL_BEFORE_GTIDS //Here's the option of until condition of start slave clause.275     Until_Log_File: 276     Until_Log_Pos: 0277    Master_SSL_Allowed: No278    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: NULL284 Master_SSL_Verify_Server_Cert: No285     Last_IO_Errno: 0286     Last_IO_Error: 287    Last_SQL_Errno: 0288     Last_SQL_Error: 289  Replicate_Ignore_Server_Ids: 290    Master_Server_Id: 1013306291     Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e292     Master_Info_File: mysql.slave_master_info293      SQL_Delay: 0294    SQL_Remaining_Delay: NULL295   Slave_SQL_Running_State: 296   Master_Retry_Count: 86400297      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-3730214303    Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730213304     Auto_Position: 1305    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.

 

相关文章