记录一下Centos7 安装mysql8遇到的问题_data dictionary upgrade from mysql 5.7 in progress

最近想试一下mysql8，所以在阿里云上安装了一下，结果遇到了各种问题

安装完成后，启动

systemctl start mysqld.service;

结果报错，我们查看一下状态

 
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Tue 2020-10-06 00:45:27 CST; 6min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 6057 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)
  Process: 6034 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 6057 (code=exited, status=1/FAILURE)
   Status: "Data Dictionary upgrade from MySQL 5.7 in progress"
    Error: 2 (No such file or directory)
 
Oct 06 00:45:26 iZm5ed06iqge5x2i1krk4iZ systemd[1]: Starting MySQL Server...
Oct 06 00:45:27 iZm5ed06iqge5x2i1krk4iZ systemd[1]: mysqld.service: main process exited, code=exited,...URE
Oct 06 00:45:27 iZm5ed06iqge5x2i1krk4iZ systemd[1]: Failed to start MySQL Server.
Oct 06 00:45:27 iZm5ed06iqge5x2i1krk4iZ systemd[1]: Unit mysqld.service entered failed state.
Oct 06 00:45:27 iZm5ed06iqge5x2i1krk4iZ systemd[1]: mysqld.service failed.
Hint: Some lines were ellipsized, use -l to show in full.

然后我们去看一下日志 

cat /var/log/mysqld.log

发现了报错信息

2020-10-05T16:52:58.656602Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.21) starting as process 6117
2020-10-05T16:52:58.759340Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2020-10-05T16:52:58.759442Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-10-05T16:52:59.305105Z 1 [ERROR] [MY-013090] [InnoDB] Unsupported redo log format (0). The redo log was created before MySQL 5.7.9
2020-10-05T16:52:59.305260Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2020-10-05T16:52:59.651915Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
2020-10-05T16:52:59.652394Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-10-05T16:52:59.652619Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-10-05T16:52:59.653915Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21)  MySQL Community Server - GPL.

 让我们看看这边

2020-10-05T16:52:59.305105Z 1 [ERROR] [MY-013090] [InnoDB] Unsupported redo log format (0). The redo log was created before MySQL 5.7.9

不支持redo log格式

直接给出解决方案

cd /var/lib/mysql

把里面的两个文件给他挪个窝

ib_logfile0  ib_logfile1

问题解决,mysql正常启动！

使用Navicat远程连接，又报错

"Host 'xxxx' is not allowed to connect to this MySQL server"

这是由于Mysql默认root用户只能本机访问

修改用户表

use mysql;
update user set host='%' where user='root';

查看一下是否已经更新

select host,user from user;

发现已经更新完成

刷新一下使之生效

flush privileges;

第二个问题解决！

然而！问题总是接踵而至

Authentication plugin 'caching_sha2_password' cannot be loaded

这个应该是密码策略问题

查看一下root的加密方式

select host,user,plugin from user;

我用的是navicat12，不支持这种加密方式

这个问题可以通过更换高版本navicat以及更改加密策略解决

我们主要来讲第二种方式

更换加密方式

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

此处的password是你设置的密码

此时你可能遇到密码不允许设置的错误

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

这是因为密码不符合校验标准，此处同样有两种解决办法

1.设置符合标准的密码

2.更改校验标准

SHOW VARIABLES LIKE 'validate_password%'

首先查看密码标准

更改设置

set global validate_password.policy=LOW;

然后重复前面报错的那一步，问题解决！