MySQL – Migrating MyISAM Tables from 5.5 to 5.6

myisamMySQLmysql-5.5mysql-5.6upgrade

I was wondering if anyone had any pointers on migrating MyIASM tables from mysql 5.5 to 5.6, specifically using Percona builds, via an rsync?

Is this safe?

I know we need to do a full mysqldump/reload for a migration for InnoDB tables but I didn't see any notes about significant changes to the MyISAM format between the two.

It would make things a little nicer for a rather large MyISAM db that would take several days to complete with a mysqldump.

Best Answer

MyISAM didn't change between 5.5 and 5.6. It's not being developed, and it's virtually stagnant.

Use FLUSH TABLES WITH READ LOCK and then it's safe to copy the MyISAM tables. This is mentioned in https://dev.mysql.com/doc/refman/5.6/en/backup-methods.html

Other solutions:

  • mysqldump --tab outputs tab-delimited data files instead of SQL, so it's much faster to import with mysqlimport.

  • mysqlhotcopy does a physical copy of MyISAM tables (it doesn't work for InnoDB tables). But be careful because mysqlhotcopy is on the chopping block for deprecation.

  • mydumper, which dumps to SQL, but it supports compression and multi-threaded execution for both dump and restore. Unfortunately it does not support an option like --tab.