We have a MySQL server at one location, replicating to another in a Master->Slave configuration. There are about 5-7 different databases on this server. We are wanting to do this...

We have a second site that we are wanting to move all our web servers to for HA, that are MySQL driven. Unfortunately we cannot move these all at once and are wanting to setup replication over our WAN link from one MySQL server to the other. Since we will be slowly transitioning web servers over to this second site, I need to slowly break off databases from the existing environment to the second site for production use. My question is, can I replicate the db's over the wire, move the web servers, point the new server to the new MySQL and promote that individual db that it needs and have it write back to original server?

Can I simply setup a Master-Master replication that the web servers are only writing to one or the other without having the auto-increment issue as described in other places here as being a concern?

Hopefully that makes sense. The web apps will only ever write to one or the other server, and not both. Is this possible?

Happy to clarify as needed.


As for the auto increment issue the obvious fix is to use offsets.

Server 1:

auto-increment-increment = 2
auto-increment-offset = 1

Server 2:

auto-increment-increment = 2
auto-increment-offset = 2

Simply undo this on the remaining server once you are done.


You can tell your slave server to only apply replication statements for specific databases thusly:



http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_replicate-do-db -- then you can just cutover your app whenever you're ready.

As for a master-master setup, it comes with some caveats, but it's totally doable. Depending on the latency/throughput between sites, you could use Percona's clustered XtraDB/galeria magic dust to ensure synchronous replication, which will get around any of the usual master-master wonkyness http://www.percona.com/software/percona-xtradb-cluster -- though like I said, there are some caveats; e.g: your code can't issue GET_LOCK

  • So I have Master -> Slave replication setup with the Master at Site A and Slave at Site B. I am setting up new web servers that use MySQL at Site B. Can I use select db's as the 'Master' and replicate them back to the other 'Master' in a slave setup? The web apps will only write to one MySQL server at a time so I'm not sure I need the auto-increment as others have mentioned. I've not had much luck finding information on what I am trying to accomplish on the web and wondered if anyone else has experience in this. Thanks. – C. Baker Oct 29 '12 at 16:58

CMIIW just additional comment

if you're using HAProxy for MySQL Master-Master, you can use the below sample setting (i use haproxy 1.4), all default connection will be forwarded to mysql1 else if mysql1 down then mysql2 will be serve (Active - Standby), and it will failback to mysql1 if mysql1 is up.

  listen www_website_com_3306 www.website.com:3306
      mode tcp
      balance roundrobin
      option tcplog
      option mysql-check user haproxy
      server mysql1 mysql1:3306 check inter 2s rise 2 fall 2 maxconn 50
      server mysql2 mysql2:3306 check **backup** inter 2s rise 2 fall 2 maxconn 50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.