Mysql - Access denied for user 'root'@'x.x.x.x'

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

For Unix like systems the following solution worked for me to reset the root password: http://www.cyberciti.biz/tips/recover-mysql-root-password.html

Jist:

Recover MySQL database server password with following five easy steps:

1. Stop the MySQL server process.

2. Start the MySQL (mysqld) server/daemon process with the --skip-grant-tables option so that it will not prompt for password.

3. Connect to mysql server as the root user.

4. Setup new mysql root account password i.e. reset mysql password.

5. Exit and restart the MySQL server.

Specific commands: (login as the root user):

1. Stop mysql service

   # /etc/init.d/mysql stop
   

   Output:

   Stopping MySQL database server: mysqld.
   

2. Start to MySQL server w/o password:

   use mysqld_safe:

   mysqld_safe --skip-grant-tables &
   

   Output:

   [1] 5988
   Starting mysqld daemon with databases from /var/lib/mysql
   mysqld_safe[6025]: started
   

3. Connect to mysql server using mysql client:

   # mysql -u root
   

   Output:

   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log
   Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
   mysql>
   

4. Setup new MySQL root user password

   mysql> use mysql;
   mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
   mysql> flush privileges;
   mysql> quit
   

5. Stop MySQL Server:

   # /etc/init.d/mysql stop
   

   Output:

   Stopping MySQL database server: mysqld
   STOPPING server from pid file /var/run/mysqld/mysqld.pid
   mysqld_safe[6186]: ended
   [1]+  Done                    mysqld_safe --skip-grant-tables
   

6. Start MySQL server and test it:

   # /etc/init.d/mysql start
   # mysql -u root -p
   

Answer 5

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

Answer 6

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.