Are you planning ahead for the MySQL 5.7 end of life? [Q&A]
The popular database MySQL version 5.7 hits end of life status on the 31st of October 2023, just a few months away.
This means organizations that are running MySQL 5.7 will have to plan ahead on their options for the future. Dave Stokes, technology evangelist at Percona, spoke to us about some of the choices that will need to be made as well as how to get started on the process.
BN: What are the issues involved in this kind of move?
DS: Databases are software components, just like any other. The big difference is that databases are seen as big, critical components, and developers don't want to touch them if they don't have to. There are a lot of specialist skills involved around databases, but many people don't want to concentrate on these deployments.
There’s been more of a shift from specialist DBAs to more generalist DevOps or Site Reliability Engineering roles, where you have to support more of the overall IT infrastructure stack. Then, if you have a more specific database problem or a project like this, then you are more likely to bring in a consultant instead rather than retaining that knowledge on the team permanently.
When you have a migration like this, planning ahead is essential. That means looking at how much work you might have to do in order to move, and what the impact will be from your shift. The most important elements to consider are whether you can do this in situ, or whether you will have to migrate from one system to another, and whether you can reverse your migration if things don't work out or you have unexpected problems. Based on this information, you can then plan what your approach should be to minimise any disruption to the business, and to consider what you might achieve in the longer term
BN: How much difference is there between MySQL 5.7 and the current version, v8.0?
DS: You can move from MySQL 5.7 to version 8.0 in place, but there are some fundamental differences that you will have to consider. MySQL 8.0 supports some additional commands like EXPLAIN ANALYZE and INVISIBLE INDEX, and it also adds support for a new character set which means it can support more international characters and emojis. There’s also the support for lateral-derived joins, Common Table Expressions (CTEs) and a new intersect clause to aid with sets. These additions can affect your current set up - for example, you won't be able to use those new commands in your table structures.
BN: What decisions do you have to make around this kind of project?
DS: Checking your current implementation is essential to start with -- use the MySQL Shell util.checkForServerUpgrade() utility for this, as it flags 21 potential issues that you will have to fix in order to migrate successfully. If you have a few problems in place, then this will be pretty easy to solve. If you have lots of incompatibilities then you have a trickier choice to make -- should you invest in making fixes to the application so you can move, or should you spend your time migrating to a different database option instead? After all, you may get more value over time from moving from MySQL to PostgreSQL compared to sticking with MySQL.
The other big decision is how you will run and host those systems in future. Many developers and DBAs might see this as an opportunity to move to hosted services or to Database as a Service options rather than carrying on running their own instances, for example. I expect to see a fair few people move to PostgreSQL, and more that decide to move to cloud as part of this migration.
BN: How can businesses get ahead of this as a problem, and what benefits will they get?
DS: Planning ahead on this helps in a couple of ways. Firstly, we all work better when we have known deadlines but without huge amounts of pressure. Getting started now will ensure that you can think things through and make the business case that is right for your systems, not just moving for the sake of it.
It also means that you can scope out your internal team, any gaps in their knowledge, and then look at where you might need to get more support or consulting expertise in place to support your existing team. Getting this in place is easier if you plan in advance, rather than trying to find resources closer to the time when costs are higher and people are already booked.
We have put together a free course as part of our Percona University for everyone planning ahead around migrations too, to help people get ahead on their planning and prepare their approach.
BN: What are the other options?
DS: You can look at whether you want to run your own infrastructure in future. For example, can you move to a cloud service or a hosted database offering instead? What does that entail as a service, and is it right for you?
Cloud services are great for developers and IT professionals that don’t have specific DBA skills in place, or where managing databases is one of multiple things that you have to bear in mind. Using a third party service can make this easier for you as it can take care of things like updates, backups and availability. However, not all cloud services are created equal, so it is worth looking at what you need compared to the services that are available.
There are some questions that you have to look at around security, around service levels, and also compatibility with open source versions of your database of choice. You don't want to trade up and accidentally lock yourself into a specific provider by accident!