Mysql – Django syncdb can’t connect to MySQL on seperate EC2 Instance

amazon ec2amazon-web-servicesdjangoMySQL

I am trying to deploy a django app on AWS with a setup where I have one EC2 instance that is running nginx/uwsgi and a second EC2 instance where I have MySQL 5.5 installed. Both belong to the default group ( enabled SSH, HTTP, HTTPS and MYSQL). Eventually, I will split the groups for DB and the app/web server but for now I want to get the app to work.

My django db settings look like:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'db',         
        'USER': 'db_user',      
        'PASSWORD': 'db_pwd',
        'HOST': '<public dns of MySQL EC2 instance(tried also private ip)>',
        'PORT': '3306', 
    }
}

Each time I try to run syncdb I get the following error:

_mysql_exceptions.OperationalError: (1130, "Host 'ip-xx-xx-xx-xxx.ec2.internal' is not allowed to connect to this MySQL
server")

Please note when I setup MySQL in the same EC2 instance as the web/app server the app works. In that instance my settings file db settings looked like:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'db',         
        'USER': 'db_user',      
        'PASSWORD': 'db_pwd',
        'HOST': '',
        'PORT': '', 
    }
}

I commented out the bind 127.0.0.1 in /etc/mysql/my.cnf file as one post suggested and even set it at 0.0.0.0 (restarted MySQL after the changes) but that didn't help.

Any suggestions?

Thanks

Binary Maze

Best Answer

[Putting together this answer from the comment stream.]

When setting up the security group, don't enable the MySQL port (3306) for the general Internet (0.0.0.0/0) as this is dangerous. Instead, enable port 3306 it only for a security group in your account so only your instances can connect to it.

You will need to use the internal IP address of the database server, not the public IP address when connecting from a client on another instance.

"bind-address = 0.0.0.0" lets MySQL accept connections from other hosts (subject to security group and iptables rules).

Granting permissions to 'someuser'@'%' allows that user to connect from any host, subject to the security group restrictions.

Related Topic