Currently we have a MySQL 5.1 - MyISAM database on a physical server. We need to move it to another server with MySQL 5.6 - InnoDB at another location.

DB size is quite large - 210GB

Can someone help - how can we acheive this.

We appreciate your assistance.

  • Pasting your question into the Google search field might be a good start. – mustaccio Jul 26 '16 at 14:38
  • Already tried Google Search. No meaningful answer. – EmpowerMe Jul 26 '16 at 14:41

What you do is to

a) take a mysqldump of your database:

mysqldump --user my_user --password=<pass> --host=my_db schema_name > my_schema.sql

b) then use a tool such as sed to substitute MyISAM for InnoDB:

sed -i 's/ENGINE=MyISAM/ENGINE=InnoDB/g' my_schema.sql

c) restore the dump on your 5.6 instance:

mysql --user=my_user --password=<pass> my_schema < my_schema.sql

If you're on Windows, you can use vim for Windows or the editing tool of your choice.

You might be better off using a command like

sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g' my_schema.sql > my_schema2.sql

or take a backup of your .sql file - with 260 GB, you never know what can go wrong - from the sed manpage

when in-place editing files, as you risk corruption or partial content in situ- ations where disk space is exhausted, etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.