Tutorial: Boost Azure Database for MySQL - Flexible Server performance with Azure Cache for Redis

APPLIES TO: Azure Database for MySQL - Flexible Server

This article demonstrates how to boost the performance of an Azure Database for MySQL flexible server instance using Azure Cache for Redis. Azure Cache for Redis is a secure data cache and messaging broker that provides high throughput and low-latency access to data for applications.

Prerequisites

For this quickstart you need:

• An Azure account with an active subscription.

  If you don't have an Azure subscription, create an Azure free account before you begin. Currently, with an Azure free account, you can try Azure Database for MySQL - Flexible Server free for 12 months. For more information, see Try Azure Database for MySQL - Flexible Server for free.

• Create an Azure Database for MySQL flexible server instance using Azure portal
  or Azure CLI if you don't have one.

• Configure networking settings of the Azure Database for MySQL flexible server instance to make sure your IP has access to it. If you're using Azure App Service or Azure Kubernetes service, enable Allow public access from any Azure service within Azure to this server setting in the Azure portal.

Having issues? Let us know

Populate the MySQL database

Connect to Azure Database for MySQL flexible server using MySQL Workbench and run the following query to populate the database.

CREATE DATABASE tododb;

CREATE TABLE tasks
(
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	title nvarchar(100) NOT NULL,
	completed TINYINT(1) NOT NULL
);

INSERT INTO tasks (id,title, completed) VALUES
(1,'Task1', 0),
(2,'Task2', 0),
(3,'Task3', 1),
(4,'Task4', 1),
(5,'Task5', 0);

Create a Redis cache

1. To create a cache, sign in to the Azure portal and select Create a resource.

   

2. On the Get Started page, type Azure Cache for Redis in the search box. Then, select Create.

   

3. On the New Redis Cache page, configure the settings for your cache.

   Setting	Choose a value	Description
   Subscription	Drop down and select your subscription.	The subscription under which to create this new Azure Cache for Redis instance.
   Resource group	Drop down and select a resource group, or select Create new and enter a new resource group name.	Name for the resource group in which to create your cache and other resources. By putting all your app resources in one resource group, you can easily manage or delete them together.
   DNS name	Enter a unique name.	The cache name must be a string between 1 and 63 characters that contain only numbers, letters, or hyphens. The name must start and end with a number or letter, and can't contain consecutive hyphens. Your cache instance's host name is <DNS name>.redis.cache.windows.net.
   Location	Drop down and select a location.	Select a region near other services that use your cache.
   Cache SKU	Drop down and select a SKU.	The SKU determines the size, performance, and features parameters that are available for the cache. For more information, see Azure Cache for Redis Overview.
   Cache size	Drop down and select a size of your cache	For more information, see Azure Cache for Redis Overview.

4. Select the Networking tab or select the Networking button at the bottom of the page.

5. In the Networking tab, select your connectivity method.

6. Select the Next: Advanced tab or select the Next: Advanced button on the bottom of the page to see the Advanced tab.

   

   • For Basic or Standard caches, toggle the selection for a non-TLS port. You can also select if you want to enable Microsoft Entra Authentication.
   • For a Premium cache, configure the settings for non-TLS port, clustering, managed identity, and data persistence. You can also select if you want to enable Microsoft Entra Authentication.

   Important

   For optimal security, Microsoft recommends using Microsoft Entra ID with managed identities to authorize requests against your cache whenever possible. Authorization with Microsoft Entra ID and managed identities provides superior security and ease of use over Shared Key authorization. For more about using managed identities with your caches, see Use Microsoft Entra ID for cache authentication.

7. Select the Next: Tags tab or select the Next: Tags button at the bottom of the page.

8. Optionally, in the Tags tab, enter the name and value if you wish to categorize the resource.

9. Select Review + create. You're taken to the Review + create tab where Azure validates your configuration.

10. After the green Validation passed message appears, select Create.

It takes a while for a cache to create. You can monitor progress on the Azure Cache for Redis Overview page. When Status shows as Running, the cache is ready to use.

Use Redis with Python

Install the latest version of Python on your local environment or on an Azure virtual machine or Azure App Service. Use pip to install redis-py.

pip install redis

The following code creates a connection to Azure Cache for Redis instance using redis-py, stores the query result into the Azure Cache for Redis and fetches the value from the cache.

import redis
import mysql.connector

r = redis.Redis(
    host='your-azure-redis-instance-name.redis.cache.windows.net',
    port=6379, 
    password='azure-redis-primary-access-key')
    
mysqlcnx = mysql.connector.connect(user='your-admin-username', password='db-user-password',
                              host='database-servername.mysql.database.azure.com',
                              database='your-databsae-name')
			      
mycursor = mysqlcnx.cursor()
mycursor.execute("SELECT * FROM tasks where completed=1")
myresult = mycursor.fetchall()

#Set the result of query in a key 
if result:
          cache.hmset(mykey, myresult)
          cache.expire(mykey, 3600)
      return result
      
#Get value of mykey
getkeyvalue= cache.hgetall(mykey)

#close mysql connection
mysqlcnx.close()

Using Redis with PHP

Install PHP on your local environment. Follow the steps below to write a PHP script that caches a SQL query from the Azure Database for MySQL flexible server database. Here are a few prerequisites before running the script:

1. Install and enable Redis PECL extension to use Azure Cache for Redis with your PHP script. See how to install the extension locally
2. Install and enable MySQL PDO extension

<?php

$redis = new Redis();
$redis->connect('azure-redis-instance-ame.redis.cache.windows.net', 6379);
$redis->auth('azure-redis-primary-access-key');

$key = 'tasks';

if (!$redis->get($key)) {
    /*Pulling data from MySQL database*/
    $database_name     = 'database-name';
    $database_user     = 'your-database-user';
    $database_password = 'your-database-password';
    $mysql_host        = 'database-servername.mysql.database.azure.com';

    $pdo = new PDO('mysql:host=' . $mysql_host . '; dbname=' . $database_name, $database_user, $database_password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql  = "SELECT * FROM tasks";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
       $tasks[] = $row;
    }

    $redis->set($key, serialize($tasks));
    $redis->expire($key, 10);

} else {
     /*Pulling data from Azure Cache for Redis*/
     $tasks = unserialize($redis->get($key));

}

echo $source . ': <br>';
print_r($tasks);

Using Redis with WordPress

The benefit of enabling Azure Cache for Redis instance to your WordPress application will allow you to deliver content faster since all of the WordPress content is stored in the database. You can cache content that is mostly read only from WordPress database to make the query lookups faster. You can use either of these plugins to setup Redis. Install and enable Redis PECL extension. See how to install the extension locally or how to install the extension in Azure App Service.

Install Redis Object cache and activate this plugin on our WordPress application. Now update the wp-config.php file right above the statement / That's all, stop editing! Happy blogging. /*

define( 'WP_REDIS_HOST', 'azure-redis-servername.redis.cache.windows.net' );
define( 'WP_REDIS_PORT', 6379 );
define( 'WP_REDIS_PASSWORD', 'azure-redis-primary-access-key' );
define( 'WP_REDIS_TIMEOUT', 1 );
define( 'WP_REDIS_READ_TIMEOUT', 1 );

// change the database for each site to avoid cache collisions
// values 0-15 are valid in a default redis config.
define( 'WP_REDIS_DATABASE', 0 );

// automatically delete cache keys after 7 days
define( 'WP_REDIS_MAXTTL', 60 * 60 * 24 * 7 );

// bypass the object cache, useful for debugging
// define( 'WP_REDIS_DISABLED', true );

/* That's all, stop editing! Happy blogging. */

Go to the Wordpress admin dashboard and select the Redis settings page on the menu. Now select enable Object Cache. Plugin will read the Azure Cache for Redis instance information from wp-config.php file.

You may also use W3 Total cache to configure Azure Cache for Redis on your WordPress app. You can evaluate the performance improvements using Query Monitor plugin, which allows you to debug database queries and it also shows total database queries grouped by a plugin.

Next steps

In this quickstart, you learned how to create an instance of Azure Cache for Redis and use it with Azure Database for MySQL flexible server. See performance best practices for Azure Database for MySQL flexible server.