What is a good solution for deploying a PHP/MySQL site via FTP?

Question

I am currently working on a web application that uses PHP and MySQL, but I do not have shell access to the server (working on that problem already...). Currently, I have source control with subversion on my local computer and I have a database on the local computer that I make all changes to. Then, once I've tested all the updates on my local computer I deploy the site manually. I use filezilla to upload the updated files and then dump my local database and import it on the deployment server.

Obviously, my current solution is not anywhere near ideal. For one major thing, I need a way to avoid copying my .svn files... Does anyone know what the best solution for this particular setup would be? I've looked into Capistrano a bit and Ant, but both of those look like it would be a problem that I do not have shell access...

Answer 1

I'm using Weex to synchronize a server via FTP. Weex is basically a non-interactive FTP client that automatically uploads and deletes files/directories on the remote server. It can be configured to not upload certain paths (like SVN directories), as well as to keep certain remote paths (like Log directories).

Unfortunately I have no solution at hand to synchronize MySQL databases as well...

Maybe you could log your database changes in "SQL patch scripts" (or use complete dumps), upload those with Weex and call a remote PHP script that executes the SQL patches afterwards.

Answer 2

You can do a subversion export (rather than checkout) to different directory from your working copy, then it will remove all the .svn stuff for you

Answer 3

I use rsync in production but you could do this:

Add a config table into your site to hold what level of DB you are currently at.

During development, store each set of SQL changes into a single file (I use something like delta_X-up.sql). These will stay in your SVN as well. So, for example, if you are at delta_5 and add a table between the current release and the new release, all the SQL needed will be put in delta_6-up.sql

When it comes time to build, export the repo instead of using a checkout. This lets you ignore all the SVN cruft that comes along since you won't need that into production.

Use Weex to push those changes into production (this would be were I would use rsync but you don't have that option). Call a remote script that checks your config DB to see what delta level you are currently at, parse the directory with you delta_x-up.sql files and see if there are any new ones. If there are, read them and run the SQL inside.