Deleting data from a large table with replication

I need to delete all the rows from a given table. The table contains millions or records. The master database is being replicated to several slaves and I wish to do that without creating a replication lag or impacting the performance.

After some research, I tried dropping the table. That took quite a few long seconds, during which time my master DB was locked out.

I know I can gradually delete in smaller batches, just wondering is there's a quicker way.

Thanks,
Z

Instead of doing TRUNCATE TABLE (which locks up any connections accessing the table), try making an empty copy of the table, swapping it in, and dropping the old table.

EXAMPLE

Suppose the table is called mydb.mytable. Do it like this

USE mydb
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;

Doing it this way let's mytable become empty immediately and does not get locked during the deletion of the data. Now, this should go quick on the Master and should replicate. The last line DROP TABLE mytable_old; might take the longest. If that concerns you, then run this on both Master and Slave (based on the answer from Jynus)

SET sql_log_bin = 0;
USE mydb
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;
SET sql_log_bin = 1;

GIVE IT A TRY !!!

CAVEATS

As for deleting data in small chunks, that would not be a good idea if you need to table immediately available after the table data is deleted. Why ?

• Doing DELETE FROM mytable.mytable; is a single transaction. All the rows would be prepared for rollback in the InnoDB Architecture. After the DELETE is complete, all that MVCC info has to be discarded. That explains why it takes so long.

• Deleting in small chunks would just create more transactions and smaller rollbacks. Notwithstanding, it still creates the same amount of rollback information and work. It may allow you to monitor how much data is left to delete.

• Please don't run DELETE FROM mydb.mytable LIMIT 1000;. Using LIMIT on a DELETE without a WHERE clause is not replication safe.

StackExchange Database Administrators Q#80695, answer score: 7