export-slow-log-table

:
# ------------------------------------------------------------
# Export-slow-log-table
#
# You can log queries to a table instead of a file in MySQL.
# But most tools that analyze the log only work on the file type logging.
# This script connects to a MySQL instance (possibly a remote instance)
# and dumps the entries of the slow-query log table in the format of
# the regular flat-file slow-query log.  Then you can use standard tools
# such as pt-query-digest on the exported file.
#
# NOTE: Query_time in the table-log is rounded down to an integer, as
# the nearest whole second.  This reduces the precision of query times
# so you really should use file-based logging if you can.
#
# Copyright 2012 Bill Karwin.
# 
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
# 
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
# 
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
# ------------------------------------------------------------

cat <<'GO' | mysql --raw --skip-column-names --quick --silent --no-auto-rehash --compress $*
SELECT CONCAT(
'# Time: ', DATE_FORMAT(start_time, '%y%m%d %H:%i:%s'), CHAR(10),
'# User@Host: ', user_host, CHAR(10),
'# Query_time: ', TIME_TO_SEC(query_time),
'  Lock_time: ', TIME_TO_SEC(lock_time),
' Rows_sent: ', rows_sent,
'  Rows_examined: ', rows_examined, CHAR(10),
'SET timestamp=', UNIX_TIMESTAMP(start_time), ';', CHAR(10),
IF(FIND_IN_SET(sql_text, 'Sleep,Quit,Init DB,Query,Field List,Create DB,Drop DB,Refresh,Shutdown,Statistics,Processlist,Connect,Kill,Debug,Ping,Time,Delayed insert,Change user,Binlog Dump,Table Dump,Connect Out,Register Slave,Prepare,Execute,Long Data,Close stmt,Reset stmt,Set option,Fetch,Daemon,Error'),
  CONCAT('# administrator command: ', sql_text), sql_text),
';'
) AS `# slow-log`
FROM `mysql`.`slow_log`;
GO
echo "#"