Delete many rows from a large percona mysql db

Question

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) :

1. Stop replication
2. Select the rows NOT to be deleted into an empty new table that has the same structure as the original table
3. Rename original table to "table_old", new table - to correct name
4. Drop the original table "table_old"
5. Start replication

The problem is that we have a lot of FK constraints. Also I am afraid to break the replication during this process.

Questions: 1) What the potential problems can be with FK constraints in this solution? 2) How do not break replication? 3) Opinions? Alternative solutions?

Thank you in advance.

Answer 1

if you can put db offline (aka no one is accessing the db except you) for a while, you can go with your solution but you need to drop the FK involved before and to recreate them after. You should also check for AUTO_INCREMENT columns that will change number with copy operation.

the FK are needed if you want the db online, I had a similar problem with some huge log tables, any try to delete all the records at a time will probably lock the database or corrupt the table.

so I went for a slow approach, I made a procedure that will delete batches of rows from the tables using clustered primary key, and then I scheduled it to run every n seconds.