--source include/have_innodb_16k.inc --echo # --echo # IMPORT/EXPORT for non-partitioned tables with autoextend_size --echo # --echo --disable_query_log call mtr.add_suppression("\\[Warning] .*MY-\\d+.* Cannot calculate statistics for table"); --enable_query_log --echo # Create source and destination databases and stored procedure to load the tables --echo let $MYSQLD_DATADIR = `SELECT @@datadir`; let $MYSQLD_TMPDIR = `SELECT @@tmpdir`; CREATE DATABASE db_source; USE db_source; DELIMITER |; CREATE PROCEDURE bulk_insert_source() BEGIN DECLARE i INT DEFAULT 1; WHILE i < 500 DO INSERT INTO tsource VALUES(i, repeat('aaaaaa', 10000)); SET i = i + 1; END WHILE; END | DELIMITER ;| CREATE DATABASE db_destination; USE db_destination; DELIMITER |; CREATE PROCEDURE bulk_insert_dest() BEGIN DECLARE i INT DEFAULT 1; WHILE i < 500 DO INSERT INTO tsource VALUES(i, repeat('aaaaaa', 10000)); SET i = i + 1; END WHILE; END | DELIMITER ;| --echo # Scenario-1: --echo # Create and populate a non-partitioned table on the source db with autoextend_size 4m --echo # Create a table on the destination db with same schema and autoextend_size --echo # values as that on the source db --echo # ALTER TABLE ... DISCARD TABLESPACE on the destination table --echo # FLUSH TABLE ... FOR EXPORT on the source table --echo # Copy .cfg and .ibd files from the source to the destination db --echo # UNLOCK TABLES --echo # ALTER TABLE ... IMPORT TABLESPACE on the destination table --echo # Verify that the number of rows seen on the destination table are same as seen on the source table --echo USE db_source; CREATE TABLE tsource (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M; call bulk_insert_source(); SELECT COUNT(*) FROM tsource; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsource%'; USE db_destination; CREATE TABLE tsource (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M; ALTER TABLE tsource DISCARD TABLESPACE; USE db_source; FLUSH TABLE tsource FOR EXPORT; --echo # List of files on source --list_files $MYSQLD_DATADIR/db_source --copy_file $MYSQLD_DATADIR/db_source/tsource.ibd $MYSQLD_DATADIR/db_destination/tsource.ibd --copy_file $MYSQLD_DATADIR/db_source/tsource.cfg $MYSQLD_DATADIR/db_destination/tsource.cfg --echo # List of files on destination --list_files $MYSQLD_DATADIR/db_destination UNLOCK TABLES; USE db_destination; ALTER TABLE tsource IMPORT TABLESPACE; SELECT COUNT(*) FROM tsource; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsource%'; DROP TABLE tsource; USE db_source; DROP TABLE tsource; --echo # Scenario-2: --echo # Same as scenario-1. But, do not copy the .cfg file and verify the contents on the source and destination --echo USE db_source; CREATE TABLE tsource (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M; call bulk_insert_source(); SELECT COUNT(*) FROM tsource; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsource%'; USE db_destination; CREATE TABLE tsource (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M; --disable_warnings ALTER TABLE tsource DISCARD TABLESPACE; --enable_warnings USE db_source; FLUSH TABLE tsource FOR EXPORT; --echo # List of files on source --list_files $MYSQLD_DATADIR/db_source --copy_file $MYSQLD_DATADIR/db_source/tsource.ibd $MYSQLD_DATADIR/db_destination/tsource.ibd --echo # List of files on destination --list_files $MYSQLD_DATADIR/db_destination UNLOCK TABLES; USE db_destination; --disable_warnings ALTER TABLE tsource IMPORT TABLESPACE; --enable_warnings SELECT COUNT(*) FROM tsource; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsource%'; DROP TABLE tsource; USE db_source; DROP TABLE tsource; --echo # Scenario-3: --echo # Create an empty table on source with autoextend_size 8m --echo # Create a table with the same schema as the source but with autoextend_size 4m --echo # Discard tablespace on destination --echo # Flush source table for export --echo # Copy .cfg and .ibd file from source to destination --echo # UNLOCK TABLES --echo # Import tablespace on the destination --echo # Load data on the destination table --echo USE db_source; CREATE TABLE tsource (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 8M; USE db_destination; CREATE TABLE tsource (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4m; ALTER TABLE tsource DISCARD TABLESPACE; USE db_source; FLUSH TABLE tsource FOR EXPORT; --echo # List of files on source --list_files $MYSQLD_DATADIR/db_source --copy_file $MYSQLD_DATADIR/db_source/tsource.ibd $MYSQLD_DATADIR/db_destination/tsource.ibd --copy_file $MYSQLD_DATADIR/db_source/tsource.cfg $MYSQLD_DATADIR/db_destination/tsource.cfg --echo # List of files on destination --list_files $MYSQLD_DATADIR/db_destination UNLOCK TABLES; USE db_destination; ALTER TABLE tsource IMPORT TABLESPACE; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsource%'; call bulk_insert_dest(); SELECT COUNT(*) FROM tsource; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsource%'; DROP TABLE tsource; USE db_source; DROP TABLE tsource; --echo # Scenario-4: --echo # Create a table with encryption turned on with autoextend_size as 8M --echo # Create a table on the destination with the same schema as the source but with autoextend_size 4M --echo # Discard tablespace on destination --echo # Flush source table for export --echo # Copy .cfg, .cfp and .ibd file from source to destination --echo # UNLOCK TABLES --echo # Import tablespace on the destination --echo # Load data on the destination table --echo USE db_source; CREATE TABLE tsource (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 8M, ENCRYPTION='Y'; call bulk_insert_source(); SELECT COUNT(*) FROM tsource; USE db_destination; CREATE TABLE tsource (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4m, ENCRYPTION='Y'; ALTER TABLE tsource DISCARD TABLESPACE; USE db_source; FLUSH TABLE tsource FOR EXPORT; --echo # List of files on source --list_files $MYSQLD_DATADIR/db_source --copy_file $MYSQLD_DATADIR/db_source/tsource.ibd $MYSQLD_DATADIR/db_destination/tsource.ibd --copy_file $MYSQLD_DATADIR/db_source/tsource.cfg $MYSQLD_DATADIR/db_destination/tsource.cfg --copy_file $MYSQLD_DATADIR/db_source/tsource.cfp $MYSQLD_DATADIR/db_destination/tsource.cfp --echo # List of files on destination --list_files $MYSQLD_DATADIR/db_destination UNLOCK TABLES; USE db_destination; ALTER TABLE tsource IMPORT TABLESPACE; SELECT COUNT(*) FROM tsource; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsource%'; DROP TABLE tsource; USE db_source; DROP TABLE tsource; --echo # Scenario-5: --echo # Create a table with encryption turned on with autoextend_size as 8M --echo # Create a table on the destination with the same schema as the source but with default autoextend_size --echo # Discard tablespace on destination --echo # Flush source table for export --echo # Copy .cfg and .ibd file from source to destination but do not copy .cfp file --echo # UNLOCK TABLES --echo # Import tablespace on the destination --echo # Load data on the destination table --echo USE db_source; CREATE TABLE tsource (c1 INT, c2 TEXT) AUTOEXTEND_SIZE 8M, ENCRYPTION='Y'; call bulk_insert_source(); SELECT COUNT(*) FROM tsource; USE db_destination; CREATE TABLE tsource (c1 INT, c2 TEXT) ENCRYPTION='Y'; ALTER TABLE tsource DISCARD TABLESPACE; USE db_source; FLUSH TABLE tsource FOR EXPORT; --echo # List of files on source --list_files $MYSQLD_DATADIR/db_source --copy_file $MYSQLD_DATADIR/db_source/tsource.ibd $MYSQLD_DATADIR/db_destination/tsource.ibd --copy_file $MYSQLD_DATADIR/db_source/tsource.cfg $MYSQLD_DATADIR/db_destination/tsource.cfg --echo # List of files on destination --list_files $MYSQLD_DATADIR/db_destination UNLOCK TABLES; USE db_destination; --error ER_TABLE_SCHEMA_MISMATCH ALTER TABLE tsource IMPORT TABLESPACE; DROP TABLE tsource; --echo # List of files to be removed from destination --list_files $MYSQLD_DATADIR/db_destination --remove_file $MYSQLD_DATADIR/db_destination/tsource.ibd --remove_file $MYSQLD_DATADIR/db_destination/tsource.cfg CREATE TABLE tsource (c1 INT, c2 TEXT) ENCRYPTION='Y'; ALTER TABLE tsource DISCARD TABLESPACE; USE db_source; FLUSH TABLE tsource FOR EXPORT; SELECT COUNT(*) FROM tsource; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsource%'; --echo # List of files on source --list_files $MYSQLD_DATADIR/db_source --copy_file $MYSQLD_DATADIR/db_source/tsource.ibd $MYSQLD_DATADIR/db_destination/tsource.ibd --copy_file $MYSQLD_DATADIR/db_source/tsource.cfg $MYSQLD_DATADIR/db_destination/tsource.cfg --copy_file $MYSQLD_DATADIR/db_source/tsource.cfp $MYSQLD_DATADIR/db_destination/tsource.cfp --echo # List of files on destination --list_files $MYSQLD_DATADIR/db_destination UNLOCK TABLES; USE db_destination; ALTER TABLE tsource IMPORT TABLESPACE; SELECT COUNT(*) FROM tsource; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsource%'; DROP TABLE tsource; USE db_source; DROP TABLE tsource; --echo # --echo # IMPORT/EXPORT for partitioned tables with autoextend_size --echo # --echo --echo # Scenario-1: --echo # Create and populate a partitioned table on the source --echo # Create similar partitioned table on the destination --echo # Discard tablespace on the destination --echo # Flush table for export on source --echo # Copy .cfg and .ibd files from source to destination --echo # UNLOCK TABLES --echo # Import tablespace on the destination --echo # Verify the contents of the source and destination tables --echo # Verify that the autoextend_size values are retained --echo # on all the partitions after import --echo USE db_source; CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (250), PARTITION p1 VALUES LESS THAN (600), PARTITION p2 VALUES LESS THAN MAXVALUE); call bulk_insert_source(); SELECT COUNT(*) FROM tsource; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsource%'; USE db_destination; CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 8M PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (250), PARTITION p1 VALUES LESS THAN (600), PARTITION p2 VALUES LESS THAN MAXVALUE); ALTER TABLE tsource DISCARD TABLESPACE; USE db_source; FLUSH TABLE tsource FOR EXPORT; --echo # List of files on source --list_files $MYSQLD_DATADIR/db_source --copy_files_wildcard $MYSQLD_DATADIR/db_source/ $MYSQLD_DATADIR/db_destination/ tsource*.ibd --copy_files_wildcard $MYSQLD_DATADIR/db_source/ $MYSQLD_DATADIR/db_destination/ tsource*.cfg --echo # List of files on destination --list_files $MYSQLD_DATADIR/db_destination UNLOCK TABLES; USE db_destination; ALTER TABLE tsource IMPORT TABLESPACE; SELECT COUNT(*) FROM tsource; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsource%'; DROP TABLE tsource; USE db_source; DROP TABLE tsource; --echo # Scenario-2: --echo # Same as scenario-1, but without copying the .cfg files to the destination db --echo USE db_source; CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (250), PARTITION p1 VALUES LESS THAN (600), PARTITION p2 VALUES LESS THAN MAXVALUE); call bulk_insert_source(); SELECT COUNT(*) FROM tsource; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsource%'; USE db_destination; CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 8M PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (250), PARTITION p1 VALUES LESS THAN (600), PARTITION p2 VALUES LESS THAN MAXVALUE); ALTER TABLE tsource DISCARD TABLESPACE; USE db_source; FLUSH TABLE tsource FOR EXPORT; --echo # List of files on source --list_files $MYSQLD_DATADIR/db_source --copy_files_wildcard $MYSQLD_DATADIR/db_source/ $MYSQLD_DATADIR/db_destination/ tsource*.ibd --echo # List of files on destination --list_files $MYSQLD_DATADIR/db_destination UNLOCK TABLES; USE db_destination; --disable_warnings ALTER TABLE tsource IMPORT TABLESPACE; --enable_warnings SELECT COUNT(*) FROM tsource; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsource%'; DROP TABLE tsource; USE db_source; DROP TABLE tsource; --echo # Scenario-3: --echo # Same as scenario-1, but without copying .cfg file for one partition --echo USE db_source; CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (250), PARTITION p1 VALUES LESS THAN (600), PARTITION p2 VALUES LESS THAN MAXVALUE); call bulk_insert_source(); SELECT COUNT(*) FROM tsource; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsource%'; USE db_destination; CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 8M PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (250), PARTITION p1 VALUES LESS THAN (600), PARTITION p2 VALUES LESS THAN MAXVALUE); ALTER TABLE tsource DISCARD TABLESPACE; USE db_source; FLUSH TABLE tsource FOR EXPORT; --echo # List of files on source --list_files $MYSQLD_DATADIR/db_source --copy_files_wildcard $MYSQLD_DATADIR/db_source/ $MYSQLD_DATADIR/db_destination/ tsource*.ibd --copy_files_wildcard $MYSQLD_DATADIR/db_source/ $MYSQLD_DATADIR/db_destination/ tsource*p0.cfg --copy_files_wildcard $MYSQLD_DATADIR/db_source/ $MYSQLD_DATADIR/db_destination/ tsource*p2.cfg --echo # List of files on destination --list_files $MYSQLD_DATADIR/db_destination UNLOCK TABLES; USE db_destination; --disable_warnings ALTER TABLE tsource IMPORT TABLESPACE; --enable_warnings SELECT COUNT(*) FROM tsource; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsource%'; DROP TABLE tsource; USE db_source; DROP TABLE tsource; --echo # Scenario-4: --echo # Create a partitioned table on the source with encryption turned on and autoextend_size 4m --echo # Load the table on the source db --echo # Create a partitioned table on the destination with encryption turned on and autoextend_size --echo # 4m and schema similar to that on the source db --echo # ALTER TABLE ... DISCARD TABLESPACE on destination db --echo # FLUSH TABLE ... FOR EXPORT on source db --echo # Copy the .ibd, .cfg and .cfp files from source db to destination db --echo # UNLOCK TABLES --echo # ALTER TABLE ... IMPORT TABLESPACE on destination db --echo # Verify that the data is successfully imported on the destination db --echo USE db_source; CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M, ENCRYPTION='Y' PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (250), PARTITION p1 VALUES LESS THAN (600), PARTITION p2 VALUES LESS THAN MAXVALUE); call bulk_insert_source(); SELECT COUNT(*) FROM tsource; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsource%'; USE db_destination; CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M, ENCRYPTION='Y' PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (250), PARTITION p1 VALUES LESS THAN (600), PARTITION p2 VALUES LESS THAN MAXVALUE); ALTER TABLE tsource DISCARD TABLESPACE; USE db_source; FLUSH TABLE tsource FOR EXPORT; --echo # List of files on source --list_files $MYSQLD_DATADIR/db_source --copy_files_wildcard $MYSQLD_DATADIR/db_source/ $MYSQLD_DATADIR/db_destination/ tsource*.ibd --copy_files_wildcard $MYSQLD_DATADIR/db_source/ $MYSQLD_DATADIR/db_destination/ tsource*.cfg --copy_files_wildcard $MYSQLD_DATADIR/db_source/ $MYSQLD_DATADIR/db_destination/ tsource*.cfp --echo # List of files on destination --list_files $MYSQLD_DATADIR/db_destination UNLOCK TABLES; USE db_destination; --disable_warnings ALTER TABLE tsource IMPORT TABLESPACE; --enable_warnings SELECT COUNT(*) FROM tsource; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsource%'; DROP TABLE tsource; USE db_source; DROP TABLE tsource; --echo # Scenario-5: --echo # Same as scenario-4, but do not copy the .cfp files from the source --echo # to the destination db --echo # Verify that the data is successfully imported on the destination db --echo USE db_source; CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M, ENCRYPTION='Y' PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (250), PARTITION p1 VALUES LESS THAN (600), PARTITION p2 VALUES LESS THAN MAXVALUE); call bulk_insert_source(); SELECT COUNT(*) FROM tsource; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsource%'; USE db_destination; CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M, ENCRYPTION='Y' PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (250), PARTITION p1 VALUES LESS THAN (600), PARTITION p2 VALUES LESS THAN MAXVALUE); ALTER TABLE tsource DISCARD TABLESPACE; USE db_source; FLUSH TABLE tsource FOR EXPORT; --echo # List of files on source --list_files $MYSQLD_DATADIR/db_source --copy_files_wildcard $MYSQLD_DATADIR/db_source/ $MYSQLD_DATADIR/db_destination/ tsource*.ibd --copy_files_wildcard $MYSQLD_DATADIR/db_source/ $MYSQLD_DATADIR/db_destination/ tsource*.cfg --echo # List of files on destination --list_files $MYSQLD_DATADIR/db_destination UNLOCK TABLES; USE db_destination; --disable_warnings --error ER_TABLE_SCHEMA_MISMATCH ALTER TABLE tsource IMPORT TABLESPACE; --enable_warnings DROP TABLE tsource; --echo # List of files to be removed from destination --list_files $MYSQLD_DATADIR/db_destination --remove_files_wildcard $MYSQLD_DATADIR/db_destination tsource*.* CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M, ENCRYPTION='Y' PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (250), PARTITION p1 VALUES LESS THAN (600), PARTITION p2 VALUES LESS THAN MAXVALUE); ALTER TABLE tsource DISCARD TABLESPACE; USE db_source; FLUSH TABLE tsource FOR EXPORT; --echo # List of files on source --list_files $MYSQLD_DATADIR/db_source --copy_files_wildcard $MYSQLD_DATADIR/db_source/ $MYSQLD_DATADIR/db_destination/ tsource*.ibd --copy_files_wildcard $MYSQLD_DATADIR/db_source/ $MYSQLD_DATADIR/db_destination/ tsource*.cfg --copy_files_wildcard $MYSQLD_DATADIR/db_source/ $MYSQLD_DATADIR/db_destination/ tsource*.cfp --echo # List of files on destination --list_files $MYSQLD_DATADIR/db_destination UNLOCK TABLES; USE db_destination; --disable_warnings ALTER TABLE tsource IMPORT TABLESPACE; --enable_warnings SELECT COUNT(*) FROM tsource; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsource%'; DROP TABLE tsource; USE db_source; DROP TABLE tsource; --echo # Scenario-6: --echo # Same as scenario-4, but do not copy the .cfp and .cfg files from the source --echo # to the destination db --echo # Verify that the data is successfully imported on the destination db --echo USE db_source; CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M, ENCRYPTION='Y' PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (250), PARTITION p1 VALUES LESS THAN (600), PARTITION p2 VALUES LESS THAN MAXVALUE); call bulk_insert_source(); SELECT COUNT(*) FROM tsource; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsource%'; USE db_destination; CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M, ENCRYPTION='Y' PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (250), PARTITION p1 VALUES LESS THAN (600), PARTITION p2 VALUES LESS THAN MAXVALUE); ALTER TABLE tsource DISCARD TABLESPACE; USE db_source; FLUSH TABLE tsource FOR EXPORT; --echo # List of files on source --list_files $MYSQLD_DATADIR/db_source --copy_files_wildcard $MYSQLD_DATADIR/db_source/ $MYSQLD_DATADIR/db_destination/ tsource*.ibd --echo # List of files on destination --list_files $MYSQLD_DATADIR/db_destination UNLOCK TABLES; USE db_destination; --disable_warnings --error ER_TABLE_SCHEMA_MISMATCH ALTER TABLE tsource IMPORT TABLESPACE; --enable_warnings DROP TABLE tsource; --echo # List of files to be removed from destination --list_files $MYSQLD_DATADIR/db_destination --remove_files_wildcard $MYSQLD_DATADIR/db_destination tsource*.* CREATE TABLE tsource(c1 INT, c2 TEXT) AUTOEXTEND_SIZE 4M, ENCRYPTION='Y' PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (250), PARTITION p1 VALUES LESS THAN (600), PARTITION p2 VALUES LESS THAN MAXVALUE); ALTER TABLE tsource DISCARD TABLESPACE; USE db_source; FLUSH TABLE tsource FOR EXPORT; --echo # List of files on source --list_files $MYSQLD_DATADIR/db_source --copy_files_wildcard $MYSQLD_DATADIR/db_source/ $MYSQLD_DATADIR/db_destination/ tsource*.ibd --copy_files_wildcard $MYSQLD_DATADIR/db_source/ $MYSQLD_DATADIR/db_destination/ tsource*.cfg --copy_files_wildcard $MYSQLD_DATADIR/db_source/ $MYSQLD_DATADIR/db_destination/ tsource*.cfp --echo # List of files on destination --list_files $MYSQLD_DATADIR/db_destination UNLOCK TABLES; USE db_destination; --disable_warnings ALTER TABLE tsource IMPORT TABLESPACE; --enable_warnings SELECT COUNT(*) FROM tsource; SELECT NAME, FILE_SIZE, AUTOEXTEND_SIZE FROM information_schema.innodb_tablespaces WHERE NAME LIKE '%tsource%'; DROP TABLE tsource; USE db_source; DROP TABLE tsource; --echo # Cleanup USE db_source; DROP PROCEDURE bulk_insert_source; DROP DATABASE db_source; USE db_destination; DROP PROCEDURE bulk_insert_dest; DROP DATABASE db_destination;