有时候你会遇到如题所示的问题,具体如下:

在安装SEAFILE 6.2.5的时候,如果对应的MySQL是8.0的,就会出现上述问题:

[root@centerbase seafile-server-6.2.5]# sh setup-seafile-mysql.sh 
Checking python on this machine ...
  Checking python module: setuptools ... Done.
  Checking python module: python-imaging ... Done.
  Checking python module: python-mysqldb ... Done.

-----------------------------------------------------------------
This script will guide you to setup your seafile server using MySQL.
Make sure you have read seafile server manual at

        https://github.com/haiwen/seafile/wiki

Press ENTER to continue
-----------------------------------------------------------------


What is the name of the server? It will be displayed on the client.
3 - 15 letters or digits
[ server name ] centerbase

What is the ip or domain of the server?
For example: www.mycompany.com, 192.168.1.101
[ This server's ip or domain ] 192.168.159.254

Where do you want to put your seafile data?
Please use a volume with enough free space
[ default "/seafile_root/seafile-data" ] 

Which port do you want to use for the seafile fileserver?
[ default "8082" ] 

-------------------------------------------------------
Please choose a way to initialize seafile databases:
-------------------------------------------------------

[1] Create new ccnet/seafile/seahub databases
[2] Use existing ccnet/seafile/seahub databases

[ 1 or 2 ] 
-------------------------------------------------------
Please choose a way to initialize seafile databases:
-------------------------------------------------------

[1] Create new ccnet/seafile/seahub databases
[2] Use existing ccnet/seafile/seahub databases

[ 1 or 2 ] 1

What is the host of mysql server?
[ default "localhost" ] 

What is the port of mysql server?
[ default "3306" ] 

What is the password of the mysql root user?
[ root password ] 

verifying password of user root ...  
verifying password of user root ...  
Failed to connect to mysql server using user "root" and password "***": Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

What is the password of the mysql root user?
[ root password ] 

查看下报错中提示的文件:

[root@centerbase ~]# ls -ltr /usr/lib64/mysql/plugin/ | grep caching
[root@centerbase ~]# ls -ltr /usr/lib64/mysql/plugin/ | grep password
-rwxr-xr-x. 1 root root   96152 Dec 20 02:01 validate_password.so
-rwxr-xr-x. 1 root root  108256 Dec 20 02:01 component_validate_password.so
[root@centerbase ~]# 
[root@centerbase ~]# ls -ltr /usr/lib64/mysql/plugin/ | grep caching_sha2_password.so
[root@centerbase ~]# 

确实没有。

【caching_sha2_password】是从MySQL 8.0开始的默认的认证插件,而在之前的版本中是:【mysql_native_password】
这个可以通过查看mysql.user证实:

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

mysql> 

因此,解决该问题,可以通过改变用户验证的插件实现。

改变其中root的认证模块:

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

永不过期
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '**********' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.08 sec)

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

修改验证插件
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '**********';
Query OK, 0 rows affected (0.04 sec)

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

mysql> 

可以看到已经改变了。

然后就没问题了。

————————————————————
以上的方法是临时的改变认证插件。
也可以用下面的方法持久的改变默认的认证插件的设置:
先看看当前的设置:

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec)

mysql>

编辑配置文件:my.cnf

[root@centerbase percona_mysql]# cp /etc/my.cnf /etc/my.cnf_orig
[root@centerbase percona_mysql]# 
[root@centerbase percona_mysql]# vi /etc/my.cnf
[root@centerbase percona_mysql]#
[root@centerbase percona_mysql]# cat /etc/my.cnf | grep -B 5 "^default-authentication-plugin=mysql_native_password"
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
default-authentication-plugin=mysql_native_password
[root@centerbase percona_mysql]# 
[root@centerbase percona_mysql]# diff /etc/my.cnf /etc/my.cnf_orig 
28d27
< default-authentication-plugin=mysql_native_password
[root@centerbase percona_mysql]# 

然后重启MySQL服务:

[root@centerbase percona_mysql]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@centerbase percona_mysql]#

再看,就彻底变掉了:

[root@centerbase percona_mysql]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.13-3 Percona Server (GPL), Release 3, Revision a920dd6

Copyright (c) 2009-2018 Percona LLC and/or its affiliates
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec)

mysql>

————————————————————————
Done。

1
说点什么

avatar
1 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
0 Comment authors
Recent comment authors

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据

  Subscribe  
提醒
trackback

[…] MySQL 8,error:Failed to connect to mysql server using user “root” and password &#822… […]