LinuxQuestions.org
Did you know LQ has a Linux Hardware Compatibility List?
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices

Reply
 
Search this Thread
Old 09-25-2006, 08:23 AM   #1
telakenesys
Member
 
Registered: May 2006
Location: Newcastle, Australia
Distribution: Ubuntu, Raspbian, RaspBMC
Posts: 33

Rep: Reputation: 15
Exclamation Lost root access in MySQL


Hi everyone.

I have a problem with the MySQL server I have running on Fedora Core 5.

I accidentally dropped 'root' from the user table in the mysql database. As a result I can't do much at all. (I have no other privileged user).

Iím sorry, but I am sure this question has probably been asked a million times but I have spent hours scouring forums looking for a way to fix this with no luck.

Everything that I try, returns:
Access denied for user ''@'localhost' to database 'mysql'


 
Old 09-25-2006, 09:07 AM   #2
odcheck
Member
 
Registered: Aug 2006
Distribution: Fedora, CentOS, RHEL, Debian
Posts: 978

Rep: Reputation: 30
What if you enter a terminal as ROOT
then
Code:
mysql -u root mysql
and then
Code:
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');
where new_password is exchanged by you desired new pw
 
Old 09-25-2006, 06:38 PM   #3
telakenesys
Member
 
Registered: May 2006
Location: Newcastle, Australia
Distribution: Ubuntu, Raspbian, RaspBMC
Posts: 33

Original Poster
Rep: Reputation: 15
Access denied for user ''@'localhost' to database 'mysql'
 
Old 09-25-2006, 07:44 PM   #4
got_nix
Member
 
Registered: Oct 2004
Location: Jamaica
Distribution: Ubuntu 6.0.6 (Dapper Drake)
Posts: 59

Rep: Reputation: 15
Quote:
Originally Posted by odcheck
What if you enter a terminal as ROOT
then
Code:
mysql -u root mysql
and then
Code:
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');
where new_password is exchanged by you desired new pw
kinda right however you need to skip the grant tables when you restart mysql

this has save me once or twice here yo go

1. Take down the mysqld server by sending a kill (not kill -9) to the mysqld server. The pid is stored in a `.pid' file, which is normally in the MySQL database directory:

Code:
     shell> kill `cat /mysql-data-directory/hostname.pid`
You must be either the Unix root user or the same user mysqld runs as to do this.
2. Restart mysqld with the --skip-grant-tables option.
3. Set a new password with the mysqladmin password command:

Code:
      shell> mysqladmin -u root password 'mynewpassword'
4. Now you can either stop mysqld and restart it normally, or just load the privilege tables with:

Code:
shell> mysqladmin -h hostname flush-privileges
5. After this, you should be able to connect using the new password.

Alternatively, you can set the new password using the mysql client:

1. Take down and restart mysqld with the --skip-grant-tables option as described above.
2. Connect to the mysqld server with:

Code:
 shell> mysql -u root mysql
3. Issue the following commands in the mysql client:

Code:
      mysql> UPDATE user SET Password=PASSWORD('mynewpassword')
          ->             WHERE User='root';
      mysql> FLUSH PRIVILEGES;
4. After this, you should be able to connect using the new password.
5. You can now stop mysqld and restart it normally.

Last edited by got_nix; 09-25-2006 at 07:46 PM.
 
Old 09-25-2006, 08:56 PM   #5
telakenesys
Member
 
Registered: May 2006
Location: Newcastle, Australia
Distribution: Ubuntu, Raspbian, RaspBMC
Posts: 33

Original Poster
Rep: Reputation: 15
When I try the first method returns:
mysqladmin: unable to change password; error: 'Access denied for user ''@'localhost' to database 'mysql''
and the second one cames back with:
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'
I think that i might just have to try to back everything up and start from sctratch.

Last edited by telakenesys; 09-26-2006 at 08:58 AM.
 
Old 09-27-2006, 06:18 AM   #6
telakenesys
Member
 
Registered: May 2006
Location: Newcastle, Australia
Distribution: Ubuntu, Raspbian, RaspBMC
Posts: 33

Original Poster
Rep: Reputation: 15
I have had a thought. If I were to delete the mysql database files and run mysql_install_db ?

Will this recreate the database that I need with root access and no password?
 
Old 09-27-2006, 10:19 AM   #7
got_nix
Member
 
Registered: Oct 2004
Location: Jamaica
Distribution: Ubuntu 6.0.6 (Dapper Drake)
Posts: 59

Rep: Reputation: 15
Quote:
Originally Posted by telakenesys
I have had a thought. If I were to delete the mysql database files and run mysql_install_db ?

Will this recreate the database that I need with root access and no password?
not a bad idea.. yeh. a total overhall on the db should work becuase it comes with the default root root uname and pass.
 
Old 09-27-2006, 07:07 PM   #8
telakenesys
Member
 
Registered: May 2006
Location: Newcastle, Australia
Distribution: Ubuntu, Raspbian, RaspBMC
Posts: 33

Original Poster
Rep: Reputation: 15
Thanks guy for all the help. I ended up getting it working by deleting the database and going again. Didn't even lose anything in the other databases.

But now after all that trouble, the app i was trying to get working is no good.
Thanks again guys.
 
Old 09-27-2006, 07:32 PM   #9
got_nix
Member
 
Registered: Oct 2004
Location: Jamaica
Distribution: Ubuntu 6.0.6 (Dapper Drake)
Posts: 59

Rep: Reputation: 15
No problem I'm Glad you got it up and running
 
Old 02-23-2011, 04:48 AM   #10
abhay_bc
LQ Newbie
 
Registered: Feb 2011
Posts: 1

Rep: Reputation: 0
Smile fixed

I had the same problem and this is what I did to fix it -

stopped mysql service
started mysql with --skip-grant-tables option
logged in mysql with no username/password ( just mysql command )
inserted a row with user=root , host=localhost and all required privileges = 'Y'
executed flush privileges
restarted mysqld service

It took care of my problem. Did not have to reinstall mysql.
 
  


Reply

Tags
access, database, denied, localhost, mysql, user


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: Access denied for user 'root'@'localhost' (using password: NO) jun_tuko Linux - Software 3 03-05-2013 06:37 AM
mysql root password lost nazir Linux - Software 6 10-09-2007 10:18 AM
mysql root privilages lost automaticaly on a database asheesh.tyagi Linux - Newbie 3 02-21-2006 09:55 AM
MySQL root access. ToothlessRebel Linux - Software 1 11-14-2005 06:35 PM
mysql root access err 1045... beware of this latino Linux - Newbie 0 03-15-2004 09:07 PM


All times are GMT -5. The time now is 05:13 AM.

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