How mysql_upgrade changed with MySQL 8.0.16 ?
To upgrade systems tables in the mysql schema, as well as objects in other schemas such as the sys schema and user schema MySQL DBA is expected to invoke mysql_upgrade manually after the installation of a new version of MySQL. Because, MySQL Server automatically upgrades the data dictionary tables at next startup. As of MySQL 8.0.16, The server now automatically performs all tasks necessary for the upgrade for the next startup without depending on MySQL DBA. MySQL 8.0.16 has new system variable / server option –upgrade which provides control over how the server performs automatic data dictionary and server upgrade operations.
MySQL upgrade happens in two steps
- Step 1: Data dictionary upgrade:
- Upgrades data dictionary tables in the mysql schema, MySQL server creates data dictionary tables with updated definitions by automatically replacing old tables with new ones and reinitializes the data dictionary.
- Upgrades metadata tables in Performance_Schema, Information_Schema and ndbinfo
- Step 2: MySQL server upgrade
- System tables in mysql schema, this include non-data dictionary tables.
- The sys schema
- User schemas
Note – As of MySQL 8.0.16, the server performs all tasks seriously handled by mysql_upgrade, This include both steps explained above.
As of MySQL 8.0.16, the –upgrade server option controls how MySQL server performs an automatic upgrade during startup:
- The server option –upgrade=AUTO, MySQL server upgrades anything which it determines to be out of date. This is default value, It completes Bothe steps mentioned above
- The server option –upgrade=NONE, In this setting MySQL upgrades nothing ( skips both step1 and step2 ). Technically, It’s not possible to run a MySQL server with out-of-date data dictionary so if the data dictionary has to be upgraded, the server exits with an error data dictionary must be upgraded
- The server option –upgrade=MINIMAL, The MySQL server upgrades data dictionary, Performance Schema and INFORMATION_SCHEMA. Group Replication cannot be started, because system tables on which replication internals depend are not updated, and you may also experience some of the functionalities not compatible.
- The server option –upgrade=FORCE, The MySQL server completes both step 1 and step 2. Now, How then FORCE is different from AUTO ? If you have configured option –upgrade=FORCE, then server also re-creates systems tables such as help tables or time zone tables if they are missing.
Conclusion
In the past we have come across multiple instances where running mysql_upgrade process was missed from our customer accidentally or by mistake leading to serious database reliability concerns, Thanks to MySQL 8.0.16 engineering team for adding this feature which makes MySQL upgrades more seamless
☛ Looking forward to engaging MinervaDB for MySQL upgrades and migration ?
| Business Function | Contact |
|---|---|
| ☎ CONTACT GLOBAL SALES (24*7) | 📞 (844) 588-7287 (USA) 📞 (415) 212-6625 (USA) 📞 (778) 770-5251 (Canada) |
| ☎ TOLL FREE PHONE (24*7) | 📞 (844) 588-7287 |
| 🚩 MINERVADB FAX | +1 (209) 314-2364 |
| 📨 MinervaDB Email - General / Sales / Consulting | contact@minervadb.com |
| 📨 MinervaDB Email - Support | support@minervadb.com |
| 📨 MinervaDB Email -Remote DBA | remotedba@minervadb.com |
| 📨 Shiv Iyer Email - Founder and Principal | shiv@minervadb.com |
| 🏠 CORPORATE ADDRESS: CALIFORNIA | MinervaDB Inc. 440 N BARRANCA AVE #9718 COVINA, CA 91723 |
| 🏠 CORPORATE ADDRESS: DELAWARE | MinervaDB Inc., PO Box 2093 PHILADELPHIA PIKE #3339 CLAYMONT, DE 19703 |
| 🏠 CORPORATE ADDRESS: HOUSTON | MinervaDB Inc., 1321 Upland Dr. PMB 19322, Houston, TX, 77043, US |