0

We currently have MySQL configured to support fail-over : Site1 <----> Site2 . While they are setup as master/master. Only one site is actively being written to by the app servers at a given point in time. We want to setup a new fail-over site. We would then have Site1<--->Site2<--->Site3. A three-way replication. Again, only one of the sites would actively be writing to the db. The other two would just be standbys. Currently Site1 and Site2 are running slightly older 5.5.29 but we'd like to use 5.6.20 on the new Site3. Eventually we'll upgrade to 5.6.20 on the others but we have to run things this way for now.

Is this possible? Any suggestions or gotchas to look out for?

thanks in advance

itgeek
  • 1
  • 2
    Why use a master-master or master-master-master configuration if you are not writing to all database servers? You can use master-slave with failover option to switch masters. – Mike Brant Oct 01 '14 at 17:48
  • because the bosses want failover to be automatic (I inherited this setup). We just change DNS entries to affect failover. – itgeek Oct 01 '14 at 18:02
  • But wouldn't your bosses think it was awesome if your SIMPLIFIED the setup rather than making it more complex? – Mike Brant Oct 01 '14 at 18:09
  • I can try to make that argument. But they are reticent to make changes from the genius that set this up. Is what I've described possible? I know I have to set some variables in the new failover(5.6.20) [binlog_checksum=NONE , binlog_row_image=FULL, binlog_rows_query_log_events=OFF,log_bin_use_v1_rows_events=1 and gtid_mode=OFF ] – itgeek Oct 01 '14 at 18:17
  • Do you really need that type of availability? You might recommend they lookinto AWS RDS service or something like that so you don't have to maintain this infrastructure. An n+2 redundancy configuration (where n = 1) seems a bit crazy honestly - like basically throwing away money and development effort. – Mike Brant Oct 01 '14 at 18:23
  • I agree, unfortunately its above my pay-grade to do anything more than voice a concern. The big question is still can I get it to work and what issues am I likely to run into? – itgeek Oct 01 '14 at 18:26

1 Answers1

1

Let's say you have servers "1" and "2" with master-master replication and willing to add server "3". Then you could make a circle of replication: configure your new server "3" to replicate from "1" (so both "2" and "3" replicate from "1"). Then point "2" to replicate from "3" instead of "1".

PS: Also to achieve HA without DNS entries manipulation I'd suggest to use keepalived (if possible in your environment), which will give your same failover and transparency for clients (they would see single virtual IP).

Sergio
  • 1,040
  • 13
  • 14