Unable to connect to dockerized mysql db remotely

Question

On my AWS ec2 server I have docker 1.9.1 installed. In an image test_image based from ubuntu:trusty official docker image, I have tried to setup the LEMP(Linux, Nginx, MySQL, PHP) architecture.

Following is the docker command i have used to start my container:

docker run --name test_1 -d -p 80:80 -p 3306:3306 test_image /bin/sh -c "while true; do echo daemonized docker container; sleep 5000; done"

I have exposed port 80 and 3306 to the host's network interface and have also allowed AWS's security group to allow inbound connections to these ports. Connection type in security group is: MYSQL/Aurora and protocol is: TCP (I know its not very secure, its only for initial implementation. Production setup will be different)

I followed this DigitalOcean tutorial: https://www.digitalocean.com/community/tutorials/how-to-install-linux-nginx-mysql-php-lemp-stack-on-ubuntu-14-04

After installing Nginx and starting it I am able to test it in the browser via ec2's pubic ip i.e. http://xxx.xxx.xxx.xxx shows the default nginx welcome page.

While installing MySQL, I followed the following commands in the docker container:

apt-get install mysql-server
mysql_install_db
/etc/init.d/mysql start
mysql_secure_installation

I have given a password to my root user and during mysql_secure_installation i had allowed remote access to root user.

mysql -u root -p command from inside the container connects me to the mysql db but not from outside the container.

Also from my local machine: I tried with mysql-client: mysql -h xxx.xxx.xxx.xxx -u root -p

I got the following error: ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (111)

and also through mysql workbench but I still can't connect to the mysql db.

What am I doing wrong?

Solution

In your host mysql's my.cnf set the bind address to 0.0.0.0 so that mysql listens on all network interfaces

bind-address = 0.0.0.0

The default config is:

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address   = 127.0.0.1

Comments

[Surender Thakran]

I tried your solution. I still can't connect but the error message on my local machine in mysql-client changed to: ERROR 1130 (HY000): Host 'yyy.yyy.yyy.yyy' is not allowed to connect to this MySQL server. 'yyy.yyy.yyy.yyy' is my local's public ip. And on the ec2 host its now: ERROR 1130 (HY000): Host 'ip-zzz-zzz-zzz-zzz.ec2.internal' is not allowed to connect to this MySQL server

[molivier]

You have to grant access to your machine: GRANT ALL PRIVILEGES ON database.* TO 'user'@'yourremotehost' IDENTIFIED BY 'newpassword'; This post is helpful too: SO

[Surender Thakran]

But I am trying to access as the root user. Shouldn't I already have all the privileges?

[molivier]

The previous command allow user with a specific ip address @remoteIP. To allow root user for all remote ip GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';

[Surender Thakran]

Thanks a lot! It worked. So basically even if the root user is trying to connect to the database from a remote host, he has to be explicitly granted privileges. Right?

[Surender Thakran]

Or rather the remote host is being provided privilege to connect using the root user.

[molivier]

You're right! It's not the default behaviour because of security concerns.