I have huge database on MySql and It's become more and more huge. So, Now I am facing performance issues with my database. Some of tables has millions of records in it. like my login detail table contain about 2 million records, most of that unused for end-user, it is just useful to my administrative staff for analysis purpose.
One possible solution to handle this situation is, create another table with same schema of login detail table with different name, say login archive. copy 1 month before data at every midnight with cron job, and remove from original table. So, when I have to present report, I have to consider both tables to get data. I am ready to do this, but problem is I have so many tables like this (e.g. 30-35 tables at least), and I have to re-write my code, which time taking job for me. that's why I am looking for another solution which is gives me performance boost as well as less changes effort.
I have read about REPLICATION, but I am clear understaning about it. Can I do SLAVE which contain all the data (eg. all records in login detail table) and MASTER which have only 1 month's data? In this situation, I can give report from SLAVE, because It has all records. In above situation, what happen If my cron job remove records from MASTER. should it reflect on SLAVE? when my end-user made changes in records like INSERT/UPDATE/DELETE must be reflect on SLAVE. Does any latency issue create in REPLICATION?
And one more thing, Can I do same (above mentioned) with AMAZON RDS read replica?
Any blogs/example/suggestions are welcome.
Thanks.