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.
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'.