Redis与MySQL双向数据同步：定期同步及无数据场景方案咨询

Great question! Let's break this down into two clear parts: syncing your updated Redis data to MySQL on a schedule, and pulling data from MySQL to Redis when it's missing from the cache. Below are practical, production-ready solutions and tools I’ve worked with or seen implemented successfully:

Option 1: Custom Scheduled Scripts (Most Flexible) #

Perfect for small to medium datasets where you need full control over sync rules.

• How it works: Write a script (Python, Go, or even Shell) that periodically fetches data from Redis, transforms it if needed, and batches it into MySQL.
• Key Tip: Use SCAN instead of KEYS to traverse Redis keys—KEYS blocks the entire Redis instance, while SCAN iterates incrementally.
• Python Script Snippet:

import redis
import pymysql
from datetime import datetime

# Initialize connections
redis_client = redis.Redis(host="localhost", port=6379, db=0)
mysql_conn = pymysql.connect(
    host="localhost", user="root", password="your_password", db="your_db"
)
cursor = mysql_conn.cursor()

def sync_redis_to_mysql():
    # Sync all keys prefixed with "user:"
    cursor_redis = 0
    while True:
        cursor_redis, keys = redis_client.scan(cursor=cursor_redis, match="user:*", count=100)
        if not keys:
            break
        
        # Bulk fetch values from Redis
        values = redis_client.mget(keys)
        # Prepare batch SQL for insert/update
        sql = """
            INSERT INTO users (user_id, payload, sync_timestamp)
            VALUES (%s, %s, %s)
            ON DUPLICATE KEY UPDATE payload=%s, sync_timestamp=%s
        """
        batch_data = []
        for key, payload in zip(keys, values):
            user_id = key.decode().split(":")[1]
            batch_data.append(
                (user_id, payload.decode(), datetime.now(), payload.decode(), datetime.now())
            )
        
        cursor.executemany(sql, batch_data)
        mysql_conn.commit()

if __name__ == "__main__":
    sync_redis_to_mysql()
    cursor.close()
    mysql_conn.close()

• Scheduling: Use Linux cron or Windows Task Scheduler to run the script on your desired interval. Example cron job for daily 2 AM sync:

  0 2 * * * /usr/bin/python3 /path/to/your/sync_script.py
  

Option 2: Redis Persistence + Parsing Tools #

Great for full syncs or when you don’t want to write custom sync logic.

• How it works:
  1. Enable Redis RDB (snapshot) or AOF (append-only log) persistence.
  2. Use tools to parse these files into MySQL-compatible SQL.
• Tools:
  • redis-rdb-tools: Parses RDB files into SQL, CSV, or JSON. Install with pip install rdbtools python-lzf, then run:

    rdb --command sql /var/lib/redis/dump.rdb > redis_data.sql
    mysql -u root -p your_db < redis_data.sql
    

  • redis-aof-parser: Parses AOF logs to extract write operations, which you can convert to MySQL inserts/updates.
• Note: For incremental syncs, combine RDB (full snapshot) with AOF (new writes) to avoid reprocessing all data every time.

Option 3: Message Queue Triggered Sync (Near-Real-Time) #

Ideal if you need low-latency sync instead of waiting for scheduled runs.

• How it works:
  1. When your application updates Redis, it sends a copy of the data to a message queue (Kafka, RabbitMQ, etc.).
  2. A consumer service listens to the queue, receives the data, and writes it directly to MySQL.
• Example Flow:
  • App code:

    # Update Redis
    redis_client.set("user:123", '{"name": "Alice", "age": 30}')
    # Send to Kafka
    kafka_producer.send("redis_sync_topic", key=b"user:123", value=b'{"name": "Alice", "age": 30}')
    

  • Consumer code: Listens to redis_sync_topic and executes the corresponding MySQL INSERT/UPDATE statement.

Option 1: Lazy Loading (Most Common) #

Perfect for datasets where most data is rarely accessed—only load data into Redis when it’s requested and missing.

• How it works: Your application first checks Redis for the data. If it’s missing, fetch from MySQL, write to Redis (with an optional TTL to prevent stale data), then return to the user.
• Python Code Snippet:

def get_user_data(user_id):
    redis_key = f"user:{user_id}"
    data = redis_client.get(redis_key)
    
    if data:
        return data.decode()
    
    # Fetch from MySQL if Redis has no data
    cursor.execute("SELECT payload FROM users WHERE user_id = %s", (user_id,))
    result = cursor.fetchone()
    
    if result:
        payload = result[0]
        # Write to Redis with 1-hour TTL
        redis_client.setex(redis_key, 3600, payload)
        return payload
    
    return None

• Pro Tip: Set a reasonable TTL (time-to-live) on Redis keys to ensure data eventually syncs with MySQL updates.

Option 2: Binlog-Based Sync (Real-Time Incremental) #

Useful if you need to keep Redis in sync with MySQL automatically as MySQL data changes.

• Tool Recommendation: Canal
  • How it works: Canal mimics a MySQL slave, parses MySQL binlogs to capture incremental updates, and pushes them to Redis.
  • Setup: Configure Canal to monitor your target MySQL tables, then define rules to map MySQL rows to Redis keys (e.g., user:{id} maps to the users table’s id column).

Option 3: Lua Script + Distributed Lock (Prevent Cache Breakdown) #

For high-concurrency scenarios where multiple requests might try to load the same missing data from MySQL at once.

• How it works: Use a Redis Lua script to atomically check for the key and acquire a lock, so only one request fetches from MySQL while others wait for the cached data.
• Lua Script Snippet:

local redis_key = KEYS[1]
local lock_key = "lock:" .. redis_key

-- Check if data exists in Redis
local data = redis.call("GET", redis_key)
if data then
    return data
end

-- Acquire lock to prevent duplicate MySQL calls
local lock_acquired = redis.call("SETNX", lock_key, "1")
if lock_acquired == 1 then
    redis.call("EXPIRE", lock_key, 10) -- Lock expires after 10s
    return nil -- Signal app to fetch from MySQL and write to Redis
else
    -- Wait a bit and retry, or return existing data if it's now cached
    return redis.call("GET", redis_key)
end

• Your app calls this script: if it returns nil, fetch from MySQL, write to Redis, then release the lock.

• Redis → MySQL: Choose scheduled scripts for flexibility, MQ for low latency, or RDB/AOF tools for full syncs.
• MySQL → Redis: Lazy loading is the simplest for most cases; use Canal for real-time sync, and Lua locks to avoid cache breakdown in high traffic.

内容的提问来源于stack exchange，提问作者sea