Restoring single or some tables from Xtrabackup!!! (Replication Gotchas)

There is this wonderful blog post  from one of the Perconian Miguel Angel Nieto, regarding restoring single table from an Xtrabackup backup. Since this is a very useful feature with respect to InnoDB where direct filecopy in MyISAM-style is not allowed, I thought of giving it a try, plus one of our client demanded this (Its not a good idea to restore 1TB of data just for a few MB table).

So moving forward, I wont list out the steps already mentioned by Miguel, but would like to mention one addition to the process. While you import the backup .ibd file in the data directory, the changed row data wont be visible unless you do a mysql restart. For eg : Here's what I did :

1) Deleted some records from a table.

mysql> select count(*) from titles_back;
+----------+
| count(*) |
+----------+
|   443308 |
+----------+
1 row in set (0.99 sec)

mysql> delete from titles_back limit 20;
Query OK, 20 rows affected (0.15 sec)

mysql> select count(*) from titles_back;
+----------+
| count(*) |
+----------+
|   443288 |
+----------+
1 row in set (0.08 sec)

2) Prepared the backup using --apply-log and --export option (I used Innobackupex script, however --prepare can be used instead if you are using xtrabackup binary).
3) Copied the tablespace file from the backup to the database schema directory. And made sure the file permissions are correct.
4) Imported the tablespace back to the table.

mysql> alter table titles_back import tablespace;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from titles_back;
+----------+
| count(*) |
+----------+
|   443288 |
+----------+
1 row in set (0.08 sec)

As we can see above, the tablespace was imported without any problems, however still the deleted data is not visible, Although it exists in the files. Looks like somehow InnoDB is still referring the page for the deleted records from buffer pool (the page-ids are same), but I am not sure about it.

5) Next an addition as I mentioned above would be to do a mysql restart for the instance.
6) After the restart we have the missing rows back :
mysql> select count(*) from titles_back;
+----------+
| count(*) |
+----------+
|   443308 |
+----------+
1 row in set (1.03 sec)

Now the restart can be actually scheduled for a later time, if not possible. InnoDB will allow any data manipulations to rows execpt the previously changed rows (the one we needed to restore from backup). Please look at the example below :

mysql> select * from titles_back limit 5;
+--------+-----------------+------------+------------+
| emp_no | title           | from_date  | to_date    |
+--------+-----------------+------------+------------+
|  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
|  10002 | Staff           | 1996-08-03 | 9999-01-01 |
|  10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
|  10004 | Engineer        | 1986-12-01 | 1995-12-01 |
|  10004 | Senior Engineer | 1995-12-01 | 9999-01-01 |
+--------+-----------------+------------+------------+
5 rows in set (0.02 sec)

mysql> delete from titles_back where emp_no=10001;
Query OK, 1 row affected (0.21 sec)

mysql> select * from titles_back limit 5;
+--------+-----------------+------------+------------+
| emp_no | title           | from_date  | to_date    |
+--------+-----------------+------------+------------+
|  10002 | Staff           | 1996-08-03 | 9999-01-01 |
|  10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
|  10004 | Engineer        | 1986-12-01 | 1995-12-01 |
|  10004 | Senior Engineer | 1995-12-01 | 9999-01-01 |
|  10005 | Senior Staff    | 1996-09-12 | 9999-01-01 |
+--------+-----------------+------------+------------+
5 rows in set (0.00 sec)

mysql> alter table titles_back discard tablespace;
Query OK, 0 rows affected (0.06 sec)

/* Copied the files from the backup to the database directory. */

mysql> alter table titles_back import tablespace;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from titles_back where emp_no=10001;
Empty set (0.00 sec)

/* MySQL restart in between */

mysql> select * from titles_back where emp_no=10001;
+--------+-----------------+------------+------------+
| emp_no | title           | from_date  | to_date    |
+--------+-----------------+------------+------------+
|  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
+--------+-----------------+------------+------------+
1 row in set (4.47 sec)

I hope this was clear. 

Now lets observe what happens when you carry out the partial restore on a Master-Slave replication setup :

I followed all the steps mentioned above, to notice that the slave had broken with an error : 
                   Last_Errno: 1030
                   Last_Error: Error 'Got error -1 from storage engine' on query. Default database: 'employees'. Query: 'alter table titles_back import tablespace'
  
This is pretty obvious since the Replication slave was able to execute the "discard tablespace" command (which infact deletes the .ibd file for the table), however it was not able to import. Simply because of the fact that .ibd file doesnt exist. Remember we copied the backup file to the master and replication doesnt handle a transfer of .ibd file as it does with "LOAD DATA INFILE ...". So all you need to do is restore the same file to the slave, and restart the mysqld on the slave. Simple.

Now this becomes a necessity, when you are discarding a tablespace and importing it, people might think of turning off sql_log_bin for the session, although this wont cause any errors and the slave to break it, but this will surely lead to inconsistent slaves (The deleted records would still not be present on the slave, but on the master.)

Hope this was helpful. Xtrabackup is a great utility to backup databases, which can save a lot of time, during the restore especially.

Disclaimer : This was strictly done on Percona MySQL server, to carry out the same on Oracle's MySQL server, some server config variables needs to set, you can refer Miguel's blogpost for that.