1. 先完成检查事项
升级之前先进行初步检查，比如有没有InnoDB、NDB之外的表使用了分区，详见：https://dev.mysql.com/doc/refman/8.0/en/upgrade-prerequisites.html
2. 设置 innodb_fast_shutdown，关闭 MySQL server
SET GLOBAL innodb_fast_shutdown = 0;
mysqladmin -u root -p shutdown
3. 修改 my.cnf 中不兼容的参数，使用 MySQL8.0 启动数据库：
./bin/mysqld_safe --defaults-file=/opt/mysql/etc/3306/my.cnf --user=mysql &
这一过程升级 mysql 库下的数据字典，如果实际数据字典版本低于当前预期版本，则将创建新的数据字典表，将持久化的元数据复制到新表中，用新表原子替换旧表，然后重新初始化数据字典。启动后可以看到 frm 文件都没有了。
error log 如下：
2019-11-13T16:58:26.526032+08:00 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2019-11-13T16:58:26.526153+08:00 0 [System] [MY-010116] [Server] /opt/mysql_8.0.15/bin/mysqld (mysqld 8.0.15) starting as process 6804
2019-11-13T16:58:29.638457+08:00 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-11-13T16:58:29.746799+08:00 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001146 - Table 'mysql.component' doesn't exist
2019-11-13T16:58:29.746819+08:00 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-003543 - The mysql.component table is missing or has an incorrect definition.
2019-11-13T16:58:29.751870+08:00 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.user].
2019-11-13T16:58:29.751881+08:00 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.db].
2019-11-13T16:58:29.751889+08:00 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.tables_priv].
2019-11-13T16:58:29.751897+08:00 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.columns_priv].
2019-11-13T16:58:29.751904+08:00 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.procs_priv].
2019-11-13T16:58:29.751917+08:00 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.proxies_priv].
2019-11-13T16:58:29.752275+08:00 0 [ERROR] [MY-013143] [Server] Column count of mysql.user is wrong. Expected 51, found 45\. The table is probably corrupted
2019-11-13T16:58:29.752311+08:00 0 [Warning] [MY-010966] [Server] ACL table mysql.role_edges missing. Some operations may fail.
2019-11-13T16:58:29.752319+08:00 0 [Warning] [MY-010966] [Server] ACL table mysql.default_roles missing. Some operations may fail.
2019-11-13T16:58:29.752327+08:00 0 [Warning] [MY-010966] [Server] ACL table mysql.global_grants missing. Some operations may fail.
2019-11-13T16:58:29.752334+08:00 0 [Warning] [MY-010966] [Server] ACL table mysql.password_history missing. Some operations may fail.
2019-11-13T16:58:29.752809+08:00 0 [ERROR] [MY-010965] [Server] Missing system table mysql.global_grants; please run mysql_upgrade to create it.
2019-11-13T16:58:29.757984+08:00 0 [Warning] [MY-010727] [Server] System table 'func' is expected to be transactional.
2019-11-13T16:58:29.760950+08:00 0 [Warning] [MY-010405] [Repl] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2019-11-13T16:58:29.760993+08:00 0 [ERROR] [MY-010422] [Repl] Error in checking mysql.slave_master_info repository info type of TABLE.
2019-11-13T16:58:29.761006+08:00 0 [ERROR] [MY-010415] [Repl] Error creating master info: Error checking repositories.
2019-11-13T16:58:29.761015+08:00 0 [ERROR] [MY-010426] [Repl] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.
2019-11-13T16:58:29.761049+08:00 0 [ERROR] [MY-010529] [Repl] Failed to create or recover replication info repositories.
2019-11-13T16:58:29.765839+08:00 0 [System] [MY-010931] [Server] /opt/mysql_8.0.15/bin/mysqld: ready for connections. Version: '8.0.15' socket: '/opt/mysql/data/3306/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2019-11-13T16:58:29.971062+08:00 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060
4. 升级 MySQL server
MySQL 升级分两部分：数据字典和 MySQL server。第3步已经升级过数据字典了，升级 MySQL server 的主要内容是：
- mysql 库下其余的非数据字典表
- sys 库
如果你对这里有疑惑，可以查看另一篇文章中关于数据字典变化的描述：https://www.jianshu.com/p/8631e89abfc2
需要注意的是从 MySQL 8.0.16 开始，数据字典的升级和 MySQL server 的升级会在 mysqld 启动时一起完成。在第3步启动 mysqld 时错误日志显示如下：
2019-11-13T17:08:28.671583+08:00 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2019-11-13T17:08:28.671673+08:00 0 [System] [MY-010116] [Server] /opt/mysql_8.0.17/bin/mysqld (mysqld 8.0.17) starting as process 13168
2019-11-13T17:08:28.696727+08:00 1 [System] [MY-011012] [Server] Starting upgrade of data directory. 100 100
2019-11-13T17:08:31.147908+08:00 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2019-11-13T17:08:31.572741+08:00 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80017' started.
2019-11-13T17:08:35.128086+08:00 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80017' completed.
2019-11-13T17:08:35.806244+08:00 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-11-13T17:08:36.009989+08:00 0 [System] [MY-010931] [Server] /opt/mysql_8.0.17/bin/mysqld: ready for connections. Version: '8.0.17' socket: '/opt/mysql/data/3306/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2019-11-13T17:08:36.138161+08:00 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060
如果是 MySQL 8.0.16 之前的版本，则需要使用 mysqld_upgrade 程序进行升级：
mysql_upgrade -u root -p
并重启 MySQL server 使得升级生效：
mysqladmin -u root -p shutdown ./bin/mysqld_safe --defaults-file=/opt/mysql/etc/3306/my.cnf --user=mysql &
升级过程日志会输出在屏幕上：
Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Upgrading system table data. Checking system database.
mysql.columns_priv OK
mysql.component OK
mysql.db OK
mysql.default_roles OK
mysql.engine_cost OK
mysql.func OK
mysql.general_log OK
mysql.global_grants OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.password_history OK
mysql.plugin OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.role_edges OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Found outdated sys schema version 1.5.1. Upgrading the sys schema. Checking databases.
sys.sys_config OK
universe.u_delay OK
Upgrade process completed successfully. Checking if update is needed.
网友评论