slow_query_log_dump.py

"""
Queries the slowlog database table maintained by Amazon RDS and outputs it in
the normal MySQL slow log text format. Modified version of the script by 
memonic (Thanks!) at https://gist.github.com/1481025

Things to change in this script for your own setup:
<root_user> to your mysql root user (e.g. "root")
<root_pass> to your mysql root password (e.g. "hunter2")
<host_domain> to your mysql root password (e.g. "prod-01.w3rfs2.us-east-1.rds.amazonaws.com")

Run this script by:
python /path/to/slow_query_log_dump.py > /path/to/slow_query_dump.log

Then you can run the normal mysqldumpslow parser on the output file (slow_query_dump.log)
Example (print the top 40 slow queries by time):
mysqldumpslow -t 40 -s t /path/to/slow_query_dump.log

To clear the slow_log table on the RDS run the following command:
mysql -u'<root_user>' -p'<root_pass>' -h <host_domain> mysql -e 'CALL rds_rotate_slow_log'

"""

import _mysql

root_user = "<root_user>"
root_pass = "<root_pass>"
host_domain = "<host_domain>"

db = _mysql.connect(db="mysql", host=host_domain, user=root_user, passwd=root_pass)
db.query("""SELECT * FROM slow_log ORDER BY start_time""")
r = db.use_result()

print """/usr/sbin/mysqld, Version: 5.1.49-3-log ((Debian)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
"""

while True:
    results = r.fetch_row(maxrows=100, how=1)
    if not results:
        break

    for row in results:
        row['year'] = row['start_time'][2:4]
        row['month'] = row['start_time'][5:7]
        row['day'] = row['start_time'][8:10]
        row['time'] = row['start_time'][11:]

        hours = int(row['query_time'][0:2])
        minutes = int(row['query_time'][3:5])
        seconds = int(row['query_time'][6:8])
        row['query_time_f'] = hours * 3600 + minutes * 60 + seconds

        hours = int(row['lock_time'][0:2])
        minutes = int(row['lock_time'][3:5])
        seconds = int(row['lock_time'][6:8])
        row['lock_time_f'] = hours * 3600 + minutes * 60 + seconds

        if not row['sql_text'].endswith(';'):
            row['sql_text'] += ';'

        print '# Time: {year}{month}{day} {time}'.format(**row)
        print '# User@Host: {user_host}'.format(**row)
        print '# Query_time: {query_time_f}  Lock_time: {lock_time_f} Rows_sent: {rows_sent}  Rows_examined: {rows_examined}'.format(**row)
        print 'use {db};'.format(**row)
        print row['sql_text']