错误1698（28000）：用户&#39；root&#39；@&#39；localhost&#39；的访问被拒绝

最新推荐文章于 2024-12-29 16:17:05 发布

翻译最新推荐文章于 2024-12-29 16:17:05 发布 · 835 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://oldbug.net/q/2EOW6/ERROR-1698-28000-Access-denied-for-user-root-localhost

文章标签：

#mysql #iredmail

本文翻译自：ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I'm setting up a new server and keep running into this problem. 我正在设置新服务器，并继续遇到此问题。

When I try to login to the MySQL database with the root user, I get the error: 当我尝试以root用户登录MySQL数据库时，出现错误：

ERROR 1698 (28000): Access denied for user 'root'@'localhost' 错误1698（28000）：用户'root'@'localhost'的访问被拒绝

It doesn't matter if I connect through the terminal(SSH), through PHPMyAdmin or a MySQL Client, eg Navicat. 是否通过终端（SSH），PHPMyAdmin或MySQL客户端（例如Navicat）连接都没有关系。 They all fail. 他们都失败了。

I looked in the mysql.user table and get the following: 我在mysql.user表中查看了以下内容：

+------------------+-------------------+
| user             | host              |
+------------------+-------------------+
| root             | %                 |
| root             | 127.0.0.1         |
| amavisd          | localhost         |
| debian-sys-maint | localhost         |
| iredadmin        | localhost         |
| iredapd          | localhost         |
| mysql.sys        | localhost         |
| phpmyadmin       | localhost         |
| root             | localhost         |
| roundcube        | localhost         |
| vmail            | localhost         |
| vmailadmin       | localhost         |
| amavisd          | test4.folkmann.it |
| iredadmin        | test4.folkmann.it |
| iredapd          | test4.folkmann.it |
| roundcube        | test4.folkmann.it |
| vmail            | test4.folkmann.it |
| vmailadmin       | test4.folkmann.it |
+------------------+-------------------+

As you can see, root should have access. 如您所见，root应该具有访问权限。

The Server is quite simple, as I have tried to troubleshoot this for a while now.. 该服务器非常简单，因为我已经尝试解决了一段时间。

It's running Ubuntu 16.04.1 LTS with Apache, MySQL and PHP, so that it can host websites, and iRedMail 0.9.5-1, so that it can host mail. 它运行Apache，MySQL和PHP的Ubuntu 16.04.1 LTS，因此可以托管网站，而iRedMail 0.9.5-1则可以托管邮件。

Login in to the MySQL database works fine before I install iRedMail. 在安装iRedMail之前，登录MySQL数据库工作正常。 I also tried, just installing iRedMail, but then root, also doesn't work... 我也尝试过，只是安装了iRedMail，但是然后以root身份也不起作用...

If someone could tell me how I fix my MySQL login problem or how I install iRedMail, on top of an existing MySQL install. 如果有人可以告诉我如何解决MySQL登录问题或如何安装iRedMail，请在现有的MySQL安装之上。 And yes I tried the Installation Tips and I can't find those variables in the config files. 是的，我尝试了安装技巧，但在配置文件中找不到那些变量。

#1楼

参考：https://stackoom.com/question/2EOW6/错误-用户-root-localhost-的访问被拒绝

#2楼

You want to access MySQL with root user but you're not providing root's correct password. 您想以root用户访问MySQL，但没有提供root的正确密码。

If you need to set a new password for root , MySQL's site has great documentation on how to do it: http://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html 如果您需要为root设置一个新密码 ，MySQL的站点上有很多有关如何执行此操作的文档： http : //dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

I'll not show the process in here because MySql documentation on the above link it's clear and concise . 我不会在这里显示该过程，因为上述链接上的MySql文档 清晰明了 。

#3楼

I would suggest to remove the Mysql connection - 我建议删除Mysql连接-

UPDATE-This is for Mysql version 5.5,if your version is different ,please change the first line accordingly UPDATE-这是针对MySQL版本5.5的，如果您的版本不同，请相应地更改第一行

sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-5.5 mysql-client-core-5.5
sudo rm -rf /etc/mysql /var/lib/mysql
sudo apt-get autoremove
sudo apt-get autoclean

And Install Again But this time set a root password yourself. 并再次安装但是这次您自己设置一个root密码。 This will save a lot of effort. 这样可以节省很多精力。

sudo apt-get update
sudo apt-get install mysql-server

#4楼

Some systems like Ubuntu, mysql is using by default the UNIX auth_socket plugin . 某些系统，例如Ubuntu，mysql，默认情况下使用UNIX auth_socket插件。

Basically means that: db_users using it, will be "auth" by the system user credentias. 基本上意味着： 使用它的db_users，将由系统用户凭据 “认证” 。 You can see if your root user is set up like this by doing the following: 您可以通过执行以下操作来查看是否以这种方式设置了root用户：

$ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;

