0

I have a 300GB database that is growing around 5GB a day. It stores time-based data, and as such, I currently have around 60 days of data. My main database server has a 450 GB NVMe, so I want to get a secondary server that has expandable cheap storage for old data.

The main server only really needs the last 15 days of data. So anything older than 15 days of data I want to move off to the secondary database which has slower but cheap storage. I think standard backup or archiving isn't viable because I want to query the data at any given moment (maybe ~100 queries a day).

I want to kill two birds one stone here and I thought if I setup replication to the secondary server and then somehow indicate the cleanup stored procedure which delete records older than 15 days doesn't propagate deletes to the slave.

Is this possible and recommended? If not, what is the best way to accomplish archived the data but to another server and allow querying. I suspect partitioning all of my tables but not all of my tables are time-based so I would need some way to move the time-based partitioned data over along with any FKs needed for it

PS: I won't be relying on the secondary server as a backup but rather my main replication. I would then do offsite backups of the secondary server periodically

1 Answer 1

1

Let me explain multiple things. Then you can figure out which combination fits best.

  • Technically the Primary and the Replica should have identical data. However...
  • You can, via a temporary setting, say that specific statements are not to be replicated. These could be anything -- INSERT, Partition commands, DROPs, etc.
  • It sounds like you want the data to be moved from fast media to slow media. This would require some form of copy. And that would probably block all operations (maybe even SELECTs) from the table while it is happening.
  • It may be possible to direct that certain PARTITIONs are in a different filesystem path. If so, that secondary path could be the other drive.
  • "Transportable tablespaces" allows for an individual PARTITION to be disconnected from a table and turned into an independent table. That could them be copied to another device, backed up, deleted, etc. (Do not do it via filesystem operations.)
  • It is probably wise to switch to PARTITION BY RANGE(TO_DAYS(...)) with daily partitions. This would facilitate such movement and eventually deleting after 60 days.
  • PARTITIONing applies to a table, so there is no need for doing it with all tables. (Perhaps you are referring to "sharding", which involves having separate, independent, instances of MySQL with each one having part of the data.
  • If you have not already set up partitioning or replication, there will be a one-time cost (in downtime) to set up either. After that, ongoing maintenance will be 'cheap'.
  • More on this use of partitioning: http://mysql.rjweb.org/doc.php/partitionmaint

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.