Re: [Maria-developers] [Maria-discuss] Per-db binlogging

On Tue, Sep 14, 2010 at 3:02 AM, MARK CALLAGHAN wrote:

+maria-discuss as they are working in replication too

To be exact, maria-developers@ is the counterpart to internals@mysql, although everyone should be reading maria-discuss@ too. I'm now leaving both in CC.

On Mon, Sep 13, 2010 at 1:52 PM, 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.)

Why I Want to Do It. -------------------

I work for an ISP that hosts (last I heard--I HOPE I'm not lying) over 2 million domains. Customers are allowed to have (almost) unlimited MySQL databases. Migrating an account from one physical server to another is a daunting task. (A typical server has easily 2000+ [sometimes far more] databases.) Global mysqld binlogging isn't feasible when we want to migrate (for example) a single account to another server.

Our proposed scenario goes something like this: <cut>

Do I understand correctly, that in the normal case you do not run with binlog on at all? You would only turn on this kind of per-database binlog when needed? If that would be the case, then I don't see why --binlog-do-db wouldn't already do exactly what you want, except that you'd require a restart to change it. So all you need to implement would be the ability to set it as a system variable and not just in the conf file. (http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html#o...) Alternatively, if you are also running a "global" binlog, then as Mark suggested, don't write additional per database binlogs, rather get your information from the "global" binlog and filter out what should be replicated instead by using --replicate-do-db. Again, it is not currently possible to set this on/off without restart. (http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option...) Finally, for anyone wishing to implement their own binlogging and replication, I should advertise our work in MariaDB on a generic "replication plugin API". Using this you could implement your own replication plugin that would do precisely what you want, but you could still leave the original replication code untouched. (You could of course "fork" it to make your own plugin if you wanted.) Kristian Nielsen in CC is working on that, and you can read about it here: http://askmonty.org/wiki/ReplicationProject http://askmonty.org/worklog/?tid=107 http://askmonty.org/worklog/?tid=120 henrik PS: It is not exactly the same use case, but also see mine and Igor's presentation from last years MySQL user conference. http://assets.en.oreilly.com/1/event/36/Valuable%20MariaDB%20Features%20That... ...from slide 10 onwards. Summary: You can use mysqlbinlog instead of replication and this allows all kinds of scriptable hacks to occur between master and slave. henrik -- henrik.ingo@avoinelama.fi +358-40-5697354 www.openlife.cc