Zero to MySQL in less than 10 minutes with Azure Database for MySQL and Azure Web Apps

I'm a long-time fan of Open Source and have spent chunks of my career knocking out LAMP solutions since before 'LAMP' was a thing!

Over the last few years we have seen a revived Microsoft begin to embrace (not 'embrace and extend') the various Open Source platforms and tools that are out there and to actively contribute and participate with them.

Here's our challenge today - setup a MySQL environment, including a web-based management UI, with zero local installation on your machine and minimal mouse clicks.

Welcome to Azure Cloud Shell

Our first step is to head on over to the Azure portal at https://portal.azure.com/ and login.

Once you are logged in open up a Cloud Shell instance by clicking on the icon at the top right of the navigation bar.

If this is the first time you've run it you will be prompted to create a home file share. Go ahead and do that :).

Once completed, run this command and note down the unique ID of the Subscription you're using (or note the ID of the one you want to use!)

az account list

MySQL Magic

Now the fun begins! I bet you're thinking "lot's of CLI action", and you'd be right. With a twist!

I'm going to present things using a series of simple bash scripts - you could easily combine these into one script and improve their argument handling, but I wanted to show the individual steps without writing one uber script that becomes impenetrable to understand!

Here's our script to setup MySQL in Azure.

create-azure-mysql.sh

#!/bin/sh

azuresub=$1
demolocation=$2
demoresourcegroup=$3
mysqlservername=$4
mysqladminuser=$5
mysqladminpass=$6

# uncomment to add local support and login to your Azure subscription
# az login

az account set --subscription $azuresub

az group create --location $demolocation --name $demoresourcegroup

az mysql server create --name $mysqlservername \
    --version 5.7 --performance-tier Basic \
    --compute-units 50 --storage-size 51200 \
    --resource-group $demoresourcegroup --location $demolocation \
     --admin-user $mysqladminuser --admin-password $mysqladminpass

Grab from this Gist if you want.

Now I could get you to type that out, or upload to your cloud share via the Portal, but that's no fun!

At your Cloud Shell prompt run the following (update the last command line with your arguments):

curl -O -L https://gist.githubusercontent.com/sjwaight/0ba37e3c3522aeaebf6bd20c9f3895b0/raw/a9fccb9126330983b8a84a71f07dea2b1c583c68/create-azure-mysql.sh
chmod 755 create-azure-mysql.sh
# make sure you update the parameters for your environment # change 'mysqldemo01', 'yawadmin' and '5ecurePass@word!'
./create-azure-mysql.sh 368ff49e-XXXX-XXXX-XXXX-eb42e73e2f25 westus mysqldemorg mysqldemo01 yawadmin 5ecurePass@word!

Note that the server name (mysqldemo01) must be gobally unique - if you get an error try adding some random text to the server name.

The other two items 'yawadmin' and '5ecurePass@word!' are Deployment Credentials that must be set in the App Service before you can use them - if you are using a new Subscription you can go ahead and set them.

Note!!! if you are already using Deployment Credentials in the Subscription you shouldn't change them without understanding the impact this will have across other App Service instances as these Credentials are shared.

After a few minutes you will have a MySQL server ready for use. Note that by default you won't be able to connect to it as the firewall around it is shut by default (which is a good thing). We'll rectify connectivity later. For now, on to the next piece of the puzzle.

Manage MySQL from a browser

No, not via some super-duper Microsoft MySQL tooling, but via everyone's old favourite phpMyAdmin.

Surely this will take a lot of work to install I hear you ask? Not at all!

Enter Azure Web App Site Extensions! Specifically the phpMyAdmin extension.

Let's get started by creating a new App Service Plan and Web App to which we can deploy our management web application.

We're going to re-use the trick we learned above - pulling a Gist from Github using curl. First have a ready through the script :).

create-azure-webapp.sh

#!/bin/sh

azuresub=$1
demolocation=$2
demoresourcegroup=$3
mysqlservername=$4
mysqladminuser=$5
mysqladminpass=$6
appplanname=$7
webappname=$8

# uncomment to add local support and login to your Azure subscription
# az login

az account set --subscription $azuresub

# idempotent so you can call as many times as you like if it already exists.
az group create --location $demolocation --name $demoresourcegroup

az appservice plan create --name $appplanname \
    --resource-group $demoresourcegroup \
    --location $demolocation --sku B1

az webapp create --name $webappname \
    --resource-group $demoresourcegroup \
    --plan $appplanname

echo "Make sure you note the "outboundIpAddresses" in the response to the previous command."

# Setup a connection string for the Web App to access our previously created MySQL instance (running in same resource group)
az webapp config connection-string set \
    --resource-group $demoresourcegroup \
    --name $webappname \
    -t mysql --settings defaultconnection="Data Source=${mysqlservername}.mysql.database.azure.com; User Id=${mysqladminuser}@${mysqlservername}; Password=${mysqladminpass}"

Grab from this Gist if you want.

You can download this Gist from within your Cloud Shell and execute it as follows. Make sure to update the command line arguments

