need help with server architecture

[edsuit]

I’m looking for the best way to use MySQL on our servers.

We’ve got three servers: two external webservers (servers A and B) on two different locations, and one local server (server C), which is configured as a backup webserver. Here are the specs:

Server A
Intel Xeon 2.8 GHz processor
2 GB RAM
Windows 2003
IIS 6.0
MySQL 5.0.37
ASP 3.0

Server B
Intel Pentium 4 2.4 Ghz processor
1 GB RAM
Windows 2003
IIS 6.0
MySQL 5.0.37
ASP 3.0

Server C
Intel Pentium III 733
256 MB RAM
Windows 2003
IIS 6.0
MySQL 5.0.37
ASP 3.0

Server A runs MySQL with one database, for one website, our ‘main’ website.
Server B runs MySQL with several databases, for several, smaller, websites. Even when put together they still have a lighter load than the main website on its own.
Server C does not run MySQL yet.

I would like to run live replications of both of the MySQL servers (A and B).
Besides that I also want to run a separate MySQL server, with copies of all the databases, which I can use
for testing purposes.

Now I’m not really sure what would be the best way to set this up. My initial idea was to make server B a dedicated webserver and server A a dedicated MySQL server, and then make C the testing server, but my company doesn’t want that.
What I’m thinking of now is to run three MySQL instances on server C: a server A replication, a server B replication and a testing server. But that doesn’t sound all that great to me…

Does anyone have any better ideas?

[sterin]

[B]edsuit wrote on Tue, 21 August 2007 13:43[/B]

I would like to run live replications of both of the MySQL servers (A and B).

Backup? Or what is the reason for the replication?

[B]edsuit wrote on Tue, 21 August 2007 13:43[/B]

Besides that I also want to run a separate MySQL server, with copies of all the databases, which I can use for testing purposes. Now I’m not really sure what would be the best way to set this up. My initial idea was to make server B a dedicated webserver and server A a dedicated MySQL server, and then make C the testing server, but my company doesn’t want that.

Good that they opted against it. Because since A and B are at different physical locations it is not a good idea to separate the webserver from the DB backend. You can get performance problems due to traffic between web server and DB server can be a lot of queries (latency in connection) or sometimes a lot of data (bandwidth) that is transferred.

[B]edsuit wrote on Tue, 21 August 2007 13:43[/B]

What I’m thinking of now is to run three MySQL instances on server C: a server A replication, a server B replication and a testing server. But that doesn’t sound all that great to me…

No it doesn't and the question is if the thought of replicating A and B was for backup purpose then you should have a production server for it. Not the same server that you are testing on. And besides how much mysql load do you expect from the replication and testing since your server C seems to be a very slow machine compared to the others.

[edsuit]

[B]sterin wrote on Wed, 22 August 2007 07:24[/B]

Backup? Or what is the reason for the replication?

Only for backup purposes.

[B]sterin wrote on Wed, 22 August 2007 07:24[/B]

No it doesn’t and the question is if the thought of replicating A and B was for backup purpose then you should have a production server for it. Not the same server that you are testing on. And besides how much mysql load do you expect from the replication and testing since your server C seems to be a very slow machine compared to the others.

There won’t be a lot of load from testing. It will be part of a testing environment to develop new features for our websites, and I will be the only one using it, only at certain times.
I don’t really know how much load there will be from replication… I guess it wouldn’t be much if it’s only for backup purposes. So it’s just the replication and that’s it.
The websites are all online magazines. So the ‘writeload’ mostly consists of visitor tracking and posting new articles and photo reports.

You say I should use a production server for backups. The only way I can think of to do that with our current means is,
to have server A run a separate MySQL instance, which would serve as replication for server B, and vice versa.
But I’m not too sure about that.