2

I am new to golang, docker and mysql. I am trying to connect to mysql running in docker locally on my macos using golang.

Here is the code:

`package main

import (
    "context"
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "log"
    "time"
)

func dbConn() *sql.DB {
    db, err := sql.Open("mysql", "root:Abc123$#@tcp(172.17.0.2:3306)/test")
    if err != nil {
        log.Printf("Error %s when opening DB connection\n", err)
        return nil
    }
    db.SetMaxOpenConns(10)
    db.SetMaxIdleConns(10)
    db.SetConnMaxLifetime(time.Minute * 2)

    ctx, cancelfunc := context.WithTimeout(context.Background(), time.Second)
    defer cancelfunc()
    err = db.PingContext(ctx)
    if err != nil {
        log.Printf("Error %s pinging DB", err)
        return db
    }
    log.Print("Connected to the DB successfully\n")

    defer func() {
        err := db.Close()
        if err != nil {
            log.Print(err)
        }
    }()
    return db
}

func main() {
    db := dbConn()
    defer db.Close()
}`

I am running docker with the following command:

docker run --name mysql -e MYSQL_ROOT_PASSWORD=abcd1234 -p 3306:3306 -d mysql:8.0.30

I get the following error:

Error dial tcp 172.17.0.2:3306: i/o timeout pinging DB

docker is running locally. I created a test-db with command-line:

`mysql> create database test_db;`

and then did a


mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
6 rows in set (0.00 sec)

Please help me understand my mistake here? Or what am I missing?

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
HSAR
  • 139
  • 1
  • 2
  • 12
  • 2
    Where does the IP address 172.17.0.2 come from? From the description, it sounds like [From inside of a Docker container, how do I connect to the localhost of the machine?](https://stackoverflow.com/questions/24319662/from-inside-of-a-docker-container-how-do-i-connect-to-the-localhost-of-the-mach) might solve your question (on a MacOS host, use `host.docker.internal` to reach a MySQL instance or anything else running outside a container). – David Maze Oct 10 '22 at 02:08
  • I tried with 127.0.0.1 in golang and used --bind-address = 127.0.0.1 to run docker as well. I see the below error: Error dial tcp 127.0.0.1:3306: connect: connection refused pinging DB – HSAR Oct 10 '22 at 06:18
  • Thanks for suggesting @DavidMaze. I got the above IP from NetworkSettings.IPAddress from docker inspect. – HSAR Oct 10 '22 at 06:28
  • I also tried adding host.docker.internal with the docker-compose.yml as below: `version: '3' services: mysql-development: image: mysql:8.0.30 environment: MYSQL_ROOT_PASSWORD: abc123 MYSQL_DATABASE: tophits ports: - "3306:3306" extra_hosts: - "host.docker.internal:host-gateway" ` This did not help either. I see see i/o timeout pinging DB error. – HSAR Oct 10 '22 at 06:54
  • I am not sure it works, I think you should enter in docker and sign in MySQL: 1. use MySQL; 2. check the host and user: 'select host, user from user'; 3. if there is no host for user 'root', you could create, update and grant privilege. – Guolei Oct 10 '22 at 08:34
  • The `docker inspect` IP address is useless, especially on MacOS; never look it up. Are you [Connecting to Postgresql in a docker container from outside](https://stackoverflow.com/questions/37694987/connecting-to-postgresql-in-a-docker-container-from-outside) (the mechanics are not database-specific)? – David Maze Oct 10 '22 at 09:41
  • host.docker.internal helped. Thanks @DavidMaze I created a separate user and provided privileges. This helped. Thanks for all the help. CREATE user ‘user2’@‘172.19.0.1' IDENTIFIED BY ‘xyz1234’; GRANT ALL PRIVILEGES ON *.* TO 'user2'@'172.19.0.1' WITH GRANT OPTION; – HSAR Oct 10 '22 at 21:29

1 Answers1

0

I used host.docker.internal in this docker-compose.yml file to create the mysql sever in docker.

version: '3'

services:

  mysql-development:
    image: mysql:8.0.30
    environment:
  MYSQL_ROOT_PASSWORD: xyz1234
  MYSQL_DATABASE: test
  ports:
    - "3306:3306"
  extra_hosts:
    - "host.docker.internal:host-gateway"

Used this to run the docker image:

docker-compose -f docker-compose.yml up

Then connected to docker mysql instance using:

docker exec -it 24f058c73227 mysql -P 3306 --protocol=tcp -u root -p

Then created a separate user and granted all privileges to the new user.

mysql> CREATE user ‘user2’@‘172.19.0.1' IDENTIFIED BY ‘xyz1234’;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user2'@'172.19.0.1' WITH GRANT 
OPTION;

This helped me connect to mysql server running on the docker locally on macos.

HSAR
  • 139
  • 1
  • 2
  • 12