How can I use the TRUNCATE sentence avoiding MySql to write it into the binlog?

Question

It seems MySQL does not support the flag "NO_ WRITE_ TO_ BINLOG" for TRUNCATE. So I have to wait until delay is 0, then stop the replication, make the TRUNCATE of the table/s, reset the master, and then start replication again. Really painful. Any other suggestion?

Answer 1

TRUNCATE is pretty heavy-handed. Can you get by with just a DELETE query?

Answer 2

You can use the command to disable binary logging for a session to do what you want.

SET SQL_LOG_BIN = 0;
TRUNCATE TABLE ;
SET SQL_LOG_BIN = 1;

This does require that you have the SUPER privilege since you are effectively breaking replication by not sending the TRUNCATE to the slave.

Answer 3

TRUNCATE is not to be used lightly, since it essentially just drops the data on the floor without logging the fact that it did it. It's transactionally unsafe and impossible to recover from, and as a result it's not compatible with replication. Even if you manage to use TRUNCATE in a replication setup, your replicated data will be corrupt, or at best invalid.