0

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?

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.