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;
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:
[URL=“Should you move from MyISAM to Innodb ? - Percona Database Performance Blog”]https://www.percona.com/blog/2009/01...sam-to-innodb/[/URL] - a blog discussion by Peter Zaitsev our CEO
[URL=“Quick tip: how to convert tables to InnoDB - Percona Database Performance Blog”]https://www.percona.com/blog/2008/07...les-to-innodb/[/URL] - a how to post by Baron Schwartz, co-writer of High Performance MySQL v3 and ex-Percona
[URL=“Moving from MyISAM to Innodb or XtraDB. Basics - Percona Database Performance Blog”]https://www.percona.com/blog/2010/11...xtradb-basics/[/URL] - 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 [URL=“Percona Live New York 2011”]https://www.percona.com/files/presen...rom-MyISAM.pdf[/URL]
… and that blog post by Rick, a pragmatic post on how to check for issues that might arise and pre-empt known challenges … [URL]http://mysql.rjweb.org/doc.php/myisam2innodb[/URL]
Anyway, I hope these help you and don’t hesitate to post again if you run into any specific issues you need advice on.
Thanks for the information and I will post here if I run into any issues.
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? [url]https://www.percona.com/software/mysql-database/percona-xtrabackup[/url]