問題描述

我正在設置一個新服務器，但一直遇到這個問題.

I'm setting up a new server and keep running into this problem.

當我嘗試以 root 用戶登錄 MySQL 數據庫時，出現錯誤:

When I try to log into the MySQL database with the root user, I get the error:

ERROR 1698 (28000): 用戶 'root'@'localhost' 的訪問被拒絕

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

我是否通過終端 (SSH)、phpMyAdmin 或MySQL 客戶端，例如 Navicat.他們都失敗了.

It doesn't matter if I connect through the terminal (SSH), through phpMyAdmin or a MySQL client, e.g., Navicat. They all fail.

我查看了 mysql.user 表并得到以下信息:

I looked in the mysql.user table and get the following:

+------------------+-------------------+
| 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 |
+------------------+-------------------+

如您所見，用戶 root 應該具有訪問權限.

As you can see, user root should have access.

服務器非常簡單，因為我已經嘗試解決這個問題有一段時間了.

The Server is quite simple, as I have tried to troubleshoot this for a while now.

它正在運行 Ubuntu 16.04.1 LTS (Xenial Xerus) 使用 Apache、MySQL 和 PHP，以便它可以托管網站，以及 iRedMail 0.9.5-1，以便它可以托管郵件.

It's running Ubuntu?16.04.1 LTS (Xenial Xerus) with Apache, MySQL and PHP, so that it can host websites, and iRedMail 0.9.5-1, so that it can host mail.

在我安裝 iRedMail 之前登錄 MySQL 數據庫工作正常.我也試過只安裝 iRedMail，但是 root 也不起作用.

Log into the MySQL database works fine before I installed iRedMail. I also tried just installing iRedMail, but then root also doesn't work.

如何修復我的 MySQL 登錄問題，或者如何通過現有的 MySQL 安裝安裝 iRedMail?是的，我嘗試了安裝技巧，但我不能在配置文件中找到這些變量.

How can I fix my MySQL login problem or how can I install iRedMail over an existing MySQL install? And yes, I tried the Installation Tips and I can't find those variables in the configuration files.

推薦答案

某些系統如 Ubuntu、MySQL 正在使用 UNIX auth_socket 插件 默認.

Some systems like Ubuntu, MySQL is using the UNIX auth_socket plugin by default.

基本上這意味著:db_users 使用它，將被驗證"；通過系統用戶憑據.您可以通過執行以下操作來查看您的 root 用戶是否是這樣設置的:

Basically it means that: db_users using it, will be "authenticated" by the system user credentials. You can see if your root user is set up like this by doing the following:

sudo mysql -u root # I had to use "sudo" since it was a 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 |
+------------------+-----------------------+

正如您在查詢中看到的，root 用戶正在使用 auth_socket 插件.

As you can see in the query, the root user is using the auth_socket plugin.

有兩種方法可以解決這個問題:

There are two ways to solve this:

1. 您可以設置root用戶使用mysql_native_password插件
2. 你可以創建一個新的db_usersystem_user(推薦)

1. You can set the root user to use the mysql_native_password plugin
2. You can create a new db_user with you system_user (recommended)

選項 1:

sudo mysql -u root # I had to use "sudo" since it was new installation

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

sudo service mysql restart

選項 2:(將 YOUR_SYSTEM_USER 替換為您擁有的用戶名)

Option 2: (replace YOUR_SYSTEM_USER with the username you have)

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 'YOUR_PASSWD';
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;

sudo service mysql restart

請記住，如果您使用選項 #2，則必須以系統用戶名 (mysql -u YOUR_SYSTEM_USER) 連接到 MySQL.

Remember that if you use option #2 you'll have to connect to MySQL as your system username (mysql -u YOUR_SYSTEM_USER).

注意:在某些系統上(例如，Debian 9 (Stretch)) 'auth_socket' 插件被稱為 'unix_socket'，所以對應的SQL命令應該是:UPDATE user SET plugin='unix_socket' WHERE User='YOUR_SYSTEM_USER';

Note: On some systems (e.g., Debian 9 (Stretch)) the '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';

從@andy 的評論看來，MySQL 8.x.x 更新/替換了 caching_sha2_password 的 auth_socket.我沒有使用 MySQL 8.x.x 的系統設置來測試這個.但是，上述步驟應該可以幫助您理解問題.回復如下:

From @andy's comment it seems that MySQL 8.x.x updated/replaced the auth_socket for caching_sha2_password. I 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:

MySQL 8.0.4 的一個變化是新的默認身份驗證插件是caching_sha2_password".新的YOUR_SYSTEM_USER"將擁有此身份驗證插件，您現在可以使用mysql -u YOUR_SYSTEM_USER -p"從 Bash shell 登錄.并在提示中提供該用戶的密碼.不需要UPDATE user SET plugin"；步驟.

對于 8.0.4 默認身份驗證插件更新，請參閱 https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/

這篇關于錯誤 1698 (28000):拒絕用戶“root"@“localhost"的訪問的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！