MySQL Error:: 'Access Denied For User 'Root'@'Localhost'

MySQL Error: : 'Access denied for user 'root'@'localhost'

  1. Open and edit /etc/my.cnf or /etc/mysql/my.cnf, depending on your distribution.
  2. Add skip-grant-tables under [mysqld]
  3. Restart MySQL
  4. You should be able to log in to MySQL now using the below command mysql -u root -p
  5. Run mysql> flush privileges;
  6. Set new password by ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
  7. Go back to /etc/my.cnf and remove/comment skip-grant-tables
  8. Restart MySQL
  9. Now you will be able to login with the new password mysql -u root -p

Access Denied for User 'root'@'localhost' (using password: YES) - No Privileges?

Use the instructions for resetting the root password - but instead of resetting the root password, we'll going to forcefully INSERT a record into the mysql.user table

In the init file, use this instead

INSERT INTO mysql.user (Host, User, Password) VALUES ('%', 'root', password('YOURPASSWORD'));
GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

On some systems, like Ubuntu, MySQL is using the Unix auth_socket plugin by default.

Basically it means that: db_users using it, will be "authenticated" by the system user credentials. You can see if your root user is set up like this by doing the following:

sudo mysql -u root # I had to use "sudo" since it was a new installation

mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;

+------------------+-----------------------+
| User | plugin |
+------------------+-----------------------+
| root | auth_socket |
| mysql.sys | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+

As you can see in the query, the root user is using the auth_socket plugin.

There are two ways to solve this:

  1. You can set the root user to use the mysql_native_password plugin
  2. You can create a new db_user with you system_user (recommended)

Option 1:

sudo mysql -u root # I had to use "sudo" since it was a new installation

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

sudo service mysql restart

Option 2: (replace YOUR_SYSTEM_USER with the username you have)

sudo mysql -u root # I had to use "sudo" since it was a new installation

mysql> USE mysql;
mysql> CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED BY 'YOUR_PASSWD';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOUR_SYSTEM_USER'@'localhost';
mysql> UPDATE user SET plugin='auth_socket' WHERE User='YOUR_SYSTEM_USER';
mysql> FLUSH PRIVILEGES;
mysql> exit;

sudo service mysql restart

Remember that if you use option #2 you'll have to connect to MySQL as your system username (mysql -u YOUR_SYSTEM_USER).

Note: On some systems (e.g., Debian 9 (Stretch)) the 'auth_socket' plugin is called 'unix_socket', so the corresponding SQL command should be: UPDATE user SET plugin='unix_socket' WHERE User='YOUR_SYSTEM_USER';

From andy's comment it seems that MySQL 8.x.x updated/replaced the auth_socket for caching_sha2_password. I don't have a system setup with MySQL 8.x.x to test this. However, the steps above should help you to understand the issue. Here's the reply:

One change as of MySQL 8.0.4 is that the new default authentication plugin is 'caching_sha2_password'. The new 'YOUR_SYSTEM_USER' will have this authentication plugin and you can log in from the Bash shell now with "mysql -u YOUR_SYSTEM_USER -p" and provide the password for this user on the prompt. There isn’t any need for the "UPDATE user SET plugin" step.

For the 8.0.4 default authentication plugin update, see MySQL 8.0.4: New Default Authentication Plugin: caching_sha2_password.

MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)

You probably have an anonymous user ''@'localhost' or ''@'127.0.0.1'.

As per the manual:

When multiple matches are possible, the server must determine which of
them to use. It resolves this issue as follows: (...)

  • When a client attempts to connect, the server looks through the rows [of table mysql.user] in sorted order.
  • The server uses the first row that matches the client host name and user name.

(...)
The server uses sorting rules that order rows with the most-specific Host values first.
Literal host names [such as 'localhost'] and IP addresses are the most specific.

Hence, such an anonymous user would "mask" any other user like '[any_username]'@'%' when connecting from localhost.

'bill'@'localhost' does match 'bill'@'%', but would match (e.g.) ''@'localhost' beforehands.

The recommended solution is to drop this anonymous user (this is usually a good thing to do anyways).


Below edits are mostly irrelevant to the main question. These are only meant to answer some questions raised in other comments within this thread.

Edit 1

Authenticating as 'bill'@'%' through a socket.



root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass --socket=/tmp/mysql-5.5.sock
Welcome to the MySQL monitor (...)

mysql> SELECT user, host FROM mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| bill | % |
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
+------+-----------+
4 rows in set (0.00 sec)

mysql> SELECT USER(), CURRENT_USER();
+----------------+----------------+
| USER() | CURRENT_USER() |
+----------------+----------------+
| bill@localhost | bill@% |
+----------------+----------------+
1 row in set (0.02 sec)

mysql> SHOW VARIABLES LIKE 'skip_networking';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| skip_networking | ON |
+-----------------+-------+
1 row in set (0.00 sec)

Edit 2

Exact same setup, except I re-activated networking, and I now create an anonymous user ''@'localhost'.



root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql
Welcome to the MySQL monitor (...)

mysql> CREATE USER ''@'localhost' IDENTIFIED BY 'anotherpass';
Query OK, 0 rows affected (0.00 sec)

mysql> Bye

root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass \
--socket=/tmp/mysql-5.5.sock
ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)
root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass \
-h127.0.0.1 --protocol=TCP
ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)
root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass \
-hlocalhost --protocol=TCP
ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)

Edit 3

Same situation as in edit 2, now providing the anonymous user's password.



root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -panotherpass -hlocalhost
Welcome to the MySQL monitor (...)

mysql> SELECT USER(), CURRENT_USER();
+----------------+----------------+
| USER() | CURRENT_USER() |
+----------------+----------------+
| bill@localhost | @localhost |
+----------------+----------------+
1 row in set (0.01 sec)

Conclusion 1, from edit 1: One can authenticate as 'bill'@'%'through a socket.

Conclusion 2, from edit 2: Whether one connects through TCP or through a socket has no impact on the authentication process (except one cannot connect as anyone else but 'something'@'localhost' through a socket, obviously).

Conclusion 3, from edit 3: Although I specified -ubill, I have been granted access as an anonymous user. This is because of the "sorting rules" advised above. Notice that in most default installations, a no-password, anonymous user exists (and should be secured/removed).

Access denied for user 'root '@'localhost' when connecting to mysql

Remove the spaces on the sides of the = signs in your application.properties. And also make sure you have no spaces after the values (notice in your question title it's trying to log in as root with a space after the t).

You do have spaces after root in the copy/pasted data in the question (couldn't possibly know in your real file)

Look at a screenshot of your question with the text selected (notice after root):

Copy/paste spaces image

Access denied for user 'root@localhost' (using password:NO)

You can reset your root password. Have in mind that it is not advisable to use root without password.

How to resolve access denied for user 'root'@'localhost' in mysql workbench windows

Everytime I open the mysql workbench, I see the access denied error 'root'@'localhost'. The below resolution works for me:

  1. Open taskmanager
  2. Go to service tab
  3. Open services
  4. For me, two services run MySQL and MySQL80.
  5. I stop the MySQL service and start the MySQL80 service.
  6. This resolves my issue.

Access denied for user 'root'@'localhost' (using password: YES) (Mysql::Error)

You need to grant access to root from localhost. Check this ubuntu help



Related Topics



Leave a reply



Submit