The error message you provided is related to using the MySQLi extension in PHP to connect to a MySQL database. This error message can occur when attempting to establish a connection to a MySQL database using the mysqli::real_connect() method. Let’s break down the components of the error message:
mysqli:real_connect(): This is a reference to thereal_connect()method of the MySQLi extension, which is used to establish a connection to a MySQL database.(HY000/1045/1698/28000): This part of the error message contains error codes related to the specific type of error that occurred. The error codes have the following meanings:HY000: The general SQLSTATE error category.1045: This is the specific error code that indicates an authentication error. It means that the username and password combination provided for connecting to the MySQL database is incorrect.1698: This code can also indicate an authentication plugin issue in MySQL.28000: This is another error code related to authentication issues in MySQL.
Access denied for user 'root'@'localhost': This part of the error message specifies that the user ‘root’ tried to access the database from the ‘localhost’ server, but the access was denied.(using password: No/Yes): This part indicates whether a password was provided for the database connection. In your error message, it seems to indicate that it’s not clear whether a password was used (“No/Yes”).
So, In this tutorial, you will find two solutions for mysqli::real_connect(): (hy000/1045/1698/28000): access denied for user ‘root’@’localhost’ (using password: no/yes) llinux ubuntu mysql.
Now, open your terminal or command line and execute the following command into it log into MySQL; is as follows:
mysql -u root -p
You can also see this if you log into MySQL using an IDE like MySQL Workbench. Or even if you use phpMyAdmin.
How to Fix mysqli_real_connect(): (hy000/1045/1698/28000) Access Denied for User ‘root’@’localhost’ (using password no/yes)
There are two solutions for fixing mysqli::real_connect(): (hy000/1045/1698/28000): access denied for user ‘root’@’localhost’ (using password: no/yes) in Linux Ubuntu:
- Solution 1: Change Password using Sudo
- Solution 2: Edit My.cnf File
Solution 1: Change Password using Sudo
By using the following steps in first solution, you can fix mysql access denied for user root@localhost error in linux ubuntu:
Step 1: Open the Terminal
Firstly, open your terminal or command line.
Step 2: Start MySQL with Sudo
Execute the following command on terminal or command line to start mysql with sudo:sudo mysql:
sudo mysql
Step 3: Enter the password
Once you have executed above given command, you will a prompt for entering a password into your terminal or command line.
Step 4: Update the root password
Execute the following command on terminal or command line to update the auth_plugin to mysql_native_password, and the password for the root account:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_new_password';
Replace your_new_password with a new secure password that you want to use for the root account.
Step 5: Flush the privileges
Now, execute the following command on terminal or command line to refresh the grant tables and apply your changes:
FLUSH PRIVILEGES;
Then execute the following command on terminal or command line to can confirm that the new authentication method, or plugin, is used by selecting from the mysql.user table:
SELECT user, plugin FROM mysql.user
Or, you can execute from mysql console by executing the following command:
Exit
That’s it, now you can login to your mysql workbench with root user and new password:
mysql -u root -p
Solution 2: Edit My.cnf File
If the above solution did not for you work, you may need to edit the mysql.cnf file to allow for changes to the root user.
Step 1: Open the Terminal
Firstly, open your terminal or command line.
Step 2: Open the my.cnf file
Now, execute the following command to open my.cnf file:
sudo nano /etc/my.cnf OR sudo nano /etc/mysql/my.cnf
Step 3: Add Skip grant
To bypass the “access denied for user ‘root’@’localhost’ (using password yes)” error, add the following line to the [mysqld] section of the my.cnf file:
[mysqld] skip-grant-tables
Step 4: Restart the MySQL server
Execute the following command on terminal or command line to restart mysql server for apply the above make changes:
sudo service mysql restart OR sudo systemctl restart mysql
Step 5: Login to the root
Execute the following command on terminal or command line to login as root user in mysql:
mysql -u root -p
Step 6: Flush the privileges
Now, execute the following command on terminal or command line to refresh the grant tables and apply your changes:
FLUSH PRIVILEGES;
Step 7: Set New Root Login Password
Execute the following command on terminal or command line to set new password for the root account:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_password';
OR
UPDATE mysql.user SET authentication_string=PASSWORD('new_password') WHERE User='root';
Replace your_new_password with a new secure password that you want to use for the root account.
Step 8: Remove the skip-grant-tables Option
After resetting the ‘root’ user password, you should remove the skip-grant-tables option from the my.cnf file to re-enable MySQL’s authentication system. Open the my.cnf file and delete the ‘skip-grant-tables’ line that you added earlier. Save the my.cnf file and exit the text editor.
Step 9: Restart the MySQL server
Execute the following command on terminal or command line to restart mysql server for apply the above make changes:
sudo service mysql restart OR sudo systemctl restart mysql
Step 10: Login to the root
That’s it, now you can login to your mysql workbench with root user and new password:
mysql -u root -p
Conclusion
In conclusion, “access denied for user ‘root’@’localhost’ (using password: yes/no)” can be a frustrating error to deal with, but there are several steps that you can take to troubleshoot and resolve the issue. By using these two solutions should hopefully solve the problem for you, and you will be able to identify and resolve the underlying cause of the error message.
