I need a fresh opinion on the case. Any thoughts are appreciated.
Input: we have a huge percona mysql (5.5) database that takes a couple of Tb (terabytes). Tables on innodb engine.
More than a half (2/3) of that size should be deleted as quick as possible.
Also we have master-slave configuration.
As the quickest way to achieve that I am considering the following solution:
Execute for each table on the slave server (to avoid production downtime) :
- Stop replication
- Select the rows NOT to be deleted into an empty new table that has the same structure as the original table
- Rename original table to “table_old”, new table - to correct name
- Drop the original table “table_old”
- Start replication
The problem is that we have a lot of FK constraints. Also I am afraid to break the replication during this process.
- What the potential problems can be with FK constraints in this solution? I do not want to disable FK checks.
- How do not break replication?
- Opinions? Alternative solutions?
Thank you in advance.