Quickstart: Use GitHub Actions to connect to Azure Database for MySQL - Flexible Server

Article
12/19/2023

APPLIES TO: Azure Database for MySQL - Single Server Azure Database for MySQL - Flexible Server

Important

Azure Database for MySQL single server is on the retirement path. We strongly recommend that you upgrade to Azure Database for MySQL flexible server. For more information about migrating to Azure Database for MySQL flexible server, see What's happening to Azure Database for MySQL Single Server?

Get started with GitHub Actions by using a workflow to deploy database updates to Azure Database for MySQL flexible server.

Prerequisites

You'll need:

An Azure account with an active subscription. Create an account for free.
A GitHub account. If you don't have a GitHub account, sign up for free.
A GitHub repository with sample data (data.sql).

Important

This quickstart assumes that you have cloned a GitHub repository to your computer so that you can add the associated IP address to a firewall rule, if necessary.
An Azure Database for MySQL flexible server instance.
- Quickstart: Create an Azure Database for MySQL flexible server instance in the Azure portal

Workflow file overview

A GitHub Actions workflow is defined by a YAML (.yml) file in the /.github/workflows/ path in your repository. This definition contains the various steps and parameters that make up the workflow.

The file has two sections:

Section	Tasks
Authentication	1. Generate deployment credentials.
Deploy	1. Deploy the database.

Create a service principal with the az ad sp create-for-rbac command in the Azure CLI. Run this command with Azure Cloud Shell in the Azure portal or by selecting the Try it button.

az ad sp create-for-rbac --name "myML" --role contributor \
                            --scopes /subscriptions/<subscription-id>/resourceGroups/<group-name> \
                            --json-auth

The parameter --json-auth is available in Azure CLI versions >= 2.51.0. Versions prior to this use --sdk-auth with a deprecation warning.

In the example above, replace the placeholders with your subscription ID, resource group name, and app name. The output is a JSON object with the role assignment credentials that provide access to your App Service app similar to below. Copy this JSON object for later.

  {
    "clientId": "<GUID>",
    "clientSecret": "<GUID>",
    "subscriptionId": "<GUID>",
    "tenantId": "<GUID>",
    (...)
  }

OpenID Connect is an authentication method that uses short-lived tokens. Setting up OpenID Connect with GitHub Actions is more complex process that offers hardened security.

If you do not have an existing application, register a new Microsoft Entra application and service principal that can access resources.
```
az ad app create --display-name myApp
```
This command will output JSON with an appId that is your client-id. The objectId is APPLICATION-OBJECT-ID and it will be used for creating federated credentials with Graph API calls. Save the value to use as the AZURE_CLIENT_ID GitHub secret later.
Create a service principal. Replace the $appID with the appId from your JSON output. This command generates JSON output with a different objectId will be used in the next step. The new objectId is the assignee-object-id.

This command generates JSON output with a different objectId and will be used in the next step. The new objectId is the assignee-object-id.

Copy the appOwnerTenantId to use as a GitHub secret for AZURE_TENANT_ID later.
```
 az ad sp create --id $appId
```
Create a new role assignment by subscription and object. By default, the role assignment will be tied to your default subscription. Replace $subscriptionId with your subscription ID, $resourceGroupName with your resource group name, and $assigneeObjectId with generated assignee-object-id (the newly created service principal object id).
```
az role assignment create --role contributor --subscription $subscriptionId --assignee-object-id  $assigneeObjectId --assignee-principal-type ServicePrincipal --scope /subscriptions/$subscriptionId/resourceGroups/$resourceGroupName
```
Run the following command to create a new federated identity credential for your Microsoft Entra application.
- Replace APPLICATION-OBJECT-ID with the objectId (generated while creating app) for your Microsoft Entra application.
- Set a value for CREDENTIAL-NAME to reference later.
- Set the subject. The value of this is defined by GitHub depending on your workflow:
  - Jobs in your GitHub Actions environment: repo:< Organization/Repository >:environment:< Name >
  - For Jobs not tied to an environment, include the ref path for branch/tag based on the ref path used for triggering the workflow: repo:< Organization/Repository >:ref:< ref path>. For example, repo:n-username/ node_express:ref:refs/heads/my-branch or repo:n-username/ node_express:ref:refs/tags/my-tag.
  - For workflows triggered by a pull request event: repo:< Organization/Repository >:pull_request.
```
az ad app federated-credential create --id <APPLICATION-OBJECT-ID> --parameters credential.json
("credential.json" contains the following content)
{
    "name": "<CREDENTIAL-NAME>",
    "issuer": "https://token.actions.githubusercontent.com",
    "subject": "repo:octo-org/octo-repo:environment:Production",
    "description": "Testing",
    "audiences": [
        "api://AzureADTokenExchange"
    ]
}
```

Copy the MySQL connection string

In the Azure portal, go to your Azure Database for MySQL flexible server instance and open Settings > Connection strings. Copy the ADO.NET connection string. Replace the placeholder values for your_database and your_password. The connection string looks similar to the following.

Important

