I often hear people making statements such as "our MySQL server machine failed", which gives me the impression that they dedicate a single machine as their MySQL server (I guess they just install the OS and only MySQL on it). As a developer not a sysadmin, I'm used to MySQL being installed as part of a LAMP stack together with the web server and the PHP.
Can someone explain to me:
- what's the point of installing MySQL on a separate server? sounds like a waste of resources when I can add the entire lamp stack there and additional servers as well.
- if the database is on a separate machine, how do the apps that need to use connect to it?
When your application platform and your database are competing for resources, that's usually the first indication that you're ready for a dedicated database server.
Secondly, high-availability: setting up a database cluster (and usually in turn, a load-balanced Web/application server cluster).
I would also say security plays a large role in the move to separate servers as you can have different policies for network access for each server (for example, a DMZ'ed Web server with a database server on the LAN).
Access to the database server is over the network. i.e. when you're usually specifying "localhost" for your database host, you'd be specifying the host/IP address of your database server. Note: usually you need to modify the configuration of your database server to permit connections/enable listening on an interface other than the loopback interface.
A separate database server is just part of good, scalable design. This does not matter if your traffic is not very high and a single server truly is enough.
But in busier services, isolating the services from each other is a Good Thing. If someone DDoSses your web server and makes it consume all the resources, that does not bully the database server at all. In shared environments more than one web server is probably using the database server, so if the database server contains data for 50 different web sites, then only one web site going down due the DDoS is better than taking everything down.
Also from sysadmin point of view it's more clear if there are dedicated servers named sensibly, such as "mysql-01.yourcompany.com" and "webserver-01.yourcompany.com". When they get alerts, they immediately see what's going on, at least in the sense of "OK, there's something wrong with the database". I know this is a weak argument since several DNS names could point to a single server but still.
Your applications would connect to a remote database server without problems over the network. Aieeeeeee! How does your web browser connect to a remote server? It's magic! Erm.... more seriously, instead of 'localhost' you just provide the server address in $programming_language_of_your_choice and you're all set.
LAMP is the application stack, but it need not all be installed on the same host. As others have noted for performance, security or scalability purposes often these are not installed on the same host. You can also find that hardware which is optimal for one part of the architecture may not be for another.
For instance, databases are all about storage management. The faster I can get information off of the disk then the faster I can get it to the requestor. If I am sharing a disk subsystem with several other application stack members, such as a web server, the contention I face on the shared resource of the read and write heds of the disk drives can actually hinder my performance. Also, having RAM split between web server and database server on a given host may not provide a large enough resource pool for either to run in its most efficient fashion, able to cache as much information in RAM without having to go to the disk either for an image, a page, or a query result set.
Administratively there are efficiencies to be gained as well. Imagine if you run your enterprise on open source applications which leverage MySQL as a common backend. Would you really want to have database server proliferation with each app? This could be a DBA nightmare, "OK, which application uses this DB?" You would have multiple versions, mulotiple configurations of hardware/software, multiple data retention strategies. You would also likely have very diffuse administrative skills. Instead, coalesce the instances to one physical piece of hardware optimized for the role and assign dedicated resources to manage the server and its data.
MySQL queries have the potential to be very resource intensive, which can slow down your LAMP server.
When you are running a large, complicated, resource heavy website, it's wise to consider moving the database to another dedicated server. That way you have two servers, one dedicated to web and one dedicated to database crunching. This has the potential to free up resources and speed up both the website and database queries.
The web server simply needs to connect to the database server address instead of
localhostto make it's database queries.