In this blog post, we’ll look at how to restore a single InnoDB table from a full backup after dropping the table.
You can also see an earlier blog post about restoring a single table from a full backup here: How to recover a single InnoDB table from a full backup.
The idea behind the actions in that blog is based on the “Transportable Tablespace” concept, which was introduced in MySQL 5.6. So when you have deleted the data from a table, you are going to quickly restore this table as follows:
- Prepare the backup
- Discard the tablespace of the original table
- Copy .ibd from the backup to the original table path
- Import the tablespace
Of course, you need to test it using the process in production, even though it is relatively straightforward.
But how about when you drop a table? It is still a great process because you will lose the table structure and the datafiles.
The actions mentioned in the previous blog will not work here, simple because it is impossible to discard a non-existing tablespace. 🙂
Instead, one solution scenario could be something like:
- Prepare the backup
- Extract the original table structure from the backup (i.e., extract the create statement from the backup .frm file)
- Create a new empty table
- Apply some locks
- Discard the newly created tablespace
- Copy back .ibd from the backup
- Import the tablespace
And now you can continue to be happy!
Let’s test this scenario. In this test, I am not going to use real world tables. I will instead use good old “t1”.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | > select * from dbtest.t1; +----+ | id | +----+ | 1 | | 1 | | 2 | | 1 | | 2 | | 3 | | 5 | | 5 | | 5 | +----+ 9 rows in set (0.01 sec) |
Take a backup:
1 2 3 4 | xtrabackup --defaults-file=/home/shahriyar.rzaev/sandboxes/rsandbox_Percona-Server-5_7_17/master/my.sandbox.cnf --user=jeffrey --password='msandbox' --target-dir=/home/shahriyar.rzaev/backup_dirs/ps_5.7_master/full/2017-03-07_08-34-17 --backup --host=localhost --port=20192 |
Prepare a backup:
1 2 | xtrabackup --prepare --target-dir=/home/shahriyar.rzaev/backup_dirs/ps_5.7_master/full/2017-03-07_08-34-17 |
Drop the table:
1 2 | drop table dbtest.t1; Query OK, 0 rows affected (0.22 sec) |
Extract the create statement from the .frm file using the mysqlfrm tool. Please read the #WARNING and #CAUTION sections below, and also review the documentation to figure out how to use this tool:
1 2 3 4 5 6 7 8 9 10 11 12 | $ sudo mysqlfrm --diagnostic dbtest/t1.frm # WARNING: Cannot generate character set or collation names without the --server option. # CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct. # Reading .frm file for dbtest/t1.frm: # The .frm file is a TABLE. # CREATE TABLE Statement: CREATE TABLE `dbtest`.`t1` ( `id` int(11) NOT NULL ) ENGINE=InnoDB; #...done. |
Create an empty table using this create statement:
1 2 3 4 | > CREATE TABLE `dbtest`.`t1` ( -> `id` int(11) NOT NULL -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.27 sec) |
Apply write lock, or take another action to ensure safety:
1 2 | > lock tables dbtest.t1 write; Query OK, 0 rows affected (0.00 sec) |
Discard the tablespace:
1 2 | > alter table dbtest.t1 discard tablespace; Query OK, 0 rows affected (0.09 sec) |
Copy back the .ibd file from backup:
1 | $ sudo cp dbtest/t1.ibd /home/shahriyar.rzaev/sandboxes/rsandbox_Percona-Server-5_7_17/master/data/dbtest/ |
Apply the proper owner:
1 2 | $ sudo chown shahriyar.rzaev:shahriyar.rzaev /home/shahriyar.rzaev/sandboxes/rsandbox_Percona-Server-5_7_17/master/data/dbtest/t1.ibd |
Import the tablespace:
1 2 3 4 5 6 7 8 9 10 11 | master [localhost] {jeffrey} ((none)) > alter table dbtest.t1 import tablespace; Query OK, 0 rows affected, 2 warnings (0.42 sec) master [localhost] {jeffrey} ((none)) > show warnings; +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1814 | InnoDB: Tablespace has been discarded for table 't1' | | Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './dbtest/t1.cfg', will attempt to import without schema verification | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) |
You can ignore the warnings.
Now check if your table is restored or not:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | > select * from dbtest.t1; +----+ | id | +----+ | 1 | | 1 | | 2 | | 1 | | 2 | | 3 | | 5 | | 5 | | 5 | +----+ 9 rows in set (0.00 sec) |
With our simple test, this method worked well. It should theoretically work in a production environment. However, you will need to test this first. Ensure it works for your test environment and test tables.
Thanks! 🙂
Thanks for sharing with the info! It helped me a lot! Regards
Maybe it is possible to explain when a DROPPING the table is not working… (doesn’t exist in engine, or ‘doesn’t exist’, in case of a corrupted table:
1. stop mysql, delete the frm and ibd-files corresponding the table
2. copy a backup-frm table
3. start mysql
4. NOW dropping had to work, then you can CREATE TABLE
maybe give the statement UNLOCK TABLES at the end