Can't login to mysql when mysql is run by xampp control

Question

I have sucessfuly reseted my root password on my localhost xampp. Now when I run the mysql daemon myself ( XAMPP/mysql/bin/mysqld.exe ), I can login with PHPMyAdmin to the MySQL administration with no problem.

However when I run MySQL from XAMPP's Control Panel (the "nice" window with start/stop etc. buttons)
I can't login through PHPMyAdmin anymore - I get error #1045...

This must be something configuration-related? What might be causing this?

Big thanks :)

Try `Get-WmiObject Win32_Process -Filter "name = 'mysqld.exe'"` and check the command line arguments. Maybe it's even another binary? — andy, Nov 03 '14 at 20:06
Sorry, that is a powershell command. You could also try [process explorer](http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx). — andy, Nov 03 '14 at 20:15
XAMPP's got these `--defaults-file="myXamppLocation\mysql\bin\my.ini" --standalone` - but what in these options is "fighting" against the login? — jave.web, Nov 03 '14 at 20:22
It is not caused by the `--standalone` param, It is caused by the `my.ini` file - contents: http://ideone.com/6HsXBF - I read through it but still can't figure out what might be causing it :/ can you ? :) — jave.web, Nov 03 '14 at 20:56
I'm afraid not. I assume you tried http://stackoverflow.com/questions/489119/mysql-error-1045-access-denied and checked the logfile for errors? — andy, Nov 03 '14 at 21:06
@andy I finaly solved it, but It could not be done without your help - so many thanks :) I will use that powershell command many times again. — jave.web, Nov 03 '14 at 23:08
Credit to http://superuser.com/questions/415360/how-do-i-find-out-command-line-arguments-of-a-running-program ^^ — andy, Nov 04 '14 at 16:47

score 0 · Accepted Answer · answered Nov 03 '14 at 23:07

It seems that mysql daemon in xampp has different default value for the basedir than the my.ini setting file.

So when I've reseted the password without adding the same --defaults-file as XAMPP does when it runs mysqld - I actually reseted a password for a different "workspace".

Thanks to @andy, because his comments led me to the solution :)

So the right way to reset XAMPP's MySQL root user ( on Windows ) is:

1) Stop MySQL with XAMPP Control

2) Prepare init file

Create file called mysql-init.txt directly on C:\ drive and fill it with this content:

#INSERT INTO mysql.user (User,Password) VALUES('root',PASSWORD('root'));
UPDATE mysql.user SET Password=PASSWORD('root') WHERE User='root';
GRANT ALL PRIVILEGES ON * . * TO 'root'@'localhost';
FLUSH PRIVILEGES;

- # is a comment - if you did not have a root user or deleted it by accident - uncomment the first line and comment the second line

These commands create a MySQL user "root" with password "root" and give him/her ALL privileges on everything.

3) Run MySQL daemon with proper params

Run YOUR_XAMPP_INSTALL\mysql\bin\mysqld.exe with these params:
Note the double slashes "\\" instead of single slashes "\"

--defaults-file="c:\\YOUR_XAMPP_INSTALL\\mysql\\bin\\my.ini"
--init-file=C:\\mysql-init.txt

So the whole command for cmd.exe could look like:

c:\YOUR_XAMPP_INSTALL\mysql\bin\mysqld.exe --defaults-file="c:\\YOUR_XAMPP_INSTALL\\mysql\\bin\\my.ini" --init-file=C:\\mysql-init.txt

Of course replace YOUR_XAMPP_INSTALL with the path to your custom xampp install ( very often it is just "xampp" resp. "C:\xampp"). And of course if you are operating on a different drive than "C", change C:\ to WhatEverLetterYouWant:\

4) Try to login on `http://localhost/phpmyadmin/`

Try to login on http://localhost/phpmyadmin/ with user root and password root, if it does not work, you have done something wrong in the previous 3 steps

5) 4) went fine? => Stop MySQL with XAMPP Control

6) Start MySQL with XAMPP control

7) Try to login again with root/root

8) 7) went fine? => Done :)

References:

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql