MySQL 5.6 Transportable Tablespaces
By : Josh -
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!