?
Solved

MySQL Access Denied

Posted on 2009-12-27
14
Medium Priority
?
680 Views
Last Modified: 2012-05-08
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?
0
Comment
Question by:elkhawajah
  • 7
  • 7
14 Comments
 
LVL 83

Expert Comment

by:leakim971
ID: 26128772
Hello elkhawajah,

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

Regards
0
 
LVL 1

Author Comment

by:elkhawajah
ID: 26128862
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?
0
 
LVL 83

Expert Comment

by:leakim971
ID: 26128867
will take a long time :

find / -name mysql

Open in new window

0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 1

Author Comment

by:elkhawajah
ID: 26130233
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'
0
 
LVL 1

Author Comment

by:elkhawajah
ID: 26130275
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)
0
 
LVL 83

Expert Comment

by:leakim971
ID: 26130687
If you follow the link you don't enter "-p" because you restart the daemon in safe mode to receover the password.
0
 
LVL 1

Author Comment

by:elkhawajah
ID: 26130707
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.
0
 
LVL 83

Expert Comment

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




SELECT * FROM mysql.user

Open in new window

0
 
LVL 1

Author Comment

by:elkhawajah
ID: 26130815
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
0
 
LVL 1

Author Comment

by:elkhawajah
ID: 26130865
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?
0
 
LVL 83

Expert Comment

by:leakim971
ID: 26130882
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

0
 
LVL 83

Accepted Solution

by:
leakim971 earned 2000 total points
ID: 26130885
If you prefer to update the row with srv01.24172.serviceprovider.de,  why not.
You will create a new one after if needed.

UPDATE mysql.user SET Host = 'localhost' WHERE User = 'root' AND Host LIKE 'srv01.24172.serviceprovider.de'

Open in new window

0
 
LVL 1

Author Comment

by:elkhawajah
ID: 26131201
It works!! Thank you so much :)
0
 
LVL 83

Expert Comment

by:leakim971
ID: 26131208
You're welcome! Thanks for the points! Have fun!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
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…
Suggested Courses
Course of the Month17 days, 2 hours left to enroll

862 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