I would just like to know if I can purge 60 days old rows from a replica. Would the replica just copy the rows again from the master after deleting the rows? Because what I would really like to do is to leave the master as is and then do all the reads for the reports on the slave. So what I would really be doing is to copy the rows older than 60 days to an archive table and then delete it from the source table. Is it ok to have an additional table(archive) on the read replica that doesn't exist on the source?
MySQL RDS – How to Archive Table Rows from Read Replica
archiveMySQLreplication
Related Question
- Mysql – Inserting into thesql table with archive engine “duplicate key” error
- MySQL Delete Rows from Slave
- PostgreSQL – Cluster Command Locking Read-Only Queries on Replica
- PostgreSQL – Binary Replication and pg_xlog Copy Requirement
- MySQL RDS Read Replica – How to Fix Replication Error
- Mysql – Auto-archive MariaDB10 database table
- MySQL / MariaDB – Start multi-source replication with mariabackup (xtrabackup)
Best Answer
A read replica is basically a read_only slave in normal MySQL speak. RDS does allow you to change the read_only parameter, so you can purge the records you want and RDS won't recopy the data from the master.
However be careful if you go this route. If you run DML statements on the master for rows that don't exist on the slave, you likely will break the replica. If replication breaks, you're only choice is to drop the replica and create a new one from the master. You'd then have to re-purge the rows.
I am curious why you want to remove data from the replicas for report queries. I would expect reports would want more data, not less, than the master.