MySQL Preacher

About a month ago I needed to compare tens of thousands of tables in hundreds of databases between a few different servers. The obvious choice was, mk-table-checksum! The only problem was, that the tool needs to know the minimum and maximum value of the column by which each table is to be subdivided into chunks and checksummed. This select min(col), max(col) from table locks all write operations on the table and on a big table it meant downtime.

Looking at the source it was clear we could make mk-table-checksum run the select min(col), max(col) from table on the read-only slave and use the values to checksum the master.

It was subtle code changes in function:
get_range_statistics adding

my $cxn_string_dc = “DBI:mysql:;host=slavehost;port=3306;mysql_read_default_group=client”;
my $user = ‘user’;
my $pass = ‘password’;
my $dbh_slave = DBI->connect($cxn_string_dc, $user, $pass);

and changing $dbh connection to $dbh_slave in the rest of the function where the min,max values were being calculated.

Good luck and enjoy a much less intrusive checksumming on your masters.

Because sharing is caring