Check that the contents of 2 databases are identical

verify_mysql_replication.py

#!/usr/bin/env python

import sys
import ConfigParser
import MySQLdb
import click
from threading import Thread


class MySQLExtrasFileParser(ConfigParser.ConfigParser):
    def as_dict(self):
        d = dict(self._sections)
        for k in d:
            d[k] = dict(self._defaults, **d[k])
            d[k].pop('__name__', None)
        return d


def build_connection(path, section, database):
    """
    Reads an ini-style config file for connection info and created a connection to MySQL.
    """
    config = MySQLExtrasFileParser()
    config.read(path)
    section = config.as_dict()[section]
    conn_args = dict(host=section['host'], user=section['user'], passwd=section['password'], db=database)
    return MySQLdb.connect(**conn_args)


def list_all_tables(connection):
    cursor = connection.cursor()
    cursor.execute("SHOW TABLES")
    return list(x[0] for x in cursor.fetchall())


def get_table_checksums(src_conn, dest_conn, table):
    def get_checksum(connection, table, results_container, key):
        cursor = connection.cursor()
        cursor.execute("CHECKSUM TABLE " + table)
        result = cursor.fetchall()
        results_container[key] = result[0][1]

    results = {}  # Mutable container for threads to store their results in
    threads = [
        Thread(target=get_checksum, args=(src_conn, table, results, 'src')),
        Thread(target=get_checksum, args=(dest_conn, table, results, 'dest'))
    ]
    _ = [t.start() for t in threads]
    _ = [t.join() for t in threads]

    return results['src'], results['dest']


@click.command()
@click.option('--database', default='yapta')
@click.argument('source_config_file', type=click.Path(exists=True))
@click.argument('destination_config_file', type=click.Path(exists=True))
def verify_mysql_replication(database, source_config_file, destination_config_file):
    """
    Verify the contents of 2 MySQL databases are identical.
    Reads MySQL extras-style ini config files for connection information.

        Uses the 'mysqldump' section of SOURCE_CONFIG_FILE.
        Uses the 'mysql' section of DESTINATION_CONFIG_FILE.
    """
    src_conn = build_connection(source_config_file, 'mysqldump', database)
    dest_conn = build_connection(destination_config_file, 'mysql', database)

    click.echo("Checking for tables...")
    src_tables = list_all_tables(src_conn)
    dest_tables = list_all_tables(dest_conn)

    if src_tables == dest_tables:
        click.secho("OK - All tables present!\n", fg='green')
    else:
        discrepancies = set(src_tables) ^ set(dest_tables)
        click.secho("FAILURE - Databases do not contain the same tables. Discrepancies: %s" % ', '.join(discrepancies),
                    fg='red')
        sys.exit(1)

    click.echo("Checking tables' checksums...")
    failures = []

    for table in src_tables:
        src_chksum, dest_chksum = get_table_checksums(src_conn, dest_conn, table)

        if src_chksum == dest_chksum:
            click.secho("OK - %s" % table, fg='green')
        else:
            click.secho("FAIL - %s" % table, fg='red')
            failures.append(table)

    click.echo('\nFinished:')
    if not failures:
        click.secho("OK - Databases are equal.", fg='green')
    else:
        click.secho("FAILURE - Database checksum mismatch(es): %s" % ', '.join(failures), fg='red')
        sys.exit(1)

    sys.exit(0)


if __name__ == '__main__':
    verify_mysql_replication()