Azure MySQL redirect without Docker
azure php mysql mariadb webapps

December 8, 2020

Both Azure Database for MySQL and Azure Database for MariaDB support the redirection functionality, which improves application performance in situations when the application establishes several connections during request processing. Redirection basically bypasses the Azure database gateway after establishing the first connection to an available server.

This article is about using the mysqlnd_azure extension for PHP, running on Azure Web Apps for Linux, without custom Docker container.

If you don’t have specific requirements against Docker, I suggest taking a look at Heyko’s post, which describes the process of creating a custom image with mysqlnd_azure baked in and demonstrates how to deploy it to Azure Web Apps.

tl;dr

Adding PHP extensions to Web Apps

The key to using redirection is to add and enable the mysqlnd_azure extension (still preview in 122020) which is not trivial on Azure Web Apps for the following reasons:

Simplistic approach

The easiest solution would be to use Docker and create an image with mysqlnd_azure baked in and all configured.

FROM appsvc/php:7.4-apache_20200522.6
ENV ACCEPT_EULA=Y
RUN apt update && apt upgrade -y
RUN pecl install mysqlnd_azure

But what if that’s not an option? You can still make it work, using a few tricks for Azure Web Apps PHP configuration.

  1. Change the redirect_enabled setting to ON on you Azure MySQL/MariaDB instance.

  2. Get the mysqlnd_azure.so binary file. You can follow these steps. I used the appsvc base image, which has almost all prerequisite software, and built it using Docker:

    # This is run in a container running the appsvc/php:7.4-apache_20200522.6 Docker image.
    apt install git -y
    git clone https://github.com/microsoft/mysqlnd_azure --depth 1
    cd mysqlnd_azure
    phpize
    ./configure
    make
    # If successful, the binary will be in ./modules/mysqlnd_azure.so.
    # To get it from the container use: 
    # docker cp <container_id>:/home/mysqlnd_azure/modules/mysqlnd_azure.so <dest_path>
  3. Add this file to your project (for instance to a ./bin folder).

  4. Create a configuration INI file (for instance in the ./ini folder).

  5. Add the following to the file:

    extension=/home/site/wwwroot/bin/mysqlnd_azure.so
    mysqlnd_azure.enableRedirect = on
  6. Change the INI scan directory for your Web App.

    az webapp config appsettings set --name <app-name> --resource-group <resource-group-name> --settings PHP_INI_SCAN_DIR="/usr/local/etc/php/conf.d:/home/site/wwwroot/ini"
  7. Push to the repo and deploy the app.

The directory structure then can look like this:

|- bin
  |-mysqlnd_azure.so
|- ini
  |-mysqlnd_setting.ini
|- index.php

Finally, test the connection via PHP:

<?php
// Based on: https://docs.microsoft.com/azure/mariadb/howto-redirection

$host = getenv("DB_HOST");
$username = getenv("DB_USERNAME");
$password = getenv("DB_PASSWORD");
$db_name = getenv("DB_NAME");

echo "mysqlnd_azure.enableRedirect: ", ini_get("mysqlnd_azure.enableRedirect"), "\n";
$db = mysqli_init();

//The connection must be configured with SSL for redirection test
$link = mysqli_real_connect ($db, $host, $username, $password, $db_name, 3306, NULL, MYSQLI_CLIENT_SSL);

if (!$link) {
    die ('Connect error (' . mysqli_connect_errno() . '): ' . mysqli_connect_error() . "\n");
}
else {
    echo $db->host_info, "\n"; //if redirection succeeds, the host_info will differ from the hostname you used used to connect
    $res = $db->query('SHOW TABLES;'); //test query with the connection
    print_r ($res);
    $db->close();
}
?>

Improvements

This simplistic approach can be of course improved.

Microsoft Docs recommend to connect to the Web App with SSH and create the ini/ folder outside of wwwroot (i.e. /home/ini). Since the /home/* directory is synchronized across instances, this change is persisted and doesn’t require you to add the ini file into the project. DevOps deployments and infrastructure as code becomes more difficult.

You could build the myslnd_azure.so binary file during application build (within a DevOps pipeline) and inject it into the deployed artifact. That would remove the need to keep it in source control or other location.

You could also build the myslnd_azure.so binary during instance startup using the pre-build.sh . It would check if the file is present and if not, run the same script with make as we did originally. As long as the output is placed into /home/* (and outside of /home/wwwroot), this would be persisted.

Reference

comments powered by Disqus