public
Last active — forked from memonic/cronjob.sh

Script to transform Amazon RDS slow log table into the MySQL slow query log format

  • Download Gist
slow_query_log_dump.py
Python
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
"""
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']

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.