MySQL Bugs: #68171: Improve documentation of how InnoDB tables can be copied between instances

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

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.

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.

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.

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.