Update or migrate? Planning for MySQL 5.7 EOL

MySQL is the most popular open source database in the world, according to DB-Engines, and it has ranked as the second most popular database overall for more than a decade. MySQL fueled the rise of the LAMP stack and has been a trusty companion to many a developer and DBA over the years. In October 2023, version 5.7 will reach end of life status, meaning that this version will no longer receive updates or security patches.

This is significant because, with four months to go, more than half of those running MySQL servers are currently on v5.7 according to the data received from those using Percona Monitoring and Management for their database management tasks and that have elected to share telemetry data with us. As this is a representative sample of database installations, that means there are a lot of database servers out there that are just four months away from end of life.

To prepare for the move, what should you be aware of? I’ve sketched out the costs and benefits of the various options below.

Migrating to MySQL 8.0

To start with, you should look at what is involved in the move from MySQL 5.7 to MySQL 8.0, which will be the only supported version of MySQL in the future. MySQL 8.0 has been on the market since 2016, so it is a very stable option, but there are some significant changes compared to the previous edition.

One major change is the enhancements to SQL (Structured Query Language) supported in MySQL 8.0 that have made it easier for developers and DBAs to support queries. For example, if you have trouble writing subqueries, you will rejoice in the support for lateral-derived joins and common table expressions (CTEs). There is also a new intersect clause to aid with sets.

MySQL 8.0 also supports new commands that are not included in MySQL 5.7. One example is EXPLAIN ANALYZE, which is a big boon to query tuning. The EXPLAIN command gives you the server’s estimated analysis of the performance of your query. Adding ANALYZE causes the query to execute, and the numbers returned to report the real numbers of the query’s performance. This provides more insight into how queries run in practice, and makes it easier to find improvements. Alongside this, the INVISIBLE INDEX command helps you test the efficiency of an index without risking a disastrous rebuild after a delete.

Alongside these changes, the updated default character set UTF8MB4 provides Unicode version 9.0 support, meaning that you can support international characters. This is especially useful if you have to support global operations.

Migrating to MySQL 8.0 is a one-way street, so you will have to determine whether your application and database will support the move. One efficient way to check this is by using MySQL Shell’s util.checkForServerUpgrade() utility, which carries out 21 different tests to find any potential problems that might come up once you start the migration process. This includes checking for any tables with names that conflict with new reserved keywords, for partitioned tables that use engines with non-native partitioning, for circular directory references in tablespace data file paths, and for usage of removed functions. Similarly, the utility will look for issues around system variables that have been removed or changed to new default values.

Depending on your existing MySQL implementation, you may only need to make some minor changes to be ready. However, if your application comes back with multiple issues and updates, then you will have more work to carry out.

Considering DBaaS and MySQL alternatives

Alongside checking your systems for potential migration problems, you should also investigate your options overall. For example, is MySQL still the best database for you and your team, or should you consider alternatives? If you will have to put significant amounts of work into your application to bring it up to scratch, should you put that effort into a migration to a different platform? Equally, will you continue to run your database infrastructure in the same way, or should you use a different approach such as database as a service (DBaaS)?

There are three choices you could make. The first option is to do nothing. You might decide that the cost of moving an application to a new database version is too high and choose to continue running on database versions that are out of support. This is not ideal, but there may be circumstances when it is the best option. One company I work with had a similar situation when looking at MySQL, and decided that they would leave their systems as they were, because the application was not directly connected to the public internet and was due for a refresh in two years.

The amount of work to get the application migrated was higher than the cost to mitigate potential security risks and buy extended support, so they decided to stay on their current version of MySQL. This was an active decision with a real business case and risk management approach, rather than digging their head in the sand to ignore the problem.

The second option is to make the move, but change where you host your databases. For example, MySQL-compatible cloud services and hosting providers can manage these machines on your behalf rather than your having to run your own infrastructure. DBaaS options can take away some of the infrastructure management headaches, but they will have to be managed and updated in their own right as well.

The third option is to migrate to a different database. When your application and database installation have to be updated and the work will be significant, then any effort put in could be used for moving to a different database. This can be useful if you want to move your systems as a whole, but it can require additional planning to look at your business logic as well as your infrastructure.

MySQL or PostgreSQL?

The most common external option for MySQL migration is PostgreSQL, as it is a similarly popular open source database with a significant community around it. PostgreSQL was itself recently updated to support the SQL command MERGE, which is commonly used across Microsoft SQL Server, Oracle Database, and MySQL. This was added in PostgreSQL 15 to make it easier to migrate to PostgreSQL without significant rewrites. This migration may require some rewriting, but if you are already having to make changes to move to MySQL 8.0, then why not stretch to a shift over?

A MERGE migration can also support using a commercial or DBaaS version of PostgreSQL. There are many database services based on PostgreSQL, thanks to its flexible open source license, so many companies tout their ability to support this. However, it is worth looking at whether any of these options are fully compatible, and truly do support open source PostgreSQL, rather than being their own specific variant. This could be a one-way street similar to MySQL migration, but with fewer options once you have made the move.

Migrating from MySQL 5.7 to MySQL 8.0 or beyond will be a task that many developers and DBAs will have to support over the next few months. Start by planning ahead and understanding your options. By looking at your existing applications, how much work you will have to put in, and what you want or need from your application infrastructure in the future, you can evaluate the costs and benefits of the different paths ahead.

An in-place MySQL update, a full migration to a new platform, or even staying in place are all options that you can consider. However, rather than sitting back or putting your head in the sand, you can get ahead of the issues and make the most of your opportunities.

Dave Stokes is technology evangelist at Percona.

—

New Tech Forum provides a venue to explore and discuss emerging enterprise technology in unprecedented depth and breadth. The selection is subjective, based on our pick of the technologies we believe to be important and of greatest interest to InfoWorld readers. InfoWorld does not accept marketing collateral for publication and reserves the right to edit all contributed content. Send all inquiries to newtechforum@infoworld.com.