This is interesting question which I thought it would be good to write about. There are obviously benefits and drawbacks for each of methods.
Smaller applications usually start with single server which has both MySQL and Web server on it. In this case it is not usually the question but once application growths larger and you need to have multiple servers you may decide ether to grow system in MySQL+Apache pairs or split MySQL And Web Server and place them on different boxes.
Generally using separate boxes for MySQL and Web Servers is rather good practice.
It is more secure – Compromising your web server does not directly give access to your database, even though most applications have enough database access permissions to be allow intruder to trash/dump data.
It is easier to analyze – Troubleshooting bottlenecks on shared boxes is more complicated compared to systems running only MySQL or only Web server. In this case you already know who is troublemaker by simply looking at system wide stats.
Easier to maintain – Same thing if box happens to run multiple things it is harder to maintain. I would not call the difference significant in this case though.
Easier to balance – Lets say you have Web application and just added some new feature, ie chat application which increases load on your web server but does not really affect database part of load. If you can operate database farm and web server farms separately you can simply increase number of web servers.
It is less expensive – You typically want database boxes to be secure, using good hardware with ECC memory to avoid database corruption, use RAID to avoid loosing database with any hard drive loss etc. Database boxes also generally require more monitoring and maintainence such as backups so you end up using some serious hardware for this boxes to keep their
number manageable. With Web boxes it is different – you’re quite OK using crappy hardware for them as all you need is CPU power. If box starts to misbehave it is easy to shut it down without affecting site operations. Also you rarely would have data corruption due to web boxes memory failure, more likely you’ll have web server crashes and this sort of things. You can ether clone web servers from template hard drive or even have them disk less booting by NFS.
So if using dedicated boxes is so great why to think about sharing MySQL and Web server at all ? Well mostly it is for cheap guys.
In many applications you will find database servers to be IO bound so CPUs are doing virtually nothing and you’re wasting resources. This is the reason for some cheap environments to have Web servers also on database boxes, might be only handling partial load etc.
I would however only use it in last resort – placing some data crunching scripts on database server is often better use of their free CPU time.
Second thing you may feel bad about it is Web Servers memory. Getting certain amount of memory is pretty cheap, ie 4GB of memory per box costs very close to 2GB, while jump from 16GB to 32GB may be much more expensive (even in price by GB).
So you can get Web boxes with relatively plenty of memory cheap but unless you’re running 500 Apache children with mod_P (php,perl,python) per box (which is probably bad idea anyway).
The good use for such extra memory is probably caching – Web page caching, if you do not have separate layer for it, local memory or cacheing type of caching (depending on your application needs) is very good idea.
One more benefit of local access to MySQL is latency. This was problem many years ago with 10Mbit network but with 1Gbit networks being commodity these days you should not worry too much about it, unless you have each page generated by 1000+ queries, which is bad idea already.
One case I should mention when shared MySQL and Web server makes sense is Web Services architecture when you can have certain boxes providing you with some simple “Services” – these could be small enough to be single shared box (or pair of shared boxes for HA). In such cases I would think about Web Server mainly being provider of different protocol to access your data – it is typically simple and would not require much of CPU and other resources itself.
For example you can see this “Shared” kind of architecture in CNET systems using ATOMICS component to talk to MySQL over HTTP. (not what I’m great fan of this idea though).