The MySQL 5.7 End of Life is now two months away. Those of you who have waited may be wondering which is the best way to upgrade – an In-Place or Logical upgrade. An In-Place upgrade is where the binaries for 5.7 are replaced with the binaries for 8.0, and the upgrade is made to an existing instance. The Logical option is a new server that is loaded with the old data from a backup.
Generally, the In-Place upgrade will be faster as you do not have to perform a backup on the old instance and then run a restore on the new platform. Those with very large instances should choose this option. And remember you should be using the four-byte UTF8MB4 character set, which most likely means you will be using more disk space, so arrange for the extra space BEFORE starting the upgrade.
Logical upgrades seem cleaner for many, and it is often possible to run the old and new instances side by side for comparisons. On rare occasions, there will be incompatibilities that will require editing of the SQL in the backup. This is my favorite as it allows you to run both versions and, hopefully, make a clean cut-over to the new system. Disk space is very cheap these days, and the ability to do A/B testing between the two versions helps reassure that all is ready to go before going into production with the new version.
MySQL upgrades are a“one-way door”
Before choosing either, make sure you have backups – plural – as the 5.7 to 8.0 upgrade is one way only. If something goes wrong, there is no downgrade process save for a full restore of data and binaries from that backup. And by backups, I mean multiple copies in multiple formats – Percona XtraBackup, mysqldump, and a logical disk snapshot at a minimum, storing those backups off the main server.
And if you are performing a Logical update and moving one database at a time, be sure to make sure you grab a copy the mysql.user table. It is easy to get lost moving the production databases and forget about your accounts and roles stored in this file. No matter what choice you make between In-Place or Logical upgrades, remember that you are going to a more feature-filled version of MySQL. And if you are still not ready to upgrade, please remember that Percona is here to help you.
No mention of replication?
Create a 5.7 replica, upgrade it to 8.0. Check read queries work. Move over to writing to the 8.0 master. This gives downtime of a few seconds. I would not consider anything else.