Connecting to a mysql running on a Docker container

[dzlab]

I'm trying to run mysql server on a Docker (installed with Docker Toolbox for Mac) container and access it from my machine running OS X Yosemite. The documentation from the official repo does not explain how to connect from outside the docker host !!

I've created a container using the official repository as follows:

$ docker pull mysql
$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest -p 3306:3306 
$ docker-machine ip default
192.168.99.100
$ docker inspect CONTAINER_ID

Then I get the ip address (172.17.0.1), but when I ping it I see time outs!!!
I tried to add port forward to the default machine:

VBoxManage controlvm "default" natpf1 "tcp-port3306,tcp,,3306,,3306";

But when connecting with Sequel pro (a mysql client) with root@192.168.99.100 on 3306, I have a connection failure.

What's the appropriate way to connect to the running mysql server?

[dzlab]

It looks mysql interpreting the -p as an argument, I just had to move the argument:

docker run -p 3306:3306 --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest

[flacoman91]

By any chance did you get this to work? I have a docker container that runs mysql, but i cannot figure out what the settings are in sequel pro to get it to connect.

What credentials are needed?

[helderco]

@flacoman91 how are you running your container (i.e., arguments to docker run, or docker compose service)?

[flacoman91]

@helderco
It's in docker-compose.yml
mysql:
build: docker-build-contexts/mysql
environment:

• MYSQL_ROOT_PASSWORD=mypassword

Running docker ps i get
fc46f6b9e08e service_mysql "/entrypoint.sh mysql" 18 hours ago Up 18 hours 3306/tcp service_mysql_1

[helderco]

You need to publish your mysql port to your host. The port can be a specific one or random. If random use docker-compose port mysql 3306 to get it at runtime (you can also see it in docker-compose ps).

Since you're using Sequel Pro, I guess you're using a Mac, with a VM for docker server. If you're using docker-machine, you can get the IP address with docker-machine ip <name>.

Credentials (Standard connection in Sequel Pro):

Host: IP of your docker machine host
Username: root
Password: mypassword
Database: mydatabase
Port: your specific port or randomly attributed one

Example for random host port (avoids conflict with other projects):

mysql:
  build: docker-build-contexts/mysql
  ports:
    - 3306
  environment:
    - MYSQL_DATABASE=mydatabase
    - MYSQL_ROOT_PASSWORD=mypassword

I actually use a script to open directly in Sequel Pro from the command line. You can get it at https://gist.github.com/helderco/e9d8d072a362ad818f6a

This is how I use it:

db-open mydatabase

It launches a new window already opened in that database in my container. Note that I assume password is root (my convention in development). It also assumes you use docker-machine. The database is optional, and you can set the other credentials manually with arguments.

[flacoman91]

THANK YOU THANK YOU!

That worked. i missed the step where you said:

If you're using docker-machine, you can get the IP address with docker-machine ip .

I kept putting in the ip for the individual container, and not the docker host, which in my case is just the default or 192.168.99.100 and the port was the randomly assigned one which was found through
docker ps

[fariazz]

Struggled with this for ages until I realised I was entering http://my-docker-ip-address instead of just my-docker-ip-address. Remove that http!

[dslopez]

Hi, I have this container:

$ docker ps
CONTAINER ID IMAGE COMMAND CREATED
STATUS PORTS NAMES
8441f5d60a49 mysql:5.7 "docker-entrypoint.sh" 2 hours ago
Up 2 hours 3306/tcp shopapp-mysql

And i run
$ docker-machine ip shopapp-mysql

Getting
Host does not exist: "shopapp-mysql"

I've tried with Container ID and have the same response. ¿How can I get the specific IP of the shopapp-mysql container?

[helderco]

You're trying to use docker machine in a container, which are different tools.

Try:

$ docker inspect shopapp-mysql

[dslopez]

I've tried that too, it give me a IPAddress=172...... I try to connect to that IP from an mysql client but I get conection timeout....

[helderco]

I assume you use docker machine with Mac or Windows.

Just find the forwarded port with:

$ docker port shopapp-mysql 3306
0.0.0.0:32768

And your docker machine IP with:

$ docker-machine ip
192.168.99.100

Now point your mysql client to (example): 192.168.99.100:32768

Note: The 3306 port needs to be exposed, which from your docker ps I can see it is not.

[dslopez]

