If you've set a password for the root user in MySQL but it's not working, the issue could be related to the multiple root user entries with different host specifications (127.0.0.1, ::1, localhost). Here’s what could be causing the problem and how to fix it:
🔎 Possible Reasons and Fixes
1️⃣ Password Not Set for All root Entries
Since MySQL treats root@localhost, root@127.0.0.1, and root@::1 as separate users, the password may only be set for one of them while others still have no password.
👉 Check which root users have passwords:
SELECT user, host, authentication_string FROM mysql.user WHERE user = 'root';
If any of them have an empty authentication_string, they are using no password.
👉 Set password for all root users:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourNewPassword';
ALTER USER 'root'@'127.0.0.1' IDENTIFIED BY 'YourNewPassword';
ALTER USER 'root'@'::1' IDENTIFIED BY 'YourNewPassword';
FLUSH PRIVILEGES;
2️⃣ MySQL Authentication Plugin Issue
MySQL allows users to authenticate using different plugins like mysql_native_password or caching_sha2_password. Some versions of XAMPP (with MariaDB) default to auth_socket, which allows root login without a password.
👉 Check the authentication plugin used by root:
SELECT user, host, plugin FROM mysql.user WHERE user = 'root';
If you see auth_socket, your MySQL is configured to allow login only via Unix socket (without a password).
👉 Fix it by setting the plugin to mysql_native_password:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourNewPassword';
ALTER USER 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'YourNewPassword';
ALTER USER 'root'@'::1' IDENTIFIED WITH mysql_native_password BY 'YourNewPassword';
FLUSH PRIVILEGES;
3️⃣ You're Using the Wrong Host
If you are logging in using mysql -u root -p, MySQL might be trying root@localhost, but your password is set for root@127.0.0.1.
👉 Try logging in explicitly using different hosts:
mysql -u root -p -h 127.0.0.1
mysql -u root -p -h localhost
mysql -u root -p -h ::1
If one works but the others don’t, you know which user entry needs fixing.
4️⃣ MySQL Server Not Restarted After Password Change
Sometimes, changes require a MySQL restart.
👉 Restart MySQL (XAMPP) and Try Again:
- Stop MySQL from the XAMPP control panel.
- Start it again.
- Try logging in.
5️⃣ FLUSH PRIVILEGES Not Run
If you've changed the password but didn’t run FLUSH PRIVILEGES;, the new password may not be applied.
👉 Run this command in MySQL:
FLUSH PRIVILEGES;
🔥 Final Fix: Reset Root Password
If nothing works, reset the root password manually:
- Stop MySQL from XAMPP.
- Open a terminal/command prompt and start MySQL in safe mode:
mysqld --skip-grant-tables --skip-networking
- Open another terminal and log in without a password:
mysql -u root
- Reset the password:
UPDATE mysql.user SET authentication_string = PASSWORD('YourNewPassword') WHERE user = 'root';
FLUSH PRIVILEGES;
- Restart MySQL and try logging in.
✅ Summary of Fixes
✔ Check if password is set for all root entries (localhost, 127.0.0.1, ::1).
✔ Ensure MySQL authentication plugin is mysql_native_password.
✔ Try logging in with different hosts (127.0.0.1, localhost, ::1).
✔ Restart MySQL after changing the password.
✔ Run FLUSH PRIVILEGES; to apply changes.
✔ If all else fails, reset the password manually.
Try these steps and let me know if you need further help! 🚀