关于Entuity监控系统数据库扩展及历史告警数据迁移的技术咨询

Hey there, let's break down this problem you're facing with Entuity—since it's a critical live system, we need to focus on low-risk, reliable solutions that don't disrupt your day-to-day operations.

First, let's recap your core needs: you're stuck with only 3 weeks of alarm stream data retention in Entuity's MySQL database, and you want to extend this by moving historical data to a secondary MySQL server on another machine. The CSV export/import workaround being proposed doesn't feel right, and I totally agree—let's look at the more robust options you've already identified, plus some practical details for each:

1. MySQL Replication with Selective Filtering #

This is probably the most straightforward approach for your use case, since it lets you keep Entuity's existing workflow intact while offloading historical data to a secondary server.

• How to set it up: Configure master-slave replication between your primary Entuity MySQL server and the secondary DB, then use replication filters like replicate-do-table to only sync the alarm-related tables to the slave. To prevent the slave from deleting old data when the master purges 3-week-old records, you can add an extra filter like replicate-ignore-table for the specific DELETE operations on those alarm tables (or use row-based replication and skip the delete events).
• Pros: Minimal changes to your existing system, near-real-time data sync, and the slave acts as a dedicated archive for historical alarm queries without impacting the master's performance.
• Gotchas: You'll need to test replication latency to make sure it doesn't lag too far behind, and verify that Entuity's write operations play nice with the replication setup (since it's an obscure system, better safe than sorry in a test environment).

2. DB Partitioning (Sharding is Overkill Here) #

Partitioning your alarm tables by time (e.g., weekly or monthly partitions) is another solid option, especially if you want to avoid replication entirely.

• How it works: Create time-based partitions on your primary alarm table. When a partition hits the 3-week cutoff, you can use ALTER TABLE DETACH PARTITION to remove it from the master, then ATTACH PARTITION to add it directly to the secondary server's alarm table.
• Pros: Data migration is fast and atomic (no long-running queries locking up the table), and you can still keep recent partitions on the master for Entuity's normal operations.
• Gotchas: You need to confirm that Entuity's application layer supports partitioned tables—since it's a niche system, it might not have been tested with this setup. Always validate this in a staging environment first.
• Note: Sharding is probably unnecessary here unless your alarm data volume is truly massive. Partitioning is simpler and fits your retention use case perfectly.

3. Custom Archiving & Purging Scripts #

If you need more control over what data gets archived (e.g., only critical alarms), a custom script or MySQL event can handle the job.

• How to implement: Use MySQL's EVENT SCHEDULER to run a stored procedure on a regular schedule (e.g., nightly). The procedure can batch-copy 3-week-old alarm data from the master to the secondary DB, then delete those records from the master. To avoid locking the table, copy data in small batches (e.g., one day's worth at a time) instead of moving everything at once.
• Pros: Full control over the archiving logic—you can filter, transform, or aggregate data as needed before moving it.
• Gotchas: Requires writing and maintaining custom code, and you have to be careful with table locks and transaction consistency to avoid disrupting the live system.

Why the CSV Approach Isn't Ideal #

You're right to be skeptical of exporting CSVs to disk or another DB. This method is error-prone (easy to miss data during exports, formatting issues during imports), makes historical data hard to query, and lacks the automation needed for a critical system. It's a band-aid, not a long-term solution.

Recommended Next Steps #

1. Start with selective master-slave replication—it's the lowest-risk option with the least disruption to your live system.
2. Test everything in a staging environment first: simulate data writes, master-side purges, and slave queries to ensure data consistency and system stability.
3. If replication doesn't work for some reason (e.g., Entuity has weird database interactions), pivot to time-based partitioning—just make sure to validate app compatibility first.

备注：内容来源于stack exchange，提问作者J B