CiviCRM Community Forums (archive)

There's an option you can add to civicrm.settings.php, "define( 'CIVICRM_DEBUG_LOG_QUERY', 1 );", which will append queries to the log (files/civicrm/ConfigAndLog/CiviCRM*log*) Documentation exists is but is pretty brief -- http://wiki.civicrm.org/confluence/display/CRMDOC/Debugging+for+developers#Debuggingfordevelopers-SettingswhichmodifyCiviCRMbehaviorfordebugging

This will log a lot more than you're actually interested in. (For example "SET NAMES utf8" is called a billion times.) In the past, I've used this option plus a mix of (a) grep, (b) manual editing, and (c) hacking CRM_Core_Error::debug_query() to get more focused info about the queries passing through. You should try enabling CIVICRM_DEBUG_LOG_QUERY and see if you can find a way to use it.

If doing it again (for the purpose that you describe), I'd probably make some local-hacks (or possibly nice, reusable patches)  for CRM_Core_Error::debug_query(), e.g.

 * Write queries to a different place and use a more structured format (e.g. CSV: "$fh=fopen('/tmp/query.log'...); fputscv($fh,array(...));" ).
 * Make it possible to turn the logging on+off at runtime (so that we can log queries while executing a specific API call)

Any reason you don't try the mysql log slow query option?