Source-Replica Tutorial

Last Update: January 2021

Exercise 6

In this exercise you will learn: How to checksum a table

• 6.1 - What is a Checksum?

  If you don’t know what a checksum is, you can read up on the details: https://en.wikipedia.org/wiki/Checksum In a nutshell, you pass arbitrary amounts of data into a hashing function that returns a value. This value is the checksum. If you pass the same exact data into the function again, you will get the same result.

  However, if even a single bit (0 or 1) is different, the resultant checksum will not match the earlier checksum. This is how you determine if two sets of data are the same. If the checksums don’t match, the data doesn’t match.

• 6.2 - Checksumming a table - Built-In

  There is a built-in MySQL command to checksum a table:

  CHECKSUM TABLE [<db>.]<table>;

  However, this is not recommend as you only get 1 answer at the end and MySQL must process all the rows in the table in one large batch. This could take hours depending on the size of your table.

• 6.3 - Checksumming a table - pt-table-checksum

  pt-table-checksum is a free tool provided by Percona. http://www.percona.com/doc/percona-toolkit/pt-table-checksum.html This script generates checksums in a much more efficient way. Instead of operating on the entire table, pt-table-checksum operates on “chunks” of rows. The default size of 1 chunk is 1000 rows.

  You can execute pt-table-checksum to checksum your entire database (all tables in all databases) via:

  pt-table-checksum -u user -p pass h=localhost

  That’s it! The script will go table by table and calculate the checksum of each “chunk”. The script is very adaptive and tries to do as much as possible in the shortest amount of time.

  By default, that time is 0.5 seconds. So, if MySQL is able to checksum 1000 rows in under 0.5s, the script will increase this chunk size for the next chunk and continue to do so until it can no longer do the calculations within 0.5s. This way, the load on your server stays low.

  Since we have a Source->Replica setup from the first group of exercises, let’s take full advantage of pt-table-checksum.

• 6.4 - Create checksum user.

  Let’s create a user that can login to both machines. This user will be used in our call to the script. pt-table-checksum can then log in to the replica(s) and monitor replication lag and pause itself if lag gets too high. We also need to create a percona database to store the checksum results.

  CREATE USER 'checksum'@'%' IDENTIFIED BY 'Checksum1#';
  
  CREATE DATABASE IF NOT EXISTS percona;
  GRANT ALL ON `percona`.* TO `checksum`@`%`;
    
  GRANT SYSTEM_VARIABLES_ADMIN, PROCESS, SHOW DATABASES ON *.* TO `checksum`@`%`;
  GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `checksum`@`%`;
    
  GRANT SELECT ON *.* TO 'checksum'@'%';
  GRANT INSERT, DELETE ON imdb.* TO 'checksum'@'%';
  

  The PROCESS privilege is needed to monitor query load, incase the tool needs to throttle itself. SYSTEM_VARIABLES_ADMIN is needed to change the checksum session to binlog format STATEMENT mode for replication purposes. ALL is granted on the percona database so that the tool can create the checksum table, or empty it and write to it. The two REPLICATION_ privileges allow the tool to discover any connected replicas, and to read and monitor replication status.

• 6.5 - Calculate the Checksums

  Now that we have set up our user, let’s run a checksum of our data. First make sure perl library is updated as follows:

  curl -O http://repo.percona.com/tools/yum/experimental/7/RPMS/x86_64/perl-DBD-MySQL-4.050-4.el7.x86_64.rpm
  yum localinstall perl-DBD-MySQL-4.050-4.el7.x86_64.rpm
  

  Normally, we would let this run and checksum all tables in all databases. As our DB1 instance is a little over 10GB of data, this will take a bit too long for classroom purposes, so let’s just checksum one table instead.

  pt-table-checksum  h=localhost -u checksum -p Checksum1# \
    -d imdb -t company_name --no-check-binlog-format --no-version-check
  

  You should see output similar to this:

              TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
   08-19T02:01:40      0      0   288801       6       0   1.739 imdb.company_name
  

  We can see the timestamp of completion of this table, if there were any errors during the checksum, if any differences were found (# of chunks different), how many rows in the table, how many chunks the script created out of those rows, if any chunks got skipped, how long it took to checksum the table and the table name.

  Congratulations! You’ve checksum’d your entire database!