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
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 DATABASE sakila2; Query OK, 1 row affected (0.00 sec) $ mysql sakila2 < schema.sql $ mysql sakila2 < data.sql …[Read more]