0

We have a large database. We want to remove previous months data from Prod DB and keep only 3 months data. We also want a Report DB with all data.

For Example: we have restored complete Prod DB backup on Report DB on 01-JAN-2018 00:00:00 on a separate server. Now on 1st of each month we want to restore previous month data on Report DB from Prod DB. From 01-Apr-2018, we will start removing oldest month data from Prod DB.

We are planning it do with MySql Queries and PHP. We will get backup from Prod DB using table date fields and execute SQL queries on Report DB for each month. But is it the right way to do it?

Does MySQL provide any facility to achieve this with efficient way.

EDIT:

I want to know that, is there any MySQL mechanism similar to master/salve replication that run database structure/data changes queries on 'Report DB' at a specific time(daily or monthly) but does not DROP/DELETE from 'Report DB' when we DROP/DELETE from 'Prod DB'. So all 'Prod DB' queries(INSERT, UPDATE, ALTER) executes on 'Report DB' but DROP/DELETE queries are restricted on 'Report DB'.

closed as too broad by LowlyDBA, Erik Darling, SqlWorldWide, McNets, mustaccio Jan 3 '18 at 16:17

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1

I would keep both, but do it slightly differently...

Keep the large table with all the data, but purge it after 3 months. (More in a minute)

Build and maintain one or more Summary tables for reporting. These will be much smaller than the main table and much faster for building Reports. These are kept forever.

For purging, I would use PARTITION BY RANGE(TO_DAYS(...)) to break it into weekly partitions, then DROP PARTITION and REORGANIZE PARTITION every week. Details here. DROP PARTITION is orders of magnitude faster than DELETEing rows.

For Summary tables, see this. I often see 10x speedup in report generation.

I have sacrificed saving "all" the data. You may be unconfortable with that. See the partition link, above, for an alternative to DROP, namely "transportable tablespaces". With this, you turn the partition into a table (and remove it from the main table, having the effect of DROP). Now this "table" (with one week's data) can be moved to some other place for safe keeping. Or move it into a non-replicated database. Or copy it to the server with a big disk that keeps it "forever". Optionally, use "transportable tablespaces" again to hook it into a partitioned table at the target.

(This could be done by the "month" instead of by the "week", if you prefer.)

  • Your suggestions and links are good and we are using most of these. We are using summary tables, monthly partitions and master/slave DBs. Our large master database is causing perfromance issues therefore we want to keep only 3 months data but sometimes we need all previous records instead of summary. – Naveed Jan 3 '18 at 12:18
  • Actually, I want to know that, is there any MySQL mechanism similar to master/salve replication that run database structure/data changes queries on 'Report DB' at a specific time(daily or monthly) but does not DROP/DELETE from 'Report DB' when we DROP/DELETE from 'Prod DB'. So all 'Prod DB' queries(INSERT, UPDATE, ALTER) executes on 'Report DB' but DROP/DELETE queries are restricted on 'Report DB'. – Naveed Jan 3 '18 at 12:18
  • Something like Differential or Incremental Backup – Naveed Jan 3 '18 at 12:24
  • What kind of performance issues on the "large master database"? Disk space? Insert speed? Something else? – Rick James Jan 3 '18 at 14:05
  • There are several "proxy" tools that can redirect queries to different servers; check them out. Meanwhile, it seems that "transportable tablespaces" (in InnoDB) is the most efficient way to transfer a month's data from one server to another. What version of MySQL are you using? – Rick James Jan 3 '18 at 14:11

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