maria-discuss team mailing list archive

[Henrik Ingo <henrik.ingo@xxxxxxxxxxxxx>]

On Tue, Sep 14, 2010 at 3:02 AM, MARK CALLAGHAN <mdcallag@xxxxxxxxx> 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 <weldon@xxxxxxxxxxx> 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#option_mysqld_binlog-do-db)

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_mysqld_replicate-do-db)

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%20Somebody%20Paid%20For%20Presentation.pdf
...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@xxxxxxxxxxxxx
+358-40-5697354
www.openlife.cc