InnoDB backup - using per-table tablespaces

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?

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: html

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

  • create table table with the same structure
  • copy table.ibd file to database dir