MySQL is a database management tool that is commonly used in Linux environments. MySQL server in Linux provides a platform for storing and managing data, allowing users to easily access and manipulate that data using SQL, a standardized language for working with databases.
While trying to work with MySQL as the root user, sometimes we encounter the “access denied for user root localhost”, which will be discussed throughout this guide with the following timeline:
- Reason 1: Access Not Yet Granted
- Solution 1: Set the Password and Login
- Bonus Tip: Change the mysql Server Password
Let’s start with the first reason.
Reason 1: Access Not Yet Granted
After installing the MySQL in the system, we try to log in to MySQL as the root user, and the access denied error occurs. This error shows that the user does not access the root resources, and the following error displays on the screen:
$ mysql -u root
Let’s solve this error using the ALTER command.
Solution 1: Set the Password and Login
We can enable the root login user to the MySQL server using the ALTER command, which can be done using the following steps:
Log in to the MySQL server with sudo privileges using the following command:
Note: The sudo user password will be entered to log in to MySQL server.
$ sudo mysql
The MySQL prompt will open up as shown above.
Use the below ALTER command to change the authentication method for login to “mysql_native_password”. The password used for root login in the below command is changed to “Password”, which will be replaced with your desired password. After entering the ALTER command, click the “Enter” button:
> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Password';
The root user authentication method is changed.
Exit the MySQL prompt using the following exit command:
> exit
Let’s log in as the root user by entering the password using the following command:
$ mysql -u root -p
The output shows that the user is logged in as the root user with the password entered in the ALTER command.
Bonus Tip: Change the MySQL Server Password
If you have already set the root user password but forgot the password, you can follow these steps to reset the root user password.
Check the version of the MySQL server utilizing the following command:
$ dpkg --get-selections | grep mysql
The MySQL version installed in my system is 8.0.
Use the below command to change the root password for the MySQL server by replacing the 8.0 with your MySQL version:
$ sudo dpkg-reconfigure mysql-server-8.0
That’s how the access denied from the user root localhost error can be fixed.
Conclusion
The MySQL error “access denied for user root localhost” can be caused when you try to login to the MySQL remote server as the root login. This error can be removed using the “ALTER” command, which changes the login authentication method to “root_native_password” and sets the root login password. After using the ALTER command, the user can log in as root to the MySQL server with the “mysql -u root -p” command.
