MySQL Realtime Replication

oleber
oleber used Ask the Experts™
on
Hy all

My project is a normal Apache/Perl/MySQL solution. All in one machine

Now, I have money to by machines and my Client is asking me to have the DB replicated in 2 or 3 machines, and shutdown one machine shall no take the system down, or if it takes the system down, the system shall be up in less then 5 min already with a penalty.

Since I'm not an Expert in DB's I need to find some solutions or documentations on how to have this done.

thanks for the help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
theGhost_k8Database Consultant

Commented:
Having set a master-master / master-slave will do the job.
Distinguished Expert 2017

Commented:
Depending on how many machines and what a circular replication
A is master of B and slave to C
A=>B=>C=>A
There have to be scripts on each.
I.e. if C looses contact with B it has to be reconfigured to the new master A.
http://www.howtoforge.com/setting-up-master-master-replication-on-four-nodes-with-mysql-5-on-debian-etch you can use three nodes the mysql directives are the same regardless of the OS you are using.

rsync can handle the file synchronization for the web site, but subversion with puppet or puppet with subversion might be a better solution for the web site.

The perl/php need to be programmed to try have the capability to attempt multiple connections to databases. i.e. try one if one is not present try the next one etc.

 

Author

Commented:
hi arnold:

The manual seems ok, but if one of the servers goes down I will need to reconfigure the servers again by hand.

Correct?

This is work that I will test before September, but as to be finished during this time. So my answer will be given in this period time
Distinguished Expert 2017

Commented:
The transition can be programmatically handled.
I.e. each server has a script to monitor its upstream server.  If the upstream is down, it needs to check whether the upstream's upstream server is up and reconfigure itself to now reflect the "new" master. Make sure to notify yourself of the change.

You would need to make sure you have a good binary log management policy.

The other issue is that when reconfiguring to a new master, the binary_logs of the new master will be processed by the server.

You would however have to manually intervene to restore the replication setup.

This type of circular replication permits writes to any. When configuring the offset and increment, there is no way that a newly entered record on one would collide with a newly entered record on the other.

The backend database goes down, but the user facing application may not reflect that since they still can query a database server that should have the same data.

It may be more difficult to manage the node failure from the program; i.e. you would need to check whether all the server are up and synchronized before performing an insert/update/delete.
If one system has an issue, your web script/application would only issue the insert/update/delete to a functional upstream.
If the setup is A=>B=>C=>A and B fails. The insert/update/delete should only be sent to C the read queries can be done against A or C.


Author

Commented:
But, it is there some sort of layer to manage this problems.

I would like to connect to a machine, that serves as MySQL Frontend that as no data. If possible having 2 Frontends like this.

This Frontend should be able of giving the impressing that everything is ok:
  - When a machine goes down, uses the remaining servers
  - When a machine comes up, replicates the missing data

I'm lazy so I would like to configure the machines the first time and after make them work by them selfs.

I may even consider to pay something for a system that does this for me, since it will reduce allot the maintenance costs in the future.

Joking: Am I thinking in a new, maybe profitable, product?

Distinguished Expert 2017

Commented:
This is part of the programming of the perl/php web script dealing with the establishment of a connection.

When a system comes up, to synchronize back up would depend on how long it's been down.

If the system is down for 10 minutes to an hour and your binary log cleanup did not kick in during this time, the synchronization would resume and will take as long as needed.
If however the system is down for an extended period of time, the replication has to be reconfigured anew.

You can have a cluster rather than replication. When one node goes down the other takes over.  The perl/php script refer to the IP/host that is shared among the nodes.

Author

Commented:
Maybe cluster is the solution, if it does what I need.

What may you tell me about it?
Distinguished Expert 2017
Commented:

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial