Stumbling Blocks
Benjamin Franklin famously said, "... In this world nothing can be said to be certain, except death and taxes." From this quotation, we can determine that he was not, in fact, a system administrator; if he were, he'd have added "software upgrades" to that list.
MySQL is no exception to Benjamin Franklin's famous quote. After all, on October 21, 2023, MySQL 5.7 entered end-of-life (EOL) status. Consequently, no patches or updates will be available from official sources, and although MySQL 8.0 has been in General Availability (GA) status since 2018, many users have not yet upgraded. Since you're reading this article, you may very well be among them.
Fortunately, with some planning, you can take the sting out of this particular mandatory upgrade. I won't be able to discuss every breaking change or possible issue you might encounter, so be sure to follow the official MySQL instructions.
To begin, I'll discuss which systems can upgrade to MySQL 8.0 and what you can do if you aren't eligible.
Who Can Upgrade?
The upgrade to MySQL 8.0 or later is only possible from the 5.7 General Availability (GA) releases, not release candidates or other development releases. The earliest GA release is 5.7.9; versions of 5.7 earlier than that will have a suffix (e.g., "5.7.8 rc" for the release candidate or "5.7.1 m11" for milestone 11).
Likewise, upgrades from versions before the 5.7 major release are not supported. So, for example, if you have a MySQL 5.6 installation still running, you will first need to upgrade to MySQL 5.7. After that, you can upgrade an installation to 8.0.
Generally speaking, MySQL only officially supports upgrades across one major release, so if you want to upgrade from, say, 5.7 to 8.2, you'll first have to upgrade your MySQL 5.7 installation to MySQL 8.0, then 8.1, and finally 8.2. It's worth noting that MySQL 8.0 goes EOL in April 2026, which is not terribly far in the future. MySQL 8.0 is a long-term support (LTS) release, however, which means you won't get much additional time by upgrading past that – at least until 8.4 is released, which is scheduled to be the next LTS release.
In any event, minor upgrades such as from 8.2.0 to 8.2.1, can be done several at a time. If, for example, you're on an older version of MySQL 5.7, there's no need to upgrade to each minor version in between individually; you can skip minor versions as needed. Officially, you can upgrade from any GA release of MySQL 5.7 to 8.0, although some have preferred to upgrade to the latest MySQL 5.7 release before attempting an upgrade to 8.0.
It's also important to note that a major breaking upgrade, such as that from MySQL 5.7 to 8.0, is a good time to evaluate your choices in technology and vendors carefully. For example, if you're considering changing operating systems, architectural changes such as moving from on-premises to cloud or vice versa, and so on, now might be a good time to schedule that move. You're likely going to have to schedule significant testing and downtime, so there may be an efficiency benefit to doing both at once.
Likewise, if you're considering changing database technologies, either to an entirely different family such as PostgreSQL or to something closely related like MariaDB, the upgrade from 5.7 is a good time to do that.
Potential Upgrade Issues
One significant change involves correlations and encoding. The default collation and encoding for MySQL 5.7 was latin1, meaning that when a CREATE TABLE statement is run, it creates the new table with the latin1 character set and the latin1_swedish_ci collation. Many installations, however, are on UTF8 encoding, which is the new default on MySQL 8.0.
The default meaning of UTF8 is different under MySQL 5.7 and MySQL 8.0. On MySQL 5.7, utf8 is interpreted as utf8mb3, whereas under MySQL 8.0 it is utf8mb4. Although utf8 and utf8mb3 are largely compatible – with utf8mb4 supporting more characters – it is worth checking to see whether your applications use utf8mb3, utf8mb4, or latin1, and it might be a good time to standardize all of your collation and encoding settings. The utf8mb4 choice would be excellent for most situations. An unexpected shift from latin1 to utf8mb4 might cause data imports or other processes to fail, so setting the encoding specifically to utf8mb4 wherever possible may be a good move. Both MySQL 5.7 and 8.0 support all three encodings, so you can change your databases and tables to utf8mb4 ahead of time to avoid a surprise caused by a change in defaults.
If you want to keep the old defaults when you upgrade to MySQL 8.0, you can add the lines
[mysqld]
character_set_server=latin1
collation_server=latin1_swedish_cito the my.cnf file before you upgrade, because those settings are supported by MySQL 5.7, as well. Conversely, you can add the lines
[mysqld]
character_set_server=utf8
collation_server=utf8mb4_0900_ai_cito your MySQL 5.7 configuration file to make its behavior match that of 8.0.
Removed Temporal Data Types
Some data types, or at least some variants of data types, have been removed from the new version of MySQL. So-called old temporal data types are no longer supported. These data types only have second precision. Now, newly created tables (i.e., any tables created after MySQL 5.5) will automatically have fractional second precision; under normal circumstances, the 5.6 upgrade will automatically upgrade some types, but under some circumstances they can still exist.
The MySQL documentation helpfully provides these two queries to detect temporal columns of the old type:
SET show_old_temporals = ON;
SELECT table_schema, table_name,column_name,column_type FROM information_schema.columns WHERE column_type LIKE 'timestamp /* 5.5 binary format */'\GIf you find any, you can fix it with the command
ALTER TABLE <some_table> FORCE;which rebuilds the table, possibly taking a good deal of time. You can also dump the table as an SQL file, recreate it, and then reload from a backup, which should recreate the columns as new, more precise columns, as well.
Naming Issues
Some foreign key constraints from prior versions of MySQL can be incompatible because of excessive length. Specifically, a partially new constraint is that foreign key names cannot exceed 64 characters; in general, the limit was already 64 characters; however, in some cases, InnoDB would generate longer foreign key constraint names, typically as a result of long table names in non-English languages with multibyte characters.
In a similar vein, before MySQL 8.0, views could have column names up to 255 characters; however, to unify column name restrictions, explicit column names should only be 64 characters long. It's unlikely most installations will experience this problem, but if you do, the automated MySQL upgrade check scripts I discuss later should catch the issue.
As has been the case with prior major version upgrades, the number of reserved words has increased. Note that simply because a term is a reserved word does not mean you cannot have a column or table with that name. It simply means that to use that word you have to enclose it in backticks. Query generation tools such as ActiveRecord or SQLAlchemy automatically use backticks when appropriate; however, it may be wisest simply to avoid the use of such reserved words to eliminate the possibilities. Several new reserved words do have names that might be plausibly found as a column or table name (e.g., active and admin); the list of reserved words, which you can find in the MySQL documentation, is worth reviewing.
MySQL used to support ordering in the GROUP BY clause; MySQL 8.0 drops that support, so queries like
SELECT <...> FROM <table> GROUP BY <something> ASC;will need to be rewritten as:
SELECT <...> FROM <table> GROUP BY <something> ORDER BY <something> ASC;GRANT Statement Changes
In MySQL 8.0 the GRANT statement has much less functionality than it did before. Previously, it could create users if they didn't already exist, and it could alter user metadata. Now, GRANT statements can only be used as the name implies: to grant privileges to already created users. If you have administration scripts that create users with a GRANT statement, you should rewrite these to explicitly use CREATE USER statements to avoid issues. Likewise, other changes to a user's metadata can be made with the ALTER USER statement.
MySQL 5.7 has both of these statements already, so these changes can be made before the MySQL 8.0 upgrade process.
Authentication Methods Break Older Clients
A very significant change was made to the default authentication method in MySQL 8.0. MySQL has pluggable authentication methods, so you can use different methods for different installations. The default method for MySQL 5.7 is called mysql_native_password. The default in MySQL 8.0, however, is caching_sha2_password.
In some cases, this transition will be seamless. Pre-existing user accounts will not be automatically changed but will be updated to the new default when their passwords are changed. Newly created accounts will use the caching_sha2_password plugin.
Some older applications might not understand how to interact with the caching_sha2_plugin. Unfortunately, such applications might fail when connecting to a server with a default authentication method of caching_sha2_plugin, even when connecting to a not-yet-updated user.
Ideally, you would update such old applications. If this isn't an option, you can set the following option in my.cnf to re-enable the old plugin:
default-authentication-plugin=mysql_native_passwordNote that this option still might not fix authentication issues if your users had been created during the period of time when the caching_sha2_plugin was the default; you can manually adjust such users with statements such as
ALTER USER '<username>'@'<somehost>' IDENTIFIED WITH mysql_native_password BY '<a_secure_password>';On a related note, the PASSWORD() function is no longer available in MySQL 8.0. If you have scripts that create users or change user passwords, they will likely need to be rewritten, as is also the case with the GRANT changes. For example, the code
SET PASSWORD FOR 'tom'@'bob' = PASSWORD('test');can be rewritten as
ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY '<new_password>'Likewise, if you're using the PASSWORD function for other purposes, you'll need to rewrite the query. Note that MySQL 8.0 does include cryptographic functionality, such as the SHA2 function, which returns a SHA2 hash of its input. You can likely replace non-MySQL authentication-related uses of the PASSWORD function with that.
Upgrading Applications
A number of steps should be taken before upgrading MySQL from 5.7 to 8.0. You'll need to find out if your applications support 8.0. Some third-party applications might require upgrades. Fortunately, because MySQL 8.0 was released for general availability in 2018, most vendors should have long since updated their products – assuming, of course, that they haven't gone defunct. If you're regularly upgrading your third-party applications, you've likely already moved to a MySQL 8.0-compatible version.
If you have any applications or scripts you've developed in-house, they will have to be checked, as well. If you're using a continuous integration (CI) tool, such as Jenkins or CircleCI, it might be wise to run your automated tests twice – once with your legacy version of MySQL and once with 8.0 -- so you will be confident that your code works with MySQL and stays working with MySQL until you perform the upgrade.
Before beginning the backup process, it is likely wise to take both a logical or physical backup, or both, of your MySQL database and attempt upgrading in a non-production environment. If it's not practical to test the entire database in this way, then a subset thereof can be used. Although performance in a test or development environment won't be identical to production, you might be able to detect errors attributable to differences in the environments.
Related to that concept, a very helpful tool called pt-upgrade, a part of the Percona Toolkit, is freely available and designed to compare two different database servers. For example, say you have a backup of your production database loaded onto two test machines, test57 and test80. Furthermore, say you've downloaded some sample queries from your production machine's slow log into a file called prod-mysql-slow.log. You can then run pt-upgrade:
pt-upgrade h=test57 h=test80 prod-mysql-slow.logThis entry will run the commands in prod-mysql-slow.log on both servers, check for errors, check that both servers returned the same data, compare performance results, and more. If you get errors to queries on MySQL 8.0 but not 5.7, then you've likely been affected by a breaking change. The pt-upgrade tool can also run arbitrary queries from text files, from packet capture, and a lot more, and you can check the Percona Toolkit documentation for more details.
Note that pt-upgrade is designed to be run in test, not production, environments; it can't produce valid timing data if one or both servers are loaded, and if the data is changing while the process is running, the consistency checks will likely produce false positives.
Upgrading Official Tools
Once you've established that the applications you run are compatible with MySQL 8.0, it's time to inspect the database itself thoroughly with one or both of two official tools. The older tool is mysqlcheck:
mysqlcheck -u root -pmy-secret-pw --check-upgrade --all-databases
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
...The MySQL Shell checkForServerUpgrade command is the newer tool. The official MySQL documentation mostly references the latter tool, but many recommend running both for the sake of completeness.
The checkForServerUpgrade command is distinct from the much older mysql command-line tool and has to be installed separately. It does have a very similar purpose, but has considerably more features – notably including built-in JSON output for queries.
You can install the MySQL Shell with either the mysql-shell Yum or Apt packages, which require you to have the official MySQL repositories enabled, or by downloading from mysql.com. Unlike the traditional mysql command, it's not included by default with MySQL server (Figure 1). Once installed, you can use the server upgrade check:
mysqlsh -e 'util.checkForServerUpgrade()'You can also simply invoke the tool with the docker command:
docker run mysql:8 mysqlsh -e 'util.checkForServerUpgrade()'
In both cases, you can add authentication options as needed through options (e.g., -u and -p) similar to the old mysql client; you can also use the new uri parameter (Figure 2):
mysqlsh --uri=root:my-secret-pw@mysql57:3306 -e 'util.checkForServerUpgrade()'
In either case, you should see output like that shown in Listing 1. As you can see in this case, the old temporal date types discussed earlier are not present. The NO_AUTO_CREATE_USER sql_mode warning relates to the GRANT USER changes mentioned earlier in the article. Because GRANT USER is no longer allowed to create users, the NO_AUTO_CREATE_USER sql_mode relating to when GRANT USERS is and is not allowed to create users has been removed. Therefore, the above-mentioned objects will need to be rewritten.
| Listing 1: mysqlsh Output |
|
Once the output of both tools is scrutinized and issues found are corrected, you can proceed to the upgrade. Immediately before upgrading, it is wise to take both a logical and physical backup, with the use of tools such as mydumper or mysqldump for the logical backup and a tool such as xtrabackup or another copying tool for the physical backup (Figure 3).
Typically, as with all upgrades, backups will be scheduled for a period of low traffic, and, depending on your situation, a maintenance window is announced. Although in theory the process can be, as in the words of Oracle's website, "seamless," it is instead wiser to plan for a somewhat "seamful" experience instead.
In-Place and Upgrade Options
The two main upgrade paths are in-place and with the upgrade command. The in-place upgrade is likely the first method most admins consider. This path involves stopping the old server process, replacing it with a new server binary, and then starting the new binary. If you've installed MySQL manually, you can keep both binaries, but realistically, most systems have MySQL installed through a package manager, so I'll discuss that in a bit more detail.
For example, on a Debian-based system with the official MySQL repositories, the routine would be
sudo apt-get update
sudo dpkg-reconfigure mysql-apt-config
sudo apt-get updateThe second command prompts you to select a MySQL major version; after selecting 8.0 you then need to run the update command again. To stop the MySQL process, enter
sudo systemctl stop mysqlIf you haven't already, now is a reasonable time to make the physical backup, because you can do so without the server running by simply copying the MySQL data directory.
After that, you can now commence the upgrade process:
sudo apt-get install mysql-serverIf you've decided to make any changes to configuration files (e.g., changing the default auth plugin, as mentioned earlier, or changing the default character set and collation) now is a good time to do so.
Now you can start the server:
sudo systemctl start mysqlAt this point, the MySQL server should automatically upgrade your tables. Unlike earlier versions of MySQL, you do not have to run the separate mysql_upgrade tool.
Taking an additional set of backups at this point is good practice. In the case of some unusual behavior, it will allow you to compare pre-upgrade and post-upgrade backups. Although you likely won't need to do this, if such information turns out to be valuable, you won't be able to get it any other way.
If you're running a Debian-based system, but not from the official repository, the official recommendation is to change to using the repository before the upgrade. Documentation on how to do that is available on the MySQL website.
On dnf-based systems, instead of apt-get, you can use
sudo systemctl stop mysql-server
sudo dnf config-manager --disable mysql57-community
sudo dnf config-manager --enable mysql80-community
sudo dnf upgrade mysql-server
sudo systemctl start mysql-serverAs before, I recommend manually stopping the process with systemctl and taking a physical backup, as well as adjusting settings before issuing the systemctl start command.
MySQL Logical Upgrade to a New Host
The process for a logical upgrade of a MySQL server is quite straightforward and entails dumping the server with a tool such as mysqldump and then restoring it. In this article, I use mysqldump, but it's worth noting that you can get a speed boost from tools such as mydumper that use parallel backup and loading. Although not an officially supported approach, some have been able to upgrade directly from much older versions of MySQL directly to 8.0 in this way. If you choose this path, definitely test it thoroughly ahead of time.
For the sake of this example, assume you are using two instances – VMs, physical machines, or containers, it doesn't matter. One server will be the pre-existing MySQL 5.7 server, and the other will be your new MySQL 8.0 server. Technically, you can do this process on the same machine, but it's best to use separate instances if possible to make handling difficulties easier, as you will see.
Typically, you first take a backup from the old server by running the command
mysqldump -u root -p --all-databases | gzip > backup.sql
sudo systemctl mysql-server stopStrictly speaking, the second command is not necessary but can be helpful to ensure no traffic is incorrectly routed to the old machine. Next, install a fresh copy of MySQL server 8.0, transfer the backup.sql file you made earlier, then run the commands
sudo systemctl mysql-server start
gunzip < backup.sql.gz | mysql -u root -pIf all goes well, this backup will be restored seamlessly. If not, for smaller databases, you can manually edit offending statements by running
gunzip backup.sql.gzand then editing the file with a text editor. For larger databases, you can simply restart the original MySQL server on the original host, adjust or remove any offending features, and then re-dump and import – repeating the process as necessary until the restore succeeds.
In many cases, however, this procedure will go smoothly, and you can then switch traffic to your newly minted MySQL 8.0 server.
Replication Topologies
In this article, I've discussed upgrading a server from MySQL 5.7 to 8.0. Of course, many organizations have long outgrown a single server and use a cluster or clusters of MySQL servers.
You can employ a similar process to handle any upgrade of any MySQL topology, in which you test applications, test the database, backup, upgrade, backup, and then resume processing transactions. Of course, this process becomes more complex to manage with more machines.
Note that a MySQL 8.0 machine can replicate from a 5.7 machine, but not vice versa. You can, for example, add a new 8.0 replica to an existing 5.7 source to verify correctness – perhaps through the use of the pt-upgrade tool mentioned earlier.
Similarly, you can upgrade your 5.7 replicas one at a time before taking down your 5.7 source and upgrading that. In this way, you can reduce downtime because your source – and therefore your cluster – only needs to be down for a relatively brief period of time.
Finally, note that cloud products like Amazon Relational Database Service (RDS) and Google Cloud SQL typically have their own routines for handling the 5.7 to 8.0 upgrade; under the hood, it will likely be similar to the approaches discussed here, but the interface and procedures used will vary, so you should follow the procedures outlined in the vendor documentation.
Conclusion
Generally, the changes implemented in MySQL 8.0 are for the best (e.g., utf8mb4 is a better default than latin1); nevertheless, they could pose significant obstacles for the unwary. If you aren't prepared for an upgrade now, the other option is to team up with one of the vendors offering extended MySQL 5.7 support beyond the official EOL date.
Fortunately, although it might be rather difficult to avoid death and taxes, an ill-planned MySQL upgrade is one thing you can certainly avoid. With a little foresight, you can confidently and successfully pull off your upgrade.
This article was made possible by support from Percona LLC, through Linux New Media's Topic Subsidy Program (https://www.linuxnewmedia.com/Topic_Subsidy).