MySQL binary logs – skip a table

binloglogsMySQL

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).

Best Answer

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.

Related Question