Connect to Azure Database for MySQL with redirection

APPLIES TO: Azure Database for MySQL - Single Server

Important

Azure Database for MySQL single server is on the retirement path. We strongly recommend that you upgrade to Azure Database for MySQL flexible server. For more information about migrating to Azure Database for MySQL flexible server, see What's happening to Azure Database for MySQL Single Server?

This article explains how to connect an application to your Azure Database for MySQL server with redirection mode. Redirection reduces network latency between client applications and MySQL servers by allowing applications to connect directly to backend server nodes.

Before you begin

Sign in to the Azure portal. Create an Azure Database for MySQL server with engine version 5.6, 5.7, or 8.0.

For details, refer to how to create an Azure Database for MySQL server using the Azure portal or Azure CLI.

Important

Redirection is currently not supported with Private Link for Azure Database for MySQL.

Enable redirection

On your Azure Database for MySQL server, configure the redirect_enabled parameter to ON to allow connections with redirection mode. To update this server parameter, use the Azure portal or Azure CLI.

PHP

Support for redirection in PHP applications is available through the mysqlnd_azure extension, developed by Microsoft.

The mysqlnd_azure extension is available to add to PHP applications through PECL, and it's highly recommended to install and configure the extension through the officially published PECL package.

Important

Support for redirection in the PHP mysqlnd_azure extension is currently in preview.

Redirection logic

Important

Redirection logic/behavior beginning version 1.1.0 was updated and it is recommended to use version 1.1.0+.

The redirection behavior is determined by the value of mysqlnd_azure.enableRedirect. The table below outlines the behavior of redirection based on the value of this parameter beginning in version 1.1.0+.

If you're using an older version of the mysqlnd_azure extension (version 1.0.0-1.0.3), the redirection behavior is determined by the value of mysqlnd_azure.enabled. The valid values are off (acts similarly as the behavior outlined in the table below) and on (acts like preferred in the table below).

mysqlnd_azure.enableRedirect value Behavior
off or 0 Redirection won't be used.
on or 1 - If the connection doesn't use SSL on the driver side, no connection is made. The following error is returned: "mysqlnd_azure.enableRedirect is on, but SSL option isn't set in the connection string. Redirection is only possible with SSL."
- If SSL is used on the driver side, but the redirection isn't supported on the server, the first connection is aborted, and the following error is returned: "Connection aborted because redirection isn't enabled on the MySQL server or the network package doesn't meet redirection protocol."
- If the MySQL server supports redirection, but the redirected connection failed for any reason, also abort the first proxy connection. Return the error of the redirected connection.
preferred or 2
(default value)
- mysqlnd_azure will use redirection if possible.
- If the connection doesn't use SSL on the driver side, the server doesn't support redirection, or the redirected connection fails to connect for any nonfatal reason while the proxy connection is still a valid one, it falls back to the first proxy connection.

For a successful connection to Azure Database for MySQL single server using mysqlnd_azure.enableRedirect, you need to follow mandatory steps of combining your root certificate as per the compliance requirements. For more details, please visit link.

The subsequent sections of the document outline how to install the mysqlnd_azure extension using PECL and set the value of this parameter.

Prerequisites

  • PHP versions 7.2.15+ and 7.3.2+
  • PHP PEAR
  • php-mysql
  • Azure Database for MySQL server
  1. Install mysqlnd_azure with PECL. It's recommended to use version 1.1.0+.

    sudo pecl install mysqlnd_azure
    
  2. Locate the extension directory (extension_dir) by running the below:

    php -i | grep "extension_dir"
    
  3. Change directories to the returned folder and ensure mysqlnd_azure.so is located in this folder.

  4. Locate the folder for .ini files by running the below:

    php -i | grep "dir for additional .ini files"
    
  5. Change directories to this returned folder.

  6. Create a new .ini file for mysqlnd_azure. Make sure the alphabet order of the name is after that of mysqnld, since the modules are loaded according to the name order of the ini files. For example, if mysqlnd .ini is named 10-mysqlnd.ini, name the mysqlnd ini as 20-mysqlnd-azure.ini.

  7. Within the new .ini file, add the following lines to enable redirection.

    extension=mysqlnd_azure
    mysqlnd_azure.enableRedirect = on/off/preferred
    

Confirm redirection

You can also confirm redirection is configured with the below sample PHP code. Create a PHP file called mysqlConnect.php and paste the below code. Update the server name, username, and password with your own.

<?php
$host = '<yourservername>.mysql.database.azure.com';
$username = '<yourusername>@<yourservername>';
$password = '<yourpassword>';
$db_name = 'testdb';
  echo "mysqlnd_azure.enableRedirect: ", ini_get("mysqlnd_azure.enableRedirect"), "\n";
  $db = mysqli_init();
  //The connection must be configured with SSL for the 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();
  }
?>

Next steps