?
Solved

Lost password - Linux sql

Posted on 2003-03-22
7
Medium Priority
?
743 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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 Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

764 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