I had a requirement to run MySQL on a VSTS hosted build agent and then to be able to run commands from outside of the container and this gave me the chance to have a play around with VSTS support for containers and builds as YAML (YAML builds / builds as YAML??).

I struggled getting started with this for a little bit longer than I would have hoped, if you have a container and want to run commands inside the container then the process is pretty straight forward, although on vsts you don’t get a TTY so need to do “docker exec -i” rather than “docker exec -it” as all the examples show. For this, I specifically wanted to run a command on the host and have MySQL exposed externally to the container which is the bit I struggled with the longest.

If you want to play along at home you can setup a build in VSTS that points to this repo or you can push the repo to your VSTS account directly:

https://github.com/GoEddie/vsts-build-as-yaml-mysql

I have broken the process up into 4 main sections.

Step 1 - installing mysql-client on the host

This is using the hosted build agents so we make sure the mysql client tools are installed and available.

Step 2 - starting MySQL “prepare-mysql.sh”

The next thing I do is to start a container with MySQL, I started using the built in “Run a docker command” task but ended up putting it in a shell script as I wanted to start it then run some additional stuff. If this wasn’t a public repo and I would have needed credentials to run this then I would have probably have used the official task as it handles credentials for you.

Things to note are:

• I use “–env=MYSQL_ROOT_PASSWORD” to hardcode a password, this container is going to be used for this build and then thrown away so I don’t care about security (the instance is locked down so only this machine can connect anyway).
• I expose the 3306 port using -p 3306:3306
• I used version 5.6 as I had trouble connecting to 8 from the tools that were in the apt repo right now and the version, for this project, doesn’t matter
• I use docker inspect to wait for the container to start, this doesn’t mean MySQL is ready for connections yet though
• I need the ip address of the machine and the docker container, the source if the build agent, destination the MySQL container
• I couldn’t find a reliable way to wait for MySQL to actually be available so I use a combination of waiting on docker logs for “Starting MySQL” and then a 15 second sleep \o/
• I run docker exec ... mysql --execute="GRANT ALL ON *.* to root@$sourceIp..." otherwise the host machine can’t connect to MySQL inside the container - if you docker exec’d everything you wouldn’t need this step but I needed to run commands from outside of the container
• I save the destinationIp and container id as VSTS variables using the awesome echo "##vso[task.setvariable] helpers - these really are as awesome as they sound (more so in fact, yep doubly more so, such an easy way to set variables from ANY language without needing a VSTS sdk or something, genius maybe??)

Step 3 - running scripts

Once that all completes then we are ready for the next step which is to run a command from outside the container. In the previous step, I set a vsts variable called destinationIp so in the later steps I can run this script:

mysql --protocol=tcp -uroot -pmypassword -h$2 < $1

$2 resolves to the ip address of the container and $1 the script file to run.

Calling it like:

`scriptPath: 'deploy/runscript.sh'
 args: 'sql/db.sql $(MySQLIp)'`

The name of the script I can pass in and the MySQLIp is set by step 2 and is a VSTS variable so I don’t need to do docker ps --all and try and figure out which container is mine.

The first script I run does a “CREATE DATABASE DeployTest”.

Step 4 - checking everything worked

The last step, for this demo, is to check that the create database call worked using a show databases;

If everything goes smoothly you should end up with something that looks like:

This won’t get everyone excited, most people can run everything inside the container and life is sweet but in this case I needed to connect to the container from the vsts host, I also had fun with writing a build in a YAML file, the future is here :)