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

Satsuma

There is no code...I just reinstalled the entire Ubuntu with web server, db and so... and now the one thing I need is to login through phpmyadmin or PHP under username root with password root... – Satsuma

post your new user creation & grant priv related code here. – Papaveraceous

Creating user: CREATE USER 'username'@'localhost' IDENTIFIED BY 'pass'; and adding privileges: GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost'; then FLUSH PRIVILEGES;...when I do this, I can login but not create database.... – Satsuma

Grant priv should like this as your user name is username : GRANT ALL PRIVILEGES ON * . * TO 'username'@'localhost'; – Papaveraceous

I made a mistake while writing comment... As I said: I cant login as root anyway except terminal, but as new user I cant create database even with all privileges – Satsuma

default password is empty like this $pass = ''; – Parlando

I tried it also with empty password. It was first, what I did... – Satsuma

and host name like this $host = 'localhost'; – Parlando

It's probably a very bad idea from a security standpoint to log into phpmyadmin (or anyting with a public face) as root. It removes one more obstacle for someone trying to crack your system. – Lichter

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.

Jamison

so development setups everywhere have to now have an extra few steps done before coding can commence :( – Wendy

Oh man! I've spent around 3h debugging every single line of code... This is strange, because I have the same environment on multiple servers, and didn't ever get this error. – Japhetic

i got the same problem with this specific version of mysql >> mysql Ver 15.1 Distrib 10.3.22-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2. I have an older version and it accepts connection using root user, but sure it is not best practice. – Huffish

Is there a way to create a user via a PHP script instead of manually via the CLI? – Astern

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

Heliozoan

I just executed this on a MariaDB 10* with a user starting with a "i". This results in a weird syntax error on " 'i "...it interprets that as a i accent aigu Í...and retuns the syntax error. odd. I thought I drop that here. – Hospodar

Might want to add the WITH GRANT OPTION ... https://mcmap.net/q/80981/-how-to-grant-all-privileges-to-root-user-in-mysql-8-0/1815624 – Schlimazel

@Schlimazel thanks - added – Heliozoan

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

Cavorilievo

This solution was successful when installing Nextcloud on Debian Stretch – Blastula

@Cavorilievo this you command has generated a big problem for me, now after your commendations, I can not even access mysql with the mysql command, error: mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) – Conservator

@Cavorilievo my mysql worked perfectly before your command, I was just trying to login as phpmyadmin root, and now I can not even use the mysql console via terminal and not even use any re-configuration hint taught in the forums, each command having, is a different error, where before your command all was well! – Conservator

Worked for me on Ubuntu 18.04 with MariaDB 10.1 – Sybaris

@ArianeMartinsGomesDoRego For the problem of Access denied for user 'root'@'localhost' (using password: NO), try mysql -u root -p instead of mysql -u root. And @Cavorilievo 's original answer solves my problem. – Bria

Worked for me on Debian 9 with MariaDB – Ligation

If this did not work for you and is causing issues. You can UNDO it with update user set plugin='mysql_native_password' where User='root'; – Conversant

This solution worked for me on Pop_OS! 20.04 with mariadb Ver 15.1 Distrib 10.3.25-MariaDB. Thanks! – Urethroscope

This answer should be removed since I just found out someone followed this advice and broke a server. The moment you do this, you do not get access from the console nor phpmyadmin. Tested on ubuntu 23.04, Fedora 38 & Debian 11.6 – Fellini

WARNING THIS PLUGIN CAUSED COMPLETE BREAK OF MYSQL AND ACCESS AS ROOT !! – Trinidadtrinitarian

Geez got stuck with this answer. Now I had another problem which is unable to run sudo mysql on server. And another problem after... Waste of time. Here is log problem User entry 'root'@'localhost' has an empty plugin value. The user will be ignored and no one can login with this user anymore. – Seaweed

Here is fix for this answer: https://mcmap.net/q/244309/-can-39-t-reset-root-password-with-skip-grant-tables-on-ubuntu-16 It was caused because you rewrite plugim from 'mysql_native_password' to '' so when you have access to mysql again (check link) then you need to type UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE User = 'root' – Seaweed

Answer 4

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;

Ducan

omg.. this was killing me; some apps were able to connect using root@localhost & some weren't. THANKS! – Electrodialysis

this one is simple and effective – Patentor

Answer 5

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;

Stubbed

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;

Historic

Thanks so much! This step worked for me, and I searched for more than an hour. I'm sorry I only have one upvote to give you :-) – Merkel

1) Why line numbers? Make it completely copy/paste. 2) And if you need to specify 'root' with mysql command you already have problems - it will prompt for a password - which will be wrong for auth-sockets. sudo su - then mysql is all you need if using auth-socket. It will use the current user login access for mysql access. Please correct it. sudo mysql -u root; should be sudo su - (if not root) then simply mysql. You don't need a semicolon for shell command line completion - enter is enough. It's redundant. – Bacchant

Also your mysql UPDATE statement is not going to work on newer versions. After logging in as root to mysql: ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'; THEN ALTER USER 'root'@'localhost' IDENTIFIED BY 'pass1234'; THEN FLUSH.. – Bacchant

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

Ebbie

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.

Baldheaded

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

Printer

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.

Bearded

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.

Stagehand

I actually had two root users with different plugin and when i removed one with auth_socket i was able to login again. – Helper

Answer 12

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.

Lonee

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Roveover

Answer 13

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.

Dezhnev

Answer 14

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

Bess

Answer 15

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.

Sosna

OK, this is what I tried, but I can't create database even if I made second account with all privileges. Also I reinstalled server several times... – Satsuma

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!

Lizzettelizzie

Answer 17

I did this on ubuntu terminal 

sudo mysql -u root

CREATE USER 'newusername'@'%' IDENTIFIED BY 'newpassword';

GRANT ALL PRIVILEGES ON . TO 'newusername'@'%' WITH GRANT OPTION;

Charis