LinuxQuestions.org
Go Job Hunting at the LQ Job Marketplace
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices

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

Rep: Reputation: 0
Locked myself out of mysql...root user db table destroyed


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!
 
Old 12-07-2006, 03:54 PM   #2
trickykid
Guru
 
Registered: Jan 2001
Posts: 24,133

Rep: Reputation: 194Reputation: 194
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, 10:35 AM   #3
organica
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:

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.
 
  


Reply

Tags
mysqld


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
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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


All times are GMT -5. The time now is 01:24 PM.

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