Adventures with Azure DevOps: Connect to Azure Database for MySQL

I have been doing a lot around Azure DevOps as of late, and started dipping my toe into the CI/CD for databases, thought of calling it DataOps, but that doesn’t look like the correct term.

Anyway, in my Azure DevOps pipeline I wanted to run a few tasks against an Azure Database for MySQL, e.g. create a new schema, create tables, load test data, etc.

Before I created my pipeline with all the tasks, I wanted to first make sure that Azure DevOps would be able to hit the Azure Database for MySQL. I assumed it would, but this was new to me.

To set things up, I first created an Azure Database for MySQL.

In the Connection Security section of my Azure Database for MySQL, I added my IP address so I could connect from my desktop.

Using MySQL Workbench, I created a schema called schema1 and a table called table1, not very original, but gets the job done.

I also added a couple of rows of data to table1 which consists of two columns named, you guessed it, named Column1 and Column2.

In Azure DevOps, I created a new YML pipeline, called MySQL Test, and started a new azure-pipelines.yml file.

The code in the azure-pipelines.yml file looks like the following:

All the pipeline does is execute a query against the schema1 schema using the AzureMysqlDeployment@1 task.

Note, there is no trigger defined in the yml file, so you have to run it manually to see it in action.

I also leveraged variables in my pipeline to pass in secrets, normally I would pull these secrets from an Azure Key Vault, but tried to keep things simple.

Everything looks good!

I ran my pipeline, and everything looked like it passed, but wanted to take a further look at the logs, specifically for the AzureMysqlDeployment@1 task.

Hmmm. Interesting.

While it says the IP address cannot connect, it still executes the query successfully.

That is odd.

See my update below, think I figured out what happened.

Let’s see what happens when I Allow access to Azure services in Connection Security for the Azure Database for MySQL.

Let’s run the pipeline again.

That looks better.

No firewall issues, but still odd my initial run was still able to run the query, will dig into that a little further, but for now, I am able to successfully connect to my Azure Database for MySQL from Azure DevOps!

Well, that’s it! Thanks for reading!

Update

Was struggling with why my query still ran when I had not added a firewall rule, diving into the AzureMysqlDeployment@1 task a little deeper and I discovered an argument called IpDetectionMethod.

For successful execution of the task, we need to enable administrators to access the Azure Database for MySQL Server from the IP Address of the automation agent. By selecting auto-detect you can automatically add firewall exception for range of possible IP Address of automation agent or else you can specify the range explicitly.

– Azure Database for Mysql Deployment task

So it looks like when it encounters that error it adds a firewall rule so that the script can run.

Not sure if I like that or not.

See Azure Database for Mysql Deployment task – Azure Pipelines | Microsoft Docs for more information.