A Content Delivery Network is usually used for static media. Some CDNs are Push and some are Pull, meaning that you either have to Push content to their servers and they replicate it among their own network of servers or they Pull from your site with a map that tells them that cdn.domain.com -> yourdomain.com/media/. Requests for static media are served from the CDN and pulled from the origin (your server) if they are not there.
A Pull CDN can also cache the origin (dynamic pages as well) keeping some functionality of your site alive if the origin server goes down. However, for a very interactive site, that won't be very helpful. A news site serving 5 minute old data is able to utilize a CDN to cache the origin a little better than a social media site where status updates taking 5 minutes to display could make or break you.
A CDN is usually a cache and holds very little intelligence at their edge. These are machines set up strictly to cache content and serve it quickly. While Akamai does support Edge Side Includes, getting them to actually do it is costly and somewhat difficult. Static content is generally what CDNs will cache.
Your mysql data and origin site functionality still exists in one place, but, your static content and possibly your cached pages would be served by the CDN. In order to run your site from multiple locations, you would need to run multiple servers with a 'director' that would send surfers to the closest server. You can do that with host naming, DNS Anycast or a director box. You would still need to run multiple LAMP servers in various data centers. There are hosting providers that can handle multiple location deployments and you can still use a CDN to offload your static content.