MySQL Upgrade Became Simpler in 8.0.16
MySQL server upgrading, the process of updating the MySQL server after the installation of a new database version, has been fully automated in MySQL 8.0.16.
Before
Before the 8.0.16 version, the upgrade process was much different. After a new database version was installed, the MySQL server automatically upgraded the data dictionary tables at the next server startup. And that was all the server did automatically. A user, or DBA had to run the mysql_upgrade to perform the following tasks:
- Upgrade the system tables in the mysql schema
- Upgrade the objects in the sys schema
- Upgrade the objects in the other schemas
After
Starting with MySQL 8.0.16, the upgrade process changed. After the installation of a new database version, the server automatically performs all the tasks listed previously, including upgrading the data dictionary tables. Additionally, the server updates the contents of the help tables, something the mysql_upgrade did not do. The upgrade process does not require a user or DBA to run the mysql_upgrade script.
The mysqld Command Options
The mysqld command is used to start a server by supplying custom options, one of the new options as of 8.0.16 being --upgrade. The --upgrade option can be used to control how the server performs an upgrade. The default setting of the --upgrade option is AUTO, and it performs the following upgrade tasks:
- Upgrades the data dictionary tables in the mysql schema. The server upgrades the data dictionary version if needed.
- Upgrades the Performance schema and the INFORMATION_SCHEMA.
- Upgrades the non-dictionary (system) tables in the mysql schema.
- Upgrades the sys schema.
- Upgrades the other schemas.
If the --upgrade option is set to NONE, the server performs no upgrade tasks during the next startup. If an upgrade is actually needed, the server does not start, and exits with an error message.
If the --upgrade option is set to MINIMAL, the server performs minimal upgrades during the next startup, which includes the following tasks:
- Upgrades the data dictionary tables in the mysql schema. The server upgrades the data dictionary version if needed.
- Upgrades the Performance schema and the INFORMATION_SCHEMA.
Note that the system tables in the mysql schema are not upgraded, which implies that the server cannot perform Group Replication because it depends on the system tables which are not upgraded.
An essential feature to note with the AUTO and MINIMAL option settings for --upgrade is that all the upgrade tasks are performed only if necessary.
The --upgrade option can be set to FORCE to perform the following upgrade tasks if necessary:
- Upgrades the data dictionary tables in the mysql schema. The server upgrades the data dictionary version if needed.
- Upgrades the Performance schema and the INFORMATION_SCHEMA.
Additionally, the following upgrade tasks are performed without first determining if they are necessary, which implies that these tasks are always performed with the FORCE setting.
- Upgrades the non-dictionary (system) tables in the mysql schema.
- Upgrades the sys schema.
- Upgrades the other schemas.
The FORCE option may be used if a user, or DBA, wants to repair the system tables as they may be damaged.
