Skip to content

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
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 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']
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.