Docker, Shiny, and MySQL

In an attempt to setup a development environment for R and Shiny-apps running on a laptop, a previous blog explained how a virtual image was built. This Ubuntu server based virtual image contained all the software that was required for the development of Shiny-apps. But, it strained the machine, even with 8GB of memory. Thus began the  attempt to test Docker for the same purpose, if successful this solution would accrue long term benefits for the developer. Take note that what follows is only applicabledocker-shiny-mysql-001 to M
acOS only.

Setting up a docker development environment turned out to be remarkably easy on MacOS. And, from the experience gleaned so far, it is looking remarkably less stressful on the system. We are only interested in solving our primary problem, and that is to create a development environment, hence the focus is on using containers from the Docker hub whenever possible.

Step 1

Install Docker for Mac application, this is currently by far the best choice compared to the use of previous Docker machine implementation for the Mac. We are going to use this to create two docker containers. One of the containers will host MySQL, and the other the shiny server.

Step 2

  1. Download the mysql container from the docker hub with the following command
    1. docker pull mysql
  1. We opted to maintain the mysql data directory container on the host, and map it to the appropriate directory of the container.
    1. Create a data directory on the host, for example, “/docker/mysql/datadir”.
    2. Start the container to run as a daemon with the following docker command,                              “docker run –name the_name_for_your_mysql_container  -v /docker/mysql/datadir:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=your_password -d mysql”
    3. Check the result by issuing the command, “docker ps”.
    4. You can open a shell in the container and perform all the usual tasks, “docker exec -it the_name_for_your_mysql_container bash -l”.
    5. If on the other hand you want to send a command to the mysql for execution, “docker exec -i the_name_for_your_mysql_container mysql -uroot -pyour_password your_database_name  <  /your_file_path/your_sql_script_file.sql”.
    6. To successfully execute the sql script file in e) above, it is assumed that you have already created your database (your_database_name). This can be done by, “docker exec -i the_name_for_your_mysql_container mysql -uroot -pyour_password  <<<  “CREATE DATABASE your_database_name”.

Step 3

With the completion of of the above step you have a fully functioning mysql docker container. Having created your database and tables you now have to load some test data. For small amounts of data you can probably use the “docker exec” as above to perform the task. But, in our case we had a large CSV file that required the use of the “LOAD DATA INFILE” command.

Since 5.7 (?) MySQL by default activates the secure file privileges. This requires the CSV file to be in the container folder /var/lib/mysql_files (this is the folder pointed to be the secure file privileges parameter). We decided not to deactivate this parameter, but work within its constraints.

  1. Copy the file from the host to the container by issuing the command, “docker cp /your_file_path/your_file_name.csv your_running_containers_uid:/var/lib/mysql-files”.
  2. The “your_running_containers_uid” can be obtained from the response to command “docker ps”.
  3. Once the file is copied to the container folder, then open a bash shell and login to mysql on the container. The “LOAD DATA INFILE” command can then be used to load the table.

With the completion of this step you have completed the set up of a fully functional mysql docker container. Go and have a cup of coffee, you deserve it!!

Step 4

It is now time to set up the core requirement for this exercise, and that is the Shiny-apps server. Once again we will do so using the rocker/shiny container from the docker hub.

  1. Get started with, “docker pull rocker/shiny”.
  2. Before we can run this image we need to set up root folder for the Shiny server. We had already set up a git repository (for mating the Shiny-apps source code) as mentioned in the previous blog, and all that is required make it available to our docker container.
    1. Create two folders on the host, for example, /your_source_path/shinyapps, and /your_source_path/shinylog.
    2. Use git to pull you code into this folder.
  3. It is also necessary to link your mysql container to this container, if you want to access the data from the tables in that container.
  4. The command required to run the rocker/shiny server pulled from the docker hub is, “docker run -d -p 3838:3838 –name the_name_for_your_shiny_container -v /your_source_path/shinyapps:/srv/shiny-server -v /your_source_path/shinylog:/var/log –link name_of_your_mysql_container:mysql rocker/shiny”
  5. This command will start your shiny server, and make it available via some docker magic on the host as http://localhost:3838

Step 5

It is now time to configure your shiny server to execute and render your code response.  This part will differ depending on the R packages required for your code. In our case it was the RMySQL, dplyr, quantmod, and xts. Since the container is a thin layer not supporting multiple users etc the packages may be installed from a bash command line.

  1. Open a bash command line with, “docker exec -it the_name_for_your_shiny_container bash -l”.
  2. The command for installing the R packages for global availability is, “sudo su – -c “R -e \” install.packages(‘package_name’, repos=‘’)\””, as mentioned before this is not necessary unless you have done more to the container.
  3. There is a very good chance that you will also need to install a MySQL client. This can be done by using the apt-get to install libmariadb-client-lgpl-dev, but do check this is what you want.

Step 6

The last thing was to change the host value in the R connection to pot to the mysql docker container, the_name_for_your_mysql_container.


  1. This was an exercise to set up a docker driven development environment and nothing more, and as such it has achieved its goal. There is nothing new here, much if not all of this spread out over the many excellent resources on the internet.
  2. But, do remember that this is insufficient for a production environment, it is in fact way short for what is required. In a production environment you have to secure your containers, although there are many that seem to ignore this in their haste to exploit docker as a cloud solution.
  3. The placement of the data directory for the mysql container, and the placement of the shiny server root folder and logs have to considered carefully including from security perspective too.
  4. A further bonus is that the developer can now use the mysql container for all development, without a need for any other local installations.
  5. This also provides a great learning opportunity for docker and cloud development, with the caveat that there is still much hidden under the rock.
  6. If you have reached this far then give yourself a break, and enjoy your journey into R, shiny and docker.

About KM Mukku

Kick-start, build and manage teams in product development (particularly in the financial domain), and enjoy all in adaptive case management, business process design and business process improvement. Currently holding the position of CTO at coMakeIT.
This entry was posted in R, ShinyApp, Technical Analysis, Uncategorized. Bookmark the permalink.

One Response to Docker, Shiny, and MySQL

  1. Great post, most informative, didn’t realise devops were into this.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s