MySQL 错误 1698 (28000)：拒绝用户“root”@“localhost”的访问

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

提问by Folkmann

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, e.g. Navicat. They all fail.

我是通过终端 (SSH)、PHPMyAdmin 还是 MySQL 客户端（例如 Navicat）连接都没有关系。他们都失败了。

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. I also tried, just installing iRedMail, but then root, also doesn't work...

在我安装 iRedMail 之前登录到 MySQL 数据库工作正常。我也试过，只是安装 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. And yes I tried the Installation Tipsand I can't find those variables in the config files.

如果有人能告诉我如何解决 MySQL 登录问题或如何在现有 MySQL 安装之上安装 iRedMail。是的，我尝试了安装提示，但在配置文件中找不到这些变量。

回答by zetacu

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.You can see if your rootuser is set up like this by doing the following:

基本上意味着：db_users 使用它，将被系统用户凭据“认证” 。您可以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 rootuser is using the auth_socketplugin

正如您在查询中看到的，root用户正在使用auth_socket插件

There are 2 ways to solve this:

有2种方法可以解决这个问题：

1. You can set the root user to use the mysql_native_passwordplugin
2. You can create a new db_userwith you system_user(recommended)

1. 可以设置root用户使用mysql_native_password插件
2. 可以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 -u YOUR_SYSTEM_USER)连接到 mysql

