Converting MYISAM database tables to INNODB

nodle

Administrator
Staff member
Member
Joined
Jan 28, 2006
Messages
42,660
Reaction score
1,473
If your hosting has you set to MYISAM and you need to convert them to INNODB, follow this guide. This is to convert all your tables at once. You can do one table at a time but this is for all of them. 

So before you do anything, back up your database

  1. Launch PHPMyAdmin. You must explicitly click on the name of your database in the top of the left column. There are sometimes multiple databases, so make sure to click the right one. Do not click on the one labelled information_schema._
     
  2. Click the Query tab
     
  3. In the box at the lower right, paste the following code into the "SQL query on database …" box, replacing "your-db-name" with your actual DB name. I found that opening the command shell works best for this at the very bottom:
     
    SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;')
    FROM INFORMATION_SCHEMA.TABLES
    WHERE ENGINE='MyISAM'
    AND table_schema = 'your-db-name';
     
     
  4. Press the submit query button to see a list of tables to alter to InnoDB.
     
     
  5. Click the "+Options" link above the results, choose the "Full texts" option, and press the "Go" button in the shaded area at the lower right. You must do this or you will not be able to see the full line of code. If this part doesn't show go back to #3 and run the command in the command shell on the bottom.
     
     
  6. Above the results, tick the "Show all" checkbox.
     
     
  7. Copy all the queries using the "Copy to clipboard" link in the "Query results operations" box below the results.
     
     
  8. Paste the result into a text editor and copy all the lines starting with "ALTER TABLE" to your clipboard. Make sure to delete everything listed above and only start at the first "ALTER TABLE".
     
     
  9. Click the SQL tab above the results.
     
     
  10. Paste the ALTER TABLE statements into the textarea and press the "Go" button in the lower right shaded area.
     



To confirm your database was successfully converted, click your database name in the left column, and all table types should now say InnoDB.

 
200.gif


 
For a single entry go to SQL at top, enter:

ALTER TABLE. table_name ENGINE=InnoDB;

then click 'Go' on bottom. Check tables.

 
Last edited by a moderator:
Back
Top