1130 Host 'amazon-ec2-ip' is not allowed to connect to this MySQL server

Question

I am facing a problem in accessing the mysql DB on one of my Amazon EC2 servers from another EC2 server. I read through various articles regarding providing appropriate permissions for mysql to be accessed from external IP addresses, and here are the steps that I followed:

1. Opened port 3306 on my host EC2 instance to allow for external Mysql connection.
2. In the file /etc/mysql/my.cnf, changed the "bind-address" from "127.0.0.1" to "0.0.0.0".
3. Opened mysql using root, and executed the following command: GRANT ALL PRIVILEGES on . to worker@'ec2-ip-address' IDENTIFIED BY 'password';

As per all the blogs/articles that I read, this should have solved the issue, but i keep getting the following error:

1130  Host 'amazon-ec2-ip' is not allowed to connect to this MySQL server

The ip address I was providing for the EC2 instance was an elastic IP that gets generated when you create an instance. To verify whether the issue is specific to EC2, I tried executing the command "3" for a different "static ip address". Now, this worked for me (i.e. I was able to login to the mysql host from that remote server), so it is sure that the above steps are correct.

Why is the Amazon EC2 ip address not working?

Answer 1

After posting this problem, as I was working, I realized that I wasn't even able to ping to the EC2 server or telnet to it. So something basic had to be wrong. Finally a friend helped me out with the problem. As I had expected, the problem was very specific to EC2.

The details are as follows:

When we create an EC2 instance, we get an external IP address which is similar to: ec2-XX-XXX-XXX-XX.ap-southeast-1.compute.amazonaws.com

While setting the permissions in the mysql I was granting the permissions to the above IP address, i.e.:

GRANT ALL PRIVILEGES on . to worker@'ec2-XX-XXX-XXX-XX.ap-southeast1.compute.amazonaws.com' IDENTIFIED BY 'password';

This does not work when you try to communicate to an EC2 instance from another local EC2 instance. For this, you need to provide the 'internal ip address" of the EC2 instance, which can be found using the ip command:

ip a:

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN 
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host 
valid_lft forever preferred_lft forever

2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 12:31:41:02:58:47 brd ff:ff:ff:ff:ff:ff
inet **XX.XX.XX.XXX/23** brd YY.YYY.YY.YYY scope global eth0
inet6 fe80::1031:41ff:fe02:5847/64 scope link 
valid_lft forever preferred_lft forever

To get things working correctly, you need to grant the permission to the ip address --"XX.XXX.XX.XXX/23", and it should work. Similarly, while connecting to the "mysql" database, the hostname provided to the mysql command should also be the "internal ip address" of the host EC2 instance.