MySQL-Docker operations. - Part 2: Customizing MySQL in Docker

Previous Episodes:

• MySQL-Docker operations. - Part 1: Getting started with MySQL in Docker

After seeing the basics of deploying a MySQL server in Docker, in this article we will lay the foundations to customising a node and eventually using more than one server, so that we can cover replication in the next one.

Enabling GTID: the dangerous approach.

To enable GTID, you need to set five variables in the database server:

• master-info-repository=table
• relay-log-info-repository=table
• enforce-gtid-consistency
• gtid_mode=ON
• log-bin=mysql-bin

For MySQL 5.6, you also need to set log-slave-updates, but we won't deal with such ancient versions here.
Using the method that we've seen in Part 1, we can use a volume to change the default /etc/my.cnf with our own.

$ cat my-gtid.cnf
[mysqld]
user  = mysql
port  = 3306
log-bin  = mysql-bin
relay-log = mysql-relay
server-id = 12345

master-info-repository=table
relay-log-info-repository=table
gtid_mode=ON
enforce-gtid-consistency

However, this approach may fail. It will work with some MySQL images, but depending on how the image is built, the server may not install at all.

$ docker run --name boxedmysql \
    -e MYSQL_ROOT_PASSWORD=secret \
    -v $PWD/my-gtid.cnf:/etc/my.cnf \
    -d mysql/mysql-server
b9c15ed3c40c078db5335dcb76c10da1788cee43b3e32e20c22b937af50248c5

$ docker exec -it boxedmysql bash
Error response from daemon: Container boxedmysql is not running

The reason for the failure is Bug#78957. When my.cnf contains log-bin and mysql is called prior to the installation to perform some detection tasks, the server creates the binary log index in the data directory. After that, the installation task will abort because the data directory is not empty. It sounds as if there is a set of unnecessary actions here (the server should not create the index without other components in place, and the installer should not complain about finding a harmless file in the data directory) but this is the way it is, and we should work around it. At the time of writing, the bug has received a temporary fix and the installation now works.
All considered, it's best that we are forced to run things this way, because there are side effects of enabling GTIDs at startup: there will be unwanted GTID sets in the server, and that could be annoying.


To avoid the possible side effects of a noisy installation that pollute the GTID record there are two methods:

• Install as we did above –provided that the image does not break because of the additional options– and then run reset master.
• Deploy the configuration file in a temporary location, move it to /etc/ after the server has been initialised, and then restart the container:
  1. We use a volume for the configuration file, but we don't replace the default one. This way, the installation will proceed with default values.
  2. After the installation is completed, we move the new configuration file in the default place.
  3. We restart the server, and it will come up with the new configuration.

Each method has its pros and cons. The single installation followed by reset master is probably the most reliable. Both methods are relatively simple to deploy.
You could also enable GTID online, but since you need GTID for replication, you also need to set log_bin and that's a non-dynamic variable that needs to be set in the configuration file. Therefore, the all-online method can be ruled out.

Checking the server health.

After we deploy the container, we don't know for sure if its installation was completed. We have two ways of checking the status. We can look at the container logs, or we can try connecting to the server and see if it answers.
Things are easy when installation succeeds, because both methods return an immediate result:

$ docker logs boxedmysql
Initializing database
Database initialized
MySQL init process in progress...
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.

/entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*


MySQL init process done. Ready for start up.

$ docker exec -it boxedmysql mysql -psecret -e 'select "ALIVE!" as status'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| status |
+--------+
| ALIVE! |
+--------+

It is more difficult to determine success or failure when the server is taking more time to be initialised. This is often the case when we deploy several images at once (for example, to set up replication.) In this case, you don't know if you are seeing a failure or a simple delay.
My preferred method of checking readiness is running a query repeatedly, until it returns an expected result. Something like this:

exit_code=-1
max_attempts=10
attempts=0
while [ "$exit_code" != "0" ]
do
    docker exec -it boxedmysql mysql -psecret -e 'select "ALIVE!" as status'
    exit_code=$?
    attempts=$(($attempts+1))
    if [ $attempts -gt $max_attempts ]
    then
        echo "Max attempts reached. Aborting"
        exit 1
    fi
done

If the test fails, we should have a look at the logs.

$ docker logs boxedmysql
Initializing database
2015-10-28T11:53:32.676232Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-10-28T11:53:32.677764Z 0 [ERROR] You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server start-up parameters documentation
2015-10-28T11:53:32.677830Z 0 [ERROR] Aborting