Note:On some systems (e.g., 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 拉伸） '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.x.x updated/replaced the auth_socketfor caching_sha2_passwordI don't have a system setup with mysql 8.x.x to test this, however the steps above should help you to understand the issue. Here's the reply:

更新：从@andy 的评论看来，mysql 8.xx 更新/替换了auth_socket因为caching_sha2_password我没有使用 mysql 8.xx 进行系统设置来测试这个，但是上面的步骤应该可以帮助你理解这个问题。这是答复：

One change as of MySQL 8.0.4 is that the new default authentication plugin is '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. No need for the "UPDATE user SET plugin" step. For the 8.0.4 default auth plugin update see, https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/

MySQL 8.0.4 的一项更改是新的默认身份验证插件是“caching_sha2_password”。新的“YOUR_SYSTEM_USER”将具有此身份验证插件，您现在可以使用“mysql -u YOUR_SYSTEM_USER -p”从 bash shell 登录，并在提示中提供该用户的密码。不需要“更新用户设置插件”步骤。对于 8.0.4 默认身份验证插件更新，请参阅https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/

回答by Nandesh

Check here:

检查这里：

NEW Version of MYSQL does it this way.

MYSQL 的新版本就是这样做的。

In the new my-sqlif the password is left empty while installing then it is based on the auth_socketplugin.

在新的 mysql 中，如果安装时密码为空，则它基于auth_socket插件。

The correct way is to login to my-sql with sudoprivilege.

正确的方法是用sudo特权登录mysql 。

$ sudo mysql -u root -p

And then updating the password using:

然后使用以下方法更新密码：

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

Once this is done stop and startthe mysql server.

完成此操作后stop and start，mysql服务器。

$  sudo service mysql stop
$  sudo service mysql start

For complete details you can refer to this link.

有关完整的详细信息，您可以参考此链接。

Do comment for any doubt.

如有任何疑问，请发表评论。

回答by Tahir Khalid

I was having this issue on an Debian 8 VM that I was interacting with through Putty on my Windows 10 desktop.

我在 Debian 8 VM 上遇到了这个问题，我在 Windows 10 桌面上通过 Putty 与之交互。

I tried the various suggestions on here but nothing quite worked and I am running MariaDB on the Debian host. 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 i.e. allowing a newly created MySql user to log into the MySql/MariaDB server:

我在这里尝试了各种建议，但没有任何效果，我在 Debian 主机上运行 MariaDB。最后我发现我无法在安全模式下启动数据库服务器，但我不需要，以下命令实际上对我有用，即允许新创建的 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

*出现提示时输入密码

回答by Eminem347

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

更新 - 这是针对 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. This will save a lot of effort.

并再次安装但这次自己设置一个root密码。这将节省很多精力。

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

回答by Raoul HATTERER

No need of sudo

不需要 sudo

The database is initialised with 2 all-privilege accounts: the first one is "root" which is inaccessible and the second one with your user name (check with command whoami).

数据库使用 2 个全特权帐户进行初始化：第一个是无法访问的“root”，第二个是您的用户名（检查命令whoami）。

To enable access to root account, you need to login with your user name

要启用对 root 帐户的访问，您需要使用您的用户名登录

mysql -u $(whoami)

and manually change password for root

并手动更改root密码

use mysql;
set password for 'root'@'localhost' = password('YOUR_ROOT_PASSWORD_HERE');
flush privileges;
quit

Login as 'root'

以“root”身份登录

mysql -u root -p

回答by Ameer Ul Islam

After hours of struggle with no solution here, this worked for me then I found a youtube video where it says the password column is now called authentication_string . So I was able to change my password as follows: First get into mysql from terminal

经过数小时的努力，这里没有解决方案，这对我有用，然后我找到了一个 youtube 视频，它说密码列现在称为 authentication_string 。所以我可以按如下方式更改我的密码：首先从终端进入 mysql

sudo mysql

then inside mysql type whatever after mysql>

然后在 mysql> 之后输入任何内容

mysql> use mysql
mysql> update user set authentication_string=PASSWORD("mypass") where user='root';
mysql> flush privileges;
mysql> quit;

at this point you are out of mysql back to your normal terminal place. You need to restart mysql for this to take effect. for that type the following:

此时您已退出 mysql 回到您正常的终端位置。您需要重新启动 mysql 才能生效。对于该类型，请执行以下操作：

sudo service mysql restart

Refer to this video linkfor better understanding

请参阅此视频链接以更好地理解

回答by Y. Joy Ch. Singha

step 1. sudo mysql -u root -p

第1步。 sudo mysql -u root -p

step 2. USE mysql;

第2步。 USE mysql;

step 3. ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'admin';

第 3 步。 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'admin';

Here 'admin' is your new password, yo can change it.

这里的“admin”是您的新密码，您可以更改它。

step 4. exit

第四步。 exit

Thanks. You are done.

谢谢。你完成了。

回答by Dorad

Thatworked for me:

这对我有用：

mysql --user=root mysql
CREATE USER 'some_user'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'some_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

回答by ángel Ugarte

First step: go to /etc/phpmyadmin/config.inc.php then uncomment lines where you find AllowNoPassword . Second step: login to your mysql default account

第一步：转到 /etc/phpmyadmin/config.inc.php 然后取消注释您找到 AllowNoPassword 的行。第二步：登录你的mysql默认账号

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

and that's all!

就这样！

回答by muhammed fairoos nm

I also faced the same issue at the first time.

我第一次也遇到了同样的问题。

Now it is fixed:

现在它是固定的：

First, you copy the /etc/mysql/mysql.conf.d/mysqld.cnffile and past in to /etc/mysql/my.cnf.

首先，您复制/etc/mysql/mysql.conf.d/mysqld.cnf文件并粘贴到/etc/mysql/my.cnf.

You can do it by command:

您可以通过命令来完成：

sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/my.cnf

Now let's Rest the password:

现在让我们重置密码：

Use the following commands in your terminal:

在终端中使用以下命令：

sudo service mysql stop 
sudo service mysql start
sudo mysql -u root

Now you are inside the mysql console.

现在您在 mysql 控制台中。

Then let's write some queries to reset our root password

然后让我们写一些查询来重置我们的 root 密码

USE mysql
update mysql.user set authentication_string=password('newpass') where user='root' and Host ='localhost';
update user set plugin="mysql_native_password"; 
flush privileges;
quit

Now we can clean /etc/mysql/my.cng

现在我们可以清理了 /etc/mysql/my.cng

Open the above file in your editor and remove the whole lines inside the file.

在编辑器中打开上述文件并删除文件中的整行。

After that let's restart mysql:

之后让我们重新启动mysql：

sudo mysql service restart 

Now let's use mysql with newly created password:

现在让我们使用 mysql 和新创建的密码：

sudo mysql -u root -p

Finally enter your newly created password.

最后输入您新创建的密码。