Some time ago, I had to convert all tables of a database from MyISAM to InnoDB on a new server. The plan was to take a logical dump on the master, exporting separately the schema and the data, then edit the
CREATE TABLE statements to ensure all tables are created with InnoDB, and reload everything on the new server.
Quite easy, isn’t it? Of course I wanted to run a test first before performing the actions on a live system.
So let’s play with the sakila database.
mysqldump has options to export schema and data separately, let’s use them:
# Export schema
$ mysqldump --no-data sakila > schema.sql
# Export data
$ mysqldump --no-create-info sakila > data.sql
Just to check that everything is fine, let’s reimport the data in a new database:
mysql> CREATE [Read more...]