MySQL 8.0.16开始 mysql_upgrade 升级程序已经废弃

朴灿烈づ我的快乐病毒、 2023-10-18 22:52 71阅读 0赞

##  ##

As of 8.0.16, the *mysql\_upgrade* binary is deprecated, but its functionality is moved into the server. Let’s call this functionality the “server upgrade”. This is added alongside the Data Dictionary upgrade (DD Upgrade), which is a process to update the data dictionary table definitions.

Why did we do that?

*  Faster upgrade
 *  Simpler process
 *  Better security
 *  Significant reduction in upgrade steps and more easily automated
 *  No restarts
 *  Just plug and play

**Before 8.0.16:**![aHR0cHM6Ly9teXNxbHNlcnZlcnRlYW0uY29tL3dwLWNvbnRlbnQvdXBsb2Fkcy8yMDE5LzA0L2ltZy0xLnBuZw][]

1.  Shutdown the server and install the new binaries
2.  Start the server and let the server upgrade the DD tables
3.  Run the *mysql\_upgrade* client to update some system tables and possibly user tables
4.  Download help table update scripts from the web
5.  Load the new help tables
6.  Restart the server and accept production traffic

**From MySQL 8.0.16 onwards:**![aHR0cHM6Ly9teXNxbHNlcnZlcnRlYW0uY29tL3dwLWNvbnRlbnQvdXBsb2Fkcy8yMDE5LzA0L2ltZy0yLnBuZw][]

1.  Shutdown the server and install the new binaries
2.  Start the server and let the server upgrade the DD tables and system tables. If user tables need to be upgraded, we now have the power of SQL at hand to analyze if any upgrade of user tables is needed. The help tables are also updated.

The time it takes to upgrade will be shorter, as you now do it in one operation instead of two. Additional time could be saved if the user tables do not require an upgrade.

Don’t want things to change?

A new server option *–upgrade* is provided. The possible values are: NONE, AUTO, MINIMAL, FORCE.

*  **NONE** makes sure that no upgrade is attempted. If an upgrade is required, the server aborts. Using this option will only allow restarting the same server version that we have already been running. Even if there are no differences between the old and new server, other than the version number, the new server version is not allowed to start.
 *  **AUTO** is the default value of this option that makes the server attempt both the Data Dictionary upgrade and the server upgrade.
 *  **MINIMAL** attempts only a Data Dictionary upgrade and not the server upgrade when starting the server. This state of the server would be exactly as the upgrades happened before 8.0.16. At this point, before 8.0.16, you would have to run the *mysql\_upgrade* client. Now you would restart the server without any upgrade option (or the options AUTO or FORCE) and the server upgrade is done.
 *  **FORCE** attempts a server upgrade, even if the server is already upgraded. This behavior is similar to the previous behavior of running the *mysql\_upgrade* client with the option *–force*.

So if you don’t want things to change, you could start the server with *–upgrade=MINIMAL*.

At every server startup, an upgrade is attempted if required, unless *–upgrade=NONE* or *–upgrade=MINIMAL* is specified.

So what did we really do?

We have tried to strengthen and streamline the process of upgrade. This is just the beginning. Let us refer to the tables/views/functions/procedures/routines/triggers present in MySQL as objects. Let us also refer to the objects necessary for MySQL to function correctly as system objects. MySQL Upgrade involves changes to these system objects. They reside in the *mysql* schema and the *sys* schema. MySQL Upgrade also changes the user schemas, if required.

According to the MySQL docs, the *mysql* schema is the system schema. It contains tables that store information required by the MySQL server as it runs. A broad categorization is that the *mysql* schema contains data dictionary tables that store object metadata, and system tables used for other operational purposes.

With this information, we can categorize the system objects into two types for upgrade:

*  System objects that belong in the transactional Data Dictionary are called DD objects
 *  The rest are server objects (including the sys schema objects)

The reason we categorize the system objects into two types is due to the two different versioning systems and infrastructures for creating and upgrading the objects. The DD objects and server objects have separate versioning systems. A change in the DD version specifies a change in the structure of DD schema. With every new server release, the server version changes. But the DD version may or may not change.

