The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database replication across the net?

I've got a main database which resides at our web host. I want to setup a couple of other file and database ( MySQL 5 ) servers to spread space requirements from our webhost. This is a very low budget system, otherwise, i'd have everything hosted at the same place.

My question is, how would i go about replicating the main database on the other ones easily across the net? Can something be done with the built-in MySQL replication features? Or am i looking at something that needs to be done in php?

Any suggestions welcome!

Guillaume Drolet-Paré Send private email
Wednesday, November 08, 2006
Keep in mind that "replication" is not synonymous with "load balancing", though you could use replicated nodes for read-only queries, to reduce the load on your DB master node.

The built-in mysql replication feature doesn't include any load-balancing functionality, so you'll have to implement it yourself in php.
BenjiSmith Send private email
Wednesday, November 08, 2006
My aim is not to balance the load on the db server. Just to keep a local copy of the main database on each additional server, for them to use locally.

I just want to know if it's possible/safe to do replication over the net.

The data isn't very sensitive, no customer credit cards or anything like that.
Guillaume Drolet-Paré Send private email
Wednesday, November 08, 2006
In theory, there are no problems with replicating databases using the Internet as the medium.

However, be aware that MySQL silently fails in some circumstances. Err... I vaguely remember a case where if it's asked to do an inappropriate data conversion, it will truncate that data to fit as opposed to aborting and reporting the error. I couldn't find it in a quick Google search, but if you're really curious, I can look further.

More to the point, you need to come up with some independent and objective mechanism to ensure that the database is indeed accurately and completely replicated. I wanted to emphasize this point because data does get lost on the 'net. I doubt MySQL has automatic checksums because it's expected you'd replicate over the local area network (which is more "reliable") and you'd do backup/restores if you were moving lots of data between networks.

I could be wrong.
Wednesday, November 08, 2006
Since there's no easy way of making sure the data gets there completely, i think i'll try replicating ( no pun intended ) the functionnality with some PHP. Maybe as a of web service since PHP5 has builtin SOAP functions.

Thanks for your input!
Guillaume Drolet-Paré Send private email
Wednesday, November 08, 2006
Hang on a second...

You're going to re-write MySQL's replication functionality in PHP? I thought you were just going to use PHP to perform distributed load balancing.

Let me suggest that rewriting MySQL replication in PHP is a tremendously bad idea.

The existing MySQL replication functionality is *designed* to work across networks (including the internet) and it even works across *disconnected* networks (synchronising the master/slave nodes whenever the slave computer re-connects to the network and requests the master node's binary log).

Here are the basics:
BenjiSmith Send private email
Thursday, November 09, 2006
This is one case where I may have to disagree with BenjiSmith. I'm not saying that he is necessarily wrong. But I will add a slightly different perspective.

Keep in mind that DBMS vendors write massive replication schemes to handle all sorts of situations. They expect that you will install and monitor them and have a DBA on hand to fix issues when they arise. The amount of code is astronomical and these systems are usually very difficult to configure and control remotely. For this reason I would never try to completely duplicate MySQL's replication functionality using PHP. But that probably isn't required anyway.

What you probably want to do is look at the minimum functionality that you need. You probably actually don't need two way replication on all tables. You may not actually need true "replication" at all. I've seen many instances where people tried using 3rd party replication systems when all they really needed was to post a copy of something into a message queue and forget about it. Keep it simple if possible. Decide what needs to be pushed to other servers and come up with a gameplan for what happens if the connection is down. Once you've done that you can contrast your design/requirements against using a full blown replication system. You may find that pushing a small amount of data through a web service will suffice. Then again, maybe you won't.
Thursday, November 09, 2006
I'm obviously going to do some testing before i decide which direction i take. My needs are relatively simple: keep a few tables data in sync.

The reason i was considering doing this in PHP is that i want to make sure the replications happens correctly. From what i was hearing, i cannot do this with the built in replication features.

I'm also not sure i want to open ports on my firewalls just for this. With php, i dont have to do anything, and i can setup some form of validation.
Guillaume Drolet-Paré Send private email
Thursday, November 09, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz