asked on

MySQL Access Denied

Hi Experts ..

I was using mysql normally without any problem until today morning, I am trying to connect but I am getting the following:
[root@srv01 mysql]# mysql -u root -p -h localhost
Enter password: *********
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

It seems I have problem in permissions, I tried to reset the root password, but I am getting the following error:
ERROR 1142 (42000) at line 1: UPDATE command denied to user ''@'localhost' for table 'user'

any idea what should I do?

leakim971

Hello elkhawajah,

You should follow the procedure of recovering lost password : http://www.cyberciti.biz/tips/recover-mysql-root-password.html

Regards

elkhawajah

ASKER

Step # 1 : Stop mysql service

# /etc/init.d/mysql stop

I don't know where mysql is installed in the server, It is a dedicated Unix server, could you please help me to find it's location?

leakim971

will take a long time :

find / -name mysql

Open in new window

elkhawajah

ASKER

The search result is :

[root@srv01 ~]# find / -name mysql
/var/lib/mysql
/var/lib/mysql/mysql
/usr/bin/mysql
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/DBD/mysql
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql
/usr/lib64/mysql

I tried them one by one, they are directories except /usr/bin/mysql
[root@srv01 ~]# /usr/bin/mysql stop
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'stop'

elkhawajah

ASKER

OK, I searched for mysql*d* instead of mysql, and followed the steps in the link you gave me, but I am still getting the error after resetting the password:

[root@srv01 mysql]# mysql --user=root -h localhost -p
Enter password:****
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

leakim971

If you follow the link you don't enter "-p" because you restart the daemon in safe mode to receover the password.

elkhawajah

ASKER

I followed the link, and I changed the password successfully, but that does not work too. I am getting the same error as in my last post.

leakim971

OK.
In safe mode (mysqld_safe) could you run the following query and post results :

SELECT * FROM mysql.user

Open in new window

elkhawajah

ASKER

The result is attached

+--------------------------------+------+------------------+-------------+------                -------+-------------+-------------+-------------+-----------+-------------+----                -----------+--------------+-----------+------------+-----------------+----------                --+------------+--------------+------------+-----------------------+------------                ------+--------------+-----------------+------------------+------------------+--                --------------+---------------------+--------------------+------------------+---                -------+------------+-------------+--------------+---------------+-------------+                -----------------+----------------------+
| Host                           | User | Password         | Select_priv | Inser                t_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shu                tdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_pri                v | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables                _priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | S                how_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | ss                l_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates |                 max_connections | max_user_connections |
+--------------------------------+------+------------------+-------------+------                -------+-------------+-------------+-------------+-----------+-------------+----                -----------+--------------+-----------+------------+-----------------+----------                --+------------+--------------+------------+-----------------------+------------                ------+--------------+-----------------+------------------+------------------+--                --------------+---------------------+--------------------+------------------+---                -------+------------+-------------+--------------+---------------+-------------+                -----------------+----------------------+
| 92.241.45.221                  | root | 67457e226a1a15bd | Y           | Y                           | Y           | Y           | Y           | Y         | Y           | Y                             | Y            | Y         | Y          | Y               | Y                          | Y          | Y            | Y          | Y                     | Y                                | Y            | Y               | Y                | Y                | Y                              | Y                   | Y                  | Y                |                          |            |             |              |             0 |           0 |                               0 |                    0 |
| srv01.24172.serviceprovider.de | root | 67457e226a1a15bd | Y           | Y                           | Y           | Y           | Y           | Y         | Y           | Y                             | Y            | Y         | Y          | Y               | Y                          | Y          | Y            | Y          | Y                     | Y                                | Y            | Y               | Y                | Y                | Y                              | Y                   | Y                  | Y                |                          |            |             |              |             0 |           0 |                               0 |                    0 |
| 127.0.0.1                      | root | 67457e226a1a15bd | Y           | Y                           | Y           | Y           | Y           | Y         | Y           | Y                             | Y            | Y         | Y          | Y               | Y                          | Y          | Y            | Y          | Y                     | Y                                | Y            | Y               | Y                | Y                | Y                              | Y                   | Y                  | Y                |                          |            |             |              |             0 |           0 |                               0 |                    0 |
| localhost                      |      |                  | N           | N                           | N           | N           | N           | N         | N           | N                             | N            | N         | N          | N               | N                          | N          | N            | N          | N                     | N                                | N            | N               | N                | N                |                                | N                   | N                  | N                |                          |            |             |              |             0 |           0                                 0 |                    0 |
| srv01.24172.serviceprovider.de |      |                  | N           | N                           | N           | N           | N           | N         | N           | N                             | N            | N         | N          | N               | N                          | N          | N            | N          | N                     | N                                | N            | N               | N                | N                |                                | N                   | N                  | N                |                          |            |             |              |             0 |           0                                 0 |                    0 |
+--------------------------------+------+------------------+-------------+----                  -------+-------------+-------------+-------------+-----------+-------------+--                  -----------+--------------+-----------+------------+-----------------+--------                  --+------------+--------------+------------+-----------------------+----------                  ------+--------------+-----------------+------------------+------------------+                  --------------+---------------------+--------------------+------------------+-                  -------+------------+-------------+--------------+---------------+------------                  -----------------+----------------------+
5 rows in set (0.00 sec)

Open in new window

ee.txt

elkhawajah

ASKER

I tried the following and it works:

mysql -u root -h srv01.24172.serviceprovider.de -p

maybe I should update the table so localhost have the same entires as srv01.24172.serviceprovider.de?

leakim971

As you can see, you've no user like root@localhost, but one with 127.0.0.1
We can update it :

UPDATE mysql.user SET Host = 'localhost' WHERE User = 'root' AND Host LIKE '127.0.0.1'

Open in new window

ASKER CERTIFIED SOLUTION

leakim971

membership

This solution is only available to members.

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

Start Free Trial

elkhawajah

ASKER

It works!! Thank you so much :)

leakim971

You're welcome! Thanks for the points! Have fun!