The issue with plain MySQL replication (espically in master-master) is that a failed query will stop replication. A good example of this is when an record is added onto each master at the same time (within a second), say a new record which gets the same primary key and replication will stop, on both hosts. That then leaves a split brain situation when you have one master writing its own data and another writing to itself, all with identical primary keys. Absolute nightmare. This can how ever be migrated with the methods the OP mentioned (I assume - but don't have experience of).
I don't think file curroption is much of an issue with DRDB, but you can only have one host write to it at anyone time (limitation of file system used), which makes the master-slave or master-backup setup ideal when coupled with HA service management software such as Pacemaker. You then have the database files in both places without the dreaded MySQL replication stories. I think the main question lyes with if the cloud admin wants a simple redundant setup or a load balanced service - it may also we worth writing some documentation for this to help future installations, although they will have to be detailed. I have never used Percona/MariaDB, does it resolve the master-master issues I mentioned earlier? Marty On Tuesday, November 5, 2013, Adrian Lewis wrote: > Seems like the Percona solution also uses Galera for their multi-master > cluster. Starting to wonder whether to go MariaDB-Galera now. Tempted just > to leave it as master slave replicated on MySQL though. Scale really not > an issue right now. Ho hum, fun to be had if I had the time to play. > > Assuming no Galera (with either MySQL/Percona/MariaDB) and just using > Centos version of MySQL - does anyone have any input as to whether to go > for replicating between two hosts using DRBD vs native MySQL replication? > I get the impression that MySQL replication is an eventually-consistent > near-realtime kind of replication whereas DRBD can be set to be completely > synchronous replication. MySQL replication just seems a lot less fiddly > than using DRBD and DRBD would replicate file corruption that MySQL > replication would be largely safe from. > > -----Original Message----- > From: Patrick Miller [mailto:patrick.mil...@sungard.com <javascript:;>] > Sent: 05 November 2013 21:51 > To: users@cloudstack.apache.org <javascript:;> > Subject: Re: Multi-master MySQL Setup > > Take a look at the percona [1] implementation of mysql and there clustered > version. > Round robin reads and writes supported. > > 1] http://www.percona.com/ > > Patrick > > > On Tue, Nov 5, 2013 at 9:55 AM, Adrian Lewis > <adr...@alsiconsulting.co.uk <javascript:;>>wrote: > > > Hi Marty/Nux!, > > > > Thanks for the feedback - sounds like multi-master is not a good thing > > then! Load will likely be very small for at least the next 6 months > > but I figured that it was one of those things that could be set easily > > now (still setting up) that I might appreciate later. > > > > Based on both your responses, I think I'll just leave it well alone! > > Need to get to grips with pacemaker/corosync anyway for other reasons > > so I'll just try that with either DRBD replication or MySQL replication. > > > > Cheers, > > > > Adrian > > > > -----Original Message----- > > From: Marty Sweet [mailto:msweet....@gmail.com <javascript:;>] > > Sent: 05 November 2013 17:23 > > To: users@cloudstack.apache.org <javascript:;> > > Subject: Re: Multi-master MySQL Setup > > > > Others may have had more success with this but from experience of > > MySQL in multi-master setups I would avoid this entirely. > > > > A common setup is using DRDB to provide a master/slave: > > Management 1 (MySQL Master) w/ virtual IP Management 2 (MySQL Slave) > > > > HA IP Address (for agents/services requiring DB write) which is > > assigned to the master (using Pacemaker). > > > > You can then send web management client to the HA IP Address as well. > > > > It may be worth considering if you need load balancing, depending on > > your setup - what loads are you experiencing? > > > > Marty > > > > > > > > On Tue, Nov 5, 2013 at 5:13 PM, Adrian Lewis > > <adr...@alsiconsulting.co.uk <javascript:;>>wrote: > > > > > Hi All, > > > > > > > > > > > > Just wondering if anyone is using a MySQL multi-master configuration > > > with auto_increment_offset (e.g.10) and auto_increment_increment (1 > > > for server 1, 2 for server 2 etc)? Does it work? Does anyone know a > > > reason why it doesn't or wouldn't work? Is there anything from an > > > application point of view that could/would trip up CS if > > > auto_increment values are set as more than 1? > > > > > > > > > > > > Not planning on deploying multimaster just yet but if I at least > > > start with an auto_increment of 10, I'd have the option of adding a > > > second master later and being able to load-balance more effectively. > > > > > > > > > > > > Thanks in advance, > > > > > > > > > > > > Adrian > > > > > > > >
