MySQL CHECKSUM

CHECKSUM TABLE is useful information when you are checking the status of a table. This is often used before and after a backup and restore to ensure data is intact.

Here is a simple way to use it via the MySQL command line and the tools already available to you.


mysql> CREATE USER 'checksumuser'@'localhost';
mysql>GRANT SELECT ON *.* TO 'checksumuser'@'localhost';


mysql>SELECT
CONCAT('mysql --user=checksumuser -e \'CHECKSUM TABLE ',TABLE_SCHEMA,'.',TABLE_NAME ,' EXTENDED\'; ') as cmd_line_query
INTO OUTFILE '/tmp/checksums.sh'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA')
AND ENGINE IS NOT NULL
GROUP BY TABLE_SCHEMA, TABLE_NAME;

mysql> exit


# chmod +x /tmp/checksums.sh
# /tmp/checksums.sh > /tmp/checksums_b4dump.sql


Now you will have all your checksum data available to you in the file listed. Simple example of the data below


Table Checksum
world.City 2011482258
Table Checksum
world.Country 580721939
Table Checksum
world.CountryLanguage 1546017027


After the dump or process you are running you can then run the same script just change the output file then load it and compare results. It is not the cleanest method but it is an easy fast check for you to do.


# /tmp/checksums.sh > /tmp/checksums_after_dump.sql


mysql> use test
mysql> CREATE TABLE `checksums` (
`checksums_id` int(11) NOT NULL AUTO_INCREMENT,
table_name varchar(100) DEFAULT NULL,
size_a int(11) DEFAULT NULL,
size_b int(11) DEFAULT NULL,
PRIMARY KEY (`checksums_id`)
) ENGINE=InnoDB ;

LOAD DATA INFILE '/tmp/checksums_b4dump.sql'
IGNORE INTO TABLE checksums
(table_name, size_a);

LOAD DATA INFILE '/tmp/checksums_after_dump.sql'
IGNORE INTO TABLE checksums
(table_name, size_b);

DELETE FROM checksums WHERE table_name = 'Table';

SELECT a.table_name , a.size_a, b.size_b
FROM checksums a
INNER JOIN checksums b ON a.table_name = b.table_name and a.checksums_id != b.checksums_id
WHERE a.size_a IS NOT NULL AND b.size_b IS NOT NULL ;
+-----------------------------------------------------------------------+------------+------------+
| table_name | size_a | size_b |
+-----------------------------------------------------------------------+------------+------------+
| world.City | 2011482258 | 2011482258 |
| world.Country | 580721939 | 580721939 |
| world.CountryLanguage | 1546017027 | 1546017027 |
+-----------------------------------------------------------------------+------------+------------+


#mysql -p
mysql> DROP USER 'checksumuser'@'localhost';


DO NOT FORGET TO DROP THE USER when done.