InnoDB backup - using per-table tablespaces

MySQL & MariaDB Other MySQL® Questions
1

Hi, I have a quesiton about the binary backup process of InnoDB using per-table tablespaces. It’s very common for us to move binary files between servers in a pool and this is very easy with MyISAM tables. (We typically move one table because we’ve added an index). However is this the same process for InnoDB?

I am aware from the docs that:
“1. stop the server
2. make sure it was a clean shutdown
3. make a copy of the following:
A. .frm
B. .ibd (includes shared and per-table tablespaces)
C. log files
D. config files”

But again, the question is, if i’m moving just one table (table.frm and table.ibd), do I need to copy the shared tablespace?

2

InnoDB stores table definition in shared tablespace so you can not freely move .idb files like you can do with MyISAM tables.

It is possible though with ALTER TABLE … DISCARD/IMPORT TABLESPACE like described at:
http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces. html

So if you have ‘clean’ table.ibd file you can:

  • create table table with the same structure
  • ALTER TABLE … DISCARD TABLESPACE
  • copy table.ibd file to database dir
  • ALTER TABLE … IMPORT TABLESPACE