Sunday, November 28, 2010

XAMPP access denied for root with NO password

So far XAMPP is great for localhost web development. No problems with tweaking it or deploying local developed stuff on robust systems like OpenSUSE or CentOS. Works on Windows and Mac.
Well it works until it doesn't. And when it stops working u start digging :D
Today it was MySQL that said NO. I will not allow this nonsense of logging in as root and using NO password!
Access denied for user 'root@localhost' (using password: NO)

Ok, I must admit!
This is somehow understandable. Root with no password is plain BAD.
But I am ROOT! And this is my DEV box! And YOU are free software! Community edition!
So WHO changed my root password!?
It was blank yesterday and worked just fine!
This had happened before. Sabotage I say!

Yes. Seems that on OSX at least when the system feels like it the root blank pass gets messed up.
So you need to change it back to your old one. My old one is blank. On XAMPP follow these steps:
  1. make sure mysql is running using the XAMPP admin panel
  2. fearlessly open a terminal window and type ps -aux | grep mysqld_safe
  3. copy the line starting from /bin/sh /Applications/XAMPP/xamppfiles/bin/mysqld_safe ... to the end (mine ends with
  4. stop mysql from the panel
  5. in terminal paste the copied command and append  --skip-grant-tables at the end so we start a mysqld without the user / table security restrictions
  6. open another terminal tab/window, go to the bin folder of XAMPP (cd /Applications/XAMPP/xamppfiles/bin) and type ./mysql -u root
  7. in the mysql prompt that opens type:
  8. use mysql;
  9. update user set password=PASSWORD("YOUR_PASS_HERE") where user='root';
  10. flush privileges;
  11. quit
  12. stop mysql from the panel
  13. start mysql again
The above steps should allow you to log in with the password you entered instead of the above YOUR_PASS_HERE. If that was PASSWORD("") it will allow you to log in as root with no password.
Please note that his is a somewhat advanced level of usage meant for the beginner XAMPP user who just wants the thing to work. So proceed with caution and make sure you copy, paste and type the correct commands and nothing more. The above commands directly modify the mysql database which is the core of the database system. So proceed with caution and think before you type ;)

Update 20.01.2011: Well I just did an OSX update (which gave me the quite nice App Store) and the blank root password got messed up again. Following the above steps fixed it so the process works. Now I'm trying to remember if I did a reboot since I wrote that. I think I did so that would mean that a system update would mess the password? Doesn't make sense but it would be good if that happens _only_ on OSX updates and not regular reboots. But maybe it's just random as all the good things in life...


Post a Comment

<< Home