MySQL 5.6 Transportable Tablespaces

MySQL 5.6 Transportable Tablespaces

By : -

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!

Leave a Reply

Your email address will not be published. Required fields are marked *