MySQL 5.7.10升级到 8.0.23过程遇到的问题

最新推荐文章于 2024-04-04 11:23:02 发布

Demonson

最新推荐文章于 2024-04-04 11:23:02 发布

阅读量1.9k

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/demonson/article/details/126347610

版权

MySQL 专栏收录该内容

63 篇文章 3 订阅

订阅专栏

问题1：5.7和8.0新建表冲突，需要重命名

mysql.index_stats - Table name used in mysql schema in 8.0
mysql.table_stats - Table name used in mysql schema in 8.0

解决：
   RENAME  TABLE mysql.index_stats TO  mysql.index_stats_bak;
   RENAME  TABLE mysql.table_stats TO  mysql.table_stats_bak;

问题2：用户库问题

Error: Following tables show signs that either table datadir directory or frm
file was removed/corrupted. Please check server logs, examine datadir to
detect the issue and fix it before upgrade

geo - present in INFORMATION_SCHEMA''s INNODB_SYS_TABLES table but missing
from TABLES table

解决：
	导出geo库，重新创建geo,导入数据

问题3：升级过程出错

2021-03-09T12:39:52.658430Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2021-03-09T12:39:52.658557Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-03-09T12:39:53.830614Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-03-09T12:39:54.332232Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2021-03-09T12:39:56.597130Z 2 [ERROR] [MY-012066] [InnoDB] Column precision type mismatch(i.e NULLs, SIGNED/UNSIGNED etc) for col: last_update
2021-03-09T12:39:56.597440Z 2 [ERROR] [MY-012070] [InnoDB] Column last_update for table: `mysql`.`innodb_table_stats_backup57` mismatches with InnoDB Dictionary
2021-03-09T12:39:56.602670Z 2 [ERROR] [MY-012066] [InnoDB] Column precision type mismatch(i.e NULLs, SIGNED/UNSIGNED etc) for col: last_update
2021-03-09T12:39:56.602950Z 2 [ERROR] [MY-012070] [InnoDB] Column last_update for table: `mysql`.`innodb_index_stats_backup57` mismatches with InnoDB Dictionary
2021-03-09T12:39:56.972162Z 2 [ERROR] [MY-011060] [Server] Incorrect definition of table mysql.event: expected column 'definer' at position 3 to have type char(93), found type char(77)..
2021-03-09T12:39:56.981816Z 2 [ERROR] [MY-011060] [Server] Incorrect definition of table mysql.proc: expected column 'definer' at position 11 to have type char(93), found type char(77)..
2021-03-09T12:39:57.044415Z 2 [Warning] [MY-010200] [Server] Resolving dependency for the view 'e6managerdb.replication_connection_status' failed. View is no more valid to use
2021-03-09T12:40:00.534686Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2021-03-09T12:40:00.536573Z 2 [Warning] [MY-011991] [InnoDB] Table id in InnoDB is 1040 while the id in global DD is 18446744073709551615
2021-03-09T12:40:00.536892Z 2 [Warning] [MY-011988] [InnoDB] Index id in InnoDB is 18 while index id in global DD is 0
2021-03-09T12:40:00.537167Z 2 [Warning] [MY-011989] [InnoDB] Index root in InnoDB is 3 while index root in global DD is 0
2021-03-09T12:40:00.539293Z 2 [Warning] [MY-012382] [InnoDB] Cannot open table mysql/innodb_table_stats_backup57Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.
2021-03-09T12:40:00.541571Z 2 [Warning] [MY-011007] [Server] Error in creating TABLE statistics entry. Fix statistics data by using ANALYZE command.
2021-03-09T12:40:00.542815Z 2 [Warning] [MY-011991] [InnoDB] Table id in InnoDB is 1041 while the id in global DD is 18446744073709551615
2021-03-09T12:40:00.543122Z 2 [Warning] [MY-011988] [InnoDB] Index id in InnoDB is 19 while index id in global DD is 0
2021-03-09T12:40:00.543436Z 2 [Warning] [MY-011989] [InnoDB] Index root in InnoDB is 3 while index root in global DD is 0
2021-03-09T12:40:00.544524Z 2 [Warning] [MY-012382] [InnoDB] Cannot open table mysql/innodb_index_stats_backup57Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.
2021-03-09T12:40:00.544965Z 2 [Warning] [MY-011009] [Server] Error in creating Index statistics entry. Fix statistics data by using ANALYZE command.
2021-03-09T12:40:00.553066Z 2 [ERROR] [MY-013140] [Server] Table 'mysql.innodb_table_stats_backup57' doesn't exist
2021-03-09T12:40:00.555352Z 2 [ERROR] [MY-013140] [Server] Table 'mysql.innodb_index_stats_backup57' doesn't exist
2021-03-09T12:40:02.406144Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80023' started.
2021-03-09T12:40:03.842378Z 5 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement '# # SQL commands for creating the user in MySQL Server which can be used by the # internal server session service # Notes: 
# This user is disabled for login # This user has: # Select privileges into performance schema tables the mysql.user table. # SUPER, PERSIST_RO_VARIABLES_ADMIN, SYSTEM_VARIABLES_ADMIN, BACKUP_ADMIN, # CLONE_ADMIN, SHUTDOWN privileges 
# INSERT IGNORE INTO mysql.user VALUES ('localhost','mysql.session','N','N','N','N','N','N','N','Y','N','N','N','N','N','N','N','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'caching_sha2_password',
'$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED','N',CURRENT_TIMESTAMP,NULL,'Y', 'N', 'N', NULL, NULL, NULL, NULL); ' failed with error code = 1136, error message = 'Column count doesn't match value count at row 1''.
2021-03-09T12:40:03.844828Z 0 [ERROR] [MY-013380] [Server] Failed to upgrade server.
2021-03-09T12:40:03.845591Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-03-09T12:40:04.953585Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.23)  MySQL Community Server - GPL.

