Switch to InnoDB engine from MyISAM

  • Filter
  • Time
  • Show
Clear All
new posts

  • Switch to InnoDB engine from MyISAM

    I am extremely new to percona. Like as of 6 months. I have used mysql but mostly sql server. So forgive me if this is a stupid question. How do you which from MyISAM to InnoDB engine? Do I create a new innodb table. Do a sql dump then get rid of the old table and rename the new table? Or do I just run
    ALTER TABLE table_name ENGINE=InnoDB;

  • #2
    Hello there and thank you for joining in with our forums. First rule - there are NO stupid questions, and even if there were, this wouldn't be one of them! As you're fairly new to Percona you probably haven't had the chance to look around our Performance Blog and some of the other resources we have. The question you have has been the subject of quite a few entries, so an interesting and valid point that will become more and more relevant as people move to MySQL 8.0 (the beginning of the end for MyISAM).

    So I have some links for you that might be useful... Also, I have found a link by MySQL community member, Rick James, who writes quite a lot of advice blogs and is independent of Percona. You might find his perspective useful too. Here you go - and you are right to be a little cautious since it is best to be aware of any 'gotchas' that might arise:

    https://www.percona.com/blog/2009/01...sam-to-innodb/ - a blog discussion by Peter Zaitsev our CEO
    https://www.percona.com/blog/2008/07...les-to-innodb/ - a how to post by Baron Schwartz, co-writer of High Performance MySQL v3 and ex-Percona
    https://www.percona.com/blog/2010/11...xtradb-basics/ - another blog discussion on switching by Peter
    https://www.percona.com/blog/2016/10...-0-end-myisam/ - Principle Consultant Alexander Rubin's discussion of support for MyISAM in MySQL 8.0 and beyond

    You might prefer Matt Yonkovit's webinar if you prefer voice over reading: https://www.percona.com/webinars/201...-myisam-innodb - Matt is our Chief Customer Officer. Matt's slides from a presentation are here https://www.percona.com/files/presen...rom-MyISAM.pdf

    ... and that blog post by Rick, a pragmatic post on how to check for issues that might arise and pre-empt known challenges ... http://mysql.rjweb.org/doc.php/myisam2innodb

    Anyway, I hope these help you and don't hesitate to post again if you run into any specific issues you need advice on.
    Last edited by lorraine.pocklington; 01-03-2018, 06:16 AM.


    • #3
      Thanks for the information and I will post here if I run into any issues.


      • #4
        You're welcome - do take a back up in any case before you go ahead. If you haven't tried it yet, you might find Percona XtraBackup worth exploring? https://www.percona.com/software/mys...ona-xtrabackup