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 :: Can not change root mySQL password
By: blainehilton U.S.A.  Date: 19/07/2003 00:00:00  English  Points: 500 Status: Answered
Quality : Excellent
I have finished a standard install of Red Hat 9 and the included mySQL (v 3.23 I believe) and I was never prompted for a password for the system. It is my understanding that I need to use a command such as "mysqladmin -u root 'newpassword' " or something along those lines to change the password from being nothing.

I am able to access the mySQL moniter by using the command "mysql -u root" however then when I try to do a "use mysql" I get an error message saying I do not have access to that database. The exact message is:

ERROR 1044: Access Denied for user: '@localhost' to database 'mysql'

I may have entered a password already when I was trying different commands to set it in the first place. Thats the only thing I can think of that is causing this. I have looked into reseting the root password by using the option to turn grant tables off, but could not understand the process enough to do it.

If you have questions for me I will be happy to answer. Any help would be greatly appreciated. TIA
By: VGR Date: 20/07/2003 05:30:00 English  Type : Comment
stop mysqld
restart in --skip-grant-tables mode
fix the "root" entry in mysql.user
By: blainehilton Date: 20/07/2003 05:36:00 English  Type : Comment
Thanks for your response, and sorry for my ignorance, but how do I restart mysqld in --skip-grant-tables mode? Can you please give me the exact command I need to type in and the location if it matters?


--
Thank you
By: blainehilton Date: 20/07/2003 06:24:00 English  Type : Comment
Okay I used the command:

safe_mysqld --skip-grant-tables

It seems to be working. I was able to access the mysql monitor and type in "use mysql" and then change the root password. Let me make sure all is working and I will accept your answer then.
By: blainehilton Date: 20/07/2003 07:15:00 English  Type : Comment
Well I'm stuck again. I thought that would work, I even believe I changed the password. However I still can not access mysql moniter using the password and I can not load PHPMyAdmin with the password. I have noticed some menition of hosts though and thought that may have something to do with it. If you could detail what you meen exactly by "fix the "root" entry in mysql.user " that would be helpful. The computer running mysql is called "Server" and in PHPMyAdmin I have it set to "localhost", however I tried Server too. I've also seen mention of locahost and localhost.localdomain.

Thanks again
By: VGR Date: 20/07/2003 07:20:00 English  Type : Comment
yes, it must be because you entered a textual password in mysql.user ( a commo mistake)

don't forget to use the PASSWORD() function when writing firectly in the grant tables with INSERT INTO ...

I'm pretty confident this is your problem .

is SELECT * FROM mysql.user; shows "clear text" passwords, then that's it : change them via UPDATE mysql.user set password=PASSWORD(password) WHERE ...
:D
By: VGR Date: 20/07/2003 07:22:00 English  Type : Comment
that's normal (passwords are MD5 so not human-readable) :

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 415015 to server version: 3.23.56-nt

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

mysql> select * from mysql.user;
+-----------+------+------------------+-------------+-------------+-------------+----------+-----------------+------------+------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv |rant_priv | References_priv | Index_priv | Alter_priv |
+-----------+------+------------------+-------------+-------------+-------------+----------+-----------------+------------+------------+
| localhost | root | 245e91aa04256439 | Y | Y | Y | | Y | Y | Y |
| % | | | N | N | N | | N | N | N |
| localhost | user | | N | N | N | | N | N | N |
| % | root | 245e91aa04256439 | Y | Y | Y | | Y | Y | Y |
By: blainehilton Date: 20/07/2003 07:30:00 English  Type : Comment
THE SQL statment I used was:

UPDATE user
SET Password = password('test')
WHERE User ='root';

The passwords apear to be encrypted when I do a select on the table. Is the above SQL statement correct?
By: blainehilton Date: 20/07/2003 07:31:00 English  Type : Comment
also after that I did a "FLUSH PRIVILEGES;"
By: VGR Date: 20/07/2003 08:13:00 English  Type : Comment
yes, correct
good

doesn't work ? which error do you get ? Have you tried the "normal way" ? :

mysql --user=root --password=test
By: blainehilton Date: 20/07/2003 08:27:00 English  Type : Comment
I entered "mysql --user=root --password=test" from the command line and I recieved:

ERROR 1045: Access denied for user: "root@localhost" (Using password: YES)

