MySQL: how to convert many MyISAM tables to InnoDB in a production database

innodbmigrationmyisamMySQL

We have a production database that is made up entirely of MyISAM tables. We are considering converting them to InnoDB to gain better concurrency & reliability.

  1. Can I just alter the myISAM tables to InnoDB without shutting down MySQL? What are the recommend procedures here?

  2. How long will such a conversion take? All the tables have a total size of about 700MB

  3. There are quite a large number of tables. Is there any way to apply ALTER TABLE to all the MyISAM tables at once instead of doing it one by one?

  4. Any pitfalls I need to be aware of?

Thank you

Best Answer

You can do it. Converting from MyISAM to InnoDB should not be a problem as InnoDB has more features which are not available in MyISAM such as transactions.

You can alter the tables while the database is running. No need for shutdown, but this operation will lock the tables.

For the time it will take, this depends on the server specs/load/db size, etc.

You can do it using a script to automate the process. Using a command show tables, you can get the list of tables. Then, using a simple script you can read this list and execute alter table commands.

Here is a simple perl script that will read the tables list from a file and write the SQL queries in another file. After invoking this script, you can feed the output file to mysql command to do the required changes.

open(INFILE, "< tables_list.txt");
open(OUTFILE, "> alter_tables.sql");
foreach my $name (<INFILE>) {
   chomp($name);
   print OUTFILE "ALTER TABLE $name engine=InnoDB;\n";
}
close(INFILE);
close(OUTFILE);
Related Topic