Simple binlog analyzer (usage per DB and table) `mysqlbinlog mysql-bin.* | binloganal`

binloganal

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import locale
import codecs
import operator
import sys

from re import match
from pprint import pprint

TOTAL = "__MY_ANAL_BINLOG_TOTAL__"
DB_TOTAL = "__MY_ANAL_BINLOG_DB_TOTAL__"
OTHER = "__MY_ANAL_BINLOG_OHTER__"

def MB(byytes):
    return byytes / 1024 /1024.0

def pybinloganal():

    expressions = {
        "INSERT": r"^INSERT [A-Z ]*(?P<table>[\w_]+)",
        "DELETE": r"^DELETE [A-Z ]*FROM (?P<table>[\w_]+)",
        "UPDATE": r"^UPDATE [A-Z ]*(?P<table>[\w_]+) SET",
        "REPLACE": r"^REPLACE [A-Z ]*(?P<table>[\w_]+)",
    }

    total = 0
    binlog_usage = {}

    use = OTHER
    for line in sys.stdin.buffer:

        line = line.decode('utf-8', 'replace')
        use_line_regex = r"^use `(?P<database>[\w_]+)`/\*!\*/;$"
        use_line = match(use_line_regex, line)
        if use_line:
            use = use_line.group('database')
            continue

        binlog_usage.setdefault(use, { DB_TOTAL: 0 , "tables": {}, "op": {} } )

        for expression, regex in expressions.items():
            exp_line = match(regex, line)
            if exp_line:
                table = exp_line.group('table')

                binlog_usage[use]["op"].setdefault(expression, 0)
                binlog_usage[use]["op"][expression] += len(line)

                binlog_usage[use]["tables"].setdefault(table, 0)
                binlog_usage[use]["tables"][table] += len(line)
                break

        binlog_usage[use][DB_TOTAL] += len(line)
        total += len(line)

    sorted_usage = sorted(binlog_usage.items(), key=lambda x: x[1][DB_TOTAL])

    for database, usage in sorted_usage:
        if database == OTHER:
            continue

        print("============ {} ============ ".format(database))

        sorted_table_usage = sorted(usage["tables"].items(), key=lambda x: x[1])
        for table, table_usage in sorted_table_usage:
            print("{}: {:.2f}MB".format(table, MB(table_usage)))

        print("------------------------------------")
        print("{} total: {:.2f}MB\n".format(database, MB(usage[DB_TOTAL])))

    print("Total: {:.2f}MB".format( MB(total) ))

if __name__ == "__main__":
    pybinloganal()