How to start MySQL server in Docker container made on a base mysql/mysql-server

Question

I want to make a docker image based on mysql/mysql-server . I have an app (golang) which should use MySQL to store data. I want to run this app in a docker

My Docker file looks like

FROM mysql/mysql-server
ENV MYSQL_ROOT_PASSWORD rootpass
ENV MYSQL_DATABASE mydb
ENV MYSQL_USER dbuser
ENV MYSQL_PASSWORD dbpass

ADD myapp / # compiled colang app connecting to localhost:3306 mysql server
CMD ["myapp"]

I build this image and run it and have error

docker run --name mycont -it myimage
dial tcp [::1]:3306: getsockopt: connection refused

What is wrong here?

Maybe i have to start mysql server somehow before to execute my app?

My app tries to connect to mysql on localhost:3306 with the user dbuser/dbpassword and database mydb. Then will create some tables in the DB

Answer 1

Well, it is possible to achieve that, but it would not be considered a good practice. The recommended approach would be to run two different containers: one running mysql and the other one running your application. Besides that, you would need an orchestration tool to run the containers.

So, with the risk of getting a few down-votes :-), instead of directly answering to your question and showing how to create a single container that would run both functionalities, I prefer to show the two-container approach that I consider a better practice.

In my example I use docker compose for orchestration. You will need to install it if you haven’t done it already. Below is the docker-compose.yml file that I used to run the two containers:

version: '3'
services:
  mysqlserver:
    image: mysql/mysql-server
    command: --default-authentication-plugin=mysql_native_password
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: "rootpass"
      MYSQL_DATABASE: "mydb"
      MYSQL_USER: "dbuser"
      MYSQL_PASSWORD: "dbpass"

  myapp:
    image: myimage
    environment:
      MYSQL_SERVER: "mysqlserver"
      MYSQL_DATABASE: "mydb"
      MYSQL_USER: "dbuser"
      MYSQL_PASSWORD: "dbpass"

The file defines two services: mysqlserver and myapp which will be run as separate containers. Each of the two services define two elements:

• the image used for creating the container
• the environment variables passed to that container

The mysqlserver service has two extra properties defined:

• command, which specifies the extra parameters passed to mysql service at runtime. To cut the story short: that --default-authentication-plugin is necessary for mysql 8. Try to run the container without it and you might end-up with some connection errors. You can read more about that on the official mysql docker page.
• ports, which specifies what container ports are exposed to the outside

Now, let’s define your app image. I don’t speak golang but I prepared a simple example using Linux shell script.

myapp code would be the following:

#!/bin/sh

echo ">> Waiting for $MYSQL_SERVER to start"
while ! `nc -z $MYSQL_SERVER 3306`; do sleep 3; done
echo ">> $MYSQL_SERVER has started"

mysql --host=$MYSQL_SERVER --user=$MYSQL_USER --password=$MYSQL_PASSWORD --database=$MYSQL_DATABASE<<EOFMYSQL
drop table if exists test;
create table test(id int, name varchar(20));
insert into test values (5, 'a'), (12, 'b');
select * from test;
EOFMYSQL

The app will first run nc command in a while loop, checks that status of the mysql server every 3 seconds and only exits the loop when the mysqlserver has fully started and accepts connections on port 3306. We need to make sure that the database server is running before trying to connect to it, right?

Once the server has started, the myapp connects to it using the credentials passed as environment variables in the docker compose file. After the connection, it runs a few sql statements (delete table, create table, insert, select). All those environment parameters could be hard-coded in the code of myapp but I think it would be better to pass them in the docker compose file.

The Dockerfile used to create myapp image is the following:

FROM alpine

RUN apk add netcat-openbsd mysql-client
ADD myapp /
RUN chmod +x /myapp
CMD /myapp

I’m usually using alpine linux as a base for my tests as it is very lightweight. I need to run apk add netcat-openbsd mysql-client for adding the nc and the mysql-client to the image. Instead of the myapp shell script you will just need to add your golang myapp file.

Now, let’s assemble everything. Do the following steps further:

• put the Dockerfile and myapp files in the same folder and build the myapp image:

docker image build -t myimage .

• run docker compose with the above docker-compose.yml file as input:

docker-compose -f docker-compose.yml up

At this moment both containers will start. myapp will wait for the mysqlserver to be ready and after that it would connect to it and run the simple queries.

I’m not sure how familiar are you with linux, shell scripting, yaml syntax, etc.. but I hope I provided you enough details to get the idea.