Not the answer you need?
Register and ask your own question!

Delete many rows from a large percona mysql db

An AnAn An EntrantInactive User Role Beginner
Hello,

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

0. Stop replication
1. Select the rows NOT to be deleted into an empty new table that has the same structure as the original table
2. Rename original table to "table_old", new table - to correct name
3. Drop the original table "table_old"
4. 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? I do not want to disable FK checks.
2) How do not break replication?
3) Opinions? Alternative solutions?

Thank you in advance.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.