Sometimes we need to restore only some tables from a full backup maybe because your data loss affect a small number of your tables. In this particular scenario is faster to recover single tables than a full backup. This is easy with MyISAM but if your tables are InnoDB the process is a little bit different story.
With Oracle’s stock MySQL you cannot move your ibd files freely from one server to another or from one database to another. The reason is that the table definition is stored in the InnoDB shared tablespace (ibdata) and the transaction IDs and log sequence numbers that are stored in the tablespace files also differ between servers. Therefore our example will be very straightforward: we’ll delete some rows from a table in order to recover the table later.
Most of these limitations are solved on Percona Server . More info about this in the conclusion section of this post. This post will be focus on how to recover a single tablespace using stock MySQL server.
First, you must meet certain prerequisites to be able to restore a ibd tablespace:
- The ibd file must be from a consistent backup with all insert buffer entries merged and have no uncommitted transactions in order to not be dependent of the shared tablespace ibdata. That is, shutting down with innodb_fast_shutdown=0. We’ll use XtraBackup to avoid the server shutdown.
- You must not drop, truncate or alter the schema of the table after the backup has been taken.
- The variable innodb_file_per_table must be enabled.
Then, our first step is to get a consistent backup.
First we need to copy all the data to an output directory:
The –export option is the magic trick that will help us to get a consistent backup with complete independent ibd files without shutting down the service. In the second step the use of –export option runs a recovery process on the backup with innodb_fast_shutdown=0 and therefore merging all the insert buffers.
# innobackupex --defaults-file=/etc/my.cnf --export /tmp/
Then apply the logs to get a consistent backup:
# innobackupex --defaults-file=/etc/my.cnf --apply-log --export /tmp/2012-01-22_14-13-20/
Now we’re going to delete some data from one table. In this case we’re going to delete the salary information from the user 10008:
mysql> SELECT * FROM salaries WHERE emp_no=10008;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10008 | 46671 | 1998-03-11 | 1999-03-11 |
| 10008 | 48584 | 1999-03-11 | 2000-03-10 |
| 10008 | 52668 | 2000-03-10 | 2000-07-31 |
+--------+--------+------------+------------+mysql> DELETE FROM salaries WHERE emp_no=10008;
The next step is where we are going to save a lot of time and some headaches 😉 Instead of recovering all the InnoDB data we are going to recover only the “salaries” table:
- Discard the tablespace of the salaries table:
mysql> set FOREIGN_KEY_CHECKS=0;
mysql> ALTER TABLE salaries DISCARD TABLESPACE;
- Copy the salaries.ibd files from the backup to the database data directory:
# cp /tmp/2012-01-22_14-13-20/employees/salaries.ibd /var/lib/mysql/data/employees/
- Import the new tablespace:
mysql> set FOREIGN_KEY_CHECKS=0;
mysql> ALTER TABLE salaries IMPORT TABLESPACE;
mysql> set FOREIGN_KEY_CHECKS=1;
mysql> SELECT * FROM salaries WHERE emp_no=10008;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10008 | 46671 | 1998-03-11 | 1999-03-11 |
| 10008 | 48584 | 1999-03-11 | 2000-03-10 |
| 10008 | 52668 | 2000-03-10 | 2000-07-31 |
+--------+--------+------------+------------+
The salary history from the user is back again!
Conclusion:
As we learned , you can also recover single InnoDB table as with MyISAM but knowing in advance that there are some prerequisites to comply.
Percona Server relaxes a lot of limitations and is able to import tables from different Server instance, when table was altered or truncated in the meanwhile. Though this only works if table was
“exported” with Xtrabackup as this exports essential information from main tablespace which is not stored in .ibd file. innodb_import_table_from_xtrabackup=1 should be enabled for such advanced import process to work. You can read more about this feature in Percona Server Documentation
In the next blog post I’ll explain how to do recovery using Percona Data Recovery toolkit.
Hi Miguel Angel, thx for this useful post.
what about constraints, foreign keys for exemple ?
Hi Cédric,
In this case, after the ibd import you should check the consistency of your data. You have more information about this in this post:
http://www.mysqlperformanceblog.com/2011/11/18/eventual-consistency-in-mysql/
Ok, thanks for the link.
Does IMPORT TABLESPACE launch a FOREIGN_KEY_CHECKS=0 by default ?
If not, it could be an additional step to the procedure.
Good point 🙂
ALTER TABLE customer DISCARD TABLESPACE;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails ()
Added to the blog post, thank you.
Hi, I have recently encounter a similar problem and would like to recover a single table
# xtrabackup_55 –backup –innodb-file-per-table –target-dir=/mnt/mysql/export/ –tables=data
# xtrabackup_55 –prepare –export –innodb-file-per-table –target-dir=/mnt/mysql/export
ALTER TABLE data DISCARD TABLESPACE;
# replace ibd with the export files
SET GLOBAL innodb_import_table_from_xtrabackup = 1;
ALTER TABLE data IMPORT TABLESPACE;
After those being done i receive the following errors
InnoDB: Import: The extended import of data is being started.
InnoDB: Import: 9 indexes have been detected.
InnoDB: Progress in %: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 done.
120323 4:38:52 InnoDB: Error: page 0 log sequence number 6094071743825
InnoDB: is in the future! Current system log sequence number 6087296438643.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
Hello All –
I have created a backup of my mysql database using the innobackupex-1.5.1 command. I have the innodb_file_per_table directive set in the my.cnf configuration file. I have the backup folder which contains xtrabackup log files, xtrabackup checkpoint file and a folder for my database (called tools) which contains .MYD, .ibd, .frm, .TRG files.
I want to restore this database (called tools). But I am not able to do a SINGLE DATABASE restore using the copy-back command.
I am able to get the database in my /var/lib/mysql folder.
But I am able to view/access ONLY MYISAM table – but none of the InnoDB tables.
Is there something which I am missing? Do I have to set any directive in the my.cnf file to access the InnoDB table? Please help.
Thanks in advance.
Abhilasj
Hi, consider this comment about foreign key and transportable tablespaces :
“To export tables that are in a foreign key relationship you must export and import all the tables together”
Source : http://blogs.innodb.com/wp/2012/04/innodb-transportable-tablespaces/
I have a replication server from where i do periodic snapshots not to affect the performance of the main server. I want to be able to recover individual files to the master db. How can i do that in innodb? in the example you give, the backups are taken from the same server, so they share the same table space. cheers –Ricardo
Hi Miguel,
I just verified this method, initially it didnt work. Here’s what I did.
1) Backup up the database as specified with –export option with the “innodb_import_table_from_xtrabackup=1” turned ON.
2) Then deleted some records from one of the tables.
3) Prepared the backup with export option.
4) Discarded the tablespace.
5) Copied the concerned .ibd file to the datadirectory
6) Imported the tablespace back, However I didnt get the records I deleted, surprisingly I got the rest of the records.
Now this meant that somehow InnoDB was able to show me a copy of table with my delete statement succeeded.
Next I tried by restarting mysql after the delete and then discarding tablespace, making sure none of the transactions are left uncommitted and everything has been flushed to disk. However the same result.
Finally after careful reading and understanding of your blog, I tried mysql restart by deleting the ib_logfiles and letting innodb create new ones, and the missing data was back, without even going through the discard and import tablespace steps.
This clearly suggests that ib_logfiles are playing some role in this case.
Next I set the option innodb_fast_shutdown=0, and restarted mysql, without any discard and import I got the missing data back.
If possible could you please add/mention a step to restart mysql, for all the insert buffers to merge and so InnoDB does a full purge.
Thanks,
Akshay S
Also to add, even with innodb_fast_shutdown set to 1(Default) and then the restart I was able to get the original data back. Looks like InnoDB is showing the modified page from the buffer pool and instead of the original page from the “just imported” datafile. The page-ids will be same even with original datafile.
Please correct me if I am wrong.
Following the above on Mysql 5.6 and after I discard the tablespace and copy the replacement into place, trying the import tablespace just results in “Error 1146 (42S02): Table ‘db.my_table’ doesn’t exist”. What now?
Important step is to ensure the .ibd is owned by the right user 🙂
After having correct permissions, the import tablespace command worked fine.
hi,
i had a question
what if some one delete records or tables after the innobackupex start and in between the backup process deletion commits ,so at the time of preparation it will apply transaction log and we wont get that data back then whats the use of backup?