Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Script to transform Amazon RDS slow log table into the MySQL slow query log format
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
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. "")
Run this script by:
python /path/to/ > /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:
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']
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment