How to resolve the error 1045 (28000): access denied for user 'root'@'localhost' (using password: yes)?
UBUNTU 22.04.1, mysql ver 8.0.30
(1) I get sudo apt install mysql-server then
sudo mysql_secure_installation
Get in loop this error for each complicated passwords I tried: (contained: '<>&(*Pp1') returns↓
Failed! Error: SET PASSWORD has no significance for user ‘root’@’localhost’ as the authentication method used doesn’t store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.(2) Then try: sudo mysql alter user 'root'@'localhost' identified with mysql_native_password by 'password';
(3) Now ever returns:
error access denied for user 'root'@'localhost' (using password: YES) error 1045 (28000): access denied for user 'root'@'localhost' (using password: yes)(4)
Then try
sudo apt-get remove --purge mysql*
sudo apt-get purge mysql*
sudo apt-get autoremove
sudo apt-get autoclean
sudo apt-get remove dbconfig-mysql
sudo apt-get dist-upgrade
sudo apt-get install mysql-server
(5)
Now ever shows:
ERROR 1045 (28000)
or
access denied for user 'root'@'localhost'
I can not refresh mysql with reinstall.
How can I retrieve the mysql and start step (1) newly cleaned from pre password configurations?
To resolve the error 1045 (28000): access denied for user 'root'@'localhost' (using password: yes) -
sudo dpkg-reconfigure mysql-server-8.0
( use sudo dpkg --get-selections | grep sql to get your version)
completely remove MySQLconfig and data?
sudo rm -r /var/lib/mysql
(Users and passwords (including the root password) are stored in the database itself, and the default data dir is /var/lib/mysql.)
Answer (1)
The error message you're encountering, "Error 1045 (28000): Access Denied for user 'root'@'localhost' (using password: YES)," typically indicates that the MySQL server rejected the connection attempt from the user 'root' because the provided password was incorrect. Here are steps to resolve this issue:
Check Password: Double-check the password you are using to connect to the MySQL server with the 'root' user. Ensure there are no typos or mistakes in the password. Remember that MySQL passwords are case-sensitive.
Reset Password: If you suspect that the password might be incorrect or if you've forgotten it, you can reset the password for the 'root' user. This requires access to the MySQL server's command line or administrative interface. Here's how you can do it:a. Log in to the MySQL server as a user with administrative privileges.b. Use the following SQL command to reset the password for the 'root' user:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
Replace 'new_password' with the new password you want to set.Verify Privileges: Ensure that the 'root' user has the necessary privileges to connect to the MySQL server from localhost. You can check the user's privileges by running the following SQL command:
SHOW GRANTS FOR 'root'@'localhost';If the user does not have the necessary privileges, you may need to grant them using the GRANT statement.
Check Host Access: Verify that the MySQL server is configured to allow connections from the 'root' user originating from localhost. Sometimes, MySQL servers may be configured to only allow connections from specific hosts. You can check the server's host access control settings in the MySQL configuration file (my.cnf) or by running the following SQL command:
SELECT host, user FROM mysql.user WHERE user='root';
Ensure that there is an entry for 'root'@'localhost' with the correct privileges.
Restart MySQL Server: After making any changes to the MySQL configuration or user accounts, restart the MySQL server to apply the changes.
Review Error Logs: If the issue persists, review the MySQL error logs for any additional information about why the connection attempt is being rejected. The error logs are usually located in the MySQL data directory.
By following these steps and addressing any issues with the password, privileges, and host access, you should be able to resolve the "Error 1045 (28000): Access Denied for user 'root'@'localhost'" error in MySQL.