MySQL – Disabling Binary Logging for INSERT and UPDATE

I have run into many situations where there would be a benefit to not replicate a large INSERT or UPDATE from the MASTER to SLAVE in order to not block replication for a long period of time. Luckily MySQL provides a means to disable binary logging for your current session. Simply execute SET SQL_BIN_LOG=0 before the SQL Statement that you do not want logged to the BINLOG. This is a session variable, meaning it will be re-enabled when you close the session, or you can set it back to 1.

In order to keep the Slave in-sync, you will need to execute the same query on the Slave. You can also use mk-table-sync from the Maatkit toolkit to re-sync your table data at a later time.

MySQL – Best way to speed up Slave replication


The number one thing that you can do to speed up Slave Replication is to set innodb_flush_log_at_trx_commit=0 in your my.cnf file. This will make the transactions less recoverable on your Slave in case of a crash, however with a Slave this is usually an acceptable risk. This setting prevents MySQL from forcing a fsync after every transaction, allowing transactions to be batched up and all fsynced in one operation. When using slower HD RAID’s, this is a huge performance benefit.

Setting sync_binlog=0 will also prove to be beneficial, but also at some level of additional risk.