How to allow mysql access on AWS/EC2 from a specific IP

If you have mysql server on AWS EC2 instances, you may need remote access to mysql server form a specific IP. Note that if you need access between two AWS instances, it is better to use security groups. Here are the steps to allow access from a specific IP:

  • Open /etc/mysql/my.cnf on the instance where you have mysql server change
    bind-address           = 127.0.0.1
    to
    bind-address            = 0.0.0.0
  • Goto security group settings on mysql hosting EC2 instance (on https://console.aws.amazon.com/ec2) and add the following:
    3306  (MYSQL) CLIENT_IP/32
    (CLIENT_IP is ip of the IP from where you want to access mysql from)
    aws_mysql_security_group_settings
  • Once this is done, click on apply rule changes in security group for these changes to take effect.
    aws_mysql_security_group_apply_rule_changes
  • To test if everything is fine, run this from client machine:
    telnet MYSQL_INSTANCE_IP 3306.
    If you see the following, then it means the port access stuff is fine.
    Connected to mysqlserver.com.
    Escape character is ‘^]’.
  • After this you will need to create a mysql user (avoid using root for remote login) and grant it appropriate privileges. e.g. you can create a “writer” user for host “%” and that will let write connect to mysql from other hosts.

Note that this article was written for AWS classic. In case of AWS vpc, instructions may be slightly different. And these instructions should only be used when you are sure that the accessing IP is static and never changes. Otherwise you may have to update the settings every time IP changes which is error prone.

Share this article: share on Google+ share on facebook share on linkedin tweet this submit to reddit

Comments

Click here to write/view comments