MySQL升级5.7到8.0但密钥环保留未能完成升级

Question

我在RedHat7.9上运行mysql社区安装程序，安装5.7.36。

首先，我为所有dbs运行xtrabackup，然后停止mysqld，然后运行，好的，在尝试重新启动mysqld时，在错误日志中得到了下面的错误。

[System] [MY-013381] [Server] Server upgrade from '50700' to '80032' started.
[ERROR] [MY-013178] [Server] Execution of server-side SQL statement 'ALTER TABLE mysql.plugin TABLESPACE = mysql; ' failed with error code = 3825, error message = 'Request to create 'encrypted' table while using an 'unencrypted' tablespace.'.
[ERROR] [MY-013380] [Server] Failed to upgrade server.
[ERROR] [MY-010119] [Server] Aborting

我的数据库是用密钥环加密的。

my.cnf配置文件：

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]

early-plugin-load=keyring_file.so

sql_mode=""

bind-address = 0.0.0.0
datadir = /var/lib/mysql

innodb_file_per_table = ON
key_buffer_size = 16M
log-error = /var/log/mysqld.log
max_allowed_packet = 1024M
max_binlog_size = 1000M
max_connections = 150
port = 3306
skip-external-locking
socket = /var/lib/mysql/mysql.sock
tmpdir = /tmp
user = mysql

#server-id = 11211
#log-bin = mysql-bin

innodb_buffer_pool_size = 2560M
innodb_log_buffer_size = 16M

innodb_redo_log_capacity=2GB

[mysqldump]
max_allowed_packet = 1024M
host = localhost

检查/var/lib/mysql dir和文件以及/var/lib/mysql-keyring/keyring文件的权限。

Answer 1

解出

这个问题发生在mysql_upgrade步骤无法解密mysql.plugin表时，因此为了避免这种情况，我回滚到5.7，解密了所有MySQL db表，然后再次运行升级。

alter table engine_cost ENCRYPTION='N';
alter table gtid_executed ENCRYPTION='N';
alter table help_category ENCRYPTION='N';
alter table help_keyword ENCRYPTION='N';
alter table help_relation ENCRYPTION='N';
alter table help_topic ENCRYPTION='N';
alter table innodb_index_stats ENCRYPTION='N';
alter table innodb_table_stats ENCRYPTION='N';
alter table plugin ENCRYPTION='N';
alter table server_cost ENCRYPTION='N';
alter table servers ENCRYPTION='N';
alter table slave_master_info ENCRYPTION='N';
alter table slave_relay_log_info ENCRYPTION='N';
alter table slave_worker_info ENCRYPTION='N';
alter table time_zone ENCRYPTION='N';
alter table time_zone_leap_second ENCRYPTION='N';
alter table time_zone_name ENCRYPTION='N';
alter table time_zone_transition ENCRYPTION='N';
alter table time_zone_transition_type ENCRYPTION='N';

效果很好。