Access denied for user 'root@localhost' (using password:NO)

To reset the MySQL root password and configure MySQL to run without grant tables using skip-grant-tablesFollow these steps:

1. Edit the MySQL Configuration File (my.ini)

• Navigate to the MySQL configuration file:

  • If you are using XAMPP, the file will likely be located at:

    C:\xampp\mysql\bin\my.ini
    

  • Open my.ini with a text editor (e.g., Notepad or Notepad++) with administrative privileges.

• Add the skip-grant-tables line: Inside the [mysqld] section, add skip-grant-tables to disable the privilege checks temporarily. This allows you to connect to MySQL without a password and reset the root password.

  Your configuration should look like this:

  [mysqld]
  port = 3306
  socket = "C:/xampp/mysql/mysql.sock"
  basedir = "C:/xampp/mysql"
  tmpdir = "C:/xampp/tmp"
  datadir = "C:/xampp/mysql/data"
  pid_file = "mysql.pid"
  # enable-named-pipe
  key_buffer = 16M
  max_allowed_packet = 1M
  sort_buffer_size = 512K
  net_buffer_length = 8K
  read_buffer_size = 256K
  read_rnd_buffer_size = 512K
  myisam_sort_buffer_size = 8M
  log_error = "mysql_error.log"
  skip-grant-tables  # Add this line
  # Change here for bind listening
  # bind-address="127.0.0.1"
  # bind-address = ::1
  

• Save the changes and close the file.

2. Restart MySQL Server in Skip-Grant-Table Mode

• Open XAMPP Control Panel.

• Stop the MySQL service if it's running.

• Start MySQL again by clicking the Start button next to MySQL.

3. Access MySQL Without Password

Since the skip-grant-tables option disables the need for a password, you can now access MySQL without any password.

• Open the Command Prompt or PowerShell.

• Enter the following command to access MySQL without a password:

  mysql -u root
  

You should now be in the MySQL shell without requiring a password.

4. Reset the Root Password

Once you're inside the MySQL prompt, follow these steps to reset the root password:

1. Select the MySQL database:

   USE mysql;
   

2. Update the root password. Replace 'YourNewPassword' with the password you want to set.

   UPDATE user SET authentication_string = PASSWORD('YourNewPassword') WHERE User = 'root';
   

3. Flush privileges to apply the changes:

   FLUSH PRIVILEGES;
   

4. Exit MySQL:

   EXIT;
   

5. Stop and Restart MySQL

• Go back to XAMPP Control Panel.

• Stop the MySQL service.

• Remove skip-grant-tables from the my.ini file (to re-enable the usual password protection).

• Start MySQL again from the XAMPP Control Panel.

6. Test the New Root Password

• Open a new Command Prompt or PowerShell window.

• Run the following command to check if the password was set successfully:

  mysql -u root -p
  

• Enter the new password when prompted, and you should be logged into MySQL.

7. Configuring phpMyAdmin (if needed)

If you are using phpMyAdmin to manage your MySQL databases, you may need to update its configuration file to reflect the new root password:

1. Navigate to your XAMPP installation directory, typically:

   C:\xampp\phpMyAdmin\config.inc.php
   

2. Open the file and find the following section:

   $cfg['Servers'][$i]['user'] = 'root';
   $cfg['Servers'][$i]['password'] = '';  // Set this to your new password if it's empty
   

3. Update the password line with your new root password, like this:

   $cfg['Servers'][$i]['password'] = 'YourNewPassword';
   

4. Save the file.

8. Access phpMyAdmin

Now you can open phpMyAdmin by visiting:

http://localhost/phpmyadmin

Log in with the username root and the new password you just set.

Important Notes:

• The skip-grant-tables option allows full access to the MySQL database, bypassing authentication checks. Use this with caution and only when you need to reset the password or fix a MySQL issue.

• Always ensure that you remove it skip-grant-tables after you've finished the reset, as leaving it active could expose your MySQL server to unauthorized access.

Let me know if you need further assistance with any of these steps!