List:Internals« Previous MessageNext Message »
From:Sergei Golubchik Date:September 15 2010 7:53pm
Subject:Re: Per-db binlogging
View as plain text  
Hi, Weldon!

On Sep 13, Weldon Whipple wrote:
> Greetings!
> 
> My employer has asked me to implement per-DB binlogging for
> MySQL. I've been working on prototypes for 3-4 weeks now, and decided
> it's time to post to this list to ask for comments, suggestions,
> condolences, etc. (I've been a "lurker" for quite awhile. I hope this
> post isn't too far off the mark) The assignment is *only* for master-side
> binlogging. (It doesn't require a slave/replication-side implementation.)
... 
> Is it plausible to identify all places where a binlog addition might
> occur? In those places, if the singleton USER_BIN_LOG_MRG exists, ask
> it if the current DB is being binlogged. If so, send the request to
> the DB_BIN_LOG instance.

By "addition" you mean when an event is written to binlog?
See where mysql_bin_log.write is called.

> Questions I Have.
> ----------------
> 
> 1. I notice that binlog is in the list of engines and plugins
>   (displayed by SHOW ...). The bottom of log.cc has
>   "mysql_declare_plugin(binlog)". Do I need one for db_plugin?
>   (probably not?)

depends.
binlog needs to be declated a plugin, to be able to pretent being a
storage engine, to be able to force two phase commit (2PC).

2PC is used to commit or rollback a transaction atomically in more than
one engine. And MySQL only uses 2PC when a transaction spans more than
one engine. But we want a transaction to be committed in the engine and
written to a binlog - and it should be done atomically too. So, binlog
pretends to be an engine, pretends to participate in a transaction,
which forces MySQL to use 2PC which can guarantee that a transaction
is either committed and written to a binlog - or not committed and not
written. Reliably, even if MySQL crashes in the middle.

So, if you don't use innodb, or don't use
innodb_flush_log_at_trx_commit=1 or don't use sync_binlog=1
you probably don't need to support 2PC as your setup is not crash proof
anyway.

If you care about innodb<->binlog consistency in the presence of
crashes, you should support 2PC in your binlog, as explained above.

> 2. I've diagrammed the binary log event taxonomy--the 24 classes that
>   inherit from Log_event (in log_event.cc/h). That was my main
>   motivation for wanting to inherit from MYSQL_BIN_LOG.

Was that a question? :)

> 3. On the other hand, I wonder if inheriting from MYSQL_BIN_LOG might
>    be overkill for "master only" binlogging--without the
>    slave/replication part of it.

No, I think inheriting from MYSQL_BIN_LOG is correct.
At least it's much easier to build on existing binlogging than to
implement everything from scratch.

> 4. What version should I be working on? (We're currently running
>    5.1.47.) What should I check out of bzr/launchpad? (Is that still
>    what we're doing with the changes in ownership?)

What version you plan to use?
If it'll be 5.1 - you have to work in 5.1, use lp:mysql-server/5.1
branch:

  bzr branch lp:mysql-server/5.1

> 5. I see that THD is involved in binlogging. (Several THD methods are
>    defined in log.cc.) Are there any caveats here?

There is a binlog cache (or buffer) in THD that stores binlog events for
the current transaction. They all are written to disk together, when a
transaction is committed. You need to decide what you want to do when
the current database is changed in the middle of a transaction - what
binlog the transaction should be written to.

> 6. What files should I use? A new h/cc pair? ... or just add to the
>    hierarchy in log.h/cc?

As you like.
New pair of files is cleaner. But it may require you to move some
declarations from log.cc to a header file, or to make some static
functions extern.
 
> What I've Implemented So Far (about 13 Prototypes).
> --------------------------------------------------
> 
> (PRELIMINARY NOTE:)
> 
> Our initial plan was to call it "Per-User Binlogging". The user here
> is a cPanel [control panel] user. cPanel's database naming convention
> is to have all of a user's database names start with the username,
> followed by an underscore, followed by a distinctive database
> name. Thus all the commands above start with
> 
> <verb> USER ...
> 
> Originally most of them could end either "DATABASE <db>" or "USER
> <user>" (with user meaning cPanel user). Thus, in the original plan
> the first command above was:
> 
> START USER BINLOG FOR (USER <user> | DATABASE <db>)
> 
> We have realized that per-database binlogging is all we really need.
> We imagine that others in our situation might want something similar.
> Since not everyone uses cPanel, we should forget per[cPanel]-user
> binlogging, focusing on per-database binlogging.

If every user is a different MySQL account - that it a different row in
the mysql.user table - than per-user binlogging is much cleaner and
better defined concept.

A statement can affect more than one database (say, UPDATE db1.t1,
db2.t2 SET ...), the current database may be changed in the middle of
transaction, and so on. On the other hand, any statement or a
transaction is always executed in the context of as single user - you
cannot change user in the middle of transaction. (*)

(*) changing security context only affect privileges, but not the
destination binlog.
 
Regards,
Sergei
Thread
Per-db binloggingWeldon Whipple13 Sep
  • Re: Per-db binloggingMARK CALLAGHAN14 Sep
    • Re: Per-db binloggingWeldon Whipple14 Sep
      • Re: Per-db binloggingMARK CALLAGHAN25 Sep
        • Re: Per-db binloggingWeldon Whipple25 Sep
          • Re: Per-db binloggingMARK CALLAGHAN25 Sep
    • Re: [Maria-discuss] Per-db binloggingHenrik Ingo15 Sep
      • Re: [Maria-discuss] Per-db binloggingWeldon Whipple16 Sep
  • Re: Per-db binloggingSergei Golubchik15 Sep
    • Re: Per-db binloggingWeldon Whipple16 Sep
      • Re: Per-db binloggingSergei Golubchik16 Sep
        • Re: Per-db binloggingWeldon Whipple16 Sep
          • Re: Per-db binloggingSergei Golubchik17 Sep
          • Re: Per-db binloggingWeldon Whipple14 Dec