php SQLSTATE[HY000] [1698] 用户 'root'@'localhost' 的访问被拒绝
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36864206/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost'
提问by lamka02sk
I just installed Ubuntu 16.04 and installed web server on it. Everything works well, but I cannot access database. Even if I create new user and grant all privileges, I can't create database In PHP I'm getting this error:
我刚刚安装了 Ubuntu 16.04 并在其上安装了 Web 服务器。一切正常，但我无法访问数据库。即使我创建新用户并授予所有权限，我也无法在 PHP 中创建数据库我收到此错误：
SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost'
When I try to login in terminal, it works, but in PHP and phpmyadmin don't.
当我尝试在终端中登录时，它可以工作，但在 PHP 和 phpmyadmin 中则不行。
PHP Code:
PHP代码：
protected $host = '127.0.0.1';
protected $db = 'dbname';
protected $name = 'root';
protected $pass = 'root';
protected $conn;
private static $settings = array(
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
);
public function __construct() {
try {
$this->conn = new PDO("mysql:host=$this->host;dbname=$this->db", $this->name, $this->pass, self::$settings);
} catch (PDOException $e) {
echo $e->getMessage();
}
}
回答by Andris
Turns out you can't use the rootuser in 5.7 anymore without becoming a sudoer. That means you can't just run mysql -u rootanymore and have to do sudo mysql -u rootinstead.
事实证明root，除非成为 sudoer，否则您无法再使用5.7 中的用户。这意味着你不能再跑mysql -u root了，sudo mysql -u root而是必须做。
That also means that it will no longer work if you're using the rootuser in a GUI (or supposedly any non-command line application). To make it work you'll have to create a new user with the required privileges and use that instead.
这也意味着如果您root在 GUI（或任何非命令行应用程序）中使用用户，它将不再起作用。要使其工作，您必须创建一个具有所需权限的新用户并使用它。
See thisanswer for more details.
有关更多详细信息，请参阅此答案。
回答by leopold
These steps worked for me on several Systems using Ubuntu 16.04, Apache 2.4, MariaDB, PDO
这些步骤在使用 Ubuntu 16.04、Apache 2.4、MariaDB、PDO 的多个系统上对我有用
log into MYSQL as root
mysql -u rootGrant privileges. To a new user execute:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost'; FLUSH PRIVILEGES;UPDATE for Google Cloud Instances
MySQL on Google Cloud seem to require an alternate command (mind the backticks).
GRANT ALL PRIVILEGES ON `%`.* TO 'newuser'@'localhost';bind to all addresses:
The easiest way is to comment out the line in your /etc/mysql/mariadb.conf.d/50-server.cnf or/etc/mysql/mysql.conf.d/mysqld.cnf file, depending on what system you are running:
#bind-address = 127.0.0.1exit mysql and restart mysql
exit service mysql restart
以 root 身份登录 MYSQL
mysql -u root授予特权。对新用户执行：
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost'; FLUSH PRIVILEGES;Google Cloud 实例的更新
Google Cloud 上的 MySQL 似乎需要一个备用命令（注意反引号）。
GRANT ALL PRIVILEGES ON `%`.* TO 'newuser'@'localhost';绑定到所有地址：
最简单的方法是将 /etc/mysql/mariadb.conf.d/50-server.cnf或/etc/mysql/mysql.conf.d/mysqld.cnf 文件中的行注释掉 ，具体取决于您使用的系统跑步：
#bind-address = 127.0.0.1退出 mysql 并重新启动 mysql
exit service mysql restart
By default it binds only to localhost, but if you comment the line it binds to all interfaces it finds. Commenting out the line is equivalent to bind-address=*.
默认情况下，它仅绑定到 localhost，但如果您注释该行，它将绑定到它找到的所有接口。注释掉该行相当于 bind-address=*。
To check the binding of mysql service execute as root:
要检查 mysql 服务的绑定，请以 root 身份执行：
netstat -tupan | grep mysql
回答by Matematikisto
Maybe a bit late, but I found this answer looking over the internet. It could help others with the same problem.
也许有点晚了，但我在互联网上找到了这个答案。它可以帮助其他有同样问题的人。
$sudo mysql -u root
[mysql] use mysql;
[mysql] update user set plugin='' where User='root';
[mysql] flush privileges;
[mysql] \q
Now you should be able to log in as root in phpmyadmin.
现在您应该能够在 phpmyadmin 中以 root 身份登录。
(Found here.)
（在这里找到。）
回答by rust
To create user for phpMyAdmin :
为 phpMyAdmin 创建用户：
sudo mysql -p -u root
Now you can add a new MySQL user with the username of your choice.
现在您可以使用您选择的用户名添加一个新的 MySQL 用户。
CREATE USER 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD';
And finally grant superuser privileges to the user you just created.
最后将超级用户权限授予您刚刚创建的用户。
GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' WITH GRANT OPTION;
For any question, please leave a comment
如有任何问题，请发表评论
回答by adjepatatje
MySQL makes a difference between "localhost" and "127.0.0.1".
MySQL 区分“localhost”和“127.0.0.1”。
It might be possible that 'root'@'localhost' is not allowed because there is an entry in the user table that will only allow root login from 127.0.0.1.
可能不允许使用 'root'@'localhost'，因为用户表中有一个条目只允许从 127.0.0.1 进行 root 登录。
This could also explain why some application on your server can connect to the database and some not because there are different ways of connecting to the database. And you currently do not allow it through "localhost".
这也可以解释为什么服务器上的某些应用程序可以连接到数据库，而有些则不能，因为连接到数据库的方式不同。而且您目前不允许通过“本地主机”。
回答by stanley mbote
If you are receiving that error even after creating a new user and assigning them the database previledges, then the one last thing to look at is to check if the users have been assigned the preiveledges in the database.
如果您在创建新用户并为他们分配数据库权限后收到该错误，那么最后一件事是检查用户是否已分配数据库中的权限。
To do this log into to your mysql(This is presumably its the application that has restricted access to the database but you as a root can be ablr to access your database table via mysql -u user -p)
要执行此操作，请登录到您的 mysql（这可能是它限制访问数据库的应用程序，但您作为 root 可以 ablr 通过 mysql -u user -p 访问您的数据库表）
commands to apply
应用命令
mysql -u root -p
password: (provide your database credentials)
密码：（提供您的数据库凭据）
on successful login
登录成功
type
类型
use mysql;
from this point check each users priveledges if it is enabled from the db table as follows
从这一点开始检查每个用户的权限是否从 db 表中启用，如下所示
select User,Grant_priv,Host from db;
if the values of the Grant_priv col for the created user is N update that value to Y with the following command
如果创建的用户的 Grant_priv col 的值为 N，则使用以下命令将该值更新为 Y
UPDATE db SET Grant_priv = "Y" WHERE User= "your user";
with that now try accessing the app and making a transaction with the database.
现在尝试访问应用程序并与数据库进行交易。
回答by SUNNETmedia
Just Create New User for MySQL do not use root. there is a problem its security issue
只为 MySQL 创建新用户不要使用 root。有问题，它的安全问题
sudo mysql -p -u root
Login into MySQL or MariaDB with root privileges
使用 root 权限登录 MySQL 或 MariaDB
CREATE USER 'troy121'@'%' IDENTIFIED BY 'mypassword123';
login and create a new user
登录并创建一个新用户
GRANT ALL PRIVILEGES ON *.* TO 'magento121121'@'%' WITH GRANT OPTION;
and grant privileges to access "." and "@" "%" any location not just only 'localhost'
并授予访问“.”的权限。和 "@" "%" 任何位置，不仅仅是 'localhost'
exit;
if you want to see your privilege table SHOW GRANTS;& Enjoy.
如果您想查看您的特权表SHOW GRANTS;并享受。
回答by Autor69
Users for mysql and for server are 2 different things, look how to add user to database and login with these credentials
mysql 和服务器的用户是两个不同的东西，看看如何将用户添加到数据库并使用这些凭据登录
回答by sebastian.roibu
With mysql Ver 14.14 Distrib 5.7.22 the update statement is now:
使用 mysql Ver 14.14 Distrib 5.7.22，更新语句现在是：
update user set authentication_string=password('1111') where user='root';
