Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Is this DB Server a Standalone DB Server? Is this DB Server a Master or Slave? –  RolandoMySQLDBA Dec 18 '12 at 16:35
add comment

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
Disable binary logging during the load process, brilliant, exactly what I was looking for, this is a very workable solution! –  David Parks Dec 20 '12 at 8:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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