2023年6月16日 (五) 12:16 FC

2023-06-16T12:16:32Z

新页面

把mediawiki从云服务器迁移到本地数据库一直不需要操作到，新建完成之后就一直没有查看过，想使用Navicat Premium连接发现连接不上以下是解决过程：

用Navicat连接提示错误：<code>1045 -Accress denied for user 'root'@'192.168.137.1'(using password:YES)</code>

# 登录服务器进入数据库：<code>mysql -u root -p</code>
# 选择数据库（用户信息存储在mysql数据库中）：<code>use mysql；</code>
# 在mysql数据库user表查看当前root用户相关信息：<code>select host, user, authentication_string, plugin from user;</code>
# 发现host是%没毛病。然后plugin是auth_socket插件，提供了额外的安全性，因为它要求用户具有操作系统的 root 权限才能连接到 MySQL。通过下面配置中代码把该功能去掉
<syntaxhighlight lang="linux-config">
mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host | user | authentication_string | plugin |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| % | root | | auth_socket |
| % | wordpressuser | | caching_sha2_password |
| localhost | admin | $A$005$_-o
}^Ufv[%JFyQAnB8WaPcUcZAvH2nc11t.P4cPZUdMzbBgKwZGyn3S. | caching_sha2_password |
| localhost | debian-sys-maint | $A$005$o+QH>qXRd>QJW/mU66b5ogL8PRXJdAprfsZqf.fbAKOzHnB0JNJtWi92/ | caching_sha2_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | wordpressuser | $A$005$?5\u
PM{E-sG;'ktyAbvqnLA2URIwudX7xBeKPS9VwW6kUNm/veqa0uY/C | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
8 rows in set (0.00 sec)
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
Query OK, 0 rows affected (0.01 sec)
mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host | user | authentication_string | plugin |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| % | root | *E709441ED7418D43A465CF7405E54F975C495EC6 | mysql_native_password |
| % | wordpressuser | | caching_sha2_password |
| localhost | admin | $A$005$_-o
}^Ufv[%JFyQAnB8WaPcUcZAvH2nc11t.P4cPZUdMzbBgKwZGyn3S. | caching_sha2_password |
| localhost | debian-sys-maint | $A$005$o+QH>qXRd>QJW/mU66b5ogL8PRXJdAprfsZqf.fbAKOzHnB0JNJtWi92/ | caching_sha2_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | wordpressuser | $A$005$?5\u
PM{E-sG;'ktyAbvqnLA2URIwudX7xBeKPS9VwW6kUNm/veqa0uY/C | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
8 rows in set (0.00 sec)

</syntaxhighlight>如果host是localhost用下面代码改为%

<code>GRANT ALL ON *.* TO 'root'@'%';</code>

如果有多个root账号用下面代码删除（user和host内容匹配）：

<code>delete from mysql.user where user=<nowiki>''</nowiki> and host=<nowiki>''</nowiki>;</code>
[[分类:Mysql8.0]]

非本地连接mysql8.0 - 版本历史

2023年6月16日 (五) 12:16 FC