Although MySQL 8 was released back in 2018, a significant share of MySQL servers out there are still running MySQL 5.x. MySQL 5 had a lengthy run from its release in 2005, and thus many organizations still have databases that were built on 5.x. But Oracle has been phasing out MySQL 5.7 support for various platforms over the past few years and end of life for MySQL 5.7 is slated for October 2023.
If you’re still running a database on MySQL 5.7, it’s time to seriously consider upgrading. You'll get several new features that give you performance improvements and security enhancements, so it is important that you do this soon — especially with the imminent end-of-life of MySQL 5.7, which means there will be no further security updates. Fortunately, this process is usually pretty straightforward, but there are several changes you may have to make. This article will cover many of the things that you should look out for when upgrading an existing database from MySQL 5.7 to 8 and walks you through the process of changing your database to be compatible with the new version.
Here's what we'll cover:
- Before you upgrade
- Character sets and collations
- Obsolete data types
- Authentication Changes
- New reserved words
- SQL mode changes
- C-style operators
- Server error codes
- Upgrading MySQL versions with no downtime
Before you upgrade, you should make sure that you have a backup of your database. Furthermore, you should ensure that your backup works. Many seasoned IT pros have not-so-fond memories of restoring a database from a backup only to find that it was corrupted or not backing up what they thought it was. If you are using a cloud service like Amazon RDS, you can use the automated backup feature to create a snapshot of your database. If you are running your own database server, you can use the
mysqldump command to create a backup of your database.
MySQL 8 has changed how character sets and collations work. The character set determines how characters are stored in the database, while the collation determines how characters are compared.
In previous versions of MySQL,
utf8 (with 3-byte characters) were commonly used. In MySQL 5.7, the default collation was
utf8mb4_general_ci. In MySQL 8, however, the default character set is
utf8mb4, and the default collation is
utf8mb4 is a more robust version of
utf8 that supports 4-byte characters. The
0900 in the collation name indicates that it is using the Unicode 9.0 standard. The
ai indicates that it is using accent-insensitive collation, and the
ci means that it is case-insensitive.
When upgrading to MySQL 8, it's a good idea to change your character set and collation to
utf8mb4_0900_ai_ci, respectively. This will ensure that your database is compatible with the new version of MySQL, and will allow your database to support more characters, such as emojis.
If you need a refresher on charsets and collations, you can check out our free video on strings in MySQL, which covers this topic in more detail.
Step 1: Change the default character set and collation for the database
To change the default character set and collation for the database, you can use the
ALTER DATABASE statement. For example, to change the default character set and collation for the
my_database database to
utf8mb4_0900_ai_ci respectively, you would use the following statement:
ALTER DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
Step 2 Change the character set and collation for each table
To change the character set and collation for each table, you can use the
ALTER TABLE statement. For example, to change the character set and collation for the
my_table table to
utf8mb4_0900_ai_ci respectively, you would use the following statement:
ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
While MySQL didn't remove any data types, there are a few that are no longer recommended for use.
Some of these are:
YEAR(2)— This stored a two-digit year. It is recommended to store year values as
YEAR(4), which uses four digits.
ENUM— You could create a field with a defined list of allowed values using an
ENUM. While still available in MySQL 8, it is no longer recommended. Instead, it is ideal to store enumerated values in a lookup table with foreign keys.
LONGTEXT— While these text types are available in MySQL 8, it is recommended to use
VARCHARwith a specified length, eg
TEXTfor long strings of text (eg longer than 255 characters) where you won't need to search for a specific substring.
CHARACTER SET, and
COLLATEclauses — While these clauses are still available in MySQL 8, they were made obsolete and are no longer recommended for use. Instead, the recommended approach for specifying character sets and collations is to use the
COLLATEoptions in the column definition or table definition.
MySQL 8 has changed how authentication works. The most significant change is that the default authentication plugin is now
caching_sha2_password instead of
mysql_native_password. This means that if you are using the default authentication plugin, you will need to update your connection strings to use the new plugin.
Legacy accounts that use the old authentication plugin must be converted to the new one using the
ALTER USER statement. It is also important to update any client applications that interact with the database to support the new authentication mechanism. Finally, thorough testing should be carried out to ensure that the database is functioning correctly with the new authentication plugin.
MySQL 8 has added a number of new reserved words. These are words that cannot be used as identifiers (e.g., table names, column names, etc). If you are using any of these words as identifiers, you will need to change them to something else or ensure that you are quoting them. For the full list, see the MySQL documentation new reserved words in MySQL 8. A few examples of these new reserved words are:
MySQL 8 has changed the default SQL mode, which has to do with the behavior of the server when evaluating queries. If you are using the default SQL mode, you will need to update your SQL statements to be compatible with the new mode.
In MySQL 8, the new default SQL mode is
ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION. This is stricter than the default mode in earlier versions of MySQL. For instance, it has more is more specific about how
GROUP BY statements are evaluated, and it will throw an error if you try to divide by zero.
Additionally, MySQL 8 has deprecated the
TRADITIONAL SQL modes, so you should remove those from your SQL statements if you are using them.
MySQL 8 has deprecated the use of the C-style
! operators. These operators are still available in MySQL 8, but they will be removed in a future version. It is recommended to update your SQL statements to use the standard
NOT operators instead.
MySQL 8 has changed the error codes for some server errors. If you are using these error codes in your application (for instance, to check for specific errors), you will need to update them to the new codes. For the full list of error codes, see the MySQL documentation on error codes.
The upgrade process should be pretty straightforward for most installations. However, it is very likely that you'll need some downtime to complete the upgrade process and any required schema changes for future-proofing your database. If you're using RDS, they state that database engine upgrades require downtime, and the downtime duration depends on the size of your database instance. Even using Blue/Green deployments requires some downtime, though it is less than the traditional route.
Fortunately, there is a way to do zero downtime MySQL upgrades.
PlanetScale offers a free import tool that allows you to import a live, production database with no downtime or data loss. We support MySQL versions 5.7 up through 8.0, so if you have a 5.7 database, you can import it through this process, and we will automatically do the no-downtime upgrade to 8.0 for you. You may be wondering how you'll migrate platforms without downtime, but don't worry! This migration process is also no downtime. After we copy your schema and data over, the PlanetScale database will essentially act as a replica to your production database, so we'll continue syncing any incoming data changes from your production database. For more information on the full process, read through our database import guide.
Once you're on PlanetScale, you won't have to worry about upgrading versions and dealing with downtime in the future. With our managed database service, your MySQL database will always be up to date.
We have platform-specific guides for migrations from Amazon RDS, DigitalOcean, GCP CloudSQL, and Azure:
We offer a generous free plan, as well as some upgraded plans to fit everybody's needs. Check out our pricing page for more information.
If you have a large or complex database and would like to hear about custom options or import assistance, we'd love to hear from you. Fill out the form and we'll be in touch shortly.
To recap, for most installations, upgrading to MySQL 8 should be a relatively straightforward process. However, it is important to test your database thoroughly after the upgrade to ensure that it is functioning correctly. Remember to ensure that you have working backups of your database before you start the upgrade process in case something goes wrong. During the process, it is ideal if you can ensure that you are using the utf8mb4 character set and utf8mb4_0900_ai_ci collation. This will ensure that your database is future-proofed for the next few years. Additionally, it is important to ensure that you are using the new authentication plugin and that you are not using any of the deprecated data types, reserved words, or SQL modes.
If you have any questions about upgrading to MySQL 8 or if you need help with the upgrade process, please feel free to contact us.