I'm not a DBA, I'm a DevOps guy trying to manage a MySQL database which is ever growing and desperately needs some archiving. My question is not how to archive. I think I have a pretty good idea and have SO for anything I might not know yet.
I'm trying to figure out "what" to archive. I don't have a clue what our apps need, or how often they access the records. I can tell you our DB has 410 tables in it. Our largest tables has 4 million records in it. I don't have the time to go through each and every one of those, ask how much they get used, or accessed, etc. I'm trying to trim down our development environments database size. It's slow to refresh them, and our RDS bill is massive. I know for certain our development environments do not need all this data. We also run a sanitizing script which rips out PII and other real world sensitive information. I don't think it is necessary to sanitize 8+ years or possibly older of records just for a development environment database.
Goal: Have a smaller developer environment database to A) trim down bill B) reduce time for data refresh.
Question: What are common things you look at when you are making decisions on what to archive, and how much to archive? Are there tools that help you identify good candidates? Are there common queries that you run to help you make this decision?