www.centos.org - Forums - CentOS 5 - Server Support

www.centos.org Forum Index

CentOS 5 - Server Support

Locked Out of MySQL

Bottom

Previous Topic

Next Topic

Topic options

Rate Thread

Rate this Thread
Excellent Good Average Bad Terrible

Search Forum

Search Forum

Advanced Search

View mode

View mode
Threaded
Newest First

Poster	Thread

CarlosinFL

Locked Out of MySQL

Regular Board Member

Joined: 2006/6/23

From Orlando, FL

Posts: 162

Before I explain, I would like to say I have attempted to reset my password at least 10 times now based on the MySQL procedures for recovering / resetting a root password. It has not worked and I find it strange I was locked out to begin with since I always use the same password and I am 100% sure I know what it is.

I am stopping MySQL server:

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

I start the server again w/o password:

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

[root@mysql ~]# /etc/init.d/mysqld status
mysqld (pid 3530) is running...

So now I can login to MySQL as root w/o a password:

[root@mysql ~]# /etc/init.d/mysqld status
mysqld (pid 3530) is running...

[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>

OK - I am in and should be able to now reset the password for root...

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

There I show I made the change successfully since with 'Changed:2.

Now I flush privileges and exit mysql and restart mysqld as normal so I can test the password for root.

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

Now when I restart the mysqld service and try and login as root, I still can't get it. I don't understand...

[root@mysql ~]# /etc/init.d/mysqld stop
STOPPING server from pid file /var/run/mysqld/mysqld.pid
090514 22:09:00  mysqld ended

Stopping MySQL:                                            [  OK  ]
[1]+  Done                    mysqld_safe --skip-grant-tables

[root@mysql ~]# 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 something is wrong. I don't understand 1st why I was locked out and now I can't understand why I can't even reset the password and login.

Please help!

Posted on: 2009/5/15 2:21

yyagol

Re: Locked Out of MySQL

Professional Board Member

Joined: 2006/6/10

From

Posts: 889

It looks more like privileges , then password ,see
http://dev.mysql.com/doc/refman/5.1/en/access-denied.html
try to re-grant privileges for root

GRANT ALL ON *.* TO 'root'@'localhost';

_________________
P Save a tree...please don't print this unless you really need to

Posted on: 2009/5/15 5:14

CarlosinFL

Re: Locked Out of MySQL

Regular Board Member

Joined: 2006/6/23

From Orlando, FL

Posts: 162

I notice that when I check the following: there is not host entry for 'localhost' but only 127.0.0.1.

mysql> select host, user, password from user;
+------------------------+-----------+------------------+
| host                   | user      | password         |
+------------------------+-----------+------------------+
| mysql.unixslut.com | root      | 0b034ec713f89a68 | 
| 127.0.0.1              | root      | 0b034ec713f89a68 | 
| localhost              | roundcube | 032c41e8435273a7 | 
| localhost              | mrbs      | 6322a1af59897de4 | 
| localhost              | phpbb     | 5d2e19393cc5ef67 | 
+------------------------+-----------+------------------+
5 rows in set (0.00 sec)

Now when I check this on my test server, I see the following:

mysql> select host, user, password from user;
+---------------+-----------+------------------+
| host          | user      | password         |
+---------------+-----------+------------------+
| localhost     | root      | 032c41e8435273a7 | 
| mail.test.com | root      | 032c41e8435273a7 | 
| 127.0.0.1     | root      |                  | 
| localhost     | username  | 5d2e19393cc5ef67 | 
| localhost     | roundcube | 5d2e19393cc5ef67 | 
+---------------+-----------+------------------+
5 rows in set (0.00 sec)

Posted on: 2009/5/15 15:39