How to Upgrade from MySQL 5.7 to 8.0

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
- How to upgrade your database to the utf8mb4 character set and utf8mb4_0900_ai_ci collation
Obsolete data types
Authentication Changes
New reserved words
SQL mode changes
C-style operators
Server error codes
Upgrading MySQL versions with no downtime
- Performing the no-downtime upgrade
Conclusion

Before you upgrade

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.

Character sets and collations

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, latin1 and 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_0900_ai_ci . 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 and 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.

How to upgrade your database to the `utf8mb4` character set and `utf8mb4_0900_ai_ci` collation

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 and utf8mb4_0900_ai_ci respectively, you would use the following statement:

SQL

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 and utf8mb4_0900_ai_ci respectively, you would use the following statement:

SQL

ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Obsolete data types

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.
TINYTEXT , MEDIUMTEXT , and LONGTEXT — While these text types are available in MySQL 8, it is recommended to use VARCHAR with a specified length, eg VARCHAR(255) or TEXT for long strings of text (eg longer than 255 characters) where you won't need to search for a specific substring.
NATIONAL , CHARACTER SET , and COLLATE clauses — 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 CHARACTER SET and COLLATE options in the column definition or table definition.

Authentication Changes

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.

New reserved words

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:

ACTIVE
ADMIN
ATTRIBUTE
COMPONENT
DEFINITION
DESCRIPTION
EMPTY
EXCLUDE
FINISH
GROUPS
INACTIVE
INITIAL
LEAD
LOCKED
MEMBER
NESTED
OFF
OLD
ORGANIZATION
OTHERS
OVER
PATH
PROCESS
RANDOM
RANK
RESOURCE
RETURNING
REUSE
ROLE
SKIP
SRID
STREAM
SYSTEM
TIES
URL
VISIBLE
ZONE

SQL mode changes

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 ONLY_FULL_GROUP_BY , STRICT_TRANS_TABLES , STRICT_ALL_TABLES , and TRADITIONAL SQL modes, so you should remove those from your SQL statements if you are using them.

C-style operators

MySQL 8 has deprecated the use of the C-style && , || , and ! 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 AND , OR , and NOT operators instead.

Server error codes

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 .

Upgrading MySQL versions with no downtime

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.

Performing the no-downtime upgrade

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.

Additionally, our product includes a no-downtime schema change workflow using database branching and deploy requests .

Note

Be sure to reference our MySQL compatibility documentation before beginning the import. There are some things you may need to change before the import that we don't support from 5.7, such as reserved keywords, but most 5.7 databases shouldn't cause any issues. Reach out to our Support team for additional guidance.

We have platform-specific guides for migrations from Amazon RDS, DigitalOcean, GCP CloudSQL, and Azure:

We offer 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.

Conclusion

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 .