In this article I will describe how to set up and connect your PHP application with a MySQL database on Microsoft Azure, as part of the App Service and a separate service called Azure Database for MySQL.
A modern web application needs to connect with a database and in PHP world, MySQL has been the most common one. Running your web application in the cloud should have no impact on your application logic and require just a configuration change.
In this article I continue from where I ended in my “Hello World From Azure” article. Please read that first before continuing here.
Microsoft Azure offers two types of MySQL services:
- MySQL in App Service
- Azure Database for MySQL
MySQL in App Service
When you run a small PHP application with little database requirements, MySQL in App Service is a very good fit. It’s a local MySQL database that runs on the same Web App Service that contains your PHP code. Think of it as the common LAMP stack (Linux, Apache, MySQL, and PHP) on a single server, but now its cloud version AWASMP (Azure Web App Service, MySQL, and PHP).
Having MySQL on the same machine is super fast and requires little to no changes in your configuration. But there are some things I should warn you about:
- If your application is data driven, scaling is not possible since each instance will have their own database and dataset, and data is not shared between instances;
- There are no procedures for data backup and restore unless you provide it yourself;
- Microsoft offers no uptime guarantee and recommends to not use it for production applications;
When you are aware about the dangers, I can now tell you that MySQL in App Service is an ideal solution if you want to host a WordPress blog, a feedback form, a music/video/book library, your bookmarks, and basically all sorts of hobby projects or professional apps that don’t require too much data storage or a fully dedicated database service.
Set up MySQL in App Service
In the Azure Portal, go to your App Service settings where you find MySQL in App Service. Flip the switch to on to enable it on your App Service instance.
Once enabled it will offer additional settings for slow query log, general log and your connection string environment variable. This is the important bit as it contains the required settings for your PHP code to connect with the MySQL database.
This environment variable MYSQLCONNSTR_localdb is not a usable DSN that you can use to instantiate a PDO resource. It is structured as follows:
Database=localdb;Data Source=127.0.0.1:12345;User Id=dbuser;Password=$3cR37!I’ve created a standalone function to help me convert this string into a PDO resource which I can use in my PHP application.
When you click on “Manage” link at the top of this blade, a new window opens with PhpMyAdmin that will allow you to manage your database.
I created a simple “test” database to see if I could connect to it. I change the code in my “index.php” file I created in my previous post with the following code:
<?php
$mysqlConnectString = getenv('MYSQLCONNSTR_localdb', true);
$pdo = azureConnectionStringToPdo($mysqlConnectString);
$stmt = $pdo->query('SHOW TABLES');
$stmt->execute();
$tables = $stmt->fetchAll(PDO::FETCH_ASSOC);
function azureConnectionStringToPdo(string $connectionString): PDO
{
$connArray = explode(';', $connectionString);
$connItems = [];
foreach ($connArray as $pair) {
list ($key, $value) = explode('=', $pair);
$connItems[$key] = $value;
}
list ($host, $port) = explode(':', $connItems['Data Source']);
$dsn = sprintf(
'mysql:host=%s;port=%d;dbname=%s',
$host, $port, $connItems['Database']
);
return new PDO($dsn, $connItems['User Id'], $connItems['Password']);
}
$helloWorld = "Hello World from Azure!";
?>
<html>
<head>
<title><?php echo $helloWorld ?></title>
</head>
<body>
<h1><?php echo $helloWorld ?></h1>
<p>I am a PHP application.</p>
<h2>Tables in local MySQL</h2>
<ul>
<?php foreach ($tables as $table): ?>
<li><?php echo $table['Tables_in_localdb'] ?></li>
<?php endforeach ?>
</ul>
</body>
</html>With this code in place I can reload my Hello World page again to see the database info being presented there as well.
Azure Database for MySQL
Azure Database for MySQL is a fully managed database service within Azure. This means that you don’t have to worry about updating and upgrading MySQL, Microsoft will do this for you. You just have to make sure you choose the right redundancy zone and scale to ensure your databases can deliver on your requirements. But since this is a fully managed service, you do pay some money for it.
In Azure Portal it’s now a matter of setting up Azure Database for MySQL and configure it so the app can connect to it.
Other than your database now runs on a different system, you also connect to it over a secure SSL/TLS connection. To make use of the certificate provided by Microsoft Azure, you need to download their CA certificate because you need it to connect with the database instances. You can download it from https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem.
I made the following changes to my index.php to make fully use of both database solutions.
<?php
$mysqlConnectString = getenv('MYSQLCONNSTR_localdb', true);
$dbsource = 'in-app MySQL server';
$ssl = false;
if ([] !== $_GET && array_key_exists('remote', $_GET)) {
$mysqlConnectString = getenv('DB4MYSQL_CONN', true);
$dbsource = 'Azure Database for MySQL';
$ssl = true;
}
$pdo = azureConnectionStringToPdo($mysqlConnectString, $ssl);
$stmt = $pdo->query('SHOW TABLES');
$stmt->execute();
$tables = $stmt->fetchAll(PDO::FETCH_ASSOC);
function azureConnectionStringToPdo(string $connectionString, bool $ssl = false): PDO
{
$connArray = explode(';', $connectionString);
$connItems = [];
foreach ($connArray as $pair) {
list ($key, $value) = explode('=', $pair);
$connItems[$key] = $value;
}
list ($host, $port) = explode(':', $connItems['Data Source']);
$dsn = sprintf(
'mysql:host=%s;port=%d;dbname=%s',
$host, $port, $connItems['Database']
);
$options = [];
if ($ssl) {
$options = [
PDO::MYSQL_ATTR_SSL_CA => __DIR__ . '/DigiCertGlobalRootG2.crt.pem',
PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
];
}
return new PDO($dsn, $connItems['User Id'], $connItems['Password'], $options);
}
$helloWorld = "Hello World from Azure!";
?>
<html lang="en">
<head>
<title><?php echo $helloWorld ?></title>
</head>
<body>
<h1><?php echo $helloWorld ?></h1>
<p>I am a PHP application running on <?php echo gethostname() ?>.</p>
<h2>Tables in <?php echo $dbsource ?></h2>
<ul>
<?php foreach ($tables as $table): ?>
<li><?php echo $table['Tables_in_localdb'] ?></li>
<?php endforeach ?>
</ul>
</body>
</html>
You might notice I specifically set “PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT” to false as for some reason the openssl failed to validate the server-side TLS connection. Don’t worry, in the next article I’m going to show you how to use certificates stored in Azure Key Vault so you can verify the connection properly.
One thing I should not forget is to set the “Connection String” for my environment variable “DB4MYSQL_CONN”, which I do in Azure App Service > Settings > Configuration blade.
All is set and I can switch between a local in-app database and the service to compare both.
In this article I explained how to set up a local MySQL instance on the App Service and how to set up a Database for MySQL service to provide high-availability, scalability and more security. I also showed that there’s a huge security improvement when using Azure Key Vault for managing certificates and secrets. But that’s for another article.
[…] my previous article I explained how to connect an App Service with a MySQL database. In order to establish this connection, I need to provide the connection details as environment […]