inplace mysql_MySQL8.0:In-place升级说明

1. 先完成检查事项

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 库

需要注意的是从 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.