Password Reset Not Working

I noticed today that I strangely was unable to login to MySQL as root.
I just assumed I forgot the password and decided to reset my root
password:

1 - /etc/init.d/mysqld stop

2 - mysqld_safe --skip-grant-tables &

3 - mysql -u root

4 - mysql> use mysql;

mysql> mysql> update user set password='letmein' where user='root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> flush privileges;

mysql> quit

5 - /etc/init.d/mysqld restart

*Now after I do all that, I get the following:*

mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
password: YES)

I have done this over and over and can't login so I am wondering if
something is wrong with MySQL or am I just not properly resetting the
password...

Someone please help!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Password Reset Not Working

Hi Carlos

Try this

mysql> update user set password=password('letmein') where user='root';


This way the password is saved encrypted, thats the way is compared when
you try to log in.

Carlos

On 5/14/2009 5:28 PM, Carlos Williams wrote:
> I noticed today that I strangely was unable to login to MySQL as root.
> I just assumed I forgot the password and decided to reset my root
> password:
>
> 1 - /etc/init.d/mysqld stop
>
> 2 - mysqld_safe --skip-grant-tables &
>
> 3 - mysql -u root
>
> 4 - mysql> use mysql;
>
> mysql> mysql> update user set password='letmein' where user='root';
> Query OK, 2 rows affected (0.00 sec)
> Rows matched: 2 Changed: 2 Warnings: 0
>
> mysql> flush privileges;
>
> mysql> quit
>
> 5 - /etc/init.d/mysqld restart
>
> *Now after I do all that, I get the following:*
>
> mysql -u root -p
> Enter password:
> ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
> password: YES)
>
> I have done this over and over and can't login so I am wondering if
> something is wrong with MySQL or am I just not properly resetting the
> password...
>
> Someone please help!
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Password Reset Not Working

On Thu, May 14, 2009 at 6:31 PM, Carlos Proal wrot=
e:
>
> Hi Carlos
>
> Try this
>
> mysql>  update user set password=3Dpassword('letmein') where user=3D=
'root';
>
>
> This way the password is saved encrypted, thats the way is compared when =
you
> try to log in.

Thanks for the reply! I followed both methods in the following URL / link:

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions .html#resetting=
-permissions-unix

