How Master-Master replication works

[Srini S]

Could you please explain me on how the MySQL Multi Master replication model is working or how it is different from a Master - Slave replication in terms of the latency since both are asynchronous. How in the Master Master replication the latency is less?.

Apart from the above I have other questions which will help me in deciding on a solution for our project,

1. Whether a Backup server is required in a model of Master-Master replication and when I have slaves of each master in a different machine, I totally will have 4 machines in each cluster.
2. How is the Alter table should be done in a Master-Master replication model? How do we distinguish between a destructive and non destructive schema change?

Srini

[Cheenu]

Could you please explain me on how the MySQL Multi Master replication
model is working or how it is different from a Master - Slave
replication in terms of the latency since both are asynchronous. How
in the Master Master replication the latency is less?

Apart from the above I have other questions which will help me in

[Alexey Kovyrin]

On 5/21/07, Srini S <sri...@gmail.com> wrote:
> Could you please explain me on how the MySQL Multi Master replication model
> is working or how it is different from a Master - Slave replication in terms
> of the latency since both are asynchronous. How in the Master Master
> replication the latency is less?.

With this "Master-Master" in the project name we mean Active+Passive
master scheme which is the same as Master+Slave, but one of the slaves
being set up to be spare master and be able to handle writes when
active master dies.

> Apart from the above I have other questions which will help me in deciding
> on a solution for our project,
>
> 1. Whether a Backup server is required in a model of Master-Master
> replication and when I have slaves of each master in a different machine, I
> totally will have 4 machines in each cluster.

Actually, there are two sides in this question:
1) You could live w/o backups because you have spare copy of all data
from active master and if you'll get some hardware problem on active
master, you'll switch to passive one, but...
2) In case of human/software logic/etc errors, you'll lost both of
your masters because of replication. So, it is up to you how to make
backups ;-)

> 2. How is the Alter table should be done in a Master-Master replication
> model? How do we distinguish between a destructive and non destructive
> schema change?

Something like this:
# mmm_control set_offline <your passive master>
...doing schema change on the passive master...
# mmm_control set_online <your passive master>
# mmm_control move_role writer <your passive master>
# mmm_control set_offline <your former active master>
...doing schema change on former active master...
# mmm_control set_online <your former active master>

So, major idea of this process with mmm is that you could say "some
server is offline" and it would be offloaded and be available for any
changes/backups/etc.

--
Alexey Kovyrin
http://kovyrin.info/

[Cheenu]

Thanks for your reply Alexey. I have couple of questions given below.

> With this "Master-Master" in the project name we mean Active+Passive
> master scheme which is the same as Master+Slave, but one of the slaves
> being set up to be spare master and be able to handle writes when
> active master dies.
>

Does that mean that there will be no latency difference in terms of a
Master-Master and Master-Slave (no additional priority for the passive
master to get replication updates when compared to a pure slave) ,
expect for the fact that it is Administratively easier to bring back
the failed master in Master-Master.

> Something like this:
> # mmm_control set_offline <your passive master>
> ...doing schema change on the passive master...
> # mmm_control set_online <your passive master>
> # mmm_control move_role writer <your passive master>
> # mmm_control set_offline <your former active master>
> ...doing schema change on former active master...
> # mmm_control set_online <your former active master>
>
> So, major idea of this process with mmm is that you could say "some
> server is offline" and it would be offloaded and be available for any
> changes/backups/etc.

In the above scenario how to restore the changes that occurred to the
active master when the passive master is offline. How the changes
during that period will be applied to the passive master when both are
online.

Moreover on the destructive/non destructive schema change, I assume
data type change, new column additions,modify column to be non
destructive and drop a column, table name changes to be destructive.
Would you recommend the above approach to both these cases.

>
> --
> Alexey Kovyrinhttp://kovyrin.info/