3

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

4 Answers 4

2

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);
1
  • A word of warning: InnoDB doesn't support full-text indexing, and I think MyISAM handles auto-increment columns slightly differently when there's a composite primary key. Other than that, shouldn't be any issues. And if you're not already using InnoDB, make sure you configure the log file size first. You have to export, drop, and reimport all InnoDB tables to change this later, I believe.
    – db2
    Apr 22, 2011 at 17:22
4

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.

2

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
1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .