Deleted user Root?

legendaryfox · 12-14-2004, 01:40 PM

Ehe, did something stupid - while I was in phpmyadmin, i think i accidentally deleted my root user (the one from %) from the users table...and now I can't log into mysql. I tried the Lost Root Password article here, but it doesn't seem to fix it...

The wierd thing is, when I first deleted the root user from the table, nothing happened...but then later, when I tried to run mysql, it said:

ERROR 1045: Access denied for user: 'root@localhost' <Using password: NO>

(tha'ts when i just run plain old mysql)

Then when I try to run it as mysql -p, and after prompting me for a password, it said

ERROR 1045: Access denied for user: 'root@localhost' <Using password: YES>

I know that shouldn't happen, because I remember changing my password to that for the other root account in phpmyadmin...

it's sorta confusing, but if you understand what i mean, please help

Thanks!

Hal · 12-23-2004, 04:12 PM

mysql -u root -p password

Proud · 12-23-2004, 04:16 PM

Check that gap after the -p, iirc it shouldnt be there.

cherylchase · 12-23-2004, 06:27 PM

If the root user has actually been deleted from the mysql database's users table, then the above suggestion won't work. (But if it did work, it would be mysql -uroot -ppassword, without any spaces).

The lost password article you pointed to gives correct instructions for changing root's password. But that won't work, I think, if the root user doesn't exist.

Do you have a backup of the mysql database files? You can stop the mysql process and just overwrite the mysql database files. They are someplace like /var/lib/mysql/mysql. You can find them with locate host.frm if you are not sure.

Another possibility would be to extract a set of pristine mysql database files from a new distribution.

tcarroll2 · 01-09-2005, 01:12 PM

Alright, I too have this problem of all users being deleted.

I tried doing what you suggested of stopping the mysql server and copying files over the database files in /var/lib/mysql/mysql from a previous installation. No luck.

I still cannot access any of the mysql databases. I cannot manipulate any users either, such as trying to add the root user, or at least change the privileges or password.

Thank you!