[Pdns-users] MySQL Multi-Master configuration?

[Johannes Ernst]

Thank you! It's great to have confirmation that it actually works.

I have little experience with (wide-area, master-master) replication: how frequently do replication errors happen?

When they do, what do you do about them? Blow away one master and re-import? Or is there a better way?


On Sep 15, 2011, at 21:35, BH wrote:

> I currently do this with no problems.
> 
> My web ui of choice is powerdns-webinterface -
> http://code.google.com/p/powerdns-webinterface/.
> 
> For mySQL multi master replication I always set an auto increment offset
> on each server. This is so that any auto increment ID's don't conflict,
> especially in the event of two servers going out of sync. I have four
> mySQL servers doing circular replication using a standard mySQL config
> with these additions for replication (as well as the standard relay-log,
> log_bin etc.):
> 
> server-id=X
> auto_increment_offset=X
> auto_increment_increment=5
> slave_compressed_protocol=1
> log-slave-updates=1
> 
> I change X to 1/2/3/4 depending so that they match up, eg. Server ID 1
> has an offset of 1, server ID 2 has an offset of 2 etc.
> 
> I used the compressed protocol for bandwidth reasons and
> log-slave-updates is so that changes get replicated in a circle (you
> won't need this unless you have more than 2 masters, assuming they are
> both using each other as a master).
> 
> I assume you have set up replication before so you have that side of
> things under control, let me know if you need any hints with getting it
> working though.
> 
> Once replication is setup for mySQL you should be able to then setup the
> web interface on 1 server first, import the DB schema on that server
> which should replicate it to the other servers. From there you can then
> just drop the web interface on each server that you would like to host
> it and point it to itself for SQL and any changes should be replicated.
> Also please keep in mind, by default mySQL replication is over plain
> text. If you don't enable SSL for mySQL or use some form of tunnel you
> will be throwing (hashed) passwords around the internet which can be a
> bad thing.
> 
> As far as pitfalls go, the biggest issue I can see is replication
> stopping for some reason and the servers going out of sync. I use Nagios
> to monitor my mySQL replication to alert me to any problems - this way
> if replication stops I can find out why and fix it before anything bad
> happens. With the auto increment offset above set that gets around most
> of the problems.
> 
> I am currently hosting approximately 125,000 zones with this method and
> am yet to come across a problem.
> 
> On 16/09/2011 12:12 PM, Johannes Ernst wrote:
>> I'm thinking of setting up a multi-master MySQL backend, where the masters are geographically distributed and run a local instance of PowerDNS each.
>> 
>> The theory is that it would provide HA, and also allow more than one name server for my domain, e.g.
>> 	ns1.example.com
>> 	ns2.example.com
>> and the master-master replication would make sure they stay consistent.
>> 
>> I could put a PowerAdmin at http://ns1.example.com/ and https://ns2.example.com/, and using either would be equivalent.
>> 
>> I have not been able to find any usable information on this setup. So my question is whether this idea sounds sane, and what the obvious pitfalls might be? Experience from anybody with a setup in any way comparable would be very helpful …
>> 
>> Thanks,
>> 
>> 
>> 
>> Johannes
>> 
>> _______________________________________________
>> Pdns-users mailing list
>> Pdns-users at mailman.powerdns.com
>> http://mailman.powerdns.com/mailman/listinfo/pdns-users
> 
> _______________________________________________
> Pdns-users mailing list
> Pdns-users at mailman.powerdns.com
> http://mailman.powerdns.com/mailman/listinfo/pdns-users