Bug #68171 Improve documentation of how InnoDB tables can be copied between instances
Submitted: 24 Jan 2013 15:33 Modified: 8 Jul 2013 13:18
Reporter: Simon Mudd (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.9-rc OS:Any
Assigned to: Bugs System CPU Architecture:Any

[24 Jan 2013 15:33] Simon Mudd
Description:
https://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html seems a bit vague. Shouldn't it show a usage example?

Like this:
ON SOURCE SERVER: do  FLUSH TABLES ... FOR EXPORT ? # prepare for the copy
COPY THE DATA OVER: e.g. scp /path/to/source/instance/mytable.{ibd,frm,cfg} remote-server:/path/to/destination/instance # copy the data over
ON SOURCE SERVER: do .... # unlock any locked tables or whatever
ON DESTINATION SERVER: do ... # import the data into the destination instance.

There are various references to this but I see no clear "full work flow":
- https://dev.mysql.com/doc/refman/5.6/en/flush.html
- https://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-tablespace-manageme...

In the past one of the reasons for using MyISAM tables has been the "easy" of copying tables from one server to another, and prior to 5.6 this was not possible.

This new facility in 5.6 is clearly useful so show how easy it is to use and people will use it.

How to repeat:
Read docs, try to find out how to move tables from one location to another.

Suggested fix:
Improve documentation to give sample workflows under unix like OS (and maybe windows too).

Oh and as a btw, why can't I unlock a single table ?

https://dev.mysql.com/doc/refman/5.6/en/lock-tables.html

This doesn't seem possible.

I can see a desire to run several copies in parallel (usually makes better use of i/o) to another server and therefore want to unlock individual tables once the copy has finished, but _not_ unlock all tables at once.
So a feature request here to make it possible to unlock individual tables would be good as otherwise we'd need to maintain an active connection for each table that is being locked.
[24 Jan 2013 19:25] MySQL Verification Team
verified as described. An example as well as limitations somewhat like todd explains here would be useful:

http://mysqlblog.fivefarmers.com/2012/09/07/understanding-innodb-transportable-tablespaces...

On the unlock tables, correct that's all or nothing with unlock table/tables,(table and tables being synonymous), i did a quick search figuring surely this had been requested before but I don't see it, nor a really good one that
points out why the unlock is for the session. I'll do a bit more research on this and see if a feature request would be helpful here.
[6 Jun 2013 13:58] Simon Mudd
Note: I've not looked at this since finding bug#68639, but now that's fixed in 5.6.12 it would be good to get some clear documentation on how we might use this.
[8 Jul 2013 13:18] Bugs System
Added an example procedure for copying a tablespace between instances (transportable tablespaces)

The new content will appear soon, in the next published documentation build.

http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html
http://dev.mysql.com/doc/refman/5.7/en/tablespace-copying.html

Thank you for the bug report.