.. _mysql:

MySQL and Docker
================

.. note:: The outline for this section is inspired by MySQL's `Basic Steps for MySQL Server Deployment with Docker <https://dev.mysql.com/doc/mysql-installation-excerpt/5.5/en/docker-mysql-getting-started.html>`__

.. note:: Another useful respource is `this Github repo <https://github.com/mysql/mysql-docker>`__ from MySQL which provides the source dockerfiles for each of the MySQL images.


Spinning Up a Container
-----------------------

Pulling Latest Version
~~~~~~~~~~~~~~~~~~~~~~

An optional (but recommended) first step to setting up a MySQL container is to
pull the latest (or desired) MySQL image:

.. code-bock:: shell

    $ docker pull mysql/mysql-server:latest

We could replace ``latest`` with ``8.0`` or ``7.0`` or any version of MySQL for
quich there is an image in the repository (use ``docker search MySQL`` as a
starting point). After running this command we will see the pulled image in
the list of images when we run ``docker images``


Hello World
~~~~~~~~~~~

We can now run a docker container from the downloaded image.

.. code-block:: shell

    $ docker run -ti -d --name mysql1 mysql/mysql-server

We now have a docker container running with MySQL server as its main process.
The built-in dockerfile from MySQL has verified this for us. So one might
wonder, what is the MySQL host/port/password?

Lets take a look at the log generated by the container with the command
``docker logs mysql1``. Here's the output:

.. code-block:: shell

    [Entrypoint] MySQL Docker Image 5.7.21-1.1.3
    [Entrypoint] No password option specified for new database.
    [Entrypoint]   A random onetime password will be generated.
    [Entrypoint] Initializing database
    [Entrypoint] Database initialized
    [Entrypoint] GENERATED ROOT PASSWORD: bbaAA3rwEd%IBTyDujacYsUm0SUR
    [Entrypoint] ignoring /docker-entrypoint-initdb.d/*
    [Entrypoint] Server shut down
    [Entrypoint] Setting root user as expired. Password will need to be changed before database can be used.
    [Entrypoint] MySQL init process done. Ready for start up.
    [Entrypoint] Starting MySQL 5.7.21-1.1.3

So we can see that the server was successfully created and started, and since
we did not specify a password option for the database, docker has generated one
for us, namely ``bbaAA3rwEd%IBTyDujacYsUm0SUR``. So if we now enter our MySQL
shell using the command:

.. code-block:: shell

    $ docker exec -ti mysql1 mysql -u root -p

We'll be prompted to enter this password. We now have access to the MySQL
shell.

**Changing Root Password**

Obviously we may not want to use the root generated password for our MySQL
server, so we instead change it. MySQL will force us to do this anyway.

.. code-block:: mysql

    ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword'

To view the data directory library of this MySQL container, we can detach
from it then reattach with the bash command

.. code-block:: shell

    $ docker exec -ti mysql1 bash
    $ ls /var/lib/mysql

We can also **start**, **restart**, and **stop** the MySQL server using the
simple commands:

.. code-block:: shell

    $ docker mysql1 start
    $ docker mysql1 restart
    $ docker mysql1 stop

And obviously we can remove it with ``docker rm mysql1``.

.. _mysql.storage:

Storage
-------

The easiest way to setup the storage of a MySQL container is to use a simple
bind-mount to the host machine. This will need to be done for both the
configuration file and the data directory of the server.

.. code-block:: bash

    $ docker run -ti --rm -d --name mysql1 \
    --mount type=bind,src=/path/to/host/dir/my.cnf,dst=/etc/my.cnf \
    -- mount type=bind,src=/path/to/datadir,dst=/var/lib/mysql \
    mysql/mysql-server

As with all bind mounts, the path on the host must already exist.