1130 Host 'amazon-ec2-ip' is not allowed to connect to this MySQL server
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.
I was also facing similar issue. Use only number part of the DNS name which is say for example the public DNS name is ec2-13-114-245-645.compute-1.amazonaws.com then use only 13.114.245.645 for connecting to that instance.
Hope it helps.