Mysql – edit queued MySQL binary logs for replication

master-slave-replicationMySQLmysql-5.7replication

I have a simple Master-Slave replication set up, but Slave keeps falling behind do to what appears to be a simple checkout system. Master can keep up because it runs multi-threaded, but since the checkout is on a single table on a single database, the slave cannot multi-thread to catch up.

Consequently my binary logs are full of queries like:

UPDATE db.foo SET process_id = 123 WHERE id = 456 AND process_id IS NULL;
UPDATE db.foo SET process_id = 234 WHERE id = 567 AND process_id IS NULL;

I've analyzed the general_log (turned on slave temporarily) to estimate 9/10 queries are a regex of the above query.

  1. Can I prevent these queries from being logged? How?
  2. Can I scrub them from log before it's relayed to slave? How?
  3. Any other suggestion?

Best Answer

Plan A:

Can you avoid updating that table entirely? If so, the Slave can be configured to have replicate-ignore-table on the Slave. If it is an entire database (or could be moved to be such), then you can use the more efficient binlog-ignore-db on the Master. Caveat -- If that table is needed on the Slave, well, it won't be there (or at least not up to date).

Plan B (close to what you are asking about):

SET SESSION sql_log_bin = 0;
UPDATE ...
SET SESSION sql_log_bin = 1;

Again, having the table inconsistent between Master and Slave leads to various Caveats.

Reference: https://dev.mysql.com/doc/refman/5.7/en/set-sql-log-bin.html

Related Question