If you write code to SQL Server then you might be interested in this: I have written a tSQLt tdd training course which has helped over 500 people learn both tSQLt and how to apply TDD practices to their SQL Server T-SQL development, you can join the course at https://courses.agilesql.club.

Running MySQL on a VSTS build agent

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:

MySQL running in a container on a vsts agent

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 :)

Subscribe

* indicates required

Please select all the ways you would like to hear from Agile Sql Club:

You can unsubscribe at any time by clicking the link in the footer of our emails. For information about our privacy practices, please visit our website.

We use Mailchimp as our marketing platform. By clicking below to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing. Learn more about Mailchimp's privacy practices here.