Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

This question already has an answer here:

I am trying to connect to a remote mysql database from my local box. Initially I was not even able to hit the mysql db. But after commenting the binding_address in the my.cnf file and restarting the mysql server, I am now able to hit it.

But now, I am getting an Access denied error

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

Is my IP being blacklisted or something? I can change that since I have admin permission on the remote box. Somebody help me.

share|improve this question

migrated from stackoverflow.com Feb 2 '11 at 8:11

This question came from our site for professional and enthusiast programmers.

marked as duplicate by Chris S May 28 at 14:02

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

add comment

7 Answers 7

up vote 5 down vote accepted

Double check to make sure root has access from hosts/IPs other than localhost.

mysql> use mysql;
mysql> SELECT host FROM user WHERE user = 'root';

If it returns just one row with "localhost", you need to grant permissions to the IP you are connecting from.

mysql> GRANT SELECT, INSERT ON mydatabase.* TO 'root'@'10.0.0.1';

Replace mydatabase with the database you want access to (or put in a wildcard), then replace the IP with the address or subnet your client is connecting from.

share|improve this answer
    
yeah.. i only have localhost there. How do i grant permission to the IP ? –  bragboy Jan 31 '11 at 22:57
    
mysql> GRANT SELECT, INSERT ON mydatabase.* TO 'root'@'10.0.0.1'; –  Serge Stepanov Jan 31 '11 at 22:58
1  
Also don't forget to flush privileges. mysql> FLUSH PRIVILEGES; –  Serge Stepanov Jan 31 '11 at 22:59
    
Do i need to restart the server after doing this ? –  bragboy Jan 31 '11 at 23:03
2  
@Bragboy you probably need to set a password for the account too rather than just leave the account passwordless. By default, each grant entry has a separate password too. Look up the GRANT command in the mysql docs, I forget the exact clause now (I usually manage users with phpmyadmin) –  Matthew Scharley Feb 1 '11 at 0:53
show 9 more comments

for me the following solution worked: http://www.cyberciti.biz/tips/recover-mysql-root-password.html

share|improve this answer
add comment

First order of business is getting in with full permissions. If you cannot, add this line to your my.cnf and restart mysql:

skip-grant-tables

I recommend doing a little recon to see what users you have:

SELECT user,host,password FROM mysql.user WHERE user = 'root' ORDER BY host ASC;

This will display your users list, something like:

mysql> SELECT user,host,password FROM mysql.user WHERE user = 'root' ORDER BY host ASC;
+------+------------+-------------------------------------------+
| user | host       | password                                  |
+------+------------+-------------------------------------------+
| root | 127.0.0.1  |                                           |
| root | ::1        |                                           |
| root | localhost  | *E0AD777475E6713F9B04317EE38888D61042DAC1 |
| root | randym-mbp |                                           |
+------+------------+-------------------------------------------+
4 rows in set (0.01 sec)

You need to see localhost in the list. If you do not, add it like this:

GRANT ALL PRIVILEGES ON *.* TO root@'localhost' IDENTIFIED BY 'your_password' WITH GRANT OPTION;

Otherwise simply update the password like this:

SET PASSWORD FOR root@'localhost' = PASSWORD('your_password');
FLUSH PRIVILEGES;

Then remove skip-grant-tables from your my.cnf and restart mysql.

share|improve this answer
add comment

try first to grant all privelages for the root user.

share|improve this answer
add comment

Unfortunately mysql permissions distinguish by host and ip. Permissions granted for a hostname do not work for an IP and vice versa.

Make sure you grant permissions for the user and host ip/host name you are connecting from.

share|improve this answer
    
This is definitely not unfortunate. Personally, I don't want the root user, or a couple other users able to be logged into remotely, but I might like to allow some other users with limited permissions to connect remotely. –  Matthew Scharley Jan 31 '11 at 23:01
2  
Permissions are checked by ip and FQDN separately. If i connect from my box and the mysql server is able to resolve my ip into a FQDN then the rules differ from the situation where my ip is not resolvable. I'd call this inconsistency unfortunate. –  Spliffster Jan 31 '11 at 23:08
add comment

Its possible that you need to grant privileges for that IP, an example would be:

mysql> GRANT ALL PRIVILEGES on dbname.* to root@'%'; mysql> FLUSH PRIVILEGES;

grants to all IPs

share|improve this answer
add comment

Passwords in mysql are setup per IP. Usually they are only set for access via localhost only, especially for the root user. Verify that there is a root@% entry setup in your permissions table.

If you really do need to allow remote root access, then please consider changing the username so that people can't easily guess your servers superuser account.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.