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

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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;

Answer 5

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;

Answer 6

In short, in MariaDB:

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

Answer 7

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

Answer 8

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.

Answer 9

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

Answer 10

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.

Answer 11

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.

Answer 12

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.

Answer 13

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

Answer 14

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.

Answer 15

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.

Answer 16

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!