thanks @helderco !! I have run my container exposing the port to my PC

$ docker run -p 3306:3306 --name shopapp-mysql -e MYSQL_ROOT_PASSWORD=myRootpwd32 -d mysql:5.7

And that works!

[TristanCP]

In this Dockerfile, a "mysql" user is created but there is no "USER mysql" instruction. So why, when you run the image without "-u" or "--user" mysqld is running as "mysql" and not as "root" ?

[yosifkit]

@TristanCP, the entrypoint script drops to the mysql user as soon as it has done the things that required root access:

mysql/8.0/docker-entrypoint.sh

Lines 62 to 67 in 64e0cf2

	if [ "$1" = 'mysqld' -a -z "$wantHelp" -a "$(id -u)" = '0' ]; then
	_check_config "$@"
	DATADIR="$(_datadir "$@")"
	mkdir -p "$DATADIR"
	chown -R mysql:mysql "$DATADIR"
	exec gosu mysql "$BASH_SOURCE" "$@"

.

[tianon]

It should also work with --user mysql if you setup permissions yourself before running the image. 👍

[ssh24]

@dslopez @helderco I was going through your conversation and the rest of it in this issue but could not find a solution to my issue.

I have followed the https://hub.docker.com/_/mysql/ to setup a mysql container. So I did something like this docker run --name mysql-inst -e MYSQL_ROOT_PASSWORD=<my_pass> -d mysql:latest.

Then I have the app which is another container itself and link it on runtime by doing this docker run --name some-app --link mysql-inst:mysql -d application-that-uses-mysql. Now the app fails to connect to the database and timeouts.

So I did docker inspect mysql-inst and got the IPAddress and tried to connect to the mysql client through my local and it would not connect either.

mysql -h172.20.0.3 -P 3306 -uroot -p<my_pass>
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '172.20.0.3' (60)

Any suggestion or idea what I am doing wrong? Ideally I would like to do using docker-compose but I want to know why this way is not working.

[yosifkit]

@ssh24, if you are on Windows or OSX using docker-machine/boot2docker, you won't be able to connect directly to a container's IP address from the host. You would need to start the container with -p 3306:3306 and then connect to the ip of the Docker VM on port 3306.

In the application that uses mysql how are you connecting to the mysql service? In other words, are you connecting to localhost? Since the --link alias is mysql (the second half of the link argument), you should be connecting from the application container to the mysql host and it will be properly resolved to the IP address.

[ssh24]

@yosifkit I was not using docker-machine to connect to the container's IP address.

I just tried this to connect to the mysql client locally after spinning up the container and still won't work. The IP address of the mysql container is 172.17.0.2
docker run --name some-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=<password> -d mysql:latest

mysql -h172.17.0.2 -P3306 -uroot -p<password>
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '172.17.0.2' (60)

[netname]

@ssh24 Are you running on Windows or Mac?

If running on Windows or Mac the containers are created on a virtual machine. That is, the host of the containers is the virtual machine not your actual machine. So find out which is the IP of the docker VM and use it instead of 172.17.0.2

[dslopez]

@ssh24 you need to do a

$ docker-machine ip

to obtain the IP address of your container.

[johanchouquet]

This is exactly what i searched for @dslopez and @netname ! This way, I can now use HeidiSql from Windows to see my DB running my container! Great! Thank you.

[aercolino]

I just found out I can connect SequelPro to a MySQL server on Docker with these data: (no need for the IP of the server)

Host: 0.0.0.0
Port: 32768
Username: wordpress
Password: wordpress

I posted my configuration here: http://andowebsit.es/blog/noteslog.com/post/how-to-install-wordpress-with-docker-on-os-sierra/

[pieisgood]

@aercolino came here to say that switching my local mysql instance from 3306 port to 32768 fixed my issue with connect to it through SequelPro. Good looking out there dude!

[kamil-kielczewski]

I have problem to set up mysql but i realized that i try connect to EARLY (!)

If I make docker run... and wait approx 5min then everything was OK - i was able to connect :)

[vickygupta0017]

@dslopez , after running "docker-machine ip" it is giving error
Error: No machine name(s) specified and no "default" machine exists

the result of the command "docker port mysql_container 3306" is : 0.0.0.0:3306
I am new with the docker and trying to access MySQL on my mac os.

[CollinChaffin]

