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.