CODEWITHSUNDEEP
mysqlcapistrano
Edd Aslin
Edd Aslin

Reputation: 294

Using Capistrano To Run mySql Command

I am trying to use capistrano to run a mysql command.

In my local termainal if i run 

mysql -u <username> -p <password> -h <server ip> <database name>

It connects to mysql and is ready for commands.

If however i run this in a capistrano task....

execute "mysql -u <username> -p <password> -h <server ip> <database name>"

If runs without failing and just hangs there. Does this mean it is connected and waiting for a command? If so how would i provide the command?

Upvotes: 1

Views: 870

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270657

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.

Upvotes: 1

Related Questions