Dull questions for web development nerds
I’d like to take a moment, if I may, to run some work I’m doing by the web application developers out there. If you’re not one, feel free to skip this post.
I’ve got a number of different websites on the go right now, including a few in development. Most of these sites include at least one web application rooted in PHP and MySQL. Traditionally, I’ve done my building and design on the live site itself, but lately I’ve finally gotten into the much better habit of working offline and uploading the finished files afterwards. If you want to get technical, my website hosts are running a LAMP software bundle, and the offline environments I’m running at home and at the office are based on WAMP software bundles.
Here’s the main question. So far, I’ve gotten into the habit of creating and updating PHP files offline, but I haven’t moved or exported any of my live SQL databases. Back when I was doing all the work in my hosted space, I could obviously just tell my PHP files to look for a database on the localhost. But that doesn’t work when you run those files on another server, like the one I’ve set up at home.
The way I see it, I’ve got two options, and I’m currently weighing the pros and cons of each one. On the one hand, I could create local copies of all the relevant SQL databases on my development servers. If I did so, then the “localhost” reference would work in any environment. But it would mean having two copies of my databases, each of which would contain different data before too long. That seems like a giant mess in the making.
On the other hand, I could replace the “localhost” value with my server’s IP address, and configure remote access from my home and work environments. I’d have only one version of my data that way, but I’d have to go through the trouble of setting up the proper permissions. This might not even be a practical solution at home, where my ISP assigns me a new address with every new connection.
What do you think? Are there pros and cons I haven’t taken into account here? Is there a third option I’m not seeing? What’s the best way to manage all of this? It might even end up being a situation where I take one approach with some sites and the other approach with the rest, which would be a little tougher to manage, but at least I’d know I’m doing what’s best for each individual site.
If you made it all the way to the end of this post, then I congratulate you and thank you, and I’d like to know where you think I ought to go from here.
Posted in Uncategorized
You probably don’t want to use your production database for testing. You could in theory futz that up too if you are making changes to your site, or want to test posting new articles, etc. I was going to write up at some point how I back up funkaoshi.com, but basically you can use mysqldump to make a backup of your database, copy it to your local box, and then use mysql to overwrite your local copy with the exported database dump. And you can automate doing all that. On the Mac I use launchservices, on a Unix box you would use Cron, and on a Windows box there is probably something similar, some sort of scheduler.
Here is what the script I use to back up funkaoshi.com looks like. The reason I don’t have to enter passwords in that script is that I use ssh-agent to save my SSH passwords, and the database passwords are all in the mysql config file, .my.cnf. Hopefully this isn’t all wicked confusing.
Heh… Some of it’s a little over my head, but it gives me a better idea of what to think about and look for. Thanks for posting!
I’ve been trying to learn more about Cron jobs as it is, mainly because I’ve got a lot of database information that I need to export on a fairly regular basis, and I’d love to set it up so it all gets emailed to me instead. All of my personal live sites, and all of my company’s sites, are hosted in the same Linux environment, so in each case it would hopefully just be a variation of the same basic Cron job.
Thinking about that makes me realize that I’m worrying too much about having two copies of the same database. One’s a live version, the other’s a development version, and what’s the big deal? But if I set up a regular backup Cron job in each case, then that could hopefully keep me caught up on the changes with a minimum amount of work on my part.
Food for thought. Thanks, Ram!
I added more comments to that file, to break down what each step is doing.