Locked myself out of mysql...root user db table destroyed

organica · 12-07-2006, 02:52 PM

Hello,

I've made a blunder that will hopefully happen only once. I removed the root user from the mysql user db. Now mysql blocks out all users. I've RTFM and tried ALL different ways to get into mysql. I can get in using the --skip-grant-tables --user=root option, but when I use INSERT or UPDATE commands to add the user root and password to the database, the password is NOT encrypted. Thus, mysql does not match the root password I give it (is there an option to start mysqld using non-encrypted passwords?).

Plus, I tried to use vi to manually copy and paste the encrypted password (in an incomplete table) in the binary /var/lib/mysql/user.MYD file, but, its binary, so no dice.

The question is, should I try to uninstall/install a fresh mysql package and then hope the existing databases just "start working" again? Plus, I can't mysqldump the databases for backup either. Just a little worried on what to do.

I have CentOS 4 Server running with mysql 4x.

Thanks and sorry for the complications!

trickykid · 12-07-2006, 03:54 PM

What is the exact SQL statements you are using to update the password with? And yes, never manually edit the binary files, you'll just destroy the data.

organica · 12-08-2006, 10:35 AM

ok, I got it. Apparently, I did not follow ALL the instructions.

Here's what I did to restore the password:

Quote:

1.

Stop mysqld and restart it with the --skip-grant-tables --user=root options (Windows users omit the --user=root portion).
2.

Connect to the mysqld server with this command:

shell> mysql -u root

3.

Issue the following statements in the mysql client:

mysql> UPDATE mysql.user SET Password=PASSWORD('newpwd')
-> WHERE User='root';
mysql> FLUSH PRIVILEGES;

Replace “newpwd” with the actual root password that you want to use.
4.

You should be able to connect using the new password.