For Azure Database for MySQL single server, use Uid=adminusername@servername. Note the @servername is required.
For Azure Database for MySQL flexible server, use Uid= adminusername without the @servername.

   Server=my-mysql-server.mysql.database.azure.com; Port=3306; Database={your_database}; Uid=adminname@my-mysql-server; Pwd={your_password}; SslMode=Preferred;

You'll use the connection string as a GitHub secret.

Configure GitHub secrets

Service principal
OpenID Connect

In GitHub, go to your repository.
Go to Settings in the navigation menu.
Select Security > Secrets and variables > Actions.
Select New repository secret.
Paste the entire JSON output from the Azure CLI command into the secret's value field. Give the secret the name AZURE_CREDENTIALS.
Select Add secret.

You need to provide your application's Client ID, Tenant ID, and Subscription ID to the login action. These values can either be provided directly in the workflow or can be stored in GitHub secrets and referenced in your workflow. Saving the values as GitHub secrets is the more secure option.

In GitHub, go to your repository.
Select Security > Secrets and variables > Actions.
Select New repository secret.
Create secrets for AZURE_CLIENT_ID, AZURE_TENANT_ID, and AZURE_SUBSCRIPTION_ID. Use these values from your Microsoft Entra application for your GitHub secrets:

GitHub secret Microsoft Entra application

AZURE_CLIENT_ID Application (client) ID

AZURE_TENANT_ID Directory (tenant) ID

AZURE_SUBSCRIPTION_ID Subscription ID
Save each secret by selecting Add secret.

GitHub secret	Microsoft Entra application
AZURE_CLIENT_ID	Application (client) ID
AZURE_TENANT_ID	Directory (tenant) ID
AZURE_SUBSCRIPTION_ID	Subscription ID

Add your workflow

Go to Actions for your GitHub repository.
Select Set up your workflow yourself.
Delete everything after the on: section of your workflow file. For example, your remaining workflow may look like this.
```
name: CI

on:
push:
    branches: [ main ]
pull_request:
    branches: [ main ]
```

Rename your workflow MySQL for GitHub Actions and add the checkout and login actions. These actions check out your site code and authenticate with Azure using the AZURE_CREDENTIALS GitHub secret you created earlier.

Service principal
OpenID Connect

name: MySQL for GitHub Actions

on:
    push:
        branches: [ main ]
    pull_request:
        branches: [ main ]

jobs:
    build:
        runs-on: windows-latest
        steps:
        - uses: actions/checkout@v1
        - uses: azure/login@v1
            with:
                creds: ${{ secrets.AZURE_CREDENTIALS }}

name: MySQL for GitHub Actions

on:
    push:
        branches: [ main ]
    pull_request:
        branches: [ main ]

jobs:
    build:
        runs-on: windows-latest
        steps:
        - uses: actions/checkout@v1
        - uses: azure/login@v1
            with:
              client-id: ${{ secrets.AZURE_CLIENT_ID }}
              tenant-id: ${{ secrets.AZURE_TENANT_ID }}
              subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}

Use the Azure MySQL Deploy action to connect to your MySQL instance. Replace MYSQL_SERVER_NAME with the name of your server. You should have a MySQL data file named data.sql at the root level of your repository.
```
- uses: azure/mysql@v1
  with:
    server-name: MYSQL_SERVER_NAME
    connection-string: ${{ secrets.AZURE_MYSQL_CONNECTION_STRING }}
    sql-file: './data.sql'
```

Complete your workflow by adding an action to sign out of Azure. Here's the completed workflow. The file appears in the .github/workflows folder of your repository.

Service principal
OpenID Connect

name: MySQL for GitHub Actions

on:
  push:
      branches: [ main ]
  pull_request:
      branches: [ main ]
jobs:
    build:
        runs-on: windows-latest
        steps:
          - uses: actions/checkout@v1
          - uses: azure/login@v1
            with:
              creds: ${{ secrets.AZURE_CREDENTIALS }}

          - uses: azure/mysql@v1
            with:
              server-name: MYSQL_SERVER_NAME
              connection-string: ${{ secrets.AZURE_MYSQL_CONNECTION_STRING }}
              sql-file: './data.sql'

            # Azure logout
          - name: logout
            run: |
              az logout

name: MySQL for GitHub Actions

on:
  push:
      branches: [ main ]
  pull_request:
      branches: [ main ]
jobs:
    build:
        runs-on: windows-latest
        steps:
          - uses: actions/checkout@v1
          - uses: azure/login@v1
            with:
              client-id: ${{ secrets.AZURE_CLIENT_ID }}
              tenant-id: ${{ secrets.AZURE_TENANT_ID }}
              subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}
          - uses: azure/mysql@v1
            with:
              server-name: MYSQL_SERVER_NAME
              connection-string: ${{ secrets.AZURE_MYSQL_CONNECTION_STRING }}
              sql-file: './data.sql'

            # Azure logout
          - name: logout
            run: |
              az logout

Review your deployment

Go to Actions for your GitHub repository.
Open the first result to see detailed logs of your workflow's run.

Clean up resources

When your Azure Database for MySQL flexible server database and repository are no longer needed, clean up the resources you deployed by deleting the resource group and your GitHub repository.

Next steps

Learn about Azure and GitHub integration