This script compares the record counts for 2 different mysql servers and creates a CSV report with the differences. This is useful for verifying that a master and slave server are the same. This does not verify the actual data at the 2 servers, it just co

Compare Mysql Databases

#!/usr/bin/perl
#
# This script compares table counts in all databases
# from 2 servers.
# This is useful for finding replication issues.
#
use strict;
use warnings;
use DBI;
 
my $_report_path = 'mysql_comparison.csv';
 
my $_db_1_name = 'master_database';
my $_db_1_user = 'root';
my $_db_1_pass = 'password';
my $_dsn_1     = 'DBI:mysql:host=123.456.78.901;database=mysql';
 
my $_db_2_name = 'slave_database';
my $_db_2_user = 'root';
my $_db_2_pass = 'password';
my $_dsn_2     = 'DBI:mysql:host=123.456.78.902;database=mysql';
# ==========================================================================
 
 
sub print2report
{
	open( FH_REPORT, ">>$_report_path" );
	print FH_REPORT $_[0];
	close( FH_REPORT );
}
 
# Connect to base database
my $_dbh_1 = DBI->connect( $_dsn_1, $_db_1_user, $_db_1_pass ) 
	or die "Cannot connect to $_db_1_name server\n";
print "Connected to $_db_1_name\n";
 
# Connect to compare database
my $_dbh_2 = DBI->connect( $_dsn_2, $_db_2_user, $_db_2_pass ) 
	or die "Cannot connect to $_db_2_name server\n";
print "Connected to $_db_2_name\n";
 
# Retrieve the datbase names
my $_sth_databases = $_dbh_1->prepare( 'SHOW DATABASES' );
$_sth_databases->execute();
while( ( my $_database ) = $_sth_databases->fetchrow_array() )
{
	#print "DATABASE:  $_database\n";
 
	if( 'mysql'              ne $_database && 
	    'test'               ne $_database && 
	    'temp'               ne $_database &&
	    'information_schema' ne $_database   )
	{
		# Switch to the database
		$_dbh_1->do( "USE `$_database`" ) or die( "Couldn't use $_database on $_db_1_name\n" );
		$_dbh_2->do( "USE `$_database`" ) or die( "Couldn't use $_database on $_db_2_name\n" );
 
		# Retrieve the table names
		my @_tables = $_dbh_1->tables();
		foreach my $_table ( @_tables )
		{
			#print "TABLE: $_table\n";
 
			my $_sth_count_1 = $_dbh_1->prepare( 'SELECT COUNT(*) FROM ' . $_table );
			$_sth_count_1->execute() or die "Couldn't get count from $_database.$_table on $_db_1_name\n";
			( my $_count_1 ) = $_sth_count_1->fetchrow_array();
 
			my $_sth_count_2 = $_dbh_2->prepare( 'SELECT COUNT(*) FROM ' . $_table );
			$_sth_count_2->execute() or die "Couldn't get count from $_database.$_table on $_db_2_name\n";
			( my $_count_2 ) = $_sth_count_2->fetchrow_array();
 
			if( $_count_1 == $_count_2 )
			{
				#print "$_database.$_table: equal record counts\n";
			}
			else
			{
				my $_diff = $_count_2 - $_count_1;
				if( $_diff > 0 )
				{
					$_diff = sprintf( '+%d', $_diff );
				}
				else
				{
					$_diff = sprintf( '%d', $_diff );
				}
				print2report( "$_database,$_table,$_count_1,$_count_2\n" );
				print "$_database.$_table\t\t$_count_1/$_count_2\t$_diff\n";
			}
		}
	}
}
 
$_dbh_1->disconnect();
print "Disconnected from $_db_1_name\n";
$_dbh_2->disconnect();
print "Disconnected from $_db_2_name\n";