Upgrades to critical software like MySQL are a necessary fact of life. The stability, performance and information security of your environment all depend on keeping your MySQL instances up-to-date. But MySQL upgrades in general require planning and testing so you don’t destabilize your application(s). And if you’re upgrading past MySQL 5.5, there are a couple critical choices you need to make. This post offers some insights.
Step 1: Convert MyISAM tables to InnoDB
Before you upgrade MySQL beyond version 5.5, it’s best to convert any remaining MyISAM tables in your environment to InnoDB, so that your applications can exploit the advantages of newer MySQL versions.
- Support for ACID-compliant transaction features (e.g., crash-recoverable transactions)
- Support for foreign keys
- Support for the new online schema change support introduced in MySQL 5.6
- Moving from MyISAM to InnoDB improves performance for most use cases as well.
About the only downside to converting from MyISAM to InnoDB is an increase in your storage footprint.
Step 2: Get to 5.5 first, then upgrade to 5.6 or 5.7
If you have any servers running “old” MySQL versions (e.g., 5.0 or 5.1), it’s an established best practice to upgrade first to MySQL 5.5 and test that out before continuing on to 5.6 or 5.7. While more time-consuming, this approach lowers your risk by allowing the upgrade process to apply changes from one major release at a time.
The MySQL folks recently discussed two “experimental” upgrade processes to enable you to jump directly to MySQL 5.7. However, these approaches are not officially supported and greatly increases your risk.
Step 3: Deal with FULLTEXT (if you need to)
Support for full-text indexing/search wasn’t introduced in InnoDB until MySQL 5.6/MariaDB10.0. So if any of your applications are using FULLTEXT, you’ll need to upgrade to at least MySQL 5.6 (or a derivative) if not 5.7.
Step 4: Pick a flavor
Once you upgrade past MySQL 5.5 you have three MySQL-compatible distributions to choose from:
- MySQL 5.7, which is developed and supported by Oracle
- MariaDB 10.1, which is developed by the open-source community around the MariaDB Foundation and commercially supported by MariaDB Corporation AB
- The recently released Percona Server 5.6
About MariaDB: Following the acquisition of Sun Microsystems and its subsidiary MySQL AB by Oracle in January 2010, a group of MySQL core developers led by the Michael Widenius, the project’s founder, separated themselves from MySQL and forked the project to create MariaDB, starting from the MySQL 5.1 release. MariaDB is feature-for-feature compatible with MySQL up through MySQL 5.6, after which MariaDB’s developers elected to stop following the MySQL trunk. To signify a breaking change with MySQL, they jumped their version number to 10.0.
About Percona Server: In 2015, the MySQL consultancy Percona released their own MySQL-compatible distribution, Percona Server, based on MySQL 5.6. It is generally understood to target high-performance installations, but the feature set does not significantly differ from MySQL or MariaDB and the future roadmap for Percona Server is unclear. As such, I don’t recommend Percona Server for most of our Boston DevOps clients.
MySQL 5.7 versus MariaDB 10.1
Should you go with MySQL 5.7 or MariaDB 10.1? In terms of features, MariaDB has all along included most of the major features of MySQL in their entirety, whereas few features have flowed the other way. As a result, I consider MariaDB’s feature set to be a full superset of MySQL 5.6.
What about the new features in MySQL 5.7? These are comparatively minor in my estimation, and probably won’t be heavily used in the near future.
In terms of distribution support, MariaDB has recently become a supported, drop-in replacement for MySQL in a number of Linux distributions, including Red Hat Enterprise Linux 7. Selecting MySQL might thus result in the need to manually manage patches and updates to MySQL in the future.
In terms of security and reliability, MariaDB’s fully open-source development model has proven to result in faster security patches and bug fixes. Further, as MariaDB has become the preferred choice for Red Hat, automated patch management via yum will likely be a lower-maintenance path.
In terms of performance, MariaDB tends to perform as well or incrementally better than MySQL in both benchmarking and real-world environments.
For all the above reasons, I recommend MariaDB 10.1 to our Boston DevOps customers who are upgrading their MySQL environments. However, if you have a current Oracle support contract that encompasses MySQL, that might change the equation for you.