PS. I'm at wits end!
By: VGR Date: 20/07/2003 08:45:00 English  Type : Comment
have you restarted the server in normal mode ? (without --skip-grant-tables )
By: blainehilton Date: 20/07/2003 08:47:00 English  Type : Comment
I believe so. I use Red Hat's services screen to stop mysqld and then start it again
By: blainehilton Date: 20/07/2003 08:49:00 English  Type : Comment
I've read and reread all of the perteaint info in the books "Running Linux", "Managing and using mySQL", mySQL manual at mysql.com, other like posts such as <A HREF="http://www.experts-exchange.com/Databases/Mysql/Q_20678779.html">http://www.experts-exchange.com/Databases/Mysql/Q_20678779.html</a> and I am still having problems. I think perhaps I should uninstall this one and do a clean install of mySQL 4.x........
By: blainehilton Date: 20/07/2003 08:51:00 English  Type : Comment
To throw this out too, I've downloaded the RPMs for the current version of mySQL and just double clicked it, nothing came up though. Could this have overwritten the users file with one for version 4.1? If this could be then how could I fix it?
By: VGR Date: 20/07/2003 08:59:00 English  Type : Comment
no, it can't be a problem like this.

as long as you've access to mysql server via "mysql --user=root [etc]", it's fine.

well, let's do it again. Perhaps your host isn't "localhost" ?

try this :

mysql --user=root --password=test --host=localhost
By: blainehilton Date: 20/07/2003 17:18:00 English  Type : Comment
in the ect/hosts file there is 2 entries

localhost
Server

I can access the apache server from <A HREF="http://Server/">http://Server/</a> from other computers on the local network and Samba using \\Server


Let me try your last command line and see what happens....
By: blainehilton Date: 20/07/2003 17:22:00 English  Type : Comment
Okay I tried it with host=localhost and host=Server with the following results:

Error 1045: Access denied for user: 'root@localhost.localdomain' (Using password: YES)
By: VGR Date: 20/07/2003 17:54:00 English  Type : Comment
this means you don't have the correct entry in the grant tables.

1) enter in skip-grant-tables

2) UPDATE mysql.user SET Host='%', Password = password('test'), Host='Server' WHERE User ='root';

3) flush privileges;
By: blainehilton Date: 20/07/2003 18:22:00 English  Type : Comment
Okay I did that and I recieved this error:

Error 1062: Duplicate entry 'Server-root' for key 1
By: VGR Date: 20/07/2003 18:31:00 English  Type : Comment
ok

show me the select * from mysql.user;
By: blainehilton Date: 20/07/2003 18:44:00 English  Type : Comment
Here is the output:


mysql> select * from user
-> ;
+-----------+------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv |
+-----------+------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+
| | root | 378b243e220ca493 | Y | Y | Y | Y | Y | Y | Y
| Y | Y | Y | Y | Y | Y | Y |
| Server | root | 378b243e220ca493 | Y | Y | Y | Y | Y | Y | Y
| Y | Y | Y | Y | Y | Y | Y |
| localhost | | | N | N | N | N | N | N | N
| N | N | N | N | N | N | N |
| Server | | | N | N | N | N | N | N | N
| N | N | N | N | N | N | N |
+-----------+------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+
4 rows in set (0.00 sec)
By: VGR Date: 20/07/2003 18:58:00 English  Type : Answer
ok, so should work :
mysql --user=root --host=Server --password=test

the rest won't work.

Do :

update user set Host='localhost' where User='root' and Host='';
update user set User='cci' where Host='Server' and User='';

this will enable you to use also :
mysql --user=cci --host=Server [you should set a password anyway]
and
mysql --user=root --host=localhost --password=test
By: blainehilton Date: 20/07/2003 19:31:00 English  Type : Comment
I was able to issue those commands, but it doesn't seem to have changed anything. Below is a history of what was going on:


[root@Server root]# mysql --user=root --host=Server --password=test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.54

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

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set Host='localhost' where User='root' and Host=";
"> update user set User='cci' where Host='Server' and User=";
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql>


[root@Server root]# mysql --user=cci --host=Server --password=test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.23.54

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

mysql> exit
Bye
[root@Server root]# mysql --user=root --host=localhost --password=test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 3.23.54

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

mysql>



IN PHPMyAdmin I get this:
'

Welcome to phpMyAdmin 2.5.1

Error

MySQL said:

Access denied for user: 'root@localhost' (Using password: YES)


thats with username set to root and password set to test.



I think the next step is going to be trying to reinstall Red Hat..... Any other ideas? Am I entering the commands wrong?
By: VGR Date: 20/07/2003 20:11:00 English  Type : Comment
have you restarted the server in between ?

BTW, apparently, you forgot to "flush privileges;" after the two updates
By: blainehilton Date: 21/07/2003 02:32:00 English  Type : Comment
What I have done is went back to my Red Hat CDs and reinstalled everything, not the best answer, but I was running out of time and it worked. I doubt I forgot to to a flush privileges, but it wouldn't surprise me thinking back, I probably didn't do it that last time. And yes I would restart the server.

Thanks for the help though, very insightful for future problems too...
By: VGR Date: 21/07/2003 04:51:00 English  Type : Comment
you're welcome. Anyway, it's a rather classical problem : once encountered, never again :D

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 804.539920 milliseconds

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