基于mysqldump备份集来恢复某个误操作的表（drop,truncate）
程序员文章站
2022-04-28 09:40:28
Preface How to rescue a dropped or truncated table online?Dropping or truncating is ddl operation which cannot be flashed back by the populare flashba ......
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.