解决：

解决：
--mysql.user
ALTER TABLE  mysql.user DROP COLUMN is_role;
ALTER TABLE  mysql.user DROP COLUMN default_role;
ALTER TABLE  mysql.user DROP COLUMN max_statement_time;

ALTER TABLE  mysql.user ADD  `Create_role_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N';
ALTER TABLE  mysql.user ADD  `Drop_role_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N';
ALTER TABLE  mysql.user ADD  `Password_reuse_history` smallint unsigned DEFAULT NULL;
ALTER TABLE  mysql.user ADD  `Password_reuse_time` smallint unsigned DEFAULT NULL;
ALTER TABLE  mysql.user ADD  `Password_require_current` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL;
ALTER TABLE  mysql.user ADD  `User_attributes` json DEFAULT NULL;

修改配置文件：

vim /etc/my.cnf

[client]
port=4336
socket=/data/mysql/mysql.sock
default-character-set=utf8 --新增

[mysqld]

#slave-skip-errors=all

port = 3306
user=mysql
datadir=/data/mysql
log-error=/data/mysql/mysqld.log
pid-file=/data/mysql/mysqld.pid
socket=/data/mysql/mysql.sock
explicit_defaults_for_timestamp
slow_query_log=ON #开启慢查询日志
slow_query_log_file=/data/mysql/slow.log
long_query_time=1

innodb_file_per_table=1 #独立表空间
#innodb_file_format=barracuda #压缩表 --8.0不识别
innodb_flush_log_at_trx_commit=2 #提交方式

max_allowed_packet = 300M #接受的数据包大小用户mysqldump导入数据
character-set-server=utf8
collation-server=utf8_general_ci
init_connect='SET NAMES utf8'
# Disable password validaion plugin 
#validate-password=off
#event_scheduler=on

server-id=2 #每台机器不重复 
log_bin=mysql-bin   #bin日志路径
#expire_logs_days=5  #日志保存时间--8.0过期
binlog_expire_logs_seconds=432000  #日志保存5天 --新增
skip-name-resolve #跳过反向解析
default_authentication_plugin=mysql_native_password #使用历史密码认证方式 --新增
log_slave_updates=off #避免日志链式复制--新增
lower_case_table_names = 1 #忽略大小写
#skip-grant-tables

#innodb_force_recovery=0

升级成功！