FYI, this may not be as easy as using the docker host IP (it did NOT work for me), even if you have defined the port (3306). Look in the compose file and all config files in etc, because in the case below, OUTSIDE of the container, on my local LAN, in a webpage being served from the docker host, instead of localhost, or any IP, I was required to enter the word "DATABASE" (http://database:3306) because THAT is what was defined in the config file as the dbhost......and it actually worked.

DB_SYSTEM=mysql
DB_HOST=database
DB_PORT=3306
DB_SOCKET=""
DB_USER=sqluser
DB_PASSWORD=sqlpass
DB_NAME=mydatabase

[leepowelldev]

Facing a similar problem using Docker on Mac:
Version 17.09.0-ce-mac35 (19611)

$ docker run -p 3306:3306 --name some-mysql -e MYSQL_ROOT_PASSWORD=mypassword -d mysql

$ docker ps
624134d3318a    mysql    "docker-entrypoint..."    14 minutes ago    Up 14 minutes    0.0.0.0:3306->3306/tcp    some-mysql

$ mysql --host 0.0.0.0 --port 3306 --user root --password

ERROR 2003 (HY000): Can't connect to MySQL server on '0.0.0.0' (61)

[tianon]

Try "127.0.0.1" for "--host" instead of "0.0.0.0".

[leepowelldev]

I have tried that with no success

[vickygupta0017]

This worked for me.

docker run -it -v --name mysql-server -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password -e MYSQL_DATABASE=demo mysql

[leepowelldev]

Not sure what happened overnight, but ran the same command this morning and it works!

[ghost]

Hi,
This worked for me:
docker run -p 3306:3306 --name mysqlserver -e MYSQL_ROOT_PASSWORD=password -d mysql:latest

and on Heidisql (Windows):
Sever / IP: 127.0.0.1
User : root
Password : password

[NextZeus]

@aercolino when i click your link below, the page was not found.

[NextZeus]

my solution with mysql 5.7.x

docker run -p 3306:3306 --name mysql2 -e MYSQL_ROOT_PASSWORD=b3RmELKOvCUrAdxIg0GEmugc3SY -e MYSQL_ROOT_HOST=% -d mysql/mysql-server:latest

MySQLWorkbench config

Hostname: 0.0.0.0
Port: 3306
Username: root
Password: b3RmELKOvCUrAdxIg0GEmugc3SY  [mysql  random secret]

nodejs mysql

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : '0.0.0.0',
  port     : 3306,
  user     : 'root',
  password : 'b3RmELKOvCUrAdxIg0GEmugc3SY',
  database : 'mysql'
});

connection.connect();

connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

connection.end();

[hailiang-wang]

connection.end();  // 应该放在  query callback 里

@NextZeus

[NextZeus]

@Samurais 嗯。https://github.com/mysqljs/mysql 的REAEME.md是这么写的，我直接复制过去的测试用的。

[gischy]

After i added the missing vbox network portforwarding rule it worked for me.

[ghost]

docker run --name=[name] -p 3306:3306 --rm -e MYSQL_ROOT_PASSWORD=[password] mysql:latest

docker exec -it [name] /bin/bash

mysql -u root -p

[jsaunie]

Mysql service configuration

mysql:
    image: mysql:8.0.19
    container_name: mysql
    restart: unless-stopped
    tty: true
    ports:
      - "4306:3306"
    volumes:
      - ./mysql:/var/lib/mysql
    environment:
      MYSQL_DATABASE: homestead
      MYSQL_USER: homestead
      MYSQL_PASSWORD: secret
      MYSQL_ROOT_PASSWORD: secret
      SERVICE_TAGS: dev
      SERVICE_NAME: mysql

Connect to the container:

• docker exec -it [mysql_container_name] sh

To connect to mysql on your container:

• mysql -u root -p

The password is the env variable MYSQL_ROOT_PASSWORD

After logging into mysql on your mysql container, run this command:

• ALTER USER 'here_env_mysql_user' IDENTIFIED WITH mysql_native_password BY 'here_env_mysql_password';

You get the message: Query OK, 0 rows affected (0.00 sec)

You can now go to Sequel Pro and connect to your mysql container.

Or simply add this command to your mysql service config in docker-compose.yml

• command: --default-authentication-plugin=mysql_native_password
  (https://hub.docker.com/_/mysql)