Tag Archives: DBaaS

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.

Cloud Shell

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.

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 :).

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!

 
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!

Done!

Browse to your freshly setup phpMyAdmin instance.

Connection Error

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.

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.

phpMyAdmin on Azure!

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

Tagged , , , , , , ,