Jump to content

Converting MYISAM database tables to INNODB


nodle
 Share

Recommended Posts

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.