Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Two-way MySQL replication?

Posted on 2014-02-01
6
Medium Priority
?
448 Views
Last Modified: 2014-03-20
There are two web servers: server1 and server2. Server1 is the primary and server2 is the backup / redundant box.

We have failover DNS setup through DNSMadeEasy so that if there is an issue with Server1, the DNS will re-point to server2 until server1 can come back online.

Both of these servers run a single php / mysql based website.

Here's the question: if we setup MySQL replication, will that accomplish the following:

1. If server1 goes down, server2 will show up with all the content. (Content is in the database).

2. If users make changes or upload things to server2, when server1 comes back up, will the changes replicate back to server1?

Right now, the though is to use MySQL replication to replicate the data, and rsync to sync the files.

I know this can be done. the question is, what are the specifics? And, how long would it take a competent MySQL admin to set this up?
0
Comment
Question by:DrDamnit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 35

Accepted Solution

by:
gr8gonzo earned 2000 total points
ID: 39826736
I've done this exact setup before. It has other names, too:
"Dual master replication"
"Bi-directional replication"

There are several guides, but I've used this one successfully before:
http://www.neocodesoftware.com/replication/

It can take hours to set up, depending on how many databases and how much data is being replicated and the transfer speeds between servers.

The most common question for people new to this idea is, "How will I avoid primary key collisions?" The answer is that in the ini setup, you specify ID offsets so that each server inserts different IDs. Server A might insert odd-numbered IDs, while Server B inserts even-numbered IDs.

When you get into needing 3 or more servers, it's best to start considering clusters.
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 39826792
It's a single database with 40 tables.

I am really only worried about the setup time. Replication time is dependent on network speeds available, etc... which I have little control over. The server receives relatively low traffic, but is mission critical to a client's business. So, it can't go down because "when we need it, we need it."
0
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 39826796
Last time I set it up was about 3 years ago, so I can't remember the exact time, but I remember that when everything went perfectly, it took like 15 minutes. Usually there's always some problem, so I would count on a couple hours of troubleshooting and learning time when one step doesn't produce the intended result. It's one of those precise operations, so sometimes it takes more than one try to get it right.
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 32

Author Comment

by:DrDamnit
ID: 39826797
Regarding primary key collisions: it should not make a difference for existing data (because the databases already have thousands of records. We should be able to setup the offsets for "new" records and avoid any sort of issue.

...that is, of course, unless someone modifies an old record with an existing ID. What happens then?
0
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 39826805
That's correct. The offsets only impact NEW primary key IDs. It doesn't matter if you update an existing record on either server. The idea behind the replication is that ALL changes are copied to the other server, and both are simultaneously active (so you can make changes to either one at any time). If the one server is unavailable, the other server simply remembers the last point in time in the query log (binary log) where successful replication happened. So when server B comes back up, server A is able to send all of the activity that happened while  B was down.

That said, it is sometimes a good idea to introduce an INTENTIONAL delay in replication (if it's not in that guide, just google around - there's a way to do it). This way, if you have someone accidentally drop a table or delete data that brings down Server A, you don't replicate the deletion right away to B, and you can have that short window of time (e.g. 30 minutes or an hour) to save your butt.

For my purposes, I didn't introduce a delay but simply had my server B perform hourly, incremental backups of itself with a full daily dump.
0
 
LVL 32

Author Comment

by:DrDamnit
ID: 39944297
gr8gonzo... not sure if you saw this, but insight is invited:
http://www.experts-exchange.com/Database/MySQL/Q_28393565.html
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question