2.11.3 What the MySQL Upgrade Process Upgrades

The mysql system schema, which contains tables that store information required by the MySQL server as it runs (see Section 5.3, “The mysql System Schema”). mysql schema tables fall into two broad categories:

Other schemas, some of which are built in and may be considered “owned” by the server, and others which are not:

The data dictionary version. This applies to the data dictionary tables.

The server version, also known as the MySQL version. This applies to the system tables and objects in other schemas.

Step 1: Data dictionary upgrade.

This step upgrades:

Step 2: Server upgrade.

This step comprises all other upgrade tasks. If the server version of the existing MySQL installation is lower than that of the new installed MySQL version, everything else must be upgraded:

Prior to MySQL 8.0.16, mysql_upgrade upgrades the Performance Schema, the INFORMATION_SCHEMA, and the objects described in step 2. The DBA is expected to invoke mysql_upgrade manually after starting the server.

As of MySQL 8.0.16, the server performs all tasks previously handled by mysql_upgrade. Although upgrading remains a two-step operation, the server performs them both, resulting in a simpler process.

With no option or with --upgrade=AUTO, the server upgrades anything it determines to be out of date (steps 1 and 2).

With --upgrade=NONE, the server upgrades nothing (skips steps 1 and 2), but also exits with an error if the data dictionary must be upgraded. It is not possible to run the server with an out-of-date data dictionary; the server insists on either upgrading it or exiting.

With --upgrade=MINIMAL, the server upgrades the data dictionary, the Performance Schema, and the INFORMATION_SCHEMA, if necessary (step 1). Note that following an upgrade with this option, Group Replication cannot be started, because system tables on which the replication internals depend are not updated, and reduced functionality might also be apparent in other areas.

With --upgrade=FORCE, the server upgrades the data dictionary, the Performance Schema, and the INFORMATION_SCHEMA, if necessary (step 1), and forces an upgrade of everything else (step 2). Expect server startup to take longer with this option because the server checks all objects in all schemas.

Perform a normal upgrade (steps 1 and 2 as necessary):

Perform only step 1 as necessary:

Perform step 1 as necessary, and force step 2:

Step 2 installs the sys schema if it is not installed, and upgrades it to the current version otherwise. An error occurs if a sys schema exists but has no version view, on the assumption that its absence indicates a user-created schema:

A sys schema exists with no sys.version view. If
you have a user created sys schema, this must be renamed for the
upgrade to succeed.

To upgrade in this case, remove or rename the existing sys schema first. Then perform the upgrade procedure again. (It may be necessary to force step 2.)

To prevent the sys schema check:

Step 2 processes all tables in all user schemas as necessary. Table checking might take a long time to complete. Each table is locked and therefore unavailable to other sessions while it is being processed. Check and repair operations can be time-consuming, particularly for large tables. Table checking uses the FOR UPGRADE option of the CHECK TABLE statement. For details about what this option entails, see Section 13.7.3.2, “CHECK TABLE Syntax”.

To prevent table checking:

To force table checking:

Step 2 saves the MySQL version number in a file named mysql_upgrade_info in the data directory.

To ignore the mysql_upgrade_info file and perform the check regardless:

Step 2 marks all checked and repaired tables with the current MySQL version number. This ensures that the next time upgrade checking occurs with the same version of the server, it can be determined whether there is any need to check or repair a given table again.

Step 2 upgrades the system tables to ensure that they have the current structure. This is true whether the server or mysql_upgrade performs the step. With respect to the content of the help tables and time zone tables, mysql_upgrade does not load either type of table, whereas the server loads the help tables, but not the time zone tables. (That is, prior to MySQL 8.0.16, the server loads the help tables only at data directory initialization time. As of MySQL 8.0.16, it loads the help tables at initialization and upgrade time.) The procedure for loading time zone tables is platform dependent and requires decision making by the DBA, so it cannot be done automatically.