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 · GitHub

Instantly share code, notes, and snippets.

allthingscode/Compare Mysql Databases

Created January 28, 2011 21:42

Show Gist options

• Download ZIP

• Star 0 (0) You must be signed in to star a gist
• Fork 0 (0) You must be signed in to fork a gist

• Select an option

  Learn more about clone URLs

  Clone this repository at <script src="https://gist.github.com/allthingscode/801036.js"></script>
• Save allthingscode/801036 to your computer and use it in GitHub Desktop.

Code Revisions 1

Select an option

Learn more about clone URLs

Clone this repository at <script src="https://gist.github.com/allthingscode/801036.js"></script>

Save allthingscode/801036 to your computer and use it in GitHub Desktop.

Download ZIP

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

Raw

Compare Mysql Databases

This file contains hidden or 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

Show hidden characters

	#!/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";

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment