How to Sync Redis with MySQL: Strategies, Code Samples, and Best Practices

Why Sync Redis and MySQL

Performance optimization : MySQL is relational and slower; Redis is in‑memory and fast. Storing hot data such as product information in Redis speeds up queries.

Data consistency requirements : When MySQL data (e.g., inventory) changes, the Redis cache must be updated to avoid issues like overselling.

Implementation approaches

(1) Database triggers

Principle : Create MySQL triggers that fire on INSERT/UPDATE/DELETE and execute Redis client commands to sync data.

Example :

import redis
r = redis.Redis(host='localhost', port=6379, db=0)

DELIMITER //
CREATE TRIGGER sync_product_insert AFTER INSERT ON products
FOR EACH ROW
BEGIN
    SET @product_key = CONCAT('product:', NEW.id);
    SET @product_name = NEW.name;
    SET @product_price = NEW.price;
    SET @redis_command = CONCAT('HMSET ', @product_key, ' name ', @product_name, ' price ', @product_price);
    SELECT sys_exec(@redis_command);
END;
//
DELIMITER ;

Note: Using sys_exec to run external commands may have security and performance implications.

(2) Application‑level double write

Principle : In application code, after modifying MySQL, also update Redis directly, offering flexibility.

Example (Django) :

from django.db import models

class Product(models.Model):
    name = models.CharField(max_length=100)
    price = models.DecimalField(max_digits=10, decimal_places=2)

import redis
r = redis.Redis(host='localhost', port=6379, db=0)

def save_product(request):
    product_name = request.POST.get('name')
    product_price = request.POST.get('price')
    new_product = Product(name=product_name, price=product_price)
    new_product.save()
    product_key = f"product:{new_product.id}"
    r.hset(product_key, "name", product_name)
    r.hset(product_key, "price", product_price)
    return HttpResponse("Product saved and synced to Redis")

This approach tightly couples business logic with sync code.

(3) Message queue

Principle : Publish a message to a queue (e.g., RabbitMQ) when MySQL changes; a consumer reads the message and updates Redis, decoupling the systems.

Example (RabbitMQ with pika) :

import pika

def send_message_to_queue(data_change_info):
    connection = pika.BlockingConnection(pika.ConnectionParameters('localhost'))
    channel = connection.channel()
    channel.queue_declare(queue='data_sync_queue')
    channel.basic_publish(exchange='', routing_key='data_sync_queue', body=data_change_info)
    connection.close()

import pika
import redis
r = redis.Redis(host='localhost', port=6379, db=0)

def callback(ch, method, properties, body):
    data_change_info = body.decode('utf-8')
    operation_type, product_id = data_change_info.split(':')
    if operation_type == "insert":
        # fetch data from MySQL (omitted)
        product_name = "Sample Name"
        product_price = 10.0
        product_key = f"product:{product_id}"
        r.hset(product_key, "name", product_name)
        r.hset(product_key, "price", product_price)

connection = pika.BlockingConnection(pika.ConnectionParameters('localhost'))
channel = connection.channel()
channel.queue_declare(queue='data_sync_queue')
channel.basic_consume(queue='data_sync_queue', on_message_callback=callback, auto_ack=True)
channel.start_consuming()

This method adds overhead of a queue but improves scalability and reliability.

Key considerations

Handling data consistency : Use distributed transactions or compensation mechanisms to mitigate latency‑induced inconsistencies.

Performance optimization : Avoid excessive sync frequency; batch or throttle updates for read‑heavy, rarely‑changed data.

Exception handling : Implement retries and fallback queues for network or service failures during sync.