Hi, what is the generally accepted method for handing a website database that is spread across two machines?

Let's say there is www.site.com and resources.site.com - both hold stuff for the site.

Let's sey both need to access the same MySQL database:

1) Do you put the database on one machine then get the second to query the first?

2) Do you have the database on both machines and then just mirror all new entries into the database (A tells B when something new arrives, and B tells A when something new arrives)

3) Or is there some server technology that is already in place that makes this transparent that a newb like me doesn't know about?

I am asking this question, so that when my site gets huge and I need to move to more than one machine, I am prepared.

JM

ps - is there a "scaling your website" tutorial I should read?

You can open up connections on remote machines, so it isn't a problem.

http://php.net/mysql_connect
resource mysql_connect ( [string server [, string username [, string password [, bool new_link [, int client_flags]]]]] )

Just change the usual 'localhost' to whatever server the database is hosted on.

So then I can also do Lock table and Delete and Insert too right?

Ohhh, I am shivering!!! I like it...much easier than I thought.

But then another question. Before it gets on two machines, should I code as if it already is on the other machine?

Example, one machine, same database, should I change localhost to the same domain name, such that a scale up is automatic?

Is there an optimization penalty for connecting to "another machine" that is actually on the same machine, rather than "localhost".

Do you see what I mean?

JM

This will depend on if you want the database to be updated from both servers. Choose your scenario first and then it will be easier to work out a solution.

Scenario 1 - Database updatable on both. This is where either server will be able to insert, update or delete records

Scenario 2 - Database updatable on only one master server. One server will be able to perform update functions whilst the other server is just in 'browsable' mode - i.e. can only read records.

Scenario 3 - Database updated manually and both servers to be browsable only. In otherwords, your database may be updated remotely by FTP or some other offline database connection, and the users of the database can only browse the data with no facility to insert, update, delete etc.

For S1 you are better off putting the database on the most reliable of the two servers. Continue to access the database from server A, and as the previous poster says, just change the localhost to the IP of the database server and access the data remotely. Note that some mysql installs have a security feature where the database can only be acccessed from the localhost machine. This is to stop cross site attacks on the server - all non remote users should turn this feature on, you will need to turn it off.

S2 is a better option for speed and gives you a bit of database redundancy. Server 1 will be able to update the server, Server 2 will be able to browse only. In this case it's best to synchronise the database so that any changes on server 1 are passed (delayed in time) to server 2. All browsers on server 2 will use the local database faster and if anything goes wrong with server 1 database you have a live backup (although some transactions will be missed depending on the time delay in updating).

S3 is similar to 2 where you just have a replicated database on the second server. Again, speed and redundancy are the benefits here but you will need to sync both databases.

Oh, I definately need the database to be updated and instered to from ALL machines....

S1 is your answer then.

Put the database on the most reliable server and just remote connect to that server for your queries.

Then another question. I won't need the answer to this for quiet sometime.

"How does Yahoo do it with a billion members?" They can't just have one big server for the database queries can they? Or can they?...with a thousand other servers that hold the static data/images?