visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: Lost password - Linux sql
By: Squibi U.S.A.  Date: 22/03/2003 00:00:00  English French  Points: 65 Status: Answered
Quality : Excellent
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.
By: VGR Date: 22/03/2003 09:48:00 English  Type : Comment
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.
By: Squibi Date: 22/03/2003 10:36:00 English  Type : Comment
*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'
By: VGR Date: 22/03/2003 11:09:00 English  Type : Answer
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) <A HREF="http://www.mysql.com/doc/en/Resetting_permissions.html">http://www.mysql.com/doc/en/Resetting_permissions.html</a>

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;
By: Squibi Date: 22/03/2003 11:34:00 English  Type : Comment
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`
By: Squibi Date: 22/03/2003 12:10:00 English  Type : Comment
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.
By: VGR Date: 22/03/2003 18:22:00 English French  Type : Comment
you're welcome, de rien.
By: Squibi Date: 24/03/2003 23:57:00 English  Type : Comment
The solution was found browsing the user comments from the link, the procedure did not work.

<A HREF="http://www.mysql.com/doc/en/Resetting_permissions.html">http://www.mysql.com/doc/en/Resetting_permissions.html</a>

Thanks for the support, VGR.

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 348.130940 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page