Combining the two versioning systems and the option *–upgrade=MINIMAL*, we create a special case. If you upgrade from MySQL version A to a compatible MySQL version B with *–upgrade=MINIMAL*, the Data Dictionary moves to version B and the server objects remain at version A. At this state, you cannot try to attempt an upgrade to a newer version, say, version C. If such an attempt is made, the server version C will refuse to start, stating that an upgrade is already pending.

In short, MySQL Upgrade now has 2 steps –

1.  Upgrade DD objects (This has not changed)
2.  Server Upgrade (previously the responsibility of *mysql\_upgrade*)
    
    1.  Upgrade server objects
    2.  Upgrade user schemas

Step 2 is the new change in MySQL 8.0.16, and is the entire process of “server upgrade”.

The conditions for a server upgrade

A server upgrade is not allowed to take place if the server is started with *–upgrade=MINIMIAL* or *–upgrade=NONE*.

Next, we look at the server version stored in the data dictionary. Let’s call this *server\_version\_a*. We compare this with the server version the MySQL binary was compiled with. Let’s call this *server\_version\_b*. For an upgrade to take place, an upgrade must be officially supported from *server\_version\_a* to *server\_version\_b*.

The final condition for a server upgrade requires any pending upgrades to be completed before attempting an upgrade to another newer version.

What the server upgrade involves

1.  **Upgrade the server objects in *mysql* schema**  
    This involves the execution of SQL queries to transform the system tables into the required definition. The statements are compiled into the server binary as strings and executed by the server.
2.  **Upgrade the server objects in *sys* schema**  
    We check the version of the *sys* schema and the number of server objects in the *sys*schema. If either is not as expected, *sys* schema is upgraded.
3.  **Upgrade all the user schemas**  
    If necessary, we run *CHECK TABLE <user tables> FOR UPGRADE*
4.  **Update the help tables**  
    Unconditionally update the contents of MySQL help tables (*help\_topic*, *help\_category*, *help\_keyword*, *help\_relation*)

The possible pitfalls

Each of the steps of MySQL upgrade is self-contained.

**Step 1 – DD upgrade** is an atomic operation. If this fails the data directory is rolled back to its original state and you can attempt the upgrade once more, after correcting the reason for the failure. You can find all you need to know about this step [here][] and [here][here 1].

**Step 2a – Upgrade server objects** consists of running SQL commands that are idempotent. If this step fails, we are at a state where the Data Dictionary is upgraded but the server objects are not completely upgraded.  
The cause of failure is likely to be one of the SQL queries executed in this step. The failing query and the reason for its failure will be in the error log. In most cases, we can restart the server with *–upgrade=MINIMAL*, fix the issue, then restart the server for the upgrade to resume.  
If you want to go back to the original state, restoring from a backup is your only option.

**Step 2b – Upgrade user schemas** is done by the server after **Step 2a**, if required, and cannot be skipped. They are either both done or both skipped, depending on the *–upgrade*option. This step is important if the user schemas are incompatible with the new MySQL version. Since this step involves running *CHECK TABLE* commands on each of the user tables, if this step fails for any reason, you can perform this step yourself when the server starts with the *mysqlcheck* utility.

Thanks for using MySQL!

[aHR0cHM6Ly9teXNxbHNlcnZlcnRlYW0uY29tL3dwLWNvbnRlbnQvdXBsb2Fkcy8yMDE5LzA0L2ltZy0xLnBuZw]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9teXNxbHNlcnZlcnRlYW0uY29tL3dwLWNvbnRlbnQvdXBsb2Fkcy8yMDE5LzA0L2ltZy0xLnBuZw
[aHR0cHM6Ly9teXNxbHNlcnZlcnRlYW0uY29tL3dwLWNvbnRlbnQvdXBsb2Fkcy8yMDE5LzA0L2ltZy0yLnBuZw]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9teXNxbHNlcnZlcnRlYW0uY29tL3dwLWNvbnRlbnQvdXBsb2Fkcy8yMDE5LzA0L2ltZy0yLnBuZw
[here]: https://mysqlserverteam.com/inplace-upgrade-from-mysql-5-7-to-mysql-8-0/
[here 1]: https://mysqlserverteam.com/upgrading-to-mysql-8-0-here-is-what-you-need-to-know/