-   Linux - Server (
-   -   Locked myself out of mysql...root user db table destroyed (

organica 12-07-2006 03:52 PM

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

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 04: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 11:35 AM

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

Here's what I did to restore the password:



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

Connect to the mysqld server with this command:

shell> mysql -u root


Issue the following statements in the mysql client:

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

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

You should be able to connect using the new password.

All times are GMT -5. The time now is 01:54 AM.