-
-
Save diafygi/1484489 to your computer and use it in GitHub Desktop.
Script to transform Amazon RDS slow log table into the MySQL slow query log format
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| """ | |
| 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