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

I use CREATE and SELECT:

 mysql> CREATE TABLE i_table LIKE table;
 mysql> ALTER TABLE i_table ENGINE=InnoDB;
 mysql> INSERT INTO i_table SELECT * FROM table;
 mysql> RENAME TABLE table TO b_table;
 mysql> RENAME TABLE i_ TO other_db.tbl_name;

That works well if you do not have many data. Better use

mysql> ALTER TABLE table ENGINE = InnoDB;

Take care before trying this method on a busy database.

Also you can dump db, change ENGINE in dump file and DATABASE name in dump file. Restore DB and then rename DB.

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);

I have an automated way to convert MyISAM to InnoDB using just MySQL

Here is a query to generate the SQL for MyISAM tables in every database:

SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL
FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN
('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length);

At the Linux prompt, run the script creation like this:

mysql -h... -u... -p.... -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" > MyISAM_To_InnoDB.sql

This generate the SQL to convert every MyISAM table in all databases from the smallest table to the biggest table.

You can easily adjust the SQL to convert all MyISAM tables into InnoDB within a specific database like this (for example database mystuff):

mysql -h... -u... -p.... -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema = 'mystuff' ORDER BY (data_length+index_length)" > MyISAM_To_InnoDB.sql

Khaled and ooshro have you covered on the semantics; questions 2 and 4 are harder to judge because it depends on your configuration and hardware. What you could do for #2 is dump the DB to a file and import it to a workstation or other server (go buy a cloud Slice for $5 and use that if you have to), then try performing your alter options on it to see how long it takes in just a general, overall sense. This isn't really a perfect test but it's better than nothing - 700MB isn't very big and won't take long if your MySQL is on a dedicated server with power (Dell 2970 w/16gig RAM e.g.).

For #4 - do not convert the mysql/ database to InnoDB. :) Overall, the process will cause a full table lock while it's running (realistically, in the background it's just creating a new table, transferring the data, then delete/renaming it into place) so you want to alter engines during the off hours at night. Ensure you get a full dump just in case something goes wrong. (mysqldump -A --flush-privileges > dbdump.sql).