MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

2.5.6.2 More Topics on Deploying MySQL Server with Docker

Note

Most of the sample commands below have mysql/mysql-server as the Docker image repository when that has to be specified (like with the docker pull and docker run commands); change that if your image is from another repository—for example, replace it with mysql/enterprise-server for MySQL Enterprise Edition images downloaded from My Oracle Support.

The Optimized MySQL Installation for Docker

Docker images for MySQL are optimized for code size, which means they only include crucial components that are expected to be relevant for the majority of users who run MySQL instances in Docker containers. A MySQL Docker installation is different from a common, non-Docker installation in the following aspects:

Configuring the MySQL Server

When you start the MySQL Docker container, you can pass configuration options to the server through the docker run command. For example:

docker run --name mysql1 -d mysql/mysql-server:tag --character-set-server=utf8mb4 --collation-server=utf8mb4_col

The command starts your MySQL Server with utf8mb4 as the default character set and utf8mb4_col as the default collation for your databases.

Another way to configure the MySQL Server is to prepare a configuration file and mount it at the location of the server configuration file inside the container. See Persisting Data and Configuration Changes for details.

Persisting Data and Configuration Changes

Docker containers are in principle ephemeral, and any data or configuration are expected to be lost if the container is deleted or corrupted (see discussions here). Docker volumes, however, provides a mechanism to persist data created inside a Docker container. At its initialization, the MySQL Server container creates a Docker volume for the server data directory. The JSON output for running the docker inspect command on the container has a Mount key, whose value provides information on the data directory volume:

shell> docker inspect mysql1
...
 "Mounts": [
            {
                "Type": "volume",
                "Name": "4f2d463cfc4bdd4baebcb098c97d7da3337195ed2c6572bc0b89f7e845d27652",
                "Source": "/var/lib/docker/volumes/4f2d463cfc4bdd4baebcb098c97d7da3337195ed2c6572bc0b89f7e845d27652/_data",
                "Destination": "/var/lib/mysql",
                "Driver": "local",
                "Mode": "",
                "RW": true,
                "Propagation": ""
            }
        ],
...

The output shows that the source folder /var/lib/docker/volumes/4f2d463cfc4bdd4baebcb098c97d7da3337195ed2c6572bc0b89f7e845d27652/_data, in which data is persisted on the host, has been mounted at /var/lib/mysql, the server data directory inside the container.

Another way to preserve data is to bind-mount a host directory using the --mount option when creating the container. The same technique can be used to persist the configuration of the server. The following command creates a MySQL Server container and bind-mounts both the data directory and the server configuration file:

docker run --name=mysql1 \
--mount type=bind,src=/path-on-host-machine/my.cnf,dst=/etc/my.cnf \
--mount type=bind,src=/path-on-host-machine/datadir,dst=/var/lib/mysql \
-d mysql/mysql-server:tag 

The command mounts path-on-host-machine/my.cnf at /etc/my.cnf (the server configuration file inside the container), and path-on-host-machine/datadir at /var/lib/mysql (the data directory inside the container). The following conditions must be met for the bind-mounting to work:

Running Additional Initialization Scripts

If there are any .sh or .sql scripts you want to run on the database immediately after it has been created, you can put them into a host directory and then mount the directory at /docker-entrypoint-initdb.d/ inside the container. For example:

docker run --name=mysql1 \
--mount type=bind,src=/path-on-host-machine/scripts/,dst=/docker-entrypoint-initdb.d/ \
-d mysql/mysql-server:tag
Connect to MySQL from an Application in Another Docker Container

By setting up a Docker network, you can allow multiple Docker containers to communicate with each other, so that a client application in another Docker container can access the MySQL Server in the server container. First, create a Docker network:

docker network create my-custom-net

Then, when you are creating and starting the server and the client containers, use the --network option to put them on network you created. For example:

docker run --name=mysql1 --network=my-custom-net -d mysql/mysql-server
docker run --name=myapp1 --network=my-custom-net -d myapp

