maria-discuss team mailing list archive

[Weldon Whipple <weldon@xxxxxxxxxxx>]

Henrik,

Thanks for your response! (See my replies below.)

On Wed, Sep 15, 2010 at 2:33 PM, Henrik Ingo <henrik.ingo@xxxxxxxxxxxxx> wrote:
> 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.

I'm replying leaving all your addressees in my reply to this note. (I
hope there aren't too many duplicates!)

<snip/>

> Do I understand correctly, that in the normal case you do not run with
> binlog on at all?

Yes. (We have tried it a few times in the past for load balancing,
etc., but aren't using it present.)

> You would only turn on this kind of per-database binlog when needed?

Yes--when we want to migrate a user's databases from one server to another.

> 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)

That is almost (precisely) what we want! I have actually written some
local hacks where my.cnf and command-line options are also settable as
system variables. I could probably do that easily.

The only difference is that we want multiple output binlog files--one
per database.

It is common for our users (who are serviced on 100s or 1000s of
physical servers in our data centers) to want/need to migrate to a
different physical server--to any one of the other servers in our data
centers. In that case, it seems to make the most sense to have
separate binlog files, which we can copy to the destination servers if
needed. If--in the period between dumping the databases on the source
server and importing it onto the new server (and just before going
online on the new server), we see that the binlog coordinates for a
given database have changed on the source server, we can copy the
binlog file(s) from the source to the destination server and run
mysqlbinlog (specifying the same coordinates displayed before the
initial dump) to bring the database up-to-date, then bring the
customer online on the new server (and offline on the old server.

On a given day, different customers on one server will often migrate
to many different servers. It would be impractical to have many slave
servers listening for changes on that one "source" server. Also, any
one destination server might need to listen to several source masters
at any given time. (This could lead to a combinatoric explosion--or at
least a difficult situation for administrators/migration tools.)

I'm jumping to a later line in your reply--the one about the
mysqlbinlog command. Because of the large number of combinations of
migration sources and destinations, we are looking at using the
mysqlbinlog command on the destination servers: If a second check of
the source server's single-database-binlog coordinates
(filename/offset) shows a change, we will copy what binlog file(s) are
necessary from the old to the new server and run mysqlbinlog--just
before "flipping the switch" to bring the customer online on the new
server/offline at the old server.

Perhaps a different approach might be to modify (if enabled by some
option) the binlog-do-db behavior (if controlled by a global variable)
to open a database-specific binlog file.

I've noticed in a later post by Sergei that "FLUSH TABLES WITH READ
LOCK FOR DATABASE dbname" would be difficult to implement. If we
decide to skip that command (in favor of a global version?) in our
first iteration, we would be left with:

START, SHOW coordinates, STOP--all for a single database--to a single
binlog file (set) and index.

I'm guessing that "UNLOCK DATABASE dbname for BINLOG" would be as
difficult as the FLUSH/LOCK command ...

If that is the case: What is the overhead/time involved for a mysqld
with thousands of databases to run
the global "FLUSH TABLES WITH READ LOCK" and "UNLOCK TABLES" whenever
we want to dump a database on the source server before copying (scp)
the data to the new server ...?

> 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

I will definitely look at the above links.

> 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.

I will read about mysqlbinlog from your slides!

>
> henrik

If I'm overlooking some brain-dead easy solution to this migration
requirement, feel free to enlighten me.!!

Thanks again!

Weldon