SQLSTATE [ HY000][1698]拒绝用户‘ root’@‘ localhost’的访问

我刚刚安装了 Ubuntu 16.04(Xenial Xerus)并安装了网络服务器。一切都很好，但我不能访问数据库。 即使我创建新用户并授予所有权限，我也不能创建数据库

在 PHP 中我得到了这个错误:

SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost'

当我尝试在终端中登录时，它能工作，但在 PHP 和 phpMyAdmin 中不能。

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();
}
}
353364 次浏览
小开

Users for MySQL and for server are two different things. Look how to add a user to the database and log in with these credentials.

小开

MySQL makes a difference between "localhost" and "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.

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".

小开

It turns out you can't use the root user in 5.7 anymore without becoming a sudo'er. That means you can't just run mysql -u root anymore and have to do sudo mysql -u root instead.

That also means that it will no longer work if you're using the root user 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.

See this answer for more details.

小开

These steps worked for me on several systems using Ubuntu 16.04 (Xenial Xerus), Apache 2.4, MariaDB, and PDO:

  1. Log into MYSQL as root

     mysql -u root

  2. Grant privileges. For 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';

    NOTE: Depending on wether your new user should be able to grant all privileges to other users as well you could extend the command by the GRANT WITH option. Please be aware that this exposes your user to be sudoer and hence become a higher security risk.

     GRANT ALL PRIVILEGES ON `%`.* TO 'newuser'@'localhost' GRANT WITH OPTION;

  3. 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.1

  4. Exit MySQL and restart 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=*.

To check the binding of the MySQL service, execute as root:

netstat -tupan | grep mysql
小开

Use:

sudo mysql -u root

And now in the MySQL client:

use mysql;
update user set plugin='' where User='root';
flush privileges;
\q

Now you should be able to log in as root in phpMyAdmin.

(It was found here.)

小开

With MySQL client version 14.14 and Distrib 5.7.22, the update statement is now:

update user set authentication_string=password('1111') where user='root';
小开

To create a user for phpMyAdmin:

sudo mysql -p -u root

Now you can add a new MySQL user with the username of your choice.

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;
小开

Just create a new user for MySQL; do not use root. There is a problem with its security issues:

sudo mysql -p -u root

Log in into MySQL or MariaDB with root privileges

CREATE USER 'troy121'@'%' IDENTIFIED BY 'mypassword123';

Log in 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'.

exit;

If you want to see your privilege table, SHOW GRANTS; and enjoy.

小开

If you are receiving that error even after creating a new user and assigning them the database privileges, then the one last thing to look at is to check if the users have been assigned the privileges in the database.

To do this, log into to your MySQL client (this is presumably the application that has restricted access to the database, but you as a root can be able to access your database table via 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 user's privileges if it is enabled from the database table as follows:

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:

UPDATE db SET Grant_priv = "Y" WHERE User= "your user";

With that, now try accessing the application and making a transaction with the database.

小开

In short, in MariaDB:

  1. sudo mysql -u root;
  2. use mysql;
  4. Password = PASSWORD('pass1234') WHERE User = 'root';
  5. FLUSH PRIVILEGES;
  6. exit;
小开

I had the same problem in my Ubuntu 20.04 (Focal Fossa) and MySQL 8.0 and I do these steps:

  1. log in to MySQL

    sudo mysql -p -u root

  2. Show the users added to MySQL

    SELECT user,plugin,host FROM mysql.user

  3. Change the root user plugin from auth_socket to mysql_native_password

    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

  4. Flush the privileges

    FLUSH PRIVILEGES;

  5. Ctrl + z to exit from MySQL

  6. Restart your MySQL service

    sudo service MySQL restart

  7. Check your phpMyAdmin page and try to log in.

小开

Use:

sudo mysql -u root
mysql> CREATE USER 'sample'@'localhost' IDENTIFIED BY 'Secure1pass!';
mysql> CREATE DATABASE testdb;
mysql> GRANT ALL PRIVILEGES ON testdb . * TO 'sample'@'localhost';

In case you just want to use your MySQL server on Ubuntu locally and want to connect with your application to a database.

小开 
sudo mysql -u root
mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;
service mysql restart

After restarting mysql server reload the app please.

小开

ALTER USER or DROP the user and create again works perfectly.

DROP USER root@localhost;
CREATE USER root@localhost IDENTIFIED BY 'root_password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
FLUSH PRIVILEGES;`
小开

I had 'user'@'%' with all privileges when getting the same error mentioning 'user'@'localhost' denied access. So I create 'user'@'localhost' with all privileges, and then flush, and even restart services to no avail.

At last I changed $host = '127.0.0.1'; to $host = 'localhost';. Now it works!

小开

None from this question reply that solving my problem but i got super easy to solving that problem!

Just open file DEBIAN.CNF :

/etc/mysql/debian.cnf

You will find default sys admin user and pass! login with this account on your PhpMyAdmin then create new user etc whatever you want!

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = 8pTMhYuRMW6jmMG1
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = 8pTMhYuRMW6jmMG1
socket   = /var/run/mysqld/mysqld.sock

提交答案