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

Php Problem Overview

I just installed Ubuntu 16.04 (Xenial Xerus) 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:

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

Php Solutions

Solution 1 - Php

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.

Solution 2 - Php

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

Log into MYSQL as root
```
 mysql -u root
```
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;
```
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
```
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

Solution 3 - Php

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

Solution 4 - Php

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;

Solution 5 - Php

In short, in MariaDB:

sudo mysql -u root;
use mysql;
UPDATE mysql.user SET plugin = 'mysql_native_password', Password = PASSWORD('pass1234') WHERE User = 'root';
FLUSH PRIVILEGES;
exit;

Solution 6 - Php

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

sudo mysql -p -u root

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

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.

Solution 7 - Php

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

Solution 8 - Php

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

Solution 9 - Php

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.

Solution 10 - Php

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.

Solution 11 - Php

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

Show the users added to MySQL

SELECT user,plugin,host FROM mysql.user

Change the root user plugin from auth_socket to mysql_native_password

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

Flush the privileges
```
FLUSH PRIVILEGES;
```
Ctrl + z to exit from MySQL
Restart your MySQL service
```
sudo service MySQL restart
```
Check your phpMyAdmin page and try to log in.

Solution 12 - Php

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.

Solution 13 - Php

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.

Solution 14 - Php

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;`

Content Type	Original Author	Original Content on Stackoverflow
Question	lamka02sk	View Question on Stackoverflow
Solution 1 - Php	Andris	View Answer on Stackoverflow
Solution 2 - Php	leopold	View Answer on Stackoverflow
Solution 3 - Php	Matematikisto	View Answer on Stackoverflow
Solution 4 - Php	user5683940	View Answer on Stackoverflow
Solution 5 - Php	Jewel	View Answer on Stackoverflow
Solution 6 - Php	SUNNETmedia	View Answer on Stackoverflow
Solution 7 - Php	adjepatatje	View Answer on Stackoverflow
Solution 8 - Php	sebastian.roibu	View Answer on Stackoverflow
Solution 9 - Php	stanley mbote	View Answer on Stackoverflow
Solution 10 - Php	Autor69	View Answer on Stackoverflow
Solution 11 - Php	Pejman Kheyri	View Answer on Stackoverflow
Solution 12 - Php	Moritz Binder	View Answer on Stackoverflow
Solution 13 - Php	Shiva Yadav	View Answer on Stackoverflow
Solution 14 - Php	FidelMunywoki	View Answer on Stackoverflow