mysqli:real_connect() (hy000): access denied for user ‘root’@’localhost’ (using password: No/Yes)

mysqli:real_connect() (hy000): access denied for user ‘root’@’localhost’ (using password: No/Yes)

Try to access phpmyadmin db and get the error mysqli::real_connect(): (hy000/1045/1698/28000): Access denied for user ‘root’@’localhost’ (using password: yes or no), This means that incorrect database details are configured on the mysql server.

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:

  1. mysqli:real_connect(): This is a reference to the real_connect() method of the MySQLi extension, which is used to establish a connection to a MySQL database.
  2. (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.
  3. 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.
  4. (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”).

How to Fix mysqli_real_connect(): (hy000/1045/1698/28000) Access Denied for User ‘root’@’localhost’ (using password no/yes)

There are two solutions to fix the error mysqli::real_connect(): (hy000/1045/1698/28000): Access denied for user ‘root’@’localhost’ (using password: no or yes) on Linux Ubuntu:

First, log in to the mysql server with your terminal or command line by running the following command:

mysql -u root -p

Solution 1: Change Password using Sudo

By using the following steps in the first solution, you can fix the mysqli_real_connect(): (hy000/1045/1698/28000) 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.

Recommended Tutorials

AuthorAdmin

Greetings, I'm Devendra Dode, a full-stack developer, entrepreneur, and the proud owner of Tutsmake.com. My passion lies in crafting informative tutorials and offering valuable tips to assist fellow developers on their coding journey. Within my content, I cover a spectrum of technologies, including PHP, Python, JavaScript, jQuery, Laravel, Livewire, CodeIgniter, Node.js, Express.js, Vue.js, Angular.js, React.js, MySQL, MongoDB, REST APIs, Windows, XAMPP, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL, and Bootstrap. Whether you're starting out or looking for advanced examples, I provide step-by-step guides and practical demonstrations to make your learning experience seamless. Let's explore the diverse realms of coding together.

Leave a Reply

Your email address will not be published. Required fields are marked *