The myapp1 container can then connect to the mysql1 container with the mysql1 hostname and vice versa, as Docker automatically sets up a DNS for the given container names. In the following example, we run the mysql client from inside the myapp1 container to connect to host mysql1 in its own container:

docker exec -it myapp1 mysql --host=mysql1 --user=myuser --password

For other networking techniques for containers, see the Docker container networking section in the Docker Documentation.

Server Error Log

When the MySQL Server is first started with your server container, a server error log is NOT generated if either of the following conditions is true:

To make MySQL Server generate an error log when either of the two conditions is true, use the --log-error option to configure the server to generate the error log at a specific location inside the container. To persist the error log, mount a host file at the location of the error log inside the container as explained in Persisting Data and Configuration Changes. However, you must make sure your MySQL Server inside its container has write access to the mounted host file.

Using MySQL Enterprise Backup with Docker

MySQL Enterprise Backup is a commercially-licensed backup utility for MySQL Server, available with MySQL Enterprise Edition. MySQL Enterprise Backup is included in the Docker installation of MySQL Enterprise Edition.

In the following example, we assume that you already have a MySQL Server running in a Docker container (see Section 2.5.6.1, “Basic Steps for MySQL Server Deployment with Docker” on how to start a MySQL Server instance with Docker). For MySQL Enterprise Backup to back up the MySQL Server, it must have access to the server's data directory. This can be achieved by, for example, bind-mounting a host directory on the data directory of the MySQL Server when you start the server:

docker run --name=mysqlserver \
--mount type=bind,src=/path-on-host-machine/datadir/,dst=/var/lib/mysql \
-d mysql/enterprise-server:8.0

With this command, the MySQL Server is started with a Docker image of the MySQL Enterprise Edition, and the host directory /path-on-host-machine/datadir/ has been mounted onto the server's data directory (/var/lib/mysql) inside the server container. We also assume that, after the server has been started, the required privileges have also been set up for MySQL Enterprise Backup to access the server (see Grant MySQL Privileges to Backup Administrator for details). Use the following steps then to backup and restore a MySQL Server instance.

To backup a MySQL Server instance running in a Docker container using MySQL Enterprise Backup with Docker:

  1. On the same host where the MySQL Server container is running, start another container with an image of MySQL Enterprise Edition to perform a back up with the MySQL Enterprise Backup command backup-to-image. Provide access to the server's data directory using the bind mount we created in the last step. Also, mount a host directory (/path-on-host-machine/backups/ in this example) onto the storage folder for backups in the container (/data/backups in the example) to persist the backups we are creating. Here is a sample command for this step, in which MySQL Enterprise Backup is started with a Docker image downloaded from My Oracle Support):

    shell> docker run \
    --mount type=bind,src=/path-on-host-machine/datadir/,dst=/var/lib/mysql \
    --mount type=bind,src=/path-on-host-machine/backups/,dst=/data/backups \
    --rm mysql/enterprise-server:8.0 \
    mysqlbackup -umysqlbackup -ppassword --backup-dir=/tmp/backup-tmp --with-timestamp \
    --backup-image=/data/backups/db.mbi backup-to-image
    
    [Entrypoint] MySQL Docker Image 8.0.11-1.1.5
    MySQL Enterprise Backup version 8.0.11 Linux-4.1.12-61.1.16.el7uek.x86_64-x86_64 [2018-04-08  07:06:45]
    Copyright (c) 2003, 2018, Oracle and/or its affiliates. All Rights Reserved.
    
    180921 17:27:25 MAIN    INFO: A thread created with Id '140594390935680'
    180921 17:27:25 MAIN    INFO: Starting with following command line ...
    ...
    
    -------------------------------------------------------------
       Parameters Summary
    -------------------------------------------------------------
       Start LSN                  : 29615616
       End LSN                    : 29651854
    -------------------------------------------------------------
    
    mysqlbackup completed OK!
    

    It is important to check the end of the output by mysqlbackup to make sure the backup has been completed successfully.

  2. The container exits once the backup job is finished and, with the --rm option used to start it, it is removed after it exits. An image backup has been created, and can be found in the host directory mounted in the last step for storing backups:

    shell> ls /tmp/backups
    db.mbi
    

