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.