I believe this problem has killed hours of time of many more DBA's like me ...!!!
** Can we really use innodb tables across servers ? **
NO is the answer many people would say till the very recent time. But not now a days. In fact we didn't have a straight away chance to do this copying but YES..... we can do this. Here you go.
Steps in simple for copying table tbl1 from database db from server A to server B:
1. On Server A, do hot backup of required tables using innobackupex().
innobackupex --databases OR --tables-list OR --include path-to-backup
2. --apply log and --export
innobackupex --apply-log --export /path-to-partial-backup-created-in-step1
This will create individual tables level files used to export to some other server.
tbl1.exp --- Used to import onto percona XtraDB enfine
tbl1.cfg --- Used to import onto mysql 5.6
tbl1.ibd --- Table space file
3. On Server B, create an empty new table as the one which we have exported from Server A.
CREATE TABLE tbl1 .............................. ;
4. DISCARD the new tables table space.
ALTER TABLE tbl1 DISCARD TABLESPACE ;
5. Copy files from Step2 to database home directory on server B and then import table space.
ALTER TABLE <new table> IMPORT TABLESPACE ;
** Can we really use innodb tables across servers ? **
NO is the answer many people would say till the very recent time. But not now a days. In fact we didn't have a straight away chance to do this copying but YES..... we can do this. Here you go.
Steps in simple for copying table tbl1 from database db from server A to server B:
1. On Server A, do hot backup of required tables using innobackupex().
innobackupex --databases OR --tables-list OR --include path-to-backup
2. --apply log and --export
innobackupex --apply-log --export /path-to-partial-backup-created-in-step1
This will create individual tables level files used to export to some other server.
tbl1.exp --- Used to import onto percona XtraDB enfine
tbl1.cfg --- Used to import onto mysql 5.6
tbl1.ibd --- Table space file
3. On Server B, create an empty new table as the one which we have exported from Server A.
CREATE TABLE tbl1 .............................. ;
4. DISCARD the new tables table space.
ALTER TABLE tbl1 DISCARD TABLESPACE ;
5. Copy files from Step2 to database home directory on server B and then import table space.
ALTER TABLE <new table> IMPORT TABLESPACE ;