In this case, we were using a configuration file with log-bin but without server-id, which is easily corrected.
Sometimes the logs are empty, because the container died before the docker engine could record the outcome. This happens sometimes with Docker in a VM (for example in OS X). If you see this, just run the command again after attempting a restart:

$ docker logs boxedmysql
Initializing database

$ docker restart boxedmysql

$ docker logs boxedmysql
2015-10-28T11:53:32.676232Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-10-28T11:53:32.677764Z 0 [ERROR] You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server start-up parameters documentation
2015-10-28T11:53:32.677830Z 0 [ERROR] Aborting

Invoking MySQL without an explicit password

You may have noticed that the output of docker exec ... mysql -psecret includes a warning about using a password on the command line. While the warning is legitimate, we have a bit of a problem if we want to compare the result of the operation with a given value. The method used to suppress this warning is to invoke mysql with a configuration file containing username and password. Notice that this file can't be in the host computer. It must be inside the container.
We can put the username and password in either /etc/my.cnf or $HOME/.my.cnf (which in the case of docker is /root/.my.cnf). However, a brute force attempt of adding username and password to either of these files as a volume will fail. The reason for this failure is that the root password will be set immediately after the initialisation, but the root user, at that moment, runs without password. Setting the password in one of these files will cause the client to be invoked with a password that still does not exist, and the installation will fail.
What we need to do is set the credentials file with a different name, and then use it:

$ cat home_my.cnf
[client]
user=root
password=secret
[mysql]
prompt='MyDocker [\h] {\u} (\d) > '

$ docker run --name boxedmysql \
   -v $PWD/my-minimal.cnf:/etc/my.cnf \
   -v $PWD/home_my.cnf:/root/home_my.cnf  \
   -e MYSQL_ROOT_PASSWORD=secret  \
   -d mysql/mysql-server
3cfec22a1c52bb4a784352bb7d03bc4bc9e5ed3bf4d3e7c1567a6d7e8a670db8

Here the installation succeeds:

$ docker exec -it boxedmysql mysql --defaults-file=/root/home_my.cnf -e 'select "ALIVE!" as status'
+--------+
| status |
+--------+
| ALIVE! |
+--------+

And now we can move the file into its expected position. The next call to mysql, without password, will succeed!

$ docker exec -it boxedmysql cp /root/home_my.cnf /root/.my.cnf

$ docker exec -it boxedmysql mysql -e 'select "LOOK MA, NO PASSWORD!" as status'
+-----------------------+
| status                |
+-----------------------+
| LOOK MA, NO PASSWORD! |
+-----------------------+

If we invoke mysql without a command, we will see the customised prompt

$ docker exec -it boxedmysql mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.9-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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.

MyDocker [localhost] {root} ((none)) >

This method will be handy when we set replication and the prompt will help us identifying the various nodes.
Once we're able to run mysql without indicating credentials on the command line, we can use this fact to get a result from the database and compare to an expected value.

$ docker exec -it boxedmysql mysql -BN -e 'select @@version'
5.7.9

Look simple. The devil is in the details, though. Look at the following:

$ VERSION=$(docker exec -it boxedmysql mysql -BN -e 'select @@version')
echo $VERSION
5.7.9

$ if [ "$VERSION" == "5.7.9" ] ; then echo correct ; else echo wrong; fi
wrong

What happened? The version must be correct. Why does the comparison fail?
There is an hidden character in the answer, and you must be aware of it when using Docker. Every response from a container will contain an extra newline. Let's try with a simple response.

$ NUM=$(docker  exec -it mysql-node1 echo 1)
$ echo "<$NUM>"
>1

You see that the last character of the string , the closing '&gt', appears at the start of the line, overwriting the initial '<'. That's because the linefeed character brings the cursor at the start of the line. We can look at the output through hexdump, and see the extra byte in there.

$ echo -n $NUM |hexdump
0000000 0d31
0000002

$ echo -n 1 |hexdump
0000000 0031
0000001

If you want your comparison to work, you need to remove the extra linefeed.

$ VERSION=$(docker exec -it boxedmysql mysql -BN -e 'select @@version' | tr -d '\r')
echo $VERSION
5.7.9

$ if [ "$VERSION" == "5.7.9" ] ; then echo correct ; else echo wrong; fi
correct

