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;
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).
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.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Note: For MySQL 5.7+, please see the answer from Lahiru to this question. That contains more current information.
For MySQL < 5.7:
The default root password is blank (i.e., an empty string), not root
. So you can just log in as:
mysql -u root
You should obviously change your root password after installation:
mysqladmin -u root password [newpassword]
In most cases you should also set up individual user accounts before working extensively with the database as well.
Connect failed: Access denied for user 'root'@'localhost' (using password: YES) from php function
I solved in this way:
I logged in with root username
mysql -u root -p -h localhost
I created a new user with
CREATE USER 'francesco'@'localhost' IDENTIFIED BY 'some_pass';
then I created the database
CREATE DATABASE shop;
I granted privileges for new user for this database
GRANT ALL PRIVILEGES ON shop.* TO 'francesco'@'localhost';
Then I logged out root and logged in new user
quit;
mysql -u francesco -p -h localhost
I rebuilt my database using a script
source shop.sql;
And that's it.. Now from php works without problems with the call
$conn = new mysqli("localhost", "francesco", "some_pass", "shop");
Thanks to all for your time :)
Related Topics
Codeigniter Default Controller in a Sub Directory Not Working
Weird Error Using PHP Simple HTML Dom Parser
Interleaving Multiple Arrays into a Single Array
Parse JSON String Contents into PHP Array
How to Convert a Docx Document to HTML Using PHP
Echo Menu Tree with Recursive Function
How to Load CSS File in Codeigniter
Woocommerce Change Loading Spinner Icon
Clean Links to PHP-Generated JavaScript and CSS
Remove or Hide "Add New" Button on Woocommerce on Bulk Order Panel
How to Format Excel File with Styles, Fonts, Colors, Tables etc with Pure PHP
Hide Variable Product Dropdown That Has a Unique Variation Selected by Default in Woocommerce
How to Make a Bbcode to Parse Url Tags into Links
How to Add a Class to Every Nth Item in a PHP Loop (Wordpress)