MySQL Error: : 'Access denied for user 'root'@'localhost'
- Open and edit
/etc/my.cnf
or/etc/mysql/my.cnf
, depending on your distribution. - Add
skip-grant-tables
under[mysqld]
- Restart MySQL
- You should be able to log in to MySQL now using the below command
mysql -u root -p
- Run
mysql> flush privileges;
- Set new password by
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
- Go back to /etc/my.cnf and remove/comment skip-grant-tables
- Restart MySQL
- 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:
- You can set the root user to use the
mysql_native_password
plugin - You can create a new
db_user
with yousystem_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
):
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:
- Open taskmanager
- Go to service tab
- Open services
- For me, two services run MySQL and MySQL80.
- I stop the MySQL service and start the MySQL80 service.
- 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
Recommended SQL Database Design For Tags or Tagging
Ordering by the Order of Values in a SQL In() Clause
Index For Finding an Element in a Json Array
Table Naming Dilemma: Singular Vs. Plural Names
How to Deal With SQL Column Names That Look Like SQL Keywords
How to Spool to a CSV Formatted File Using Sqlplus
Convert Datetime Column from Utc to Local Time in Select Statement
Select Max Value of Each Group
Group By/Aggregate Function Confusion in Sql
How to Declare a Variable in a Postgresql Query
How to Update Two Tables in One Statement in SQL Server 2005
How to Delete Using Inner Join With SQL Server
SQL Server - Return Value After Insert
Is There Any Function in Oracle Similar to Group_Concat in MySQL