Capistrano: HowTo deploy MySQL database for a PHP

Question

I am developing a PHP based application and using Capistrano to deploy it to my webserver.

Until now, I was not using Databases and hence, the deploy was running all fine.

However, now I am trying to use a MySQL database as well with this application and was wondering, if there is a possibility of deploying database, as well on remote server with Capistrano - the way it is done for Rails' databases.

Regards
Nikhil Gupta

Answer 1

the whole magic of database deployment is a native functionality of RoR, you might want to mimic it to get the same results.

You will need to prepare scripts for migrating your database, so do not use one script, each change of database requires a new script. You need also to store somewhere list of the already performed migrations, rails uses database table for this, but an file might be good for this too.

You might want to try with this code:

set :mysql_params, "-u user -ppassword"
set :mysql_db_name, "database_name"

after :deploy, :migrate
desc "migrate database on server"
task :migrate do
  run "touch #{shared_path}/migration.list ;
ls -1v #{current_path}/sql/*.sql 2>/dev/null > #{shared_path}/migration.available;
diff #{shared_path}/migration.available #{shared_path}/migration.list | awk \"/^</ {print \\$2}\" | while read f ;
do echo \"migrating $(basename $f)\"; mysql #{mysql_params} #{mysql_db_name} < $f && echo $f >> #{shared_path}/migration.list ; done;
rm -f #{shared_path}/migration.available"
end

after "deploy:setup", :create_db
desc "create database on server"
task :create_db do
  run "mysql #{mysql_params} -e \"CREATE DATABASE #{mysql_db_name}\""
end

and most important to preserve order of migrations you should name your migrations with consecutive numbers or date_time, so example output of ls -1v #{current_path}/migrations/*.sql would look like:

0001_create_database.sql
0002_create_user_table.sql
0003_add_password_to_users.sql
20101205_141534_add_admin_user.sql
20110108_090712_create_post_table.sql
20110210_165609_create_comment_table.sql

the date_time entries use format YYYYmmdd_hhMMss_title.sql

Answer 2

As far as I know there are 3 fully automatic approaches to deploy database to the production server.

• Use liquibase, write (or generate) changesets, which contain your migration code.
• Use event sourcing, with a relational read cache having the same structure as normally. Send a schema dump with your releases. Purge the whole read cache including tables. Recreate the tables using the dump. Reinsert data in a single transaction using the event storage and the projections. Rollback is not so hard, you can dump your old read cache, or you can follow the same steps with the old release.
• Write your own migration script and sql for every release. Not recommended, because it is error prone.

Answer 3

I'm late to the party regarding this question but going to post anyway as this is a common question with few answers. I've had great success using Phing and Liquibase together, you can use Liquibase to roll database changes forward and backward in a highly formalized manner, and can even track your changes in version control.

I've presented on this topic several times and posted my slides (HTML format) to GitHub: https://github.com/wjgilmore/Automating-Deployments-with-Phing--Capistrano-and-Liquibase

Includes bonus material for deploying PHP websites using Capistrano. :-)