Skip to content

Instantly share code, notes, and snippets.

@jalaziz
Forked from diafygi/slow_query_log_dump.py
Created May 16, 2013 22:49

Revisions

  1. @diafygi diafygi revised this gist Dec 16, 2011. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion slow_query_log_dump.py
    Original file line number Diff line number Diff line change
    @@ -8,7 +8,7 @@
    <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 command by:
    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)
  2. @diafygi diafygi revised this gist Dec 16, 2011. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion slow_query_log_dump.py
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,7 @@
    """
    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")
    @@ -15,7 +16,7 @@
    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 ''
    mysql -u'<root_user>' -p'<root_pass>' -h <host_domain> mysql -e 'CALL rds_rotate_slow_log'
    """

  3. @diafygi diafygi revised this gist Dec 16, 2011. 1 changed file with 9 additions and 1 deletion.
    10 changes: 9 additions & 1 deletion slow_query_log_dump.py
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,6 @@
    """
    Queries the slowlog database table maintained by Amazon RDS and outputs it in
    the normal MySQL slow log text format.
    the normal MySQL slow log text format. Modified version of the script by
    Things to change in this script for your own setup:
    <root_user> to your mysql root user (e.g. "root")
    @@ -9,6 +9,14 @@
    Run this command 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 ''
    """

    import _mysql
  4. @diafygi diafygi revised this gist Dec 16, 2011. 2 changed files with 13 additions and 13 deletions.
    11 changes: 0 additions & 11 deletions cronjob.sh
    Original file line number Diff line number Diff line change
    @@ -1,11 +0,0 @@
    #!/bin/bash
    (/usr/local/bin/db2log | \
    mk-query-digest --fingerprints \
    --filter '$event->{user} !~ m/^(bi|memonic)$/') 2>&1 | \
    mail -s "MySQL slow logs" root

    # Rotate slow logs. Will move them into the backup table slow_log_backup. If
    # that table exists it's overwritten with the primary slow log.
    # So with this strategy we can still access yesterday's slow log by querying
    # slow_log_backup.
    mysql mysql -e 'CALL rds_rotate_slow_log'
    15 changes: 13 additions & 2 deletions db2log.py → slow_query_log_dump.py
    Original file line number Diff line number Diff line change
    @@ -1,12 +1,23 @@
    #!/usr/bin/env python
    """
    Queries the slowlog database table maintained by Amazon RDS and outputs it in
    the normal MySQL slow log text format.
    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 command by:
    python /path/to/slow_query_log_dump.py > /path/to/slow_query_dump.log
    """

    import _mysql

    db = _mysql.connect(db="mysql", read_default_file="/root/.my.cnf")
    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()

  5. memonic revised this gist Dec 15, 2011. 1 changed file with 11 additions and 0 deletions.
    11 changes: 11 additions & 0 deletions cronjob.sh
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,11 @@
    #!/bin/bash
    (/usr/local/bin/db2log | \
    mk-query-digest --fingerprints \
    --filter '$event->{user} !~ m/^(bi|memonic)$/') 2>&1 | \
    mail -s "MySQL slow logs" root

    # Rotate slow logs. Will move them into the backup table slow_log_backup. If
    # that table exists it's overwritten with the primary slow log.
    # So with this strategy we can still access yesterday's slow log by querying
    # slow_log_backup.
    mysql mysql -e 'CALL rds_rotate_slow_log'
  6. memonic created this gist Dec 15, 2011.
    46 changes: 46 additions & 0 deletions db2log.py
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,46 @@
    #!/usr/bin/env python
    """
    Queries the slowlog database table maintained by Amazon RDS and outputs it in
    the normal MySQL slow log text format.
    """

    import _mysql

    db = _mysql.connect(db="mysql", read_default_file="/root/.my.cnf")
    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']