Table of Contents

About

A MySQL Server installation with Docker at https://hub.docker.com/r/mysql/mysql-server/

Management

Image

There exists 2 mysql docker images

Create a container and start it

Basic

Run it and forward the port

  • Bash
docker run \
    --name mysql \
    -p 3306:3306 \
    -e MYSQL_ROOT_PASSWORD=secret \
    -e MYSQL_ROOT_HOST=% \
    -v /my/conf:/etc/mysql/conf.d
    -d \
    mysql/mysql-server
  • Dos
docker run ^
    --name mysql ^
    -p 3306:3306 ^
    -e MYSQL_ROOT_PASSWORD=secret ^
    -e MYSQL_ROOT_HOST=% ^
    -d ^
    mysql/mysql-server

where the user configuration are:

  • MYSQL_ROOT_HOST=% will allow a connection from all machines
  • MYSQL_ROOT_PASSWORD define the root password

Configuration

You can change the configuration:

  • via a custom configuration file
  • or via inline argument
Configuration file
  • with a configuration file located in the directory /my/conf . The startup will use a combined settings from /etc/mysql/my.cnf and /etc/mysql/conf.d/custom-file.cnf
docker run \
    --name mysql \
    -p 6603:3306 \
    -e MYSQL_ROOT_PASSWORD=secret \
    -e MYSQL_ROOT_HOST=% \
    -v /my/conf:/etc/mysql/conf.d \ # for the mysql image
    -v /my/conf/my.cnf:/etc/my.cnf \ # for the mysql-server image
    -d \
    mysql/mysql-server
inline configuration
docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

# all conf can be seen with
docker run -it --rm mysql:latest --verbose --help

Database creation

The docker image will execute files with extensions .sh, .sql and .sql.gz that are found in /docker-entrypoint-initdb.d in alphabetical order.

In a lot of docker image, the sql scripts found in the /docker-entrypoint-initdb.d/ folder.

You can populate mysql services by mounting a SQL dump into that directory

SQL files will be imported by default to the database specified by the MYSQL_DATABASE variable.

example: debezium/docker-images/tree/master/examples/mysql/0.7

Data File

docker run --name some-mysql \
   -v /my/own/datadir:/var/lib/mysql \
   -e MYSQL_ROOT_PASSWORD=my-secret-pw \
   -d \
   mysql:tag

where:

  • var/lib/mysql is where MySQL by default write its data files.

Check the logs

docker logs mysql-server

[Entrypoint] MySQL Docker Image 5.7.19-1.1.0
[Entrypoint] Initializing database
[Entrypoint] Database initialized
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.

[Entrypoint] ignoring /docker-entrypoint-initdb.d/*

[Entrypoint] Server shut down

[Entrypoint] MySQL init process done. Ready for start up.

[Entrypoint] Starting MySQL 5.7.19-1.1.0

Connect

With mysql command

With docker, you can connect with the mysql cli: 

docker exec -it mysql-server mysql -uroot -psecret

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 492
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

With a client tool

With MySQLWorkbench or Dbeaver, you can connect with the following value:

  • host: localhost or your docker host ip
  • port: 6603
  • user: root
  • pwd: secret

Dbeaver: allowPublicKeyRetrieval=true

Support

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

When checking the logs, you may get an Access denied 

docker logs mysql-name

[Entrypoint] MySQL Docker Image 5.7.19-1.1.0
[Entrypoint] Initializing database
[Entrypoint] Database initialized
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Their is a bug when you try to create the database. Suppress the parameter MYSQL_DATABASE, MYSQL_USER, MYSQL_PWD and create it yourself.

Host '192.168.99.1' is not allowed to connect to this MySQL server

Add the parameter MYSQL_ROOT_HOST to your docker run command. See create_a_container_and_start_it. The value:

  • % will allow a connection from all machines
  • ip will allow a connection only for this IP.

Public Key Retrieval is not allowed

With DBeaver, you need to set the property allow public key retrieval

Dbeaver Allow Public Key Retrieval

Documentation / Reference