curl -O -L https://gist.githubusercontent.com/sjwaight/d65a04036cd6ceda63bf90485e22adb4/raw/6802a35d21a9d46e0abb5e3f1c88f32551f325da/create-azure-webapp.sh
chmod 755 create-azure-webapp.sh
# make sure you update the parameters for your environment
./create-azure-webapp.sh 368ff49e-XXXX-XXXX-XXXX-eb42e73e2f25 westus mysqldemorg mysqldemo01 yawadmin 5ecurePass@word! mydemoapplan msqlmgewebapp

In order to deploy the Web App Sit Extension we are going to dig a bit behind the covers of Azure App Services and utilise the REST API provided by the kudu site associated with our Web App (this appears as 'Advanced tools' in the Portal). If you want to understand more about its capabilities you can, and specifically about how to work with Site Extensions read their excellent documentation.

Note: if you haven't previously setup a Git / FTP deployment user you should uncomment the line that does this. Note that this step sets the same credentials for all instances in the Subscription, so if you already have credentials defined think twice before uncommenting this line!

enable-phpmyadmin.sh

#!/bin/sh

azuresub=$1
demoresourcegroup=$2
webappname=$3
deployuser=$4
deploypass=$5

# CAUTION! RESTS CREDS for *all* Web Apps
# az webapp deployment user set --user-name $deployuser --password $deploypass

# read the phpmyadmin extension configuration (required to create it)
wget --user $deployuser --password $deploypass https://${webappname}.scm.azurewebsites.net/api/siteextensions/phpmyadmin --output-document phpmyadmin.ext.json

# enable the phpmyadmin extension - sends the extension definition in the body
curl -u $deployuser:$deploypass -i -X PUT -H "Content-Type:application/json" https://${webappname}.scm.azurewebsites.net/api/siteextensions/phpmyadmin -d @phpmyadmin.ext.json

# restart our Web App to ensure the route is registered to phpMyAdmin
az webapp restart --name ${webappname} --resource-group $demoresourcegroup

echo
echo "Open a web browser at https://"$webappname".scm.azurewebsites.net/phpMyAdmin/ to access the MySQL web manager."

Grab from this Gist if you want.

curl -O -L https://gist.githubusercontent.com/sjwaight/8a530aff0e5f3991484176825b49a563/raw/be8cfa68f95a29687f2dae6af9ca163e8e7877ac/enable-phpmyadmin.sh
chmod 755 enable-phpmyadmin.sh
# make sure you update the parameters for your environment
./enable-phpmyadmin.sh 368ff49e-XXXX-XXXX-XXXX-eb42e73e2f25 mysqldemorg msqlmgewebapp deployuser d3pl0yP455!

Grab from this Gist if you want.

Done!

Browse to your freshly setup phpMyAdmin instance.

Oh noes!

Yes, we forgot to open up the firewall surrounding the Azure Database for MySQL instance. We can do this pretty easily.

Remember those 'outboundIpAddresses' values you captured when you created the Web App above? Good, this is where you will need them.

You should find that you have four source IP addresses from which outbound traffic can originate. These won't change as long as you don't "stop" or delete the Web App.

Here's our simple script to enable the Web App to talk to our MySQL instance.

update-mysql-firewal.sh

#!/bin/sh

azuresub=$1
demoresourcegroup=$2
mysqlservername=$3
firstip=$4
secondip=$5
thirdip=$6
fourthip=$7

# uncomment to add local support and login to your Azure subscription
# az login

az account set --subscription $azuresub

az mysql server firewall-rule create \
    --resource-group $demoresourcegroup \
    --server-name $mysqlservername \
    --name allowwebapp01 \
    --start-ip-address $firstip \
    --end-ip-address $firstip
az mysql server firewall-rule create \
    --resource-group $demoresourcegroup \
    --server-name $mysqlservername \
    --name allowwebapp02 \
    --start-ip-address $secondip \
    --end-ip-address $secondip
az mysql server firewall-rule create \
    --resource-group $demoresourcegroup \
    --server-name $mysqlservername \
    --name allowwebapp03 \
    --start-ip-address $thirdip \
    --end-ip-address $thirdip
az mysql server firewall-rule create \
    --resource-group $demoresourcegroup \
    --server-name $mysqlservername \
    --name allowwebapp04 \
    --start-ip-address $fourthip \
    --end-ip-address $fourthip

Grab from this Gist if you want.

Now the usual drill.

Note: You might have more than four outbound IP addresses to allow - if so simply edit the script to suit.

curl -O -L https://gist.githubusercontent.com/sjwaight/bbd71ca3f094abe5cf438c1b826b83fe/raw/4b6aaddee8f6a21b81061ed4c4085258c0271068/update-mysql-firewall.sh
chmod 755 update-mysql-firewall.sh
# make sure you update the parameters for your environment
./update-mysql-firewall.sh 368ff49e-XXXX-XXXX-XXXX-eb42e73e2f25 mysqldemorg mysqldemo01 192.168.1.1 192.168.2.2 192.168.3.3 192.168.4.4

Once the rules are applied, try refreshing the web browser and you will now see the you are viewing the glorious phpMyAdmin.

Congratulations, you now have a fully functional MySQL environment, with no installs and minimal configuration required!