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
-
Set
redirect_enabled
to ON on your MariaDB/MySQL database. -
Use
mysqlnd_azure
extension on Azure Web Apps, enable SSL connection. -
PECL installation doesn't work on Azure Web Apps - manual build with
make
is needed. -
Web Apps filesystem doesn't persist anything outside the
/home/
directory. -
Pre-built
mysqlnd_azure
binary can be added to a folder within application (/home/site/wwwroot/bin
) and activated on instance boot. -
The
PHP_INI_SCAN_DIR
application setting tells PHP on Web Apps to look into custom directories for configuration - such as one in/home/site/wwwroot
... -
The INI file should contain this:
extension=/home/site/wwwroot/bin/mysqlnd_azure.so
mysqlnd_azure.enableRedirect = on
Adding PHP extensions to Web Apps
The key to using redirection is to add and enable the mysqlnd_azure extension (still preview in 12/2020) which is not trivial on Azure Web Apps for the following reasons:
- The filesystem of Web Apps is set up in a way that it doesn't persist anything outside the
/home/
(%HOME%
) directory. So even if the extension was installed after instance provisioning, every restart or scaling would remove it. - To enable the extension, php.ini or any of the config files need to be modified. These files live outside of
/home/
and therefore none of the changes are persisted. - To actually load the extension into PHP, Web Apps need to restart the instance. And again - that means wiping the filesystem and any additional extensions with it.
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.
-
Change the
redirect_enabled
setting to ON on you Azure MySQL/MariaDB instance. -
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>
-
Add this file to your project (for instance to a
./bin
folder). -
Create a configuration INI file (for instance in the
./ini
folder). -
Add the following to the file:
extension=/home/site/wwwroot/bin/mysqlnd_azure.so
mysqlnd_azure.enableRedirect = on
- 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"
- 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
Feedback
Found something inaccurate or plain wrong? Was this content helpful to you? Let me know!
š§ codez@deedx.cz