Let's say I have a traditional webapp with two servers, one web and one mysql. Using a VPS or cloud-based provider, how would I go about scaling my database? Without a traditional SAN, how would I make it so that my mysql DB knows where the datafiles are stored?
Have you considered Amazon RDS?
Amazon Relational Database Service (Amazon RDS) is a web service that makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and resizable capacity while managing time-consuming database administration tasks, freeing you up to focus on your applications and business.
Amazon RDS gives you access to the full capabilities of a familiar MySQL database. This means the code, applications, and tools you already use today with your existing MySQL databases work seamlessly with Amazon RDS...
In many cloud environments (Google App Engine, Windows Azure) you cannot install full SQL RDBMs, you must use the storage APIs the cloud vendor has set up for you.
On Amazon EC2 you get something that resembles a 'full' PC, and you install operating system and applications yourself. You must use Elastic Block Storage (EBS) if you want 'harddisk space', i.e. a storage volume that survives a node crash.
How EBS is implemented is AFAIK not publicized. Most likely it's not a SAN, but rather a software layer created by Amazon. It doesn't really matter, as far as each EC2 hosted operating system is concerned, it looks like a local harddisk. Performance isn't that great though, so most people stripe multiple EBS volumes together for speed.
As Traveling Tech Guy points out, if you don't want to muck around with EBS, then Amazon conveniently offers a managed MySQL service.
Other cloud providers may have entirely different ways of handling this, see their docs...
Your MySQL database is bare metal physical system at the moment. If you were going to mount traditional cloud storage that would be a huge bottleneck since it is not going to perform anywhere near close to what your local storage is capable of.
There are cloud providers that offer high speed storage this might be what you are looking for. If your going for high speed and dynamic storage I would recommend fully going virtual for the servers in order to reduce the latency over the different channels.
If you need some recommendations on the solutions I am more than happy to offer these available.