Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Lost password - Linux sql

Posted on 2003-03-22
7
Medium Priority
?
750 Views
Last Modified: 2012-05-04
I have just started to use mysql, and was using webmin as a front end.  I changed the password and now I get the error:
SQL select * from user order by user failed : Access denied for user: 'root@localhost' to database 'mysql'

trawling through the docs I find that I can --flush-privilege-tables.  I assumed the syntax would be
#./mysqld --flush-privilege-tables

but this is obviously wrong

I can log into mysql but when I try to change anything or create a database I get ERROR 1044: Access denied for user: 'root@localhost' to database 'newdatabase'

In fact I'm quite familiar with ERROR 1044: Access denied for user: 'root@localhost'... its all I ever seem to get (note of despair).

So how do I start again so that I can learn how to use it?
And how did I get myself into this mess - is there a problem using mysql as root?

Many thanks in advance.
0
Comment
Question by:Kingsize
  • 4
  • 3
7 Comments
 
LVL 15

Expert Comment

by:VGR
ID: 8187942
if you can log in into mysql, it means you have a valid login&password !

I assume you tried this :
mysql --user=root --password=...

Apparently you would have a privileges problem.
I suggest this :
use mysql;
select * from user;
look at the host and manually reset password (using md5() I think, see MySql online documentation) of modify privileges with a GRANT * ON ... (see doc) and then only FLUSH PRIVILEGES;

And no, there is no problem using mysql as root. This is not the *nix 'root' superuser, it's just the superuser of MySql. Don't worry.
0
 
LVL 2

Author Comment

by:Kingsize
ID: 8188063
*sigh*

[root@localhost init.d]# mysql -u root -pmylamepassword

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 3.23.52

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mysql;
Database changed
mysql> select * from user;
ERROR 1044: Access denied for user: 'root@localhost' to database 'mysql'
mysql> select*from user;
ERROR 1044: Access denied for user: 'root@localhost' to database 'mysql'

0
 
LVL 15

Accepted Solution

by:
VGR earned 195 total points
ID: 8188185
how strange :/

looks like the root user has lost his privileges (all)

There is a procedure to follow here to reset initial privileges (and change the root password in case you lost it) http://www.mysql.com/doc/en/Resetting_permissions.html

try this first :
Take down and restart mysqld with the --skip-grant-tables mysql -u root mysql
GRANT ALL ON *.* TO 'root' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 2

Author Comment

by:Kingsize
ID: 8188251
This is where I stumble a little.  I'm starting sql from /etc/rc.d/init.d

[root@localhost init.d]# ./mysqld --skip-grant-tables mysql -u root mysql
Usage: ./mysqld {start|stop|status|condrestart|restart}

[root@localhost init.d]# ./mysqld start --skip-grant-tables mysql -u root mysql
Starting MySQL:                                            [  OK  ]
[root@localhost init.d]# mysql
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
[root@localhost init.d]# mysql -u root -pmylamepassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.52

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT ALL ON *.* TO 'root' IDENTIFIED BY 'mylamepassword' WITH GRANT OPTION;
ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)
mysql> exit;

I don't know the right syntax for restarting mysqld with the --skip-grant-tables option.

I use this command to stop mysql
/etc/rc.d/init.d/mysqld stop

I have trouble with this one - I havn't got a .pid file for mysql.
shell> kill `cat /mysql-data-directory/hostname.pid`
0
 
LVL 2

Author Comment

by:Kingsize
ID: 8188359
OK,  I did this:
from my init.d directory
safe_mysqld --skip-grant-tables &

[root@localhost init.d]# safe_mysqld --skip-grant-tables &


[1] 5792
[root@localhost init.d]# Starting mysqld daemon with databases from /var/lib/mysql

That shell prompt then hung.  So I opened another and did this:

mysql

use mysql;

update user set password = password('.......') where
user = 'root' and host='localhost';

And joy of joys it now works!!!  It only took err a day to figure that one out.

Many thanks VGR.
0
 
LVL 15

Expert Comment

by:VGR
ID: 8189148
you're welcome, de rien.
0
 
LVL 2

Author Comment

by:Kingsize
ID: 8194681
The solution was found browsing the user comments from the link, the procedure did not work.

http://www.mysql.com/doc/en/Resetting_permissions.html

Thanks for the support, VGR.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question