asked on

Lost password - Linux sql

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.

VGR

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.

Kingsize

ASKER

*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'

ASKER CERTIFIED SOLUTION

VGR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kingsize

ASKER

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`

Kingsize

ASKER

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.

VGR

you're welcome, de rien.

Kingsize

ASKER

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.