openEuler22.03安装mysql8.0.31报错解决

现象描述

首先下载MySQL

https://repo.mysql.com//mysql80-community-release-el8-4.noarch.rpm

yum install mysql80-community-release-el8-4.noarch.rpm -y

通过运行以下命令并检查其输出来验证是否已启用和禁用正确的子存储库

yum repolist enabled | grep mysql

安装MySQL

yum -y install mysql-community-server
systemctl enable mysqld
systemctl start mysqld
systemctl status mysqld
mysql -V
grep 'temporary password' /var/log/mysqld.log

登录：

注意：

第一次登录后，必须修改密码才可以使用，且密码必须满足复杂度要求

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>  ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'x2Euler@123';
Query OK, 0 rows affected (0.02 sec)

授权远程登录：继续报错

可能原因：

mysql8不允许这样授权

定位思路：

需要在表中执行update

处理步骤：

mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

mysql> update user set host = '%' where user = 'root' and host='localhost';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

刷新并重新授权：

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql> grant all on *.* to 'root'@'%';
Query OK, 0 rows affected (0.02 sec)

至此可以远程登录：

[root@centos-01 ~]# mysql -h 192.168.137.14 -P 3306 -uroot -px2Euler@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

远程登录成功，尝试用navicat工具连接：

直接可登录

但测试centos7安装mysql8.0.24(el7)时，在此步骤下会报错

此种问题是加密方式问题，需要进行修改

修改如下：

修改如下：
mysql> use mysql;
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password by '******';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

如此才会能用工具连接上数据库。

而本次测试openEuler22.03安装mysql8.0.31（el8）没有此问题，应该是el8版本MySQL已修复此问题。

参考信息：

无