I have confidential data stored in a mysql database that is served by a web server running on the same physical box. If I separate the two servers (and even put a firewall in between) how exactly have I made accessing the mysql database any harder if the web server is compromised? The web application stores the connection strings in a plain text config file. All the hacker has to do is connect to the database server using these credentials and there is no way for the DB server to know this is not the authorized client.
There is another attack method that separation defends against. If the attacker is lucky enough to get shell access to the web-server, they can then grab the database files themselves for offline analysis, or any number of other methods of grabbing data. By having the database on a separate server they're restricted to whatever they can query by way of whatever authentication tokens they can find, which may not be enough to get at the good stuff.
Their are a number of aspects to security, an important concept is prevention and detection.
The firewall is obviously preventative, however you should also have detection systems in place. With detection in place then any system that slows your hacker down becomes a time pressure on him/her. Imagine two sets of locked doors in a house and how that might put off a burglar who would simply think "I'll go somewhere easier"
Practically you could also ensure that your connection files can only be read by a relevant account, which the attacker wouldn't necessarily have if he got some sort of access on the box.
Also bear in mind that it might be your perimeter firewall that is compromised at which point with only one tier the attacker would be be able to directly query your db. With 2 tiers they have to beat another firewall.
There's a bit more to it than just separating the tiers onto different machines. You also need to limit what the web app can do with your database by connecting to it with least privilege. If you connect, say, as the database owner, then an attacker who finds a way to run arbitrary code in your web server process will be able to do anything you can do with that database.
So it's important to connect with least privilege to that remote database. If you do that, then you're getting some benefit. Of course, if you don't follow Mark's advice and implement some form of detection, you won't derive nearly as much benefit. If an attacker gains a foothold on your web server, he'll start attacking your database server from there, and if you're not aware this is happening, he may eventually find a way in. Separation slows down the attack so you have time to notice and react (e.g., patch the problem in the web server/app that allowed the exploit in the first place).