能否伪装为MySQL服务器直接处理Federated存储引擎传入的数据？

Great question—let’s break this down based on your exact scenario, since you’re dealing with constrained partner databases, massive write volumes, and conflicting needs for performance and data timeliness.

Core Question: Can you fake a MySQL server to handle Federated engine traffic directly? #

Absolutely. The Federated storage engine communicates with remote databases using the standard MySQL client-server protocol. That means if you build (or repurpose) a lightweight service that speaks this protocol, you can trick your local Federated tables into sending data directly to you instead of a real MySQL server. This skips all the overhead of writing to your local MySQL instance entirely.

How to implement this: #

• Leverage existing protocol libraries: You don’t need to write the MySQL protocol from scratch. Tools like Python’s mysql-protocol package, Go’s go-mysql library, or even Java’s mysql-connector (with custom handler logic) can handle the handshake, query parsing, and response handling for you.
• Build a lightweight proxy service: Your service will:
  1. Accept connections from your local Federated tables (mimicking a MySQL server’s handshake)
  2. Parse incoming write operations (INSERT, UPDATE, DELETE)
  3. Buffer these operations in Redis (using structures like Streams for ordered batches, Hashes for record storage, or Sorted Sets for time-based grouping)
  4. Implement batch-flush logic (e.g., flush to persistent storage every 10 seconds, or when 1000 records accumulate) to balance timeliness and performance.

This approach completely eliminates the local MySQL write bottleneck, since you’re bypassing the storage engine entirely and handling data directly in memory/RAM via Redis.

Alternative: Fixing the Blackhole + Binlog Approach (If You Want a Quick Win) #

Your original Blackhole idea is solid, but disk IO is the bottleneck. Here’s how to tweak it to perform better:

• Store binlogs in tmpfs: Mount a tmpfs (in-memory filesystem) for your MySQL binlog directory. This turns disk writes into memory operations, which is way faster. Just note: you’ll lose binlogs on server restart, so pair this with a real-time consumer (like a Redis writer) to pull binlog events as they’re generated.
• Tune binlog parameters: Increase max_binlog_size to reduce the number of file rotations, and set binlog_cache_size higher to keep more transactions in memory before flushing.

That said, this is still a workaround compared to the fake MySQL server approach, since you’re still relying on MySQL’s binlog subsystem—you’ll get better control and performance with a custom proxy.

Redis as Unified Storage: Key Considerations #

Since you’re using Redis for multi-source data unification:

• Use Redis Streams for ordered batches: Streams are perfect for capturing write events in order, and you can use consumer groups to process batches across multiple workers if needed.
• Avoid single-point bottlenecks: Cluster Redis if you’re dealing with truly massive volumes, to distribute both storage and processing load.
• Plan for persistence: If you need long-term data storage, set up a periodic batch job to flush Redis data to a columnar store (like ClickHouse) or even a slower MySQL instance—this way you get the speed of Redis for real-time processing, plus durable storage for historical data.

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