Of course, if you query the database using an local application connected to port 3306 in the container, you won't have this problem. Even using a local mysql client connected to the container IP will work well. Just be carefully when comparing anything that comes from a docker exec command.

Enabling GTID: a safer approach.

We have seen what can make the installation fail. Let's see now how we can enable GTID with a safer method.

$ docker run --name=boxedmysql \
    -v $PWD/my-gtid.cnf:/etc/my_new.cnf \
    -e MYSQL_ROOT_PASSWORD=secret \
    -v $PWD/home_my.cnf:/root/home_my.cnf \
    -d mysql/mysql-server
8887640b20056ac6912732eeeb54c1a6f1d0a17589a14a9c075de021f52a8c90

The customized configuration file was copied to a non-standard location, and therefore it won't be used by the server, which will initialize using its default values. We can prove it:

$ docker exec -ti boxedmysql mysql  --defaults-file=/root/home_my.cnf -e 'select @@server_id, @@gtid_mode'
+-------------+-------------+
| @@server_id | @@gtid_mode |
+-------------+-------------+
|           0 | OFF         |
+-------------+-------------+

The server is running with its default values. We can use dicker exec to copy the candidate file into the default location. After we restart the server, the database will be running with GTID enabled.

$ docker exec -ti boxedmysql cp /etc/my_new.cnf /etc/my.cnf
$ docker restart boxedmysql
boxedmysql
$ docker exec -ti boxedmysql mysql  --defaults-file=/root/home_my.cnf -e 'select @@server_id, @@gtid_mode'
+-------------+-------------+
| @@server_id | @@gtid_mode |
+-------------+-------------+
|       12345 | ON          |
+-------------+-------------+

Sharing data between servers.

The last trick that we look at in this article is a method for sharing data between containers. This problem is felt especially when running replication systems. If you want to add a new node, having a shared storage for backup and restore operations between nodes will greatly simplify things. The recipe is simple:

1. Create an empty "data container" with a directory that we want to share.
2. Create a regular container that gets a volume from the data container.
3. Repeat step #2 for all nodes.

$ docker create -v /dbdata --name dbdata mysql/mysql-server /bin/true
a89396abcb8bc19c58d7e5376e57a63ae69bdca2d20fd24d4037456a8180f11b

$ docker run --name mysql1 -e MYSQL_ROOT_PASSWORD=secret  --volumes-from dbdata   -d mysql/mysql-server
4773807c9aabb7eebba9f5396e52b1ee2e1aeea322dbc4e3d0f1d00f600d90cd

$ docker run --name mysql2 -e MYSQL_ROOT_PASSWORD=secret  --volumes-from dbdata   -d mysql/mysql-server
f3a86114d880a5b1d7c786a9f68334528eb56140e6e2d6ecbe3987bd8c794586

Now the two containers mysql1 and mysql2 can see and use /dbdata.

$ docker exec -it mysql1 bash
[root@4773807c9aab /]# ls -l /dbdata/
total 0
[root@4773807c9aab /]#  mysqldump -p --all-databases > /dbdata/mysql1.sql
Enter password:
[root@4773807c9aab /]# ls -l /dbdata/
total 3140
-rw-r--r-- 1 root root 3214497 Oct 28 13:44 mysql1.sql
[root@4773807c9aab /]# ls -lh /dbdata/
total 3.1M
-rw-r--r-- 1 root root 3.1M Oct 28 13:44 mysql1.sql

The first container has created a backup in /dbdata.

$ docker exec -it mysql2 bash
[root@f3a86114d880 /]# ls -l /dbdata/
total 3140
-rw-r--r-- 1 root root 3214497 Oct 28 13:44 mysql1.sql

The second container can see and use it.
In this example, the shared directory is inside a docker internal volume. If we want to use a directory in the host operating systems, we change the creation slightly:

$ docker create -v /opt/docker/data:/dbdata --name dbdata mysql/mysql-server /bin/true
a89396abcb8bc19c58d7e5376e57a63ae69bdca2d20fd24d4037456a8180f11b

With this command, /dbdata now points to an external directory, which is also shared among containers.

What's next

This article has given us most of the elements necessary to run more complex operations.
In Part 3 we will finally run MySQL containers in replication, with a set of scripts that automate the procedure. You will see a master with 10 slaves being deployed and operational in less than one minute.