Welcome to the most active Linux Forum on the web.
Go Back > Forums > Linux Forums > Linux - Server
User Name
Linux - Server This forum is for the discussion of Linux Software used in a server related context.


  Search this Thread
Old 12-07-2006, 03:52 PM   #1
LQ Newbie
Registered: Jul 2006
Posts: 9

Rep: Reputation: 0
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!
Old 12-07-2006, 04:54 PM   #2
LQ Guru
Registered: Jan 2001
Posts: 24,149

Rep: Reputation: 250Reputation: 250Reputation: 250
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.
Old 12-08-2006, 11:35 AM   #3
LQ Newbie
Registered: Jul 2006
Posts: 9

Original Poster
Rep: Reputation: 0
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.



Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql user table corruption creolophus Linux - Software 1 04-23-2006 09:12 PM
Duplicate root user in MySQL buddhahat Linux - Newbie 4 08-29-2004 11:34 PM
mysql 'user' table read-only fuelinjection Linux - General 2 06-13-2004 10:05 AM
How to import MS ACCESS Table including OLE filed into the MySQL Table ? myunicom Linux - General 1 11-28-2003 12:30 PM
Routing table destroyed (reset) after reboot daveb93 Linux - Networking 1 09-23-2003 04:43 PM > Forums > Linux Forums > Linux - Server

All times are GMT -5. The time now is 03:10 PM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration