Lock down the Azure Database for MySQL Flexible Server instance by redeploying it in a subnet

To start, you need to lock down access to your MySQL database by redeploying it inside a subnet. MySQL Flexible Server currently doesn’t support private endpoint connections, this is why you’ll need to deploy it inside of a subnet. You can use the following guidance to perform this task:

Private Network Access for Azure Database for MySQL - Flexible Server.
Create and manage virtual networks for Azure Database for MySQL Flexible Server using the Azure CLI

Step by step guidance

As a first step delete the previous MySQL Flexible server instance you had.

az mysql  flexible-server delete \
    --name $MYSQL_SERVER_NAME \
    --resource-group $RESOURCE_GROUP \
    --yes

Next create a private DNS zone for the new MySQL Flexible Server instance. You’ll set the DNS zone to private.mysql.database.azure.com.

MYSQL_DNS="private.mysql.database.azure.com"
az network private-dns zone create \
    -g $RESOURCE_GROUP \
    -n $MYSQL_DNS

Create an extra subnet for the MySQL Flexible Server instance.

DATABASE_SUBNET_CIDR=10.1.3.0/24
DATABASE_SUBNET_NAME=database-subnet
   
az network vnet subnet create \
    --name $DATABASE_SUBNET_NAME \
    --resource-group $RESOURCE_GROUP \
    --vnet-name $VIRTUAL_NETWORK_NAME \
    --address-prefix $DATABASE_SUBNET_CIDR

Next, in the database subnet, recreate the MySQL Flexible Server and link it to the DNS zone. When you link the new server instance to the DNS zone, MySQL Flexible server will link your DNS Zone to your VNet and it will add an A record to the DNS zone for the name of your database.

MYSQL_SERVER_NAME=mysql-vnet$APPNAME-$UNIQUEID
az mysql flexible-server create \
        --name ${MYSQL_SERVER_NAME} \
        --resource-group ${RESOURCE_GROUP}  \
        --location $LOCATION \
        --admin-user myadmin \
        --admin-password ${MYSQL_ADMIN_PASSWORD} \
        --sku-name Standard_B1ms  \
        --tier Burstable \
        --version 5.7 \
        --storage-size 20 \
        --vnet $VIRTUAL_NETWORK_NAME \
        --subnet $DATABASE_SUBNET_NAME \
        --private-dns-zone $MYSQL_DNS

Also recreate the petclinic database.

az mysql flexible-server db create \
    --server-name $MYSQL_SERVER_NAME \
    --resource-group $RESOURCE_GROUP \
    -d $DATABASE_NAME

Display the FQDN of your newly created MySQL Flexible Server, you will use this value to update the spring.datasource.url property in your config repo.

az mysql flexible-server show \
    --name $MYSQL_SERVER_NAME \
    --resource-group $RESOURCE_GROUP \
    --query fullyQualifiedDomainName

From the Git Bash window, in the config repository you cloned locally, use your favorite text editor to open the application.yml file. Update the url of the datasource to now use your MYSQL Vnet integrated instance.
```
url: jdbc:mysql://<your-vnet-integrated-server-name>.mysql.database.azure.com:3306/petclinic?useSSL=true
```

You will need to re-assign the managed identity for the database admin account to this new MySQL server.

az mysql flexible-server identity assign \
    --resource-group $RESOURCE_GROUP \
    --server-name $MYSQL_SERVER_NAME \
    --identity $DB_ADMIN_USER_ASSIGNED_IDENTITY_NAME

In this server as well, you will need to re-create a database administrator based on your current user account.

az mysql flexible-server ad-admin create \
    --resource-group $RESOURCE_GROUP \
    --server-name $MYSQL_SERVER_NAME \
    --object-id $CURRENT_USER_OBJECTID \
    --display-name $CURRENT_USER \
    --identity $DB_ADMIN_USER_ASSIGNED_IDENTITY_NAME

You can now create a sql file for creating a database user for the user assigned managed identity for usage in the spring-petclinic namespace of the cluster.

IDENTITY_LOGIN_NAME="mysql_conn"
   
cat <<EOF >createuser.sql
SET aad_auth_validate_oids_in_tenant = OFF;
DROP USER IF EXISTS '${IDENTITY_LOGIN_NAME}'@'%';
CREATE AADUSER '${IDENTITY_LOGIN_NAME}' IDENTIFIED BY '${USER_ASSIGNED_CLIENT_ID}';
GRANT ALL PRIVILEGES ON ${DATABASE_NAME}.* TO '${IDENTITY_LOGIN_NAME}'@'%';
FLUSH privileges;
EOF

Get an access token for the database and execute the sql script with this access token.

RDBMS_ACCESS_TOKEN=$(az account get-access-token \
    --resource-type oss-rdbms \
    --query accessToken \
    --output tsv) 
echo $RDBMS_ACCESS_TOKEN
   
az mysql flexible-server execute \
    --name ${MYSQL_SERVER_NAME} \
    --admin-user ${CURRENT_USER} \
    --admin-password ${RDBMS_ACCESS_TOKEN} \
    --file-path "createuser.sql"   

Restart the apps in the AKS cluster that use the backend database to make sure they use of the new connection string info. Do this by deleting their pods.
```
kubectl get pods
kubectl delete pod <customers-service-pod> 
kubectl delete pod <vets-service-pod> 
kubectl delete pod <visits-service-pod> 
```
You might see some failing pods. This is because we deleted the old database and recreated it with a new one at a different address.
In case you see errors or crashloops of your pods, you can use the below statements to diagnose what might be going wrong. A first statement you can try is look at the logs of your pod.

   kubectl logs <pod-name>

You should be able to browse the spring petclinic app and see the data again.
In the Azure Portal navigate to your newly created MySQL Flexible Server and select the Networking menu. In the menu you will notice you can no longer lock down the server firewall. The server however only allows incoming calls through the virtual network.