SQLSTATE[HY000] [1698] Access denied for user '

问题:

I just installed Ubuntu 16.04 and installed web server on it. Everything works well, but I cannot access database. I tried everything on internet, also here, but nothing works...even if I create new user and grant all privileges, I can't create database :( In PHP I'm gettin' this error:

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. Please help.

PHP Code:

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();
    }
}

回答1:

Turns out you can't use the root user in 5.7 anymore without becoming a sudoer. 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.

回答2:

These steps worked for me on several Systems using Ubuntu 16.04, Apache 2.4, MariaDB, PDO

1. log into MYSQL as root

   mysql -u root
   

2. Grant privileges. To a new user execute:

   CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
   GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';
   FLUSH PRIVILEGES;
   

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 mysql service execute as root:

netstat -tupan | grep mysql

回答3:

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.

(Found here.)

回答4:

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

回答5:

Users for mysql and for server are 2 different things, look how to add user to database and login with these credentials

回答6:

With mysql Ver 14.14 Distrib 5.7.22 the update statement is now:

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