blog
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 OKThis 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.
