Debug School

rakesh kumar
rakesh kumar

Posted on • Updated on

PHP Error: Warning: mysqli::__construct(): (HY000/1044): Access denied for user

Error
Warning: mysqli::__construct(): (HY000/1044): Access denied for user

how-to-fix-warning-mysqli-construct-hy000-1045-access-denied-for-user
how-to-fix-warning-mysqli-construct-hy000-1045-access-denied-for-user
access-denied-for-user-root-localhost-mysql

user-rootlocalhost-using-password-yes-no-privileges

mysql-error-access-denied-for-user-rootlocalhost

Image description

Image description

Example Code – 1 

<?php
function OpenCon()
 {
 $dbhost = "localhost";
 $dbuser = "testuser";
 $dbpass = 'GHSJ$D47687';
 $db = "vijayji";
 $conn = new mysqli($dbhost, $dbuser, $dbpass, $db) or die("Connect failed: %s\n". $conn -> error);

 return $conn;
 }

function CloseCon($conn)
 {
 $conn -> close();
}

$conn = OpenCon();
CloseCon($conn);
Enter fullscreen mode Exit fullscreen mode

Example Code – 2

<?php
$servername = "localhost";
$username = "username";

// PASSWORD MUST BE IN SINGLE QUOTE
$password = 'password';

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
Enter fullscreen mode Exit fullscreen mode

Example Code – 2

mysql-error-access-denied-for-user-rootlocalhost

Try remove if exists ~/.my.cnf file
Enter fullscreen mode Exit fullscreen mode

Example Code – 4

All solutions I found were much more complex than necessary and none worked for me. Here is the solution that solved my problem. There isn't any need to restart mysqld or start it with special privileges.

sudo mysql

-- for MySQL

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
Enter fullscreen mode Exit fullscreen mode

-- for MariaDB

ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('root');
Enter fullscreen mode Exit fullscreen mode

Example Code – 5

  1. Open and edit /etc/my.cnf or /etc/mysql/my.cnf, depending on your distribution.
  2. Add skip-grant-tables under [mysqld]
  3. Restart MySQL
  4. You should be able to log in to MySQL now using the below command mysql -u root -p
  5. Run mysql> flush privileges;
  6. Set new password by ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';
  7. Go back to /etc/my.cnf and remove/comment skip-grant-tables
  8. Restart MySQL
  9. Now you will be able to login with the new password mysql -u root -p

Example Code – 6

Next, check which authentication method each of your MySQL user accounts use with the following command:

SELECT user,authentication_string,plugin,host FROM mysql.user;
Enter fullscreen mode Exit fullscreen mode

Output

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)
Enter fullscreen mode Exit fullscreen mode

In this example, you can see that the root user does in fact authenticate using the auth_socket plugin. To configure the root account to authenticate with a password, run the following ALTER USER command. Be sure to change password to a strong password of your choosing, and note that this command will change the root password you set in Step 2:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Enter fullscreen mode Exit fullscreen mode

Then, run FLUSH PRIVILEGES which tells the server to reload the grant tables and put your new changes into effect:

FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

Check the authentication methods employed by each of your users again to confirm that root no longer authenticates using the auth_socket plugin:

SELECT user,authentication_string,plugin,host FROM mysql.user;
Enter fullscreen mode Exit fullscreen mode

Output

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *3636DACC8616D997782ADD0839F92C1571D6D78F | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)
Enter fullscreen mode Exit fullscreen mode

You can see in this example output that the root MySQL user now authenticates using a password. Once you confirm this on your own server, you can exit the MySQL shell:

exit

Example Code – 7

I tried many steps to get this issue corrected. There are so many sources for possible solutions to this issue that is is hard to filter out the sense from the nonsense. I finally found a good solution here:

Step 1: Identify the database version

mysql --version
Enter fullscreen mode Exit fullscreen mode

You'll see some output like this with MySQL:

mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper
Or output like this for MariaDB:

mysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1
Make note of which database and which version you're running, as you'll use them later. Next, you need to stop the database so you can access it manually.

Step 2: Stopping the database server

To change the root password, you have to shut down the database server beforehand.

You can do that for MySQL with:

sudo systemctl stop mysql
Enter fullscreen mode Exit fullscreen mode 
And for MariaDB with:

sudo systemctl stop mariadb
Enter fullscreen mode Exit fullscreen mode

Step 3: Restarting the database server without permission checking

If you run MySQL and MariaDB without loading information about user privileges, it will allow you to access the database command line with root privileges without providing a password. This will allow you to gain access to the database without knowing it.

To do this, you need to stop the database from loading the grant tables, which store user privilege information. Because this is a bit of a security risk, you should also skip networking as well to prevent other clients from connecting.

Start the database without loading the grant tables or enabling networking:

sudo mysqld_safe --skip-grant-tables --skip-networking &
Enter fullscreen mode Exit fullscreen mode

The ampersand at the end of this command will make this process run in the background so you can continue to use your terminal.

Now you can connect to the database as the root user, which should not ask for a password.

mysql -u root
Enter fullscreen mode Exit fullscreen mode

You'll immediately see a database shell prompt instead.

MySQL Prompt

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
MariaDB Prompt

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>
Now that you have root access, you can change the root password.

Step 4: Changing the root password

mysql> FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

Now we can actually change the root password.

For MySQL 5.7.6 and newer as well as MariaDB 10.1.20 and newer, use the following command:

m

ysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
For MySQL 5.7.5 and older as well as MariaDB 10.1.20 and older, use:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
Enter fullscreen mode Exit fullscreen mode

Make sure to replace new_password with your new password of choice.

Note: If the ALTER USER command doesn't work, it's usually indicative of a bigger problem. However, you can try UPDATE ... SET to reset the root password instead.

[IMPORTANT] This is the specific line that fixed my particular issue:

mysql> UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE User = 'root' AND Host = 'localhost';
Enter fullscreen mode Exit fullscreen mode

Remember to reload the grant tables after this.

In either case, you should see confirmation that the command has been successfully executed.

Query OK, 0 rows affected (0.00 sec)
The password has been changed, so you can now stop the manual instance of the database server and restart it as it was before.

Step 5: Restart the Database Server Normally

The tutorial goes into some further steps to restart the database, but the only piece I used was this:

For MySQL, use:

sudo systemctl start mysql
Enter fullscreen mode Exit fullscreen mode

For MariaDB, use:

sudo systemctl start mariadb
Enter fullscreen mode Exit fullscreen mode

Now you can confirm that the new password has been applied correctly by running:

mysql -u root -p
Enter fullscreen mode Exit fullscreen mode

The command should now prompt for the newly assigned password. Enter it, and you should gain access to the database prompt as expected.

Example Code – 8 

mysql> UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE User = 'root' AND Host = 'localhost';
Enter fullscreen mode Exit fullscreen mode

Top comments (0)