MySQL 5.6 Transportable Tablespaces

Transportable tablespaces in mysql 5.6 are an amazing feature allows InnoDB tables to be exported from one database and imported into another. This allowed me to recently consolidate 4 large but lightly used databases onto one server with little down time by exporting, copying to a new server, and importing.

In versions prior to 5.6 your options were to mysqldump or backup/restore and it was difficult to do that with a large (200GB+) database as it could take several days to a week. With transportable tablespaces, I was able to export/copy/import a 200GB+ database in under 5 hours.

Note that you need to have innodb_file_per_table enabled for this to work.

Also, if you get the following error, you need to ALTER the column(s) in question to upgrade to the 5.6 storage format:

ERROR 1808 (HY000): Schema mismatch (Column updated_at precise type mismatch.)

For more information on the timestamp,date,datetime storage changes, check here. Note that running an optimize on the table to rebuild it will *not* work (I tried). You must run an alter on the specific column(s) in question.

Ie, to update a datetime column in the wp_users table that has the following defintion:

`user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'

Use this statement:


ALTER TABLE wp_users
CHANGE COLUMN user_registered user_registered datetime NOT NULL DEFAULT '0000-00-00 00:00:00';


Also check out online DDL in MySQL 5.6!