Geek Answers Handbook

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

I had a problem accessing the mysql database on one of my Amazon EC2 servers from another EC2 server. I have read various articles regarding granting appropriate permissions to access mysql from external IP addresses, and here are the steps I followed:

  • Opened port 3306 on my host EC2 instance to allow an external Mysql connection.
  • In the file /etc/mysql/my.cnf changed "bind-address" from "127.0.0.1" to "0.0.0.0".
  • I opened mysql with root and ran the following command: GRANT ALL PRIVILEGES to. for working @ 'ec2-ip-address' IDENTIFIED BY 'password';

Like all the blogs / articles I read, this should have solved the problem, but I keep getting the following error:

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

The IP address that I provided for the EC2 instance was the elastic IP that was generated when the instance was created. To check if the problem is specific to EC2, I tried to execute the "3" command for another "static IP address". Now this worked for me (i.e., I was able to log into the mysql host from this remote server), so it is sure that the above steps are correct.

Why is the Amazon EC2 IP address not working?

+6
source share
4 answers

After posting this problem when I was working, I realized that I couldn’t even ping to the EC2 or telnet server. So something basic was supposed to be wrong. Finally a friend helped me with the problem. As I expected, the problem was very specific to EC2.

Details:

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

When setting permissions in mysql, I granted 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 if you are trying to contact an EC2 instance from another local EC2 instance. To do 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

For everything to work correctly, you need to provide permission for the IP address - "XX.XXX.XX.XXX/23", and it should work. Likewise, when connecting to the mysql database, the host name provided to the mysql command must also be the "internal IP address" of the host EC2 instance.

+4
source

I also ran into a similar problem. Use only the part number of the DNS name, for example, the public DNS name is ec2-13-114-245-645.compute-1.amazonaws.com, then use only 13.114.245.645 to connect to this instance.

Hope this helps.

+1
source

Log in to the local mariadb / mysql server & enter the command below:

GRANT ALL PRIVILEGES. TO 'root'@'10.0.1.40', identified by 'redhat' with the GRANT option;

Here, 10.0.1.40 is my IP address of the remote ec2 instance.

It should work.

0
source

The same problem that I solved on my AWS Ubuntu server (18.04). Here are the steps:

  • Open port 3306 from the rule security_group-> InBound.
  • Configure the file /etc/mysql/mysql.conf.d/mysqld.cnf: bind-address = 0.0.0.0 . With it, all IP addresses will connect remotely to mySql
  • .Add privilege to user in MySQL :
    • GRANT ALL PRIVILEGES. to YOUR_USER@ 'YOUR.PRIVATE_IP.ADDRESS.HERE' IDENTIFIED 'AN_USER_PASSWORD' ;

The last step is important. Here you add User, Private IP address with a password that the user will use for remote login.

An example :

GRANT ALL PRIVILEGES. to root@ 'XX.XX.XX.XX' IDENTIFIED 'ADMIN_AWS123';

Here root is your user, XX.XX.XX.XX is your private IP address, and ADMIN_AWS123 is your remote password.

Hope this helps you :)

0
source

More articles:


All Articles