Is there a way to skip a specific table in the binary logs?

I've got binary logs configured as my incremental backup strategy between nightly backups. But we have one huge reference table that we're going to update nightly from a batch process (70GB table). This table is only read, and it's updated nightly using mysqlimport.

During the update process for this reference-only table the binary logs are causing a pretty serious delay (especially when I do a full 70GB update). It's got to do 70GB twice basically. yuck.

The reference table is currently using MyISAM (I chocked InnoDB trying to load it, so trying out MyISAM).

  • Is this DB Server a Standalone DB Server? Is this DB Server a Master or Slave? Dec 18, 2012 at 16:35

2 Answers 2


There is no mysql option to ignore a table in binary log.

If you could move the table to a separate database you might use binlog-ignore-db option, but this has its gotchas. You should understand how MySQL evaluates replication rules.

If you can write a wrapper script to load the data instead of mysqlimport (the manpage says that it's a command line interface to LOAD DATA INFILE), and the user you are importing the data with can have the SUPER privilege you might use sql_log_bin session variable like:

    SET @@session.sql_log_bin=0;
    LOAD DATA INFILE 'file_name' 
      -- other LOAD DATA INFILE options ;
    SET @@session.sql_log_bin=1;

This will disable binary logging for the current session, load the data and enable binary logging again.

  • Disable binary logging during the load process, brilliant, exactly what I was looking for, this is a very workable solution! Dec 20, 2012 at 8:39

you can use --replicate-ignore-table=db_name.tbl_name

See http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_replicate-ignore-table for details.

  • 8
    The problem with this is that replicate-ignore-table is a slave setting, so the master will create loads of binlog entries, they get transferred to the slave (eating bandwidth/space in the process), and only then ignored. It would be much more efficient to not generate those entries in the first place.
    – SVD
    Oct 9, 2014 at 1:33

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.