Limiting binary logging by table

I have a server that has a database that has a back policy as follows:
once a day the DB is mysqldump'ed, and the logs purged. This dump file
is copied to a remote location. Every 10 minutes the contents of the
binary log directory are tar'ed up and also copied to the remote
location. This means my remote location always contains a snapshot and
the binaries required to update that snapshot to within 10mins ago. This
works fine, but it needs an optimisation.

The server is running the Drupal CMS which holds a table called 'cache',
containing cached web pages which is delivers as required. The problem
is that this cache table gets updated several times a day and these
large updates go into the binary log. I'm sending something like 8GB of
these cache table updates over to my remote location each month, and
since they're of no value, that's an expense I'd like to avoid.

So, is there a way of limiting which tables or updates get binary
logged? I see it can be done at the DB level. Alternatively, can I
somehow selectively trim updates to a given table from a binary log?

--
Derek Fountain on the web at http://www.derekfountain.org/

Derek Fountain <nomail@hursley.ibm.com> wrote:
> The server is running the Drupal CMS which holds a table called 'cache',
> containing cached web pages which is delivers as required. The problem

> So, is there a way of limiting which tables or updates get binary
> logged? I see it can be done at the DB level. Alternatively, can I
> somehow selectively trim updates to a given table from a binary log?

If you can split the cache table out to another database, you can
explicitly disable binary logging for that database:

binlog-ignore-db=db_name

Although, this only affects default database queries so if a statement
identifies the cache table using the database name (ie. UPDATE
drupalcache.cache SET blah=blah) then that will still get logged.
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

Another option is to post-process the binary logs using mysqlbinlog,
converting the binlogs into SQL statements (much like mysqldump) and
then parse out the statements affecting the cache table. Then you can
tar/gzip those up and ship them across.
--
--Jeff

"I am not available for comment"