There's often the need to run a Web App in Node, PHP, .NET or other languages backed by a MySQL database on Azure. Here are some common options:
- You can go the full PaaS route, with an Azure Web App and a MySQL database provided through Azure's partnership with ClearDB
- You can have an Azure Web App and host MySQL in a Docker container in a Virtual Machine (PaaS + Docker)
- You can have an Azure Web App and host MySQL yourself on a Virtual Machine
- You can have both the web app (Node, ASP.Net, PHP, etc) and MySQL in the same or separate Docker containers
In this post I describe the first option: full PaaS. I have also blogged about the second Pass + Docker option.
This assumes you already have a new active Azure subscription. You can get a free trial if you don't have one.
Full PaaS - Azure Web App and ClearDB MySQL
This one is pretty straight forward because of the Azure Marketplace: there is an Azure Marketplace option that allows you to create the Web App and the ClearDB MySQL database in one go:
Clicking the green Create Web App > button takes you to the Azure portal. After logging in with your credentials, the portal will open a blade with the options to fill out the details for your Web App and MySQL database. It will take you through creating:
- An App Service Plan where your Web App will be hosted - this represents a set of features and capacity that you can share across multiple Web, Mobile, Logic, and API apps. This post shows you how to create the plan in a Free tier.
- An Azure Web App that runs in the App Service Plan described above - where you can host your PHP, Node.js, .NET, Java, or Python websites
- An Application Insights resource for monitoring of your Web App
- A MySQL database provided by ClearDB
- A Resource Group as the container for all of the above
Start by entering a name in the Web App text box. What you type here becomes part of your web app public URL. Also create a new Resource Group by entering a name in the Resource Group text box:
The Web App name needs to be globally unique as it will form part of your web app URL. The resource group will be a new container in your Azure subscription for what you are creating.
Next, create a new App Service plan and location. For example, I chose Create New, West US as the location, and changed the pricing to F1 Free:
Can't see the F1 Free option? Make sure you click on View All first on the Choose your pricing tier blade:
Then click the Select and OK buttons at the bottom to save your changes.
Now that the App Service Plan is configured, let's configure the MySQL Database option. Choose a database name, and the same location as you chose for your App Service Plan, and a pricing tier. For example, I chose West US and the free Mercury tier:
The next step is to review the ClearDB legal terms and if you agree, click Ok:
And that's all the config options done! Finally, click the Create button:
After a few minutes the Azure Portal will notify you that your new resource group has been created with your Web App, App Service Plan, Application Insights, and MySQL database in it (click Resource Groups in the Azure Portal menu to see it):
You can find connection and user details about your new database by clicking on the database icon in your resource group and going into All Settings and Properties:
Those are the details you'd need to enter into MySQL Workbench or other MySQL editor and from your Web Apps:
Here's an example of creating a new table in your MySQL database using MySQL Workbench:
Also, because you have created the Web App and MySQL database at the same time, the Web App will already come with an app setting preconfigured with the MySQL database connection string, called defaultConnection:
This means that from your Web App code you can get to the connection string for your MySQL db by looking for an environment variable called MYSQLCONNSTR_defaultConnection.
You can now create a local PHP, Node.js, .NET, Java, or Python web app and point it at your new Database, and then when ready, publish your site to your new Web App via git or other ways.
Note on the free tiers
The instructions above will get you started with a basic free set up for a Website and a MySQL database on Azure. The instructions created them using free tiers, which have a lot of limitations and throttles. If you plan to use your site and database not in a development scenario review the other tiers for Web Apps and ClearDB MySQL.