Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Our main table, tblLink, will have its primary key as foreign in a few other tables too and one of the tables will be tblMainData which over time will be millions of rows.
We would like to maintain only 3 months of data.
The rest we would like to purge and keep to generate history reports. What is the best mechanism to go about in this scenario?
Should we run a cron job to keep deleting data which is more than 3 months old? If so, how can we store it in a .sql file?
As you have foreign keys in your tables so you can't go with partitioning directly.As Partitioning does not support foreign keys in MySQL.
You may go with partitioning but then you will have to create the work around for that.you have to ensure your integrity by using triggers for (INSERT/UPDATE/DELETE) on tables.
i.e Insert into the Child table will only be allowed if the corresponding record exist in Parent table. You have to maintain all these integrity(INSERT/UPDATE/DELETE) by using triggers.
Then you can create the partitioning on table's on a quarter basis and then drop the partition.
for creating the .sql files you may use --where clause with mysqldump it will create the backup in .sql file format.
You can also create the csv's for your old data for the future reference.
