Using Capistrano To Run mySql Command

The mysql command line client has a few different methods of providing queries on the command line. It can accept them with the --execute/-e flag like:

mysql --execute 'SELECT * FROM tbl' --user=user dbname

Or it can accept them from STDIN by pipe or file redirection:

echo 'SELECT * FROM tbl' | mysql --user=user dbname
# Or:
mysql --user=user dbname < file_containing_queries.sql

For your application in Capistrano, executing them via --execute should work well. You may delimit multiple statements with semicolons, but be sure to properly quote everything. The full set of SQL statements should be one quoted string.

# In a Capistrano task:
execute "mysql -e'SELECT * FROM tbl1; SELECT * FROM tbl2;' -u <username> -p <password> -h <server ip> <database name>"

This may be a good use of Ruby's alternative %q/%Q quoting to avoid issues quoting string literals inside the SQL statements:

execute %q{mysql -e"SELECT * FROM tbl1 WHERE col='value'; SELECT * FROM tbl2 WHERE col='value';" -u user -ppasswd -hhost dbname}

When launching the mysql client via SSH in Capistrano, it appears that the client is loaded on the server and accepting statements, but feeding them interactively would be considerably more difficult. MySQL provides --execute and STDIN options for this reason, so it would be best to use them.