Monday, February 29, 2016

MySQL Sandboxes in Docker

Overview

When I got interested in Docker, I started playing idly with the idea of integrating containers and MySQL Sandbox. My first experiments were not encouraging. Using a container the same way I would use a regular server produced horrible results. I started by creating a Debian or CentOS container, installing MySQL Sandbox, and then importing an expanded tarball into the container. What happens is that tarballs of recent MySQL versions expand to roughly 2 GB of binaries. When you try to put that into a container you get a bloated file system. If you want to expand more than one tarball, you get an enormous unusable blob that is contrary to what containers should be used for. There is, of course, the possibility of using volumes, which would avoid the problem of making the container too big, but would not make it easier for users. The current difficulty of MySQL Sandbox is its non-intuitiveness. I want to use containers to make things easier for users. Getting the tarballs for more than one version and using them transparently is possible, but not simple. This was my starting point.

Shrinking MySQL tarballs

I read two articles recently about reducing the size of a MySQL distribution. I hadn't given much thought to this issue, other than noticing that the binaries are taking more and more storage in my hosts. While this has not been a huge problem in my servers so far, it would be nice to save a few gigabytes in my laptop. So these two posts made me think again:

The procedure is simple. What you need to run a MySQL server in a sandbox is only a handful of files. For example, for MySQL 5.7.11, you need, from the ./bin directory:

3.5M my_print_defaults
4.4M mysql
4.1M mysqladmin
4.8K mysql_config
 25M mysqld
 26K mysqld_safe

This is about 33M. The sizes are small because I run strip on all binaries. Additionally, I also need some files from the ./share directory, from which I shave away all languages except English, leaving me with about 3.5M

To complete the usability of the sandbox, I need these files, which add 8.6M to the total.

4.4M mysqlbinlog
4.2M mysqldump

This brings the grand total for MySQL 5.7 to ~ 47M. Compared to the original size of the tarball at 2GB, it's an impressive reduction. However, I need to add something if I plan to use plugins in the sandbox. This requires the ./lib directory, with the contents of ./lib/plugin. This will double the size of the repository. But 90M is hardly a problem, compared to dealing with the size of original tarballs.

Of course, I have automated the process, with a file list for every version of MySQL. After the reduction, I got the following (sizes in MB):

Version original sizereduced size
5.037144
5.148559
5.568749
5.6110061
5.7200091



Building the images

My goal with using Docker in conjunction with MySQL Sandbox is to simplify the user experience, while keeping intact the advanced functionalities. After a few unsatisfactory attempts that allowed me to learn a few tricks and avoid pitfalls, I came up with the plan of producing three images:

  • One with the MySQL Sandbox software and the libraries to support it;
  • One with that also includes the MySQL tarballs compressed;
  • And one that includes the tarballs expanded.

The main image mysql-sb-base

This image datacharmer/mysql-sb-base is the base for the others, but it can be used stand-alone with the tarballs already in your host computer. The image is relatively light, is based on Debian, and its size is 167 MB.

For example, you have expanded tarballs in $HOME/opt/mysql

$ ls $HOME/opt/mysql
5.6.28  5.7.11

$ docker run -ti --name sbox -v $HOME/opt/mysql:/opt/mysql datacharmer/mysql-sb-base bash
msandbox@3d4a8d9ca186:~$ make_sandbox 5.6.28 -- --no_show
The MySQL Sandbox,  version 3.1.05
(C) 2006-2016 Giuseppe Maxia
loading grants
. sandbox server started
Your sandbox server was installed in $HOME/sandboxes/msb_5_6_28
msandbox@3d4a8d9ca186:~$ ~/sandboxes/msb_5_6_28/use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.28 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.

mysql [localhost] {msandbox} ((none)) >

Or, you may have a tarball in a directory:

$ ls $HOME/downloads
mysql-5.1.72-linux-x86_64-glibc23.tar.gz

$ docker run -ti --name sbox -v $HOME/downloads:/opt/mysql datacharmer/mysql-sb-base bash

