Open
Description
As gh-ost
is very flexible in where and how it can migrate a new table, we can add MySQL transportable tablespaces in the gh-ost
process to not have to rebuild very big tables on the whole replication tree which would reduce impact, but also improve migration time considerably.
We could do this:
- MySQL Replication tree:
Master
,ProdSlave
,NonProdSlave
- Start a migration, read binary logs from
NonProdSlave
, create new table and copy all rows inNonProdSlave
and process the binary log as as necessary, but try to use as much resources as possible and do not throttle :-). (You might as variant just to anALTER TABLE
statement if it's faster) - When this is finished,
NonProdSlave
has the new table structure and all data in it, but changes will still happen on the master. NonProdSlave
: DoFLUSH TABLE .. FOR EXPORT
. This can take a while and will ensure change buffer and dirty pages are merged and the tablespace is clean. Keep the lock by keeping the connection open.Master
&ProdSlave
: Create the empty table withSQL_LOG_BIN=0
Master
&ProdSlave
: Copy thetable.{ibd,cfg}
files fromNonProdSlave
Master
&ProdSlave
:ALTER TABLE table IMPORT TABLESPACE
NonProdSlave
:UNLOCK TABLE
- Continue
gh-ost
magic as if it was performing the migration directly from the master and process binary logs.
This however changes the architecture of gh-ost
as now only MySQL client access is necessary but copying of files have to become possible somehow.
Documentation:
- https://dev.mysql.com/doc/refman/5.7/en/tablespace-copying.html
- Examples: https://dev.mysql.com/doc/refman/5.7/en/innodb-transportable-tablespace-examples.html
Limitations:
- MySQL 5.6 >=
- Only supported when major versions are the same
FLUSH TABLES ... FOR EXPORT
makes a table readonly. The replica you run it on will start to lag.