PHP/Mysql Database that spans across multiple servers

[ShawnJClapper]

Ok, I think I'm really over my head on this one. I've been designing a Flash/PHP/mySQL database driven e-commerce site for the company I work for, however now I'm informed that they want to have this span across multiple servers. I can handle php/mySQL enough to get an e-commerce site up and running, but have no idea where to begin programming for it to work across multiple servers. Basically they have servers set up in different areas (and countries) and want to split the load across them. Does anyone know of any informational sites and or books that could give me an idea of what to do here?

 

[Luca200]

I'm not sure I understood the right way, but could be you need MySql clustering?

 

[ingresman]

spreading the php load is easy enough, you need load balancing hardware e.g. cisco css or you might get a software solutuion fir linux or similar. You need to be wary of maing anything "sticky" to a particular server e.g. sessions by default use files in /tmp. You will have to register a database handler to get around this. At least one faq exists on this site to show you how.
The mysql thing is more interesting. I've never come accross mysql clustering but it would be a way to do it.
I belive mysql has a replication mode which might help you out if you can stand the latency of updates.
Or if you are are just wanting to have a diufferent databasde for each geographic region you would have identical database structures around the world and get your php to choose the appropriate one based on user location.
hope this helps !

 

[ericbrunson]

You have to work around limitations of mysql replication, but this is how I do it.

Mysql can only replicate from a master to a slave, you can't have two masters replicating each other, so I designate a single server as the "write master". Since most datbase operations are lookups I put a slave on each web server replicating the master and doing all lookups from the local copy.

All changes are written to the master and then the changes replicate to each of the slaves. I enforce this by connecting locally with a user that doesn't have write permissions to the tables so I can't accidentally write to a slave.

The intelligence for all this is written into my database access objects so I never have to worry about which server I'm connected to, the objects always do the right thing.

I handle failover via IP addresses. If the master goes down I move the IP address for that service to one of the slaves and the other slaves start replicating to that one until the real master comes back up.

 

[ShawnJClapper]

Ok, thanks alot guys. I'll look into mySQL replication and once I learn how that is done Eric's ideas sound good. I'm getting a grasp on the theory of this, but not sure on the codeing I would need. All my PHP/Mysql references I have don't seem to mention this, or only vaugly go over replication. Anyone have a good book on the subject they can recommend?

 

[lgarner]

High Performance MySQL, from O'Reilly

 

[ericbrunson]

Googling for "mysql manual replication" turned up the manual page on the first hit. The second link is conveniently titled, "MySQL Manual | 6.4 How to Set Up Replication".