Articles in this section

See more

Migrate MyISAM tables to INNODB using mysqldump

Avatar
Johan
  • Updated
Follow

In this article you will find information how to convert MyISAM tables to INNODB. 

The procedure here is an offline procedure, i.e, you cannot have changes on the SOURCE database, while you do this. A better way could be to setup replication between the SOURCE and the TARGET, but that is not described here.

On the source database

In this example we dump out the database mydb into mydb_schema.sql and mydb_data.sql

mysqldump -uroot -p --no-data  -R --triggers mydb > mydb_schema.sql

Then dump out the data:

mysqldump -uroot -p --no-create-info  -R --triggers mydb > mydb_data.sql

Change Storage Engine

You must verify first you don't have any MYISAM tables with FULLTEXT, since innodb only supports FULLTEXT from MySQL version 5.6.

If you have FULLTEXT indexes, then change manually all but the full text tables to innodb (not shown here).

sed -i.bak 's#MyISAM#innodb#g' mydb_schema.sql

On the TARGET database server

Create the database on the target database server and load in the dump into the database mydb:

# create the database
mysql -uroot -p -e "create database mydb" 
# create the schema
mysql -uroot -p mydb < mydb_schema.sql 
# load in the data
mysql -uroot -p mydb < mydb_data.sql

Related articles

Comments

4 comments

Sort by Date Votes
  • Avatar
    David Bennett
    • Edited

    The -R and --triggers options are not needed on the second mysqldump (data) as the functions, stored procedures, and triggers will be migrated in the first (schema) dump.   Recent changes in MySQL may make this even more problematic if your schema phase requires updates to the stored procedures and functions (such as ERROR 1418 validations)

    0
    Comment actions Permalink
  • Avatar
    bastiat666

    Explain sed-i.bak.
    Is that a typo?

    0
    Comment actions Permalink
  • Avatar
    Sofiya Williams

    I like your writing style. This article is helpful for my team. As a team leader, I personally thank you through this comment.MyISAM tables to INNODB

    0
    Comment actions Permalink
  • Avatar
    Delta Executor

    To migrate MyISAM tables to InnoDB using mysqldump

    1. Dump the Database:

      bash
      Copy code
      mysqldump -u username -p dbname > backup.sql

    2. Replace Storage Engine: Use sed to change MyISAM to InnoDB:

      bash
      Copy code
      sed -i 's/ENGINE=MyISAM/ENGINE=InnoDB/g' backup.sql

    3. Restore the Dump:

      bash
      Copy code
      mysql -u username -p dbname < backup.sql

    This converts all tables to InnoDB.

    0
    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk