Databases, DevOps and Development (mainly) anytime (except teatime)
If you write code to SQL Server then you migt be interested in this: at the end of 2017 I wrote a tSQLt tdd training course which has helped over 200 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. The course is free if you are happy to wait 10 weeks to complete it, with 1 lesson being made available per week - if you are in more of a hurry or you would like to help support the project you can purchase the course which makes it instantly available to you.
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:
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
- 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 inspectto 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 logsfor “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
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 :)