V2: How to use a locally hosted MySQL container from inside the docker-container?

[saschafoerster]

I am using a VM, where I already have a MySQL running on the VM itself. Now to simplify backups and to not have 2 MySQL-loads on the same server, I would like to change the docker-compose.yml, so that Mailtrain will use the mySQL on the localhost and not in another container. Does someone have an example? Thank you!

[forgeOneHQ]

If you remove the configuration for mysql as service from the docker-compose.yml so that mysql is not deployed, you need to make the local mysql service available.

Have you checked the configuration file which values are set in the docker file?

[jonathanmmm]

I am using a VM, where I already have a MySQL running on the VM itself. Now to simplify backups and to not have 2 MySQL-loads on the same server, I would like to change the docker-compose.yml, so that Mailtrain will use the mySQL on the localhost and not in another container. Does someone have an example? Thank you!

Either host mode (which is maybe not preffered, host should be avoided, unless really needed for services that need it)

or via this:

include this in to the docker-compose.yml file (in the mailtrain section - e.g. same number of spaces/tabs as e.g. ports section) (linux)

extra_hosts:
    - "host.docker.internal:host-gateway"

Mac/Windows seem not to need it host.docker.internal

You can then use then the hostname as host.docker.internal

You can replace host.docker.internal with whatever you like (at least in linux, don't know excactly on Mac and Windows)

The third (but not possible option, as Mailtrain does not support it) would be to mount the mysql socket via volumes and connect this way.

As this was more of a docker question and not a specific mailtrain question, I would propose you search next time yourself, myself included, should sometimes do a bit more research before asking (please take it as kindly and ask if you don't let it yourself discourrage from research and asking if you don't come any further)

https://stackoverflow.com/questions/24319662/from-inside-of-a-docker-container-how-do-i-connect-to-the-localhost-of-the-mach and this https://www.cloudsavvyit.com/14114/how-to-connect-to-localhost-within-a-docker-container/

[saschafoerster]

I hoped it is easy to accomplish with mailtrain, but it's not.

• I created a copy of the MySQL inside the docker-container and imported it into the mariadb, that is hosted on the machine itself.
• I created a user in MariaDB "mailtrain" with a password and the allowed domain "172.17.0.1", so it can be used from inside docker (all the other mariadb-users are only reachable from localhost)
• I changed to the bind-address of mariadb to 0.0.0.0, so every ip-address, including the docker one, can connect to the MariaDB-server. I tried to ping it from inside the container which worked.
• Of course I changed the docker-compose.yml: I removed the service "mysql", I added the env-variables and the extra_hosts:

    environment:
    #Local SQL
    - MYSQL_HOST=172.17.0.1
    - MYSQL_DATABASE=mailtrain
    - MYSQL_USER=mailtrain
    - MYSQL_PASSWORD=PASSWORD
    ....
    extra_hosts:
    - "host.docker.internal:host-gateway"

So when I start the docker-compose.yml, I get this messages:

docker-compose up
Recreating mailtrain_mailtrain_1 ... done
Starting mailtrain_mongo_1       ... done
Starting mailtrain_redis_1       ... done
Attaching to mailtrain_redis_1, mailtrain_mongo_1, mailtrain_mailtrain_1
mailtrain_1  | Info: Generating application/production.yaml
mailtrain_1  | Info: LDAP not enabled
mailtrain_1  | Info: CAS not enabled
mailtrain_1  | Info: Generating server/production.yaml
mailtrain_1  | Info: Waiting for MySQL Server

So I looked further into the production.yml int the container and it seems to me a little bit to empty, to be functional:

bash-5.0# vi server/services/workers/reports/config/production.yaml

mysql:
  host: 172.17.0.1
log:
  level: warn

This production.yml is created with this script, and this script only uses the host-variable, not the others:

https://github.com/Mailtrain-org/mailtrain/blob/53e34d234c09cddd186e47654cee41b4e5a86621/docker-entrypoint.sh#L183

So maybe someone can help my to extend this script, so it's also useable with the externally hosted mysql-file?

[saschafoerster]

A proposition would be to change to add to the script this:

mysql:
  host: $MYSQL_HOST
  user: $MYSQL_USER
  password: $MYSQL_PASSWORD
  database: $MYSQL_DATABASE
  port: $MYSQL_PORT

[jonathanmmm]

I hoped it is easy to accomplish with mailtrain, but it's not.

• I created a copy of the MySQL inside the docker-container and imported it into the mariadb, that is hosted on the machine itself.
• I created a user in MariaDB "mailtrain" with a password and the allowed domain "172.17.0.1", so it can be used from inside docker (all the other mariadb-users are only reachable from localhost)
• I changed to the bind-address of mariadb to 0.0.0.0, so every ip-address, including the docker one, can connect to the MariaDB-server. I tried to ping it from inside the container which worked.
• Of course I changed the docker-compose.yml: I removed the service "mysql", I added the env-variables and the extra_hosts:

    environment:
    #Local SQL
    - MYSQL_HOST=172.17.0.1
    - MYSQL_DATABASE=mailtrain
    - MYSQL_USER=mailtrain
    - MYSQL_PASSWORD=PASSWORD
    ....
    extra_hosts:
    - "host.docker.internal:host-gateway"

So when I start the docker-compose.yml, I get this messages:

docker-compose up
Recreating mailtrain_mailtrain_1 ... done
Starting mailtrain_mongo_1       ... done
Starting mailtrain_redis_1       ... done
Attaching to mailtrain_redis_1, mailtrain_mongo_1, mailtrain_mailtrain_1
mailtrain_1  | Info: Generating application/production.yaml
mailtrain_1  | Info: LDAP not enabled
mailtrain_1  | Info: CAS not enabled
mailtrain_1  | Info: Generating server/production.yaml
mailtrain_1  | Info: Waiting for MySQL Server

So I looked further into the production.yml int the container and it seems to me a little bit to empty, to be functional:

bash-5.0# vi server/services/workers/reports/config/production.yaml

mysql:
  host: 172.17.0.1
log:
  level: warn

This production.yml is created with this script, and this script only uses the host-variable, not the others:

https://github.com/Mailtrain-org/mailtrain/blob/53e34d234c09cddd186e47654cee41b4e5a86621/docker-entrypoint.sh#L183

So maybe someone can help my to extend this script, so it's also useable with the externally hosted mysql-file?

If you host MYSQL on the host MYSQL_HOST should be host.docker.internal and not 172.17.0.1. Did you try that?

[saschafoerster]

@jonathanmmm I tried both and both didn't work. The dns-adress host.docker.internal actually is 172.17.0.1 on my server. I pinged both from inside the docker-container, which worked. (172.17.0.2 didn't ping).

[saschafoerster]

This production.yml is created with this script, and this script only uses the host-variable, not the others:

https://github.com/Mailtrain-org/mailtrain/blob/53e34d234c09cddd186e47654cee41b4e5a86621/docker-entrypoint.sh#L183

I still think the script here should have more variables for external SQL to work. :)

[forgeOneHQ]

We are going to start with the development and testing of mailtrain v3 in the next weeks.

You are welcome to help us with the testing as soon as the first release candidate is available.