To restore a MySQL Server instance in a Docker container using MySQL Enterprise Backup with Docker:

  1. Stop the MySQL Server container, which also stops the MySQL Server running inside:

    docker stop mysqlserver
  2. On the host, delete all contents in the bind mount for the MySQL Server data directory:

    rm -rf /path-on-host-machine/datadir/*
    
  3. Start a container with an image of MySQL Enterprise Edition to perform the restore with the MySQL Enterprise Backup command copy-back-and-apply-log. Bind-mount the server's data directory and the storage folder for the backups, like what we did when we backed up the server:

    shell> docker run \
    --mount type=bind,src=/path-on-host-machine/datadir/,dst=/var/lib/mysql \
    --mount type=bind,src=/path-on-host-machine/backups/,dst=/data/backups \
    --rm mysql/enterprise-server:8.0 \
    mysqlbackup --backup-dir=/tmp/backup-tmp --with-timestamp \
    --datadir=/var/lib/mysql --backup-image=/data/backups/db.mbi copy-back-and-apply-log
    
    [Entrypoint] MySQL Docker Image 8.0.11-1.1.5
    MySQL Enterprise Backup version 8.0.11 Linux-4.1.12-61.1.16.el7uek.x86_64-x86_64 [2018-04-08  07:06:45]
    Copyright (c) 2003, 2018, Oracle and/or its affiliates. All Rights Reserved.
    
    180921 22:06:52 MAIN    INFO: A thread created with Id '139768047519872'
    180921 22:06:52 MAIN    INFO: Starting with following command line ...
    ...
    180921 22:06:52 PCR1    INFO: We were able to parse ibbackup_logfile up to
              lsn 29680612.
    180921 22:06:52 PCR1    INFO: Last MySQL binlog file position 0 155, file name binlog.000003
    180921 22:06:52 PCR1    INFO: The first data file is '/var/lib/mysql/ibdata1'
                                  and the new created log files are at '/var/lib/mysql'
    180921 22:06:52 MAIN    INFO: No Keyring file to process.
    180921 22:06:52 MAIN    INFO: Apply-log operation completed successfully.
    180921 22:06:52 MAIN    INFO: Full Backup has been restored successfully.
    
    mysqlbackup completed OK! with 3 warnings
    

    The container exits once the backup job is finished and, with the --rm option used when starting it, it is removed after it exits.

  4. Restart the server container, which also restarts the restored server:

    docker restart mysqlserver

    Or, start a new MySQL Server on the restored data directory:

    docker run --name=mysqlserver2 \
    --mount type=bind,src=/path-on-host-machine/datadir/,dst=/var/lib/mysql \
    -d mysql/enterprise-server:8.0

    Log on to the server to check that the server is running with the restored data.

Docker Environment Variables

When you create a MySQL Server container, you can configure the MySQL instance by using the --env option (-e in short) and specifying one or more of the following environment variables.

Notes
  • None of the variables below has any effect if the data directory you mount is not empty, as no server initialization is going to be attempted then (see Persisting Data and Configuration Changes for more details). Any pre-existing contents in the folder, including any old server settings, are not modified during the container startup.

  • The boolean variables including MYSQL_RANDOM_ROOT_PASSWORD, MYSQL_ONETIME_PASSWORD, MYSQL_ALLOW_EMPTY_PASSWORD, and MYSQL_LOG_CONSOLE are made true by setting them with any strings of nonzero lengths. Therefore, setting them to, for example, 0, false, or no does not make them false, but actually makes them true. This is a known issue of the MySQL Server containers.