+------------------+-----------------------+
| User             | plugin                |
+------------------+-----------------------+
| root             | auth_socket           |
| mysql.sys        | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+

As you can see in the query, the root user is using the auth_socket plugin 如您在查询中所看到的， root用户正在使用auth_socket插件

There are 2 ways to solve this: 有两种解决方法：

You can set the root user to use the mysql_native_password plugin 您可以设置root用户使用mysql_native_password插件
You can create a new db_user with you system_user (recommended) 您可以使用db_user创建一个新的system_user （推荐）

Option 1: 选项1：

$ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ service mysql restart

Option 2: (replace YOUR_SYSTEM_USER with the username you have) 选项2 ：（用您拥有的用户名替换YOUR_SYSTEM_USER）

$ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED BY '';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOUR_SYSTEM_USER'@'localhost';
mysql> UPDATE user SET plugin='auth_socket' WHERE User='YOUR_SYSTEM_USER';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ service mysql restart

Remember that if you use option #2 you'll have to connect to mysql as your system username ( mysql -u YOUR_SYSTEM_USER ) 请记住，如果使用选项＃2，则必须以系统用户名连接到mysql（ mysql -u YOUR_SYSTEM_USER ）

Note: On some systems (eg, Debian stretch) 'auth_socket' plugin is called 'unix_socket' , so the corresponding SQL command should be: UPDATE user SET plugin='unix_socket' WHERE User='YOUR_SYSTEM_USER'; 注意：在某些系统上（例如Debian Stretch），“ auth_socket”插件称为“ unix_socket” ，因此相应的SQL命令应为： UPDATE user SET plugin='unix_socket' WHERE User='YOUR_SYSTEM_USER';

Update: from @andy's comment seems that mysql 8.xx updated/replaced the auth_socket for caching_sha2_password I don't have a system setup with mysql 8.xx to test this, however the steps above should help you to understand the issue. 更新： @andy的评论似乎是mysql 8.xx已更新/替换了auth_socket的caching_sha2_password我没有使用mysql 8.xx进行系统安装的测试程序，但是上述步骤应该可以帮助您理解问题。 Here's the reply: 这是答复：

One change as of MySQL 8.0.4 is that the new default authentication plugin is 'caching_sha2_password'. 自MySQL 8.0.4起，一项更改是新的默认身份验证插件为'caching_sha2_password'。 The new 'YOUR_SYSTEM_USER' will have this auth plugin and you can login from the bash shell now with "mysql -u YOUR_SYSTEM_USER -p" and provide the password for this user on the prompt. 新的“ YOUR_SYSTEM_USER”将具有此auth插件，您现在可以使用“ mysql -u YOUR_SYSTEM_USER -p”从bash shell登录，并在提示符下提供该用户的密码。 No need for the "UPDATE user SET plugin" step. 不需要“更新用户SET插件”步骤。 For the 8.0.4 default auth plugin update see, https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/ 有关8.0.4默认身份验证插件更新，请参见https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/

#5楼

I was having this issue on an Debian 8 VM that I was interacting with through Putty on my Windows 10 desktop. 我在通过Windows 10桌面上的Putty与之交互的Debian 8 VM上遇到了这个问题。

I tried the various suggestions on here but nothing quite worked and I am running MariaDB on the Debian host. 我在这里尝试了各种建议，但没有任何效果，并且我在Debian主机上运行MariaDB。 In the end I found that I couldn't start the db server in safe mode but I didn't need to and the following commands actually worked for me ie allowing a newly created MySql user to log into the MySql/MariaDB server: 最后，我发现我无法以安全模式启动数据库服务器，但我并不需要这样做，以下命令实际上对我有用：即允许新创建的MySql用户登录MySql / MariaDB服务器：

sudo service mysql restart
sudo mysql # logs in automatically into MariaDB
use mysql;
update user set plugin='' where user='your_user_name';
flush privileges;
exit;
sudo service mysql restart # restarts the mysql service

If the above doesn't quite work for you, follow the steps outlined in zetacu's post above ( zetacu ) then follow my steps. 如果上述方法不太适合您，请按照上面zetacu的帖子（ zetacu ）中概述的步骤进行操作，然后按照我的步骤进行操作。

Now you should be able to use a remote terminal client and securely log into mysql using the command: 现在，您应该能够使用远程终端客户端，并使用以下命令安全地登录mysql了：

mysql -u your_user_name -p

*type in the password when prompted *提示时输入密码

#6楼

First step: go to /etc/phpmyadmin/config.inc.php then uncomment lines where you find AllowNoPassword . 第一步：转到/etc/phpmyadmin/config.inc.php，然后取消注释行，在其中找到AllowNoPassword。 Second step: login to your mysql default account 第二步：登录到您的mysql默认帐户

mysql -u root -p
use mysql;
update user set plugin="" where user='root';
flush privilege;

and that's all! 就这样！