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.
[root@zlm2 :: ~/sysbench-1.0/src/lua]
#sysbench oltp_read_write.lua --mysql-host=192.168.1.101 --mysql-port= --mysql-user=zlm --mysql-password=zlmzlm --mysql-db=sysbench --tables= --table-size= --mysql-storage-engine=innodb prepare
sysbench 1.0. (using bundled LuaJIT 2.1.-beta2) Creating table 'sbtest1'...
Inserting records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Inserting records into 'sbtest3'
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest4'...
Inserting records into 'sbtest4'
Creating a secondary index on 'sbtest4'...
Creating table 'sbtest5'...
Inserting records into 'sbtest5'
Creating a secondary index on 'sbtest5'...
Creating table 'sbtest6'...
Inserting records into 'sbtest6'
Creating a secondary index on 'sbtest6'...
Creating table 'sbtest7'...
Inserting records into 'sbtest7'
Creating a secondary index on 'sbtest7'...
Creating table 'sbtest8'...
Inserting records into 'sbtest8'
Creating a secondary index on 'sbtest8'...
Creating table 'sbtest9'...
Inserting records into 'sbtest9'
Creating a secondary index on 'sbtest9'...
Creating table 'sbtest10'...
Inserting records into 'sbtest10'
Creating a secondary index on 'sbtest10'... ([email protected] )[sysbench]>show tables;
+--------------------+
| Tables_in_sysbench |
+--------------------+
| sbtest1 |
| sbtest10 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
| sbtest6 |
| sbtest7 |
| sbtest8 |
| sbtest9 |
+--------------------+
rows in set (0.00 sec)
Backup the database sysbench with mysqldump.
[root@zlm2 :: ~]
#mysqldump --single-transaction --master-data= -A > db3306_`date +%Y%m%d`.sql
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. [root@zlm2 :: ~]
#ls -l
total
drwxr-xr-x root root Jul :
-rw-------. root root Jul anaconda-ks.cfg
-rw-r--r-- root root Jul : db3306_20180726.sql
-rw-r--r-- root root Jul : db.sql
-rwxr-xr-x root root Jun : mysqld.sh
-rwxr-xr-x root root Jul : percona-xtrabackup--2.4.-.el7.x86_64.rpm
drwxr-xr-x root root Jul : sysbench-1.0 [root@zlm2 :: ~]
#scp db3306_20180726.sql zlm3:/data/backup
root@zlm3's password:
db3306_20180726.sql % 19MB .5MB/s : [root@zlm2 :: ~]
Drop one table in database "sysbench".
([email protected] )[sysbench]>drop table sbtest10;
Query OK, rows affected (0.01 sec) ([email protected] )[sysbench]>show tables;
+--------------------+
| Tables_in_sysbench |
+--------------------+
| sbtest1 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
| sbtest6 |
| sbtest7 |
| sbtest8 |
| sbtest9 |
+--------------------+
rows in set (0.00 sec) ([email protected] )[sysbench]>
Create a rescue environment in an initialized instance on zlm3.
([email protected] )[(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
rows in set (0.00 sec) ([email protected] )[(none)]>create database sysbench; //Create a same name database.
Query OK, row affected (0.00 sec) ([email protected] )[(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| sysbench |
+--------------------+
rows in set (0.00 sec) ([email protected] )[(none)]>create user rescue@'192.168.1.%' identified by 'rescue'; //Create a rescue user called "rescue".
Query OK, rows affected (0.00 sec) ([email protected] )[(none)]>grant all privileges on sysbench.sbtest10 to rescue@'192.168.1.%'; //Grant privileges to user ""rescue.
ERROR (): GRANT command denied to user 'zlm'@'zlm3' for table 'sbtest10' //It seems current user does not has the privilege to grant.
([email protected] )[(none)]>exit
Bye [root@zlm3 :: ~]
#mysql -uroot -pPassw0rd -hlocalhost -S /tmp/mysql3306.sock //Login with root user.
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.7.-log MySQL Community Server (GPL) Copyright (c) , , Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@localhost mysql3306.sock)[(none)]>grant all privileges on sysbench.sbtest10 to rescue@'192.168.1.%'; //Grant privileges again.It works.
Query OK, rows affected (0.00 sec)
Check the backup set and import it.
[root@zlm3 :: /data/backup]
#ls -l|grep db3306
-rw-r--r-- root root Jul : db3306_20180726.sql [root@zlm3 :: /data/backup]
#mysql -urescue -prescue -h192.168.1. -P3306 -f < db3306_20180726.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR () at line : Access denied; you need (at least one of) the SUPER privilege(s) for this operation
ERROR () at line : Access denied; you need (at least one of) the SUPER privilege(s) for this operation
ERROR () at line : Access denied for user 'rescue'@'192.168.1.%' to database 'mysql'
ERROR () at line : Access denied for user 'rescue'@'192.168.1.%' to database 'mysql'
ERROR (3D000) at line : No database selected //A bundle of "No database seelcted" message has been omitted. ERROR (3D000) at line : No database selected
ERROR () at line : Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
ERROR () at line : DROP command denied to user 'rescue'@'zlm3' for table 'sbtest1'
ERROR () at line : CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest1'
ERROR () at line : Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
ERROR () at line : ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest1'
ERROR () at line : INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest1'
ERROR () at line : INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest1'
ERROR () at line : ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest1'
ERROR () at line : Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
ERROR () at line : DROP command denied to user 'rescue'@'zlm3' for table 'sbtest2'
ERROR () at line : CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest2'
ERROR () at line : Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
ERROR () at line : ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest2'
ERROR () at line : INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest2'
ERROR () at line : INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest2'
ERROR () at line : ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest2'
ERROR () at line : DROP command denied to user 'rescue'@'zlm3' for table 'sbtest3'
ERROR () at line : CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest3'
ERROR () at line : Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
ERROR () at line : ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest3'
ERROR () at line : INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest3'
ERROR () at line : INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest3'
ERROR () at line : ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest3'
ERROR () at line : DROP command denied to user 'rescue'@'zlm3' for table 'sbtest4'
ERROR () at line : CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest4'
ERROR () at line : Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
ERROR () at line : ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest4'
ERROR () at line : INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest4'
ERROR () at line : INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest4'
ERROR () at line : ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest4'
ERROR () at line : DROP command denied to user 'rescue'@'zlm3' for table 'sbtest5'
ERROR () at line : CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest5'
ERROR () at line : Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
ERROR () at line : ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest5'
ERROR () at line : INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest5'
ERROR () at line : INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest5'
ERROR () at line : ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest5'
ERROR () at line : DROP command denied to user 'rescue'@'zlm3' for table 'sbtest6'
ERROR () at line : CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest6'
ERROR () at line : Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
ERROR () at line : ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest6'
ERROR () at line : INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest6'
ERROR () at line : INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest6'
ERROR () at line : ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest6'
ERROR () at line : DROP command denied to user 'rescue'@'zlm3' for table 'sbtest7'
ERROR () at line : CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest7'
ERROR () at line : Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
ERROR () at line : ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest7'
ERROR () at line : INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest7'
ERROR () at line : INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest7'
ERROR () at line : ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest7'
ERROR () at line : DROP command denied to user 'rescue'@'zlm3' for table 'sbtest8'
ERROR () at line : CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest8'
ERROR () at line : Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
ERROR () at line : ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest8'
ERROR () at line : INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest8'
ERROR () at line : INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest8'
ERROR () at line : ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest8'
ERROR () at line : DROP command denied to user 'rescue'@'zlm3' for table 'sbtest9'
ERROR () at line : CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest9'
ERROR () at line : Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
ERROR () at line : ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest9'
ERROR () at line : INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest9'
ERROR () at line : INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest9'
ERROR () at line : ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest9'
ERROR () at line : Access denied for user 'rescue'@'192.168.1.%' to database 'zlm'
ERROR () at line : Access denied for user 'rescue'@'192.168.1.%' to database 'zlm'
ERROR () at line : Access denied; you need (at least one of) the SUPER privilege(s) for this operation //The other tables in backup set will be skipped except for table "sbtest10".
Check the rescued table "sbtest10".
(root@localhost mysql3306.sock)[(none)]>use sysbench
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A Database changed
(root@localhost mysql3306.sock)[sysbench]>show tables;
+--------------------+
| Tables_in_sysbench |
+--------------------+
| sbtest10 |
+--------------------+
row in set (0.00 sec) (root@localhost mysql3306.sock)[sysbench]>select count(*) from sbtest10;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec) //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.
//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".
([email protected] )[sysbench]>select count(*) from sbtest9;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec) ([email protected] )[sysbench]>delete from sbtest9 limit ;
Query OK, rows affected (0.07 sec) ([email protected] )[sysbench]>select count(*) from sbtest9;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec) ([email protected] )[sysbench]>flush logs;
Query OK, rows affected (0.04 sec) ([email protected] )[sysbench]>delete from sbtest9 limit ;
Query OK, rows affected (0.04 sec) ([email protected] )[sysbench]>select count(*) from sbtest9;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec) ([email protected] )[sysbench]>flush logs;
Query OK, rows affected (0.02 sec)
Truncate the table to mimic the miss operation.
([email protected] )[sysbench]>truncate table sbtest9;
Query OK, rows affected (0.02 sec) ([email protected] )[sysbench]>select count(*) from sbtest9;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec) ([email protected] )[sysbench]>show master status;
+------------------+----------+--------------+------------------+------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------------+
| mysql-bin. | | | | 1b7181ee-6eaf-11e8-998e-080027de0e0e:- |
+------------------+----------+--------------+------------------+------------------------------------------------+
row in set (0.00 sec)
Clear the environment and grant the right privileges.
(root@localhost mysql3306.sock)[sysbench]>drop table sbtest10;
Query OK, rows affected (0.03 sec) (root@localhost mysql3306.sock)[sysbench]>revoke all privileges on sysbench.sbtest10 from rescue@'192.168.1.%';
Query OK, rows affected (0.00 sec) (root@localhost mysql3306.sock)[sysbench]>grant all privileges on sysbench.sbtest9 to rescue@'192.168.1.%';
Query OK, rows affected (0.00 sec)
Restore the table "sbtest9" from mysqldump backup.
[root@zlm3 :: /data/backup]
#mysql -urescue -prescue -h192.168.1. -P3306 -f < db3306_20180726.sql ... //Omitted. (root@localhost mysql3306.sock)[sysbench]>show tables;
+--------------------+
| Tables_in_sysbench |
+--------------------+
| sbtest9 |
+--------------------+
row in set (0.00 sec) (root@localhost mysql3306.sock)[sysbench]>select count(*) from sbtest9;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec) //On account of restoring from an old mysqldump backup,we cannot rescue the incremental data in the table "sbtest9".
//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.
//Fetch the gtid_purged infomation from mysqldump backup.
[root@zlm3 :: /data/backup]
#grep "SET @@GLOBAL.GTID_PURGED" db3306_20180726.sql
SET @@GLOBAL.GTID_PURGED='1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730210'; (root@localhost mysql3306.sock)[sysbench]>reset master;
Query OK, rows affected (0.01 sec) (root@localhost mysql3306.sock)[sysbench]>reset slave;
Query OK, rows affected (0.02 sec) (root@localhost mysql3306.sock)[sysbench]>show slave status\G
*************************** . row ***************************
Slave_IO_State:
Master_Host: 192.168.1.101
Master_User: repl
Master_Port:
Connect_Retry:
Master_Log_File:
Read_Master_Log_Pos:
Relay_Log_File: relay-bin.
Relay_Log_Pos:
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
row in set (0.00 sec) //Set gtid_purged variable.
(root@localhost mysql3306.sock)[sysbench]>SET @@GLOBAL.GTID_PURGED='1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730210';
Query OK, rows affected (0.00 sec) (root@localhost mysql3306.sock)[sysbench]>show slave status\G
*************************** . row ***************************
Slave_IO_State:
Master_Host: 192.168.1.101
Master_User: repl
Master_Port:
Connect_Retry:
Master_Log_File:
Read_Master_Log_Pos:
Relay_Log_File: relay-bin.
Relay_Log_Pos:
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:- //After set @@global.gtid_purged operation,Executed_Gitd_Set will contain it.
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
row in set (0.00 sec) //Start IO Thread.
(root@localhost mysql3306.sock)[sysbench]>start slave io_thread;
Query OK, rows affected (0.01 sec) (root@localhost mysql3306.sock)[sysbench]>show slave status\G
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.101
Master_User: repl
Master_Port:
Connect_Retry:
Master_Log_File: mysql-bin. //The newly binlog has been pulled to local server.
Read_Master_Log_Pos:
Relay_Log_File: relay-bin.
Relay_Log_Pos:
Relay_Master_Log_File:
Slave_IO_Running: Yes //The IO Thread working normally.
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:- //The newest gtid information has been got(3730211-3730214).
Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:-
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
row in set (0.00 sec) //Specify the replication filter only for table "sbtest9".
(root@localhost mysql3306.sock)[sysbench]>CHANGE REPLICATION FILTER REPLICATE_DO_TABLE = (sysbench.sbtest9);
Query OK, rows affected (0.00 sec) //Analyze the binlog on master to find out the right postion of gtid_set.
[root@zlm2 :: ~]
#mysqlbinlog -v --base64-output=decode-rows /data/mysql/mysql3306/logs/mysql-bin. > .log [root@zlm2 :: ~]
#cat .log
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at
# :: server id end_log_pos Start: binlog v , server v 5.7.-log created ::
# Warning: this binlog is either in use or was not closed properly.
# at
# :: server id end_log_pos Previous-GTIDs
# 1b7181ee-6eaf-11e8-998e-080027de0e0e:-
# at
# :: server id end_log_pos GTID last_committed= sequence_number= rbr_only=no
SET @@SESSION.GTID_NEXT= '1b7181ee-6eaf-11e8-998e-080027de0e0e:3730214'/*!*/;
# at
# :: server id end_log_pos Query thread_id= exec_time= error_code=
use `sysbench`/*!*/;
SET TIMESTAMP=/*!*/;
SET @@session.pseudo_thread_id=/*!*/;
SET @@session.foreign_key_checks=, @@session.sql_auto_is_null=, @@session.unique_checks=, @@session.autocommit=/*!*/;
SET @@session.sql_mode=/*!*/;
SET @@session.auto_increment_increment=, @@session.auto_increment_offset=/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=,@@session.collation_connection=,@@session.collation_server=/*!*/;
SET @@session.lc_time_names=/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
truncate table sbtest9 //Here's the truncate operation,we are supposed the sql_thread just stop before this operation.
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; //Start SQL Thread using until clause.
(root@localhost mysql3306.sock)[sysbench]>start slave sql_thread until SQL_BEFORE_GTIDS='1b7181ee-6eaf-11e8-998e-080027de0e0e:3730214';
Query OK, rows affected (0.00 sec) (root@localhost mysql3306.sock)[sysbench]>show slave status\G
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.101
Master_User: repl
Master_Port:
Connect_Retry:
Master_Log_File: mysql-bin.
Read_Master_Log_Pos:
Relay_Log_File: relay-bin.
Relay_Log_Pos:
Relay_Master_Log_File: mysql-bin.
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table: sysbench.sbtest9 //Here's the "do table" option of replication filter.
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: SQL_BEFORE_GTIDS //Here's the option of until condition of start slave clause.
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:-
Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:-
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
row in set (0.00 sec) //Check the contents of rescued table.
(root@localhost mysql3306.sock)[sysbench]>select count(*) from sbtest9;
+----------+
| count(*) |
+----------+
| | //This is the correct number of records before we truncate the table on master.
+----------+
row in set (0.00 sec) //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.