Hi,

I have binary logging enabled for our MySQL 5.0 instance. We have
several databases in the MySQL instance. So one binary log file is
recording the transactions for all of the databases. The log gets
flushed each night, after the full backup, and the old log files are
preserved.

Is it possible to get MySQL to do a separate binary log file for each
database? I'm asking because if I need to do a point-in-time recovery
of just one database it is extremely tedious to weed through the
binary log files to pull out the database-specific transactions. It
would be much easier if each database had it's own binary log. I've
looked in the MySQL documentation but can't seem to find anything
indicating this is possible.

If it's not possible to have a separate log, can anyone suggest a
strategy for dealing with editing the binary logs if you have to do a
recovery? (I'm already using mysqlbinlog to write the binary logs
to .sql files. So it's the .sql files I'm editing, not the binary log
files themselves).

Thanks in advance for your help!

Sarah B. Anderson
The Nature Conservancy
Arlington, VA

Well ... nevermind, maybe. I just did more searching of the MySQL
documentation and found a reference to being able to use a switch with
mysqlbinlog that will just output the log entries for one database.
I'll play with that and see if it's doing what I hope it is. Thanks
anyway!

Sarah


On Jan 13, 11:40*am, Sarah <sander... (AT) tnc (DOT) org> wrote:

Sarah <sanderson (AT) tnc (DOT) org> wrote:
Beware! That option is buggy in some versions. Also it filters on
the selected database, so cross database updates (USE db1; UPDATE
db2.table1 ...) will not be correctly filtered.

The better approach to roll forward only a specific set of tables,
is to create a user with write permission only on those tables and
then use

mysqlbinlog ... | mysql -f ...

RTFM for mysql -f


XL

Beware that it is possible to use a cross-database query, and if
you do, restoring *one database* to a point-in-time doesn't make a
lot of sense.

Replication might fail if this was done and a cross-database query
appeared. Even a single-database query on a database that wasn't
the current one would likely end up in the wrong binary log.

If you don't do cross-database queries, I think mysqlbinlog's -d
option might do what you want.