After doing both successfully, I was unable to login over and over. I
think something is wrong with MySQL. I have never seen this before :(

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Password Reset Not Working

Check how many root rows do you have on the user table (select * from
user where user='root';), some times there are several rows with
different grants and probably you are going through and invalid rule.

Carlos

On 5/14/2009 5:39 PM, Carlos Williams wrote:
> On Thu, May 14, 2009 at 6:31 PM, Carlos Proal wrote:
>
>> Hi Carlos
>>
>> Try this
>>
>> mysql> update user set password=password('letmein') where user='root';
>>
>>
>> This way the password is saved encrypted, thats the way is compared when you
>> try to log in.
>>
>
> Thanks for the reply! I followed both methods in the following URL / link:
>
> http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions .html#resetting-permissions-unix
>
> After doing both successfully, I was unable to login over and over. I
> think something is wrong with MySQL. I have never seen this before :(
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Password Reset Not Working

On Thu, May 14, 2009 at 6:44 PM, Carlos Proal wrote:
>
> Check how many root rows do you have on the user table (select * from user
> where user='root';), some times there are several rows with different grants
> and probably you are going through and invalid rule.

I checked and when I ran the command you suggested:

mysql> use mysql;
Database changed

mysql> select * from user where user='root';

I get a bunch of gibberish on the screen but the only thing I can make
out are two entries for root:

| mysql.unixslut.com | root | 6d21bd9609b168e4 | Y | Y | Y
| 127.0.0.1 | root | 6d21bd9609b168e4 | Y | Y
| Y

So what does this mean and how can I resolve this? I am trying this
from the machine locally so I would assume localhost works fine...

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Password Reset Not Working

try running the command like this

select * from user where user='root' \G

Capital G is a must.

thanks Doug



Carlos Williams wrote:
> On Thu, May 14, 2009 at 6:44 PM, Carlos Proal wrote:
>
>> Check how many root rows do you have on the user table (select * from user
>> where user='root';), some times there are several rows with different grants
>> and probably you are going through and invalid rule.
>>
>
> I checked and when I ran the command you suggested:
>
> mysql> use mysql;
> Database changed
>
> mysql> select * from user where user='root';
>
> I get a bunch of gibberish on the screen but the only thing I can make
> out are two entries for root:
>
> | mysql.unixslut.com | root | 6d21bd9609b168e4 | Y | Y | Y
> | 127.0.0.1 | root | 6d21bd9609b168e4 | Y | Y
> | Y
>
> So what does this mean and how can I resolve this? I am trying this
> from the machine locally so I would assume localhost works fine...
>
>

--
* Douglas Nelson *
Senior Technical Consultant

*Need Sun JES Help?*
Software Field Practice
Phone +1 877-234-2879/x51438
Mobile 919-259-3719
Email sfp-help@Sun.COM



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Password Reset Not Working

The machine mysql.unixslut.com is not the same than localhost, right ??,
If you only need root access from localhost you can delete the first row
(delete from user where user='root' and host='mysql.unixslut.com';)


Carlos


On 5/14/2009 5:55 PM, Carlos Williams wrote:
> On Thu, May 14, 2009 at 6:44 PM, Carlos Proal wrote:
>
>> Check how many root rows do you have on the user table (select * from user
>> where user='root';), some times there are several rows with different grants
>> and probably you are going through and invalid rule.
>>
>
> I checked and when I ran the command you suggested:
>
> mysql> use mysql;
> Database changed
>
> mysql> select * from user where user='root';
>
> I get a bunch of gibberish on the screen but the only thing I can make
> out are two entries for root:
>
> | mysql.unixslut.com | root | 6d21bd9609b168e4 | Y | Y | Y
> | 127.0.0.1 | root | 6d21bd9609b168e4 | Y | Y
> | Y
>
> So what does this mean and how can I resolve this? I am trying this
> from the machine locally so I would assume localhost works fine...
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Password Reset Not Working

Carlos Proal wrote:
>
> The machine mysql.unixslut.com is not the same than localhost, right ??,
> If you only need root access from localhost you can delete the first
> row (delete from user where user='root' and host='mysql.unixslut.com';)
>
>
> Carlos
>
>
> On 5/14/2009 5:55 PM, Carlos Williams wrote:
>> On Thu, May 14, 2009 at 6:44 PM, Carlos Proal
>> wrote:
>>
>>> Check how many root rows do you have on the user table (select *
>>> from user
>>> where user='root';), some times there are several rows with
>>> different grants
>>> and probably you are going through and invalid rule.
>>>
>>
>> I checked and when I ran the command you suggested:
>>
>> mysql> use mysql;
>> Database changed
>>
>> mysql> select * from user where user='root';
>>
>> I get a bunch of gibberish on the screen but the only thing I can make
>> out are two entries for root:
>>
>> | mysql.unixslut.com | root | 6d21bd9609b168e4 | Y |
>> Y | Y
>> | 127.0.0.1 | root | 6d21bd9609b168e4 | Y | Y
>> | Y
>>
>> So what does this mean and how can I resolve this? I am trying this
>> from the machine locally so I would assume localhost works fine...
>>
>>
Pardon, for butting in, but are you seriou? unix slut ? My first
impression based on that would be, man you've been hacked! :-D

--
Hervé Kempf: "Pour sauver la planète, sortez du capitalisme."
------------------------------------------------------------ -
Phil Jourdan --- pj@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com/andypantry.php


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Password Reset Not Working

On Thu, May 14, 2009 at 7:01 PM, Douglas Nelson wrote:
> try running the command like this
>
> select * from user where user='root' \G
>
> Capital G is a must.

I did the following:

[root@mysql ~]# /etc/init.d/mysqld stop
Stopping MySQL: [ OK ]

[root@mysql ~]# mysqld_safe --skip-grant-tables &
[1] 3072 Starting mysqld daemon with databases from /var/lib/mysql

[root@mysql ~]# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.45 Source distribution

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> select * from user where user='root' \G
*************************** 1. row ***************************
Host: mysql.unixslut.com
User: root
Password: 6d24bd789879jhs
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
*************************** 2. row ***************************
Host: 127.0.0.1
User: root
Password: 6d24bd789879jhs
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
2 rows in set (0.00 sec)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Password Reset Not Working

On Thu, May 14, 2009 at 7:06 PM, Carlos Proal wrot=
e:
>
> The machine  mysql.unixslut.com is not the same than localhost, righ=
t ??,
> If you only need root access from localhost you can delete the first row
> (delete from user where user=3D'root' and host=3D'mysql.unixslut.com';)

Carlos,

Yes, my machine is localhost / 127.0.0.1/8.
They're both the same machine. It's just that I was told MySQL manages
connection for users on a local / domain basis so that is the reason
for both entries. In my opinion, both entries are valid. I could be
wrong.

**No, the server has not been hacked**

Thanks for your continued support!]

- Carlos

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Password Reset Not Working

Can someone please explain why I have 3 entries for root or if this is
normal behavior for MySQL? I thought after a installation of MySQL,
you normally have 2:

1 - localhost
2 - host.domain.com

For some reason I had a 3rd entry:

3 - 127.0.0.1

I don't know if I did the right thing but I ran the following command:

update user set host="localhost" where host='127.0.0.1';

This basically changed the 127.0.0.1 entry in mysql databased, host
section to localhost.

I am not sure if MySQL needs to have the 3rd host entry for root that
equals 127.0.0.1 or if it generally should just have the localhost and
fqdn.

Can anyone please clear this up for me?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Password Reset Not Working

It is important to understand deeply mysql client access control.

Basically you need only one root account from the localhost for
administration purposes.
Keep in mind that when you login specifying 'localhost' (either by the
-h flag or implicit) MySQL will use the mysql client socket interface,
if you specify '127.0.0.1' it will use the TCP/IP port (3306).
'localhost' is a sort of keyword telling the client to use the unix
socket file, '127.0.0.1' is bound to the tcp/ip port.

Host matching is always done BEFORE user matching.
MySQL sorts HOSTS with more specific entries on top and less ones on
bottom ('%' wildcarded entries)
MySQL sorts USERS with more specific entries on top and less ones on
bottom ('' empty user)

Bottom line, you need a triplet of USER-HOST-PASS for each account.

having localhost AND 127.0.0.1 it is only related to the different
connection method (socket , tcpip)
for any other (remote) account tcp-ip will be used.

I don't know if you are now more ore less confused!

Claudio


Carlos Williams wrote:
> Can someone please explain why I have 3 entries for root or if this is
> normal behavior for MySQL? I thought after a installation of MySQL,
> you normally have 2:
>
> 1 - localhost
> 2 - host.domain.com
>
> For some reason I had a 3rd entry:
>
> 3 - 127.0.0.1
>
> I don't know if I did the right thing but I ran the following command:
>
> update user set host="localhost" where host='127.0.0.1';
>
> This basically changed the 127.0.0.1 entry in mysql databased, host
> section to localhost.
>
> I am not sure if MySQL needs to have the 3rd host entry for root that
> equals 127.0.0.1 or if it generally should just have the localhost and
> fqdn.
>
> Can anyone please clear this up for me?
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Password Reset Not Working

On Fri, May 15, 2009 at 3:57 PM, Claudio Nanni wrote:
> I don't know if you are now more ore less confused!
>
> Claudio

I would say less because you basically explained that I need to have
localhost & 127.0.0.1.
Now my problem is that no longer have this and would like to know what
I can do to resolve
this by re-adding the 127.0.0.1 host / root user parameter back into MySQL.

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> select user, host, password from user;
+-----------+------------------------+------------------+
| user | host | password |
+-----------+------------------------+------------------+
| root | host.domain.com | 032c41e8435273a7 |
| root | localhost | 032c41e8435273a7 |
| roundcube | localhost | 032c41e8435273a7 |
| mrbs | localhost | 6322a1af59897de4 |
| phpbb | localhost | 5d2e19393cc5ef67 |
+-----------+------------------------+------------------+
5 rows in set (0.00 sec)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Password Reset Not Working

Exactly, you need as many rows as many combination user/host

we can also say that an account in MySQL is not the username BUT the
username AND host combination.

If you want to duplicate any account (also the root/localhost) do this:

mysql> show grants for 'root'@'localhost';

then have fun!

Claudio



Carlos Williams wrote:
> On Fri, May 15, 2009 at 3:57 PM, Claudio Nanni wrote:
>
>> I don't know if you are now more ore less confused!
>>
>> Claudio
>>
>
> I would say less because you basically explained that I need to have
> localhost & 127.0.0.1.
> Now my problem is that no longer have this and would like to know what
> I can do to resolve
> this by re-adding the 127.0.0.1 host / root user parameter back into MySQL.
>
> 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> select user, host, password from user;
> +-----------+------------------------+------------------+
> | user | host | password |
> +-----------+------------------------+------------------+
> | root | host.domain.com | 032c41e8435273a7 |
> | root | localhost | 032c41e8435273a7 |
> | roundcube | localhost | 032c41e8435273a7 |
> | mrbs | localhost | 6322a1af59897de4 |
> | phpbb | localhost | 5d2e19393cc5ef67 |
> +-----------+------------------------+------------------+
> 5 rows in set (0.00 sec)
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org