HELP! phpadmin deleted all users

[vgould]

I was exploring phpMyAdmin and accidentally deleted all users.

Now I can't log back in to phpMyAdmin: Error
MySQL said:

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

Can anyone tell me how to make a new user so I can log back into phpMyAdmin? I looked in config.inc.php and it has root listed, but I don't know what else I need to do to add the user into mysql.

[caltuna]

If you don't have any databases (or don't have a dump file of them) It might be easier to back up your htdocs folder and re-install XAMPP and then restore the htdocs files.

You might look in your config.inc.php file and see if it looks like this:

Code: Select all

 * First server
 */
$i++;
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'config';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = '';
$cfg['Servers'][$i]['connect_type'] = 'socket';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['AllowNoPasswordRoot'] = true;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysql';

[vgould]

Unfortunately I do have 2 databases I need that I don't have a dump of.

my config.inc.php file looks like:
$cfg['Servers'][$i]['auth_type'] = 'config';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = '';
$cfg['Servers'][$i]['connect_type'] = 'socket';
$cfg['Servers'][$i]['compress'] = false;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysql';

It seems like there should be a way to go through bin/mysql to create a new user, but it won't let me in.

Any creative solutions?

[newbie2244]

Greetings-

1. If you are on Windows, try doing a "system restore" to a time right before you deleted your user db. If you don't know how to do this, access MicroSoft Documentation and search for "System Restore -how to". This may or may not work.

2. If you can access xampp, Go into Security and see what you can do - read all files.

I hope this suggests some options.

Regards

[newbie2244]

Just realized that you are on a MAC - sorry, but I don't know the system.

Can you access XAMPP through localhost??

[vgould]

Yes, I can access xampp through localhost, but not phpMyAdmin. Status shows mysql as deactivated.

I guess I'll have to reinstall xampp, but it was a pain to get it set up the first time.

[MegaChriz]

I suppose you are not using Time Machine.

There a might be another way to access the data of your MySQL-database, I'm not sure if it will really work. I once tried it, but some of the data got a little corrupted then (maybe because of using other character sets, or because of using another MySQL-version).

In the following folder all the data of the mysql-databases is stored:
/Applications/XAMPP/xamppfiles/var/mysql/

You can try this:
1. Make a backup of your current XAMPP installation.
2. Move or rename the XAMPP-folder in your Applications-folder.
3. Do a fresh installation of XAMPP.
4. Start XAMPP.
4. Go to phpMyAdmin and create the databases you want to have back with the exact names and character sets you used in the previous XAMPP installation.
5. Stop XAMPP.
6. Now, go to /Applications/XAMPP/xamppfiles/var/mysql/ and delete the folders of the databases you just created. You may need to give the password of your administrator account.
7. Go into your backup to XAMPP/xamppfiles/var/mysql/ and copy the folders with the databases-names you had created to /Applications/XAMPP/xamppfiles/var/mysql/. Again it's possible you need to give the password of your administrator account.

I'm not sure if it will really work, but it might be worth the try. It's also possible that some of the steps are not necessary. I have tried this only once some years ago.

As for the rest, you can get most of your configuration back to get the folder XAMPP/etc/ of your backup. In the same way you can store back your files stored in the htdocs-folder.

[caltuna]

You should be able to get into mysql via the command line. What happens when you do this:

sudo /Applications/XAMPP/xamppfiles/xampp startmysql

What happens if you use the command line:
http://dev.mysql.com/doc/refman/5.1/en/mysql.html

sudo /Applications/XAMPP/xamppfiles/xampp/mysql -u root -p ''

Al

[vgould]

When I enter either of the two commands below it asks for password then takes me back to the command prompt. Since there is no error message, it looks like both commands were successful.

sudo /Applications/XAMPP/xamppfiles/xampp startmysql

sudo /Applications/XAMPP/xamppfiles/xampp/mysql -u root -p ''

[caltuna]

Try using the word "root" as a password. Also try a space. It may be one of those.
Al

[vgould]

I ended up reloading xampp, and it worked for a whole day. But now (a new day after rebooting my computer) when I try to go the phpMyAdmin I get an error saying "The server is not responding (or the local MySQL server's socket is not correctly configured)"

Not sure why it worked on day one and won't work anymore.

Any ideas?

[caltuna]

I wonder if you have some thing in your start-up routine that is starting the Apple built-in Apahce. First, make sure in System Preferences that Sharing, Personal Web is not checked. Second, open up a Terminal window and enter: cat /etc/hostconfig and see if it says
WEBSERVER=-NO-
MYSQLCOM=-NO-

Check your router (if using one) to see if somehow port 80 is being blocked, as well as firewall. (I doubt this is an issue, but can't hurt to look)

Finally re-start and then check Activity Monitor and see if you have any httpd processes going. You shouldn't.

Start up XMAMPP and check the log files for any errors.

[vgould]

When I do this: Terminal window and enter: cat /etc/hostconfig and see if it says
WEBSERVER=-NO-
MYSQLCOM=-NO-

WEBSERVER says no, but MYSQLCOM=-YES-

[vgould]

I finally fixed the problem - must have resulted from my own lack of experience using Mac OS X.

It works when I change all the permissions of the xampp folder to make me the owner with read/write, Group is Admin with read/write and others has read/write.

When I edit a file, permissions change to owner: system read/write, group: admin read only, others read only. Then, mysql no longer works because it can't do what it needs to do to the mysql.sock file. There is probably a better why to set up the permissions than leaving it all wide open, but it works so now I can get back to work.

Thanks for all your suggestions and advice,

Vincent

[caltuna]

When I do this: Terminal window and enter: cat /etc/hostconfig and see if it says
WEBSERVER=-NO-
MYSQLCOM=-NO-

WEBSERVER says no, but MYSQLCOM=-YES-

Well, I'm sure that the next time you re-start OS X will try to start some version of MYSQL. You should edit hostconfig and set it to -NO- (don't forget the dashes.) Edit it with pico in terminal

sudo pico /etc/hostconfig

Pico is a line editor. Just use the arrow keys and change YES to -NO- and the control-X to write and exit.

Al