How to Perform Rolling Upgrades for MySQL

There are different reasons for upgrading your databases. It could be to apply security fixes, to use new features, solve compatibility issues, or just to keep your system up-to-date. This upgrade can be a major upgrade or a minor one, and depending on the technology, there are different approaches to do this job, but if you need your systems running all the time with zero downtime, performing a rolling upgrade could be the best option.

In this blog, we will see some considerations to take into account before upgrading and how to perform a rolling upgrade on MySQL.

Minor vs Major Upgrades

In general, minor upgrades are safe in the way that you can easily downgrade or rollback it and should be compatible with the previous packages and features.

Major version upgrade involves some risks like database package removal, configuration and connectors compatibility, deprecated features, and more.

So, even when testing is important for both scenarios, in case of major upgrades, it is a must if you want to avoid serious problems for your business.

Before Upgrading

Now, let’s see some considerations before upgrading, to avoid future issues.

Backups

Backups are always important, and even more if you want to upgrade your database. If something goes wrong, and other disaster recovery options fail, you will need a backup to restore your database. So, before starting the task, take a full backup (physical or/and logical) of your current database and keep it safe until make sure everything is working properly for a couple of days/weeks.

Review Deprecated Features

If you are using a feature that is deprecated in the new version, your application could fail, and you will need to rollback to recover your systems, which will generate downtime (depends on the approach) and a loss of time. Checking the deprecated feature and comparing them to the ones that you are using will avoid this failed upgrade attempt.

Testing

This is important not only for upgrades but also for any change in your database or application. Having a test environment that replicates the production environment could save your time and avoid unexpected issues during any upgrade or database change.

Rollback

In any upgrade, it is important to have a rollback ready to be used if needed to have your database up and running ASAP. Otherwise, it could affect your RTO (Recovery Time Objective) in case you need to recreate the cluster from a backup or another recovery plan option.

Keep in mind that sometimes downgrades are not possible, so you should have a secondary plan in case you need to rollback your changes.

Vendor Checks

Depending on the vendor and version, you may use the mysqlcheck command to perform the preliminary check on your current installation and confirm that you are ready to go.

$ mysqlcheck -u root -p --all-databases --check-upgrade

Enter password:

mysql.columns_priv                                 OK

mysql.db                                           OK

mysql.engine_cost                                  OK

mysql.event                                        OK

mysql.func                                         OK

mysql.general_log                                  OK

mysql.gtid_executed                                OK

mysql.help_category                                OK

mysql.help_keyword                                 OK

mysql.help_relation                                OK

mysql.help_topic                                   OK

mysql.innodb_index_stats                           OK

mysql.innodb_table_stats                           OK

mysql.ndb_binlog_index                             OK

mysql.plugin                                       OK

mysql.proc                                         OK

mysql.procs_priv                                   OK

mysql.proxies_priv                                 OK

mysql.server_cost                                  OK

mysql.servers                                      OK

mysql.slave_master_info                            OK

mysql.slave_relay_log_info                         OK

mysql.slave_worker_info                            OK

mysql.slow_log                                     OK

mysql.tables_priv                                  OK

mysql.time_zone                                    OK

mysql.time_zone_leap_second                        OK

mysql.time_zone_name                               OK

mysql.time_zone_transition                         OK

mysql.time_zone_transition_type                    OK

mysql.user                                         OK

sys.sys_config                                     OK

This is a first check to perform before upgrading, and it will check that there is not:

• Tables using obsolete data types or functions
• Orphan frm files
• Triggers with missing or empty definer or an invalid creation context

There are a few more things you need to check but to avoid an extensive blog post, you can refer to the official MySQL documentation for this.

Manual Rolling Upgrades for MySQL

There are different approaches to perform a rolling upgrade. It could be in place, using replication, or even a mix of them. In any case, if you want to avoid downtime you shouldn’t perform changes in your application during the upgrade. For this, you can add a Load Balancer in front of your databases. Your application will connect to your Load Balancer, and it will redirect the traffic to the available nodes.

Let’s say you have a MySQL Replication with 1 master and 2 slave nodes, and 1 HAProxy node in front of them:

A simplified way to perform a manual rolling upgrade on this environment could be:

• Disable one slave node from your HAProxy
• Make sure you don’t have traffic on this slave node
• Upgrade the slave node manually
• Check the replication status to make sure is up-to-date
• Disable the master node in your HAProxy
• Make sure you don’t have traffic on your master node
• Promote the upgraded slave node
• Enable it in your HAProxy
• Confirm that the new master is receiving traffic
• Reconfigure your second slave to replicate from the new master
• Disable the second slave from your HAProxy
• Make sure you don’t have traffic on this slave node
• Upgrade the slave node manually
• Check the replication status to make sure is up-to-date
• Enable it in your HAProxy
• Confirm that the slave node is receiving traffic (if needed)
• Reconfigure your old master to replicate from the new master
• Upgrade the old master node manually
• Check the replication status to make sure is up-to-date
• Enable it in your HAProxy
• Confirm that the old master (now slave) is receiving traffic (if needed)

As you can see, even in a simplified way, this task requires many steps, and it means more possibilities of something going wrong.

ClusterControl Rolling Upgrades for MySQL

The best way to reduce the possibility of failure is by automating all (or almost all) these steps. Using ClusterControl you can perform a minor rolling upgrade of your MySQL cluster with just a few clicks.

To do this, go to ClusterControl -> Select Cluster -> Manage -> Upgrades, where you see the Upgrade option.

Upgrades are online and are performed on one node at a time. The node will be stopped, the software will be updated, and then the node will be started again. If a node fails to upgrade, the process is aborted.

If you choose the Upgrade option, you will see a confirmation about the version that will be upgraded:

And you just need to press on Upgrade to confirm this job. After this, you can monitor the upgrade process in the ClusterControl Activity Section:

At the same time, ClusterControl will reconfigure your Load Balancers to send the traffic to the available nodes.

ClusterControl only supports minor upgrades, because, as we mentioned before, a major upgrade is a risky task that requires testing and research to make sure that your application will work fine on the new major version.

Conclusion

Upgrading is an important task in all companies and it could be risky if you don’t take certain precautions and follow all the upgrade steps correctly.

In this blog, we mentioned some considerations to take into account before upgrading your database, and we showed the difference between doing this task manually and using ClusterControl, which helps you to minimize the possibility of failure.