msandbox@2b0a53ad8e71:~$ make_sandbox ~/opt/mysql/mysql-5.1.72-linux-x86_64-glibc23.tar.gz -- --no_show
unpacking /home/msandbox/opt/mysql/mysql-5.1.72-linux-x86_64-glibc23.tar.gz
    The MySQL Sandbox,  version 3.1.05
    (C) 2006-2016 Giuseppe Maxia
loading grants
. sandbox server started
Your sandbox server was installed in $HOME/sandboxes/msb_5_1_72

msandbox@2b0a53ad8e71:~$ ~/sandboxes/msb_5_1_72/use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.72 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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 [localhost] {msandbox} ((none)) >

The intermediate image mysql-sb-gz

If you want to use the reduced tarballs that I have prepared, the less expensive image is datacharmer/mysql-sb-gz. It contains all the binaries mentioned above (from 5.0 to 5.7) still compressed. The image weighs 272 MB, and you can use the binaries with the help of a script that ships with the image.

$ docker run -ti --name sbox datacharmer/mysql-sb-gz bash

msandbox@b0672d141e3d:~$ ls
opt  setup.sh  README
msandbox@b0672d141e3d:~$ ./setup.sh
# expanding 5.0.96.tar.gz
# expanding 5.1.72.tar.gz
# expanding 5.5.48.tar.gz
# expanding 5.6.28.tar.gz
# expanding 5.7.11.tar.gz

msandbox@b0672d141e3d:~$ ls ~/opt/mysql/
5.0.96  5.1.72  5.5.48  5.6.28  5.7.11

msandbox@b0672d141e3d:~$ make_sandbox 5.7.11 -- --no_show
    The MySQL Sandbox,  version 3.1.05
    (C) 2006-2016 Giuseppe Maxia
loading grants
. sandbox server started
Your sandbox server was installed in $HOME/sandboxes/msb_5_7_11
msandbox@b0672d141e3d:~$
msandbox@b0672d141e3d:~$ ~/sandboxes/msb_5_7_11/use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.11 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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 [localhost] {msandbox} ((none)) >

The complete image mysql-sb-full

The image that allows you to use a sandbox without any extra work is datacharmer/mysql-sb-full. This one contains the expanded binaries that are ready for consumption.

$ docker run -ti datacharmer/mysql-sb-full bash
msandbox@ed3cbbc088a7:~$ ls ~/opt/mysql
5.0.96  5.1.72  5.5.48  5.6.28  5.7.11  COPYING
msandbox@ed3cbbc088a7:~$ make_sandbox 5.5.48 -- --no_show
    The MySQL Sandbox,  version 3.1.05
    (C) 2006-2016 Giuseppe Maxia
loading grants
.. sandbox server started
Your sandbox server was installed in $HOME/sandboxes/msb_5_5_48
msandbox@ed3cbbc088a7:~$ make_replication_sandbox 5.6.28
installing and starting master
installing slave 1
installing slave 2
starting slave 1
. sandbox server started
starting slave 2
. sandbox server started
initializing slave 1
initializing slave 2
replication directory installed in $HOME/sandboxes/rsandbox_5_6_28
msandbox@ed3cbbc088a7:~$ ~/sandboxes/use_all 'select version()'
version()
5.5.48
# master
version()
5.6.28-log
# server: 1:
version()
5.6.28-log
# server: 2:
version()
5.6.28-log
msandbox@ed3cbbc088a7:~$

What's next

This is a first attempt, and I am sure it can be improved. Things that come to mind, in no particular order:

  • Making the container run a sandbox transparently in the host computer. This is already feasible, but not out-of-the-box. It will require a wrapper script.
  • Adding binaries for more versions, such as Percona and MariaDB servers, or preview releases (MySQL Labs);
  • Finding a way of adding binaries seamlessly, without making the images unnecessarily big;
  • Getting the binaries from a centralised repository instead of shipping them with the image. This is also feasible, but not immediately reliable as of today. The ideal solution would be for Oracle, Percona, and MariaDB to release and maintain reduced binaries of every shipped version.

The code to create the images is already available on GitHub. Contributions are welcome!

No comments: