Connecting with MySQL

An introduction on how to connect with a local MySQL database or a fully managed MySQL cluster with Azure Web App Service.

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.

MySQL in App settings blade

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.

MySQL in App enabled

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.

phpMyAdmin Dashboard for managing your MySQL in App tables and data

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.

Hello World from Azure, now with a database connection

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.

Azure Database for MySQL configured and running

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.

Adding my Connection String settings as environment variables to the App Service

All is set and I can switch between a local in-app database and the service to compare both.

Switching from local in-app MySQL database to Azure Database for MySQL service

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.

Michelangelo
Michelangelo

Passionate PHP developer, architect and community leader with nearly 20 years of experience in developing, modernizing and improving quality of complex PHP applications for enterprise, government, SMB and start-up markets.

Articles: 8

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.