Mysqltoolkit is a bunch of MySQL utilities based on perl written by Baron Schwartz.
Essentially he found no tool or software available to help him, so decided to write his own.
Not only that, he turned around and released them for any DBA to use.
As soon as I reviewed the list of tools available I immediately wanted to give mysql-table-checksum a test. Like many DBAs who support MySQL, master-slave replication is almost synonymous with MySQL databases. For all of its shortcomings, MySQL replication is the easiest database to set up replication on.
So the best test was getting the checksum to work, the nicer option though was testing the checksum of the master database against a slave database.
So I fired up my trusted EC2 MySQL 5.1 AMI, twice, practiced making a LVM snapshot backup (as practice makes perfect) and made a simple MySQL master-slave replication pair.
Comments:
Mysql-table-checksum works as stated. I had to get the permissions right on the slave for it work on the slaves as well.
You will need to install Perl DBI and DBD::MySQL, I have found running the cpan module is the easiest method to handling any dependencies.
If this was blended back into the MySQL distro at some stage, I am guessing a compiled program would be nicer so there is no additional work required to run.
As before I have included my edited screen dumps of the process.
Have Fun
Paul
Creating the Sakila sample database into MySQL master database
mysql -u root -p < sakila-schema.sql
Enter password:
mysql -u root -p < sakila-data.sql
Enter password: mysql -u root -p
Enter password:
Welcome to the MySQL monitor.
Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.20-beta-log
MySQL Community Server (GPL)
Type 'help;' or '\h' for help.
Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| sakila |
| test |
+--------------------+
4 rows in set (0.00 sec)
Install DBD::Mysql (dependency is DBI)
yum install perl-DBD-MySQL.i386
Install missing perl module
cpan> install Term::ReadKey
Installing MySQLtoolkit
perl Makefile.PL
Writing Makefile for mysqltoolkit
make
Manifying blib/man1/mysql-find.1
Manifying blib/man1/mysql-duplicate-key-checker.1
Manifying blib/man1/mysql-table-sync.1
Manifying blib/man1/mysql-deadlock-logger.1
Manifying blib/man1/mysql-slave-restart.1
Manifying blib/man1/mysql-slave-delay.1
Manifying blib/man1/mysql-profile-compact.1
Manifying blib/man1/mysql-archiver.1
Manifying blib/man1/mysql-query-profiler.1
Manifying blib/man1/mysql-visual-explain.1
Manifying blib/man1/mysql-table-checksum.1
Manifying blib/man1/mysql-checksum-filter.1
Manifying blib/man1/mysql-show-grants.1
Manifying blib/man3/mysqltoolkit.3pm
make test
cp lib/mysqltoolkit.pm blib/lib/mysqltoolkit.pm
cp bin/mysql-find blib/script/mysql-find
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-find
cp bin/mysql-duplicate-key-checker blib/script/mysql-duplicate-key-checker
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-duplicate-key-checker
cp bin/mysql-table-sync blib/script/mysql-table-sync
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-table-sync
cp bin/mysql-deadlock-logger blib/script/mysql-deadlock-logger
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-deadlock-logger
cp bin/mysql-slave-restart blib/script/mysql-slave-restart
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-slave-restart
cp bin/mysql-slave-delay blib/script/mysql-slave-delay
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-slave-delay
cp bin/mysql-profile-compact blib/script/mysql-profile-compact
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-profile-compact
cp bin/mysql-archiver blib/script/mysql-archiver
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-archiver
cp bin/mysql-query-profiler blib/script/mysql-query-profiler
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-query-profiler
cp bin/mysql-visual-explain blib/script/mysql-visual-explain
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-visual-explain
cp bin/mysql-table-checksum blib/script/mysql-table-checksum
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-table-checksum
cp bin/mysql-checksum-filter blib/script/mysql-checksum-filter
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-checksum-filter
cp bin/mysql-show-grants blib/script/mysql-show-grants
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/mysql-show-grants
No tests defined for mysqltoolkit extension.
make install
Installing /usr/lib/perl5/site_perl/5.8.5/mysqltoolkit.pm
Installing /usr/share/man/man1/mysql-find.1
Installing /usr/share/man/man1/mysql-duplicate-key-checker.1
Installing /usr/share/man/man1/mysql-table-sync.1
Installing /usr/share/man/man1/mysql-deadlock-logger.1
Installing /usr/share/man/man1/mysql-slave-restart.1
Installing /usr/share/man/man1/mysql-slave-delay.1
Installing /usr/share/man/man1/mysql-profile-compact.1
Installing /usr/share/man/man1/mysql-archiver.1
Installing /usr/share/man/man1/mysql-query-profiler.1
Installing /usr/share/man/man1/mysql-visual-explain.1
Installing /usr/share/man/man1/mysql-table-checksum.1
Installing /usr/share/man/man1/mysql-checksum-filter.1
Installing /usr/share/man/man1/mysql-show-grants.1
Installing /usr/share/man/man3/mysqltoolkit.3pm
Installing /usr/bin/mysql-find
Installing /usr/bin/mysql-duplicate-key-checker
Installing /usr/bin/mysql-table-sync
Installing /usr/bin/mysql-deadlock-logger
Installing /usr/bin/mysql-slave-restart
Installing /usr/bin/mysql-slave-delay
Installing /usr/bin/mysql-profile-compact
Installing /usr/bin/mysql-archiver
Installing /usr/bin/mysql-query-profiler
Installing /usr/bin/mysql-visual-explain
Installing /usr/bin/mysql-table-checksum
Installing /usr/bin/mysql-checksum-filter
Installing /usr/bin/mysql-show-grants
Writing /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi/auto/mysqltoolkit/.packlist
Appending installation info to /usr/lib/perl5/5.8.5/i386-linux-thread-multi/perllocal.pod
mysql-table-checksum help printout
mysql-table-checksum --help
Usage: mysql-table-checksum [OPTION]... HOST [HOST...]
--algorithm -a Checksum algorithm (ACCUM|CHECKSUM|BIT_XOR)
--askpass Prompt for username and password for connections
--chunkcol Explicitly specifies a column to use for chunking
--chunksize -C Number of rows to checksum at a time (disallows -a CHECKSUM)
--chunksize-exact Try not to exceed chunk sizes
--[no]count -r Do the count (default)
--[no]crc -c Do the CRC (default)
--databases -d Only do this comma-separated list of databases
--defaults-file -F Only read default options from the given file
--emptyrepltbl Empty table given by --replicate before starting
--engine -e Only do this comma-separated list of storage engines
--explain Show, but do not execute, checksum queries
--explainhosts Print connection information and exit
--float-precision Precision for FLOAT and DOUBLE column comparisons
--function -f Cryptographic hash function (SHA1, MD5...)
--help Show this help message
--ignoredb -g Ignore this comma-separated list of databases
--ignoretbl -n Ignore this comma-separated list of tables
--index -i Index to use for ACCUM checksum algorithm
--lock -k Lock table on master until done on slaves (implies -l)
--[no]optxor -o Optimize BIT_XOR with user variables (default)
--password -p Password to use when connecting
--port -P Port number to use for connection
--quiet -q Do not print checksum results
--recursecheck Do --replcheck recursively (implies --replcheck)
--replcheck Connect to slaves and check --replicate results
--replicate -R Replicate checksums in a table (disallows -a CHECKSUM)
--separator -s Separator for CONCAT_WS()
--slavelag -l Report how far slaves lag master
--sleep Sleep time between checksums
--sleep-coef Sleep time as a multiple of last checksum time
--socket -S Socket file to use for connection
--tab -b Output separated with tabs
--tables -t Only do this comma-separated list of tables
--user -u User for login if not current user
--[no]verify -v Verify checksum compatibility across servers (default)
--version Output version information and exit
--wait -w How long to do MASTER_POS_WAIT() on slaves (implies -kl)
--where -W Only do rows matching this WHERE clause (disallows -a CHECKSUM)
mysql-table-checksum efficiently checksums MySQL tables on one or more hosts.
If you specify multiple hosts, the first is assumed to be the master.
Connection options are read from MySQL option files. For more details, please
read the documentation:
perldoc mysql-table-checksum
Running simple checksum:
mysql-table-checksum h=localhost,u=root,p=$MYSQLPASS
DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
mysql columns_priv 1 localhost MyISAM 0 NULL 0 0 NULL NULL
mysql db 1 localhost MyISAM 2 c62b1e99966f5652a478847b624df26b4dfd1377 0 0 NULL NULL
mysql event 1 localhost MyISAM 0 NULL 0 0 NULL NULL
mysql func 1 localhost MyISAM 0 NULL 0 0 NULL NULL
mysql general_log 1 localhost CSV 0 NULL 0 0 NULL NULL
mysql help_category 1 localhost MyISAM 37 9ef62663c76f84a5bd7e875f65ff3f63c2994479 0 0 NULL NULL
mysql help_keyword 1 localhost MyISAM 425 db02c9d0f5a3d3eab678af8cdbb557c84518b14c 0 0 NULL NULL
mysql help_relation 1 localhost MyISAM 898 56c6eaa49e593718a3e15ccb6a8f5b3178b52466 0 0 NULL NULL
mysql help_topic 1 localhost MyISAM 478 0b15c5e97f5cec4b857550848ac5263170e3487e 1 0 NULL NULL
mysql host 1 localhost MyISAM 0 NULL 0 0 NULL NULL
mysql ndb_binlog_index 1 localhost MyISAM 0 NULL 0 0 NULL NULL
mysql plugin 1 localhost MyISAM 0 NULL 0 0 NULL NULL
mysql proc 1 localhost MyISAM 6 d388df6dabb27d2451457c3ce0f778ef938f246e 0 0 NULL NULL
mysql procs_priv 1 localhost MyISAM 0 NULL 0 0 NULL NULL
mysql servers 1 localhost MyISAM 0 NULL 0 0 NULL NULL
mysql slow_log 1 localhost CSV 0 NULL 0 0 NULL NULL
mysql tables_priv 1 localhost MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone 1 localhost MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone_leap_second 1 localhost MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone_name 1 localhost MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone_transition 1 localhost MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone_transition_type 1 localhost MyISAM 0 NULL 0 0 NULL NULL
mysql user 1 localhost MyISAM 3 53720f720d5057c0154b176727cdadbba0fe3779 0 0 NULL NULL
DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
sakila actor 1 localhost InnoDB 200 e460daf0c941a35e07595f3b76686132d3994e49 0 0 NULL NULL
sakila address 1 localhost InnoDB 603 175a2192effdad0b4a8c16ee5038de16e7b32223 0 0 NULL NULL
sakila category 1 localhost InnoDB 16 579c8b0ad142daf527bff6d52abbf904bb873985 0 0 NULL NULL
sakila city 1 localhost InnoDB 600 3577100ea5241d5e6600c0f6ef0d31ce55787fa0 0 0 NULL NULL
sakila country 1 localhost InnoDB 109 47146889e8990bc79d4b9aaf7344ffbf279eade1 0 0 NULL NULL
sakila customer 1 localhost InnoDB 599 5109a39e50e0c29cee8b6805be3c8be2542bba74 0 0 NULL NULL
sakila film 1 localhost InnoDB 1000 3857ddc3f576d2246e35366b57bbf02d18455bac 0 0 NULL NULL
sakila film_actor 1 localhost InnoDB 5462 4b2f6d38e7b75bab9cce775748c0a655643bdaad 0 0 NULL NULL
sakila film_category 1 localhost InnoDB 1000 45a4418ebcc3a9c4808512414112abbe0f5e64e8 0 0 NULL NULL
sakila film_text 1 localhost MyISAM 1000 355c6e8af91556fcfba2af381539e285e368e2b0 0 0 NULL NULL
sakila inventory 1 localhost InnoDB 4581 59c10c2dce34a5b5132abe984e52b0b8b09ff6f9 0 0 NULL NULL
sakila language 1 localhost InnoDB 6 95ef63c60bdc024031c746232262f77ad7ead342 0 0 NULL NULL
sakila payment 1 localhost InnoDB 16049 d63f0af5124c1598061e1d99af03785db0a600e3 1 0 NULL NULL
sakila rental 1 localhost InnoDB 16044 047b7143e51a64348451dfe57d0c78c4da8950f9 0 0 NULL NULL
sakila staff 1 localhost InnoDB 2 3c08fd6c4482c4e48c2c692c731e57f991e21089 0 0 NULL NULL
sakila store 1 localhost InnoDB 2 9976f802fe471c5ac6c9c037a28557ff2b24da3f 0 0 NULL NULL
Timing the runs of various different algorithms available
time mysql-table-checksum h=localhost,u=root,p=$MYSQLPASS --algorithm=ACCUM
real 0m2.232s
user 0m0.200s
sys 0m0.020s
time mysql-table-checksum h=localhost,u=root,p=$MYSQLPASS --algorithm=CHECKSUM
real 0m2.176s
user 0m0.230s
sys 0m0.000s
time mysql-table-checksum h=localhost,u=root,p=$MYSQLPASS --algorithm=BIT_XOR
real 0m1.984s
user 0m0.220s
sys 0m0.020s
Create a slave using LVM snapshot backup
mysql> create user checksum_user@'mysql-master' identified by 'ch3cksum';
Query OK, 0 rows affected (0.00 sec)
mysql> grant super on *.* to checksum_user@'mysql-master' identified by 'ch3cksum';
Query OK, 0 rows affected (0.00 sec)
Doesn't work. Need to grant "GRANT ALL PRIVILEGES to root user from master host"
GRANT ALL PRIVILEGES ON *.* TO 'root'@'mysql-master' IDENTIFIED BY '...'
mysql-table-checksum h=mysql-master,u=root,p=$MYSQLPASS
DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
mysql columns_priv 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql db 1 mysql-master MyISAM 2 c62b1e99966f5652a478847b624df26b4dfd1377 0 0 NULL NULL
mysql event 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql func 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql general_log 1 mysql-master CSV 0 NULL 0 0 NULL NULL
mysql help_category 1 mysql-master MyISAM 37 9ef62663c76f84a5bd7e875f65ff3f63c2994479 0 0 NULL NULL
mysql help_keyword 1 mysql-master MyISAM 425 db02c9d0f5a3d3eab678af8cdbb557c84518b14c 0 0 NULL NULL
mysql help_relation 1 mysql-master MyISAM 898 56c6eaa49e593718a3e15ccb6a8f5b3178b52466 0 0 NULL NULL
mysql help_topic 1 mysql-master MyISAM 478 0b15c5e97f5cec4b857550848ac5263170e3487e 0 0 NULL NULL
mysql host 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql ndb_binlog_index 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql plugin 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql proc 1 mysql-master MyISAM 6 9f1b2aad83c5274e4d95881ea0e241933c141862 0 0 NULL NULL
mysql procs_priv 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql servers 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql slow_log 1 mysql-master CSV 0 NULL 0 0 NULL NULL
mysql tables_priv 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone_leap_second 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone_name 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone_transition 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone_transition_type 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql user 1 mysql-master MyISAM 4 4166a9a1d0b61dd90167da4631e7ac1fadcb716e 0 0 NULL NULL
DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
sakila actor 1 mysql-master InnoDB 200 e460daf0c941a35e07595f3b76686132d3994e49 0 0 NULL NULL
sakila address 1 mysql-master InnoDB 603 175a2192effdad0b4a8c16ee5038de16e7b32223 0 0 NULL NULL
sakila category 1 mysql-master InnoDB 16 579c8b0ad142daf527bff6d52abbf904bb873985 0 0 NULL NULL
sakila city 1 mysql-master InnoDB 600 3577100ea5241d5e6600c0f6ef0d31ce55787fa0 0 0 NULL NULL
sakila country 1 mysql-master InnoDB 109 47146889e8990bc79d4b9aaf7344ffbf279eade1 0 0 NULL NULL
sakila customer 1 mysql-master InnoDB 599 5109a39e50e0c29cee8b6805be3c8be2542bba74 0 0 NULL NULL
sakila film 1 mysql-master InnoDB 1000 3857ddc3f576d2246e35366b57bbf02d18455bac 0 0 NULL NULL
sakila film_actor 1 mysql-master InnoDB 5462 4b2f6d38e7b75bab9cce775748c0a655643bdaad 0 0 NULL NULL
sakila film_category 1 mysql-master InnoDB 1000 45a4418ebcc3a9c4808512414112abbe0f5e64e8 0 0 NULL NULL
sakila film_text 1 mysql-master MyISAM 1000 355c6e8af91556fcfba2af381539e285e368e2b0 0 0 NULL NULL
sakila inventory 1 mysql-master InnoDB 4581 59c10c2dce34a5b5132abe984e52b0b8b09ff6f9 0 0 NULL NULL
sakila language 1 mysql-master InnoDB 6 95ef63c60bdc024031c746232262f77ad7ead342 0 0 NULL NULL
sakila payment 1 mysql-master InnoDB 16049 d63f0af5124c1598061e1d99af03785db0a600e3 1 0 NULL NULL
sakila rental 1 mysql-master InnoDB 16044 047b7143e51a64348451dfe57d0c78c4da8950f9 1 0 NULL NULL
sakila staff 1 mysql-master InnoDB 2 3c08fd6c4482c4e48c2c692c731e57f991e21089 0 0 NULL NULL
sakila store 1 mysql-master InnoDB 2 9976f802fe471c5ac6c9c037a28557ff2b24da3f 0 0 NULL NULL
On slave, check root@mysql-master has privileges to connect
mysql> show grants for 'root'@'mysql-master';
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for root@mysql-master |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'mysql-master' IDENTIFIED BY PASSWORD '*81D69EF224A834201E2351343112163A99A5476F' |
+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Running command on master only
mysql-table-checksum h=mysql-master,u=root,p=$MYSQLPASS
DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
mysql columns_priv 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql db 1 mysql-master MyISAM 2 c62b1e99966f5652a478847b624df26b4dfd1377 0 0 NULL NULL
mysql event 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql func 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql general_log 1 mysql-master CSV 0 NULL 0 0 NULL NULL
mysql help_category 1 mysql-master MyISAM 37 9ef62663c76f84a5bd7e875f65ff3f63c2994479 0 0 NULL NULL
mysql help_keyword 1 mysql-master MyISAM 425 db02c9d0f5a3d3eab678af8cdbb557c84518b14c 0 0 NULL NULL
mysql help_relation 1 mysql-master MyISAM 898 56c6eaa49e593718a3e15ccb6a8f5b3178b52466 0 0 NULL NULL
mysql help_topic 1 mysql-master MyISAM 478 0b15c5e97f5cec4b857550848ac5263170e3487e 0 0 NULL NULL
mysql host 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql ndb_binlog_index 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql plugin 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql proc 1 mysql-master MyISAM 6 9f1b2aad83c5274e4d95881ea0e241933c141862 0 0 NULL NULL
mysql procs_priv 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql servers 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql slow_log 1 mysql-master CSV 0 NULL 0 0 NULL NULL
mysql tables_priv 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone_leap_second 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone_name 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone_transition 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone_transition_type 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql user 1 mysql-master MyISAM 6 57c3425f2dcd64664840aa6e858916322fdf1cc0 0 0 NULL NULL
DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
sakila actor 1 mysql-master InnoDB 200 e460daf0c941a35e07595f3b76686132d3994e49 0 0 NULL NULL
sakila address 1 mysql-master InnoDB 603 175a2192effdad0b4a8c16ee5038de16e7b32223 0 0 NULL NULL
sakila category 1 mysql-master InnoDB 16 579c8b0ad142daf527bff6d52abbf904bb873985 0 0 NULL NULL
sakila city 1 mysql-master InnoDB 600 3577100ea5241d5e6600c0f6ef0d31ce55787fa0 0 0 NULL NULL
sakila country 1 mysql-master InnoDB 109 47146889e8990bc79d4b9aaf7344ffbf279eade1 0 0 NULL NULL
sakila customer 1 mysql-master InnoDB 599 5109a39e50e0c29cee8b6805be3c8be2542bba74 0 0 NULL NULL
sakila film 1 mysql-master InnoDB 1000 3857ddc3f576d2246e35366b57bbf02d18455bac 0 0 NULL NULL
sakila film_actor 1 mysql-master InnoDB 5462 4b2f6d38e7b75bab9cce775748c0a655643bdaad 0 0 NULL NULL
sakila film_category 1 mysql-master InnoDB 1000 45a4418ebcc3a9c4808512414112abbe0f5e64e8 0 0 NULL NULL
sakila film_text 1 mysql-master MyISAM 1000 355c6e8af91556fcfba2af381539e285e368e2b0 1 0 NULL NULL
sakila inventory 1 mysql-master InnoDB 4581 59c10c2dce34a5b5132abe984e52b0b8b09ff6f9 0 0 NULL NULL
sakila language 1 mysql-master InnoDB 6 95ef63c60bdc024031c746232262f77ad7ead342 0 0 NULL NULL
sakila payment 1 mysql-master InnoDB 16049 d63f0af5124c1598061e1d99af03785db0a600e3 0 0 NULL NULL
sakila rental 1 mysql-master InnoDB 16044 047b7143e51a64348451dfe57d0c78c4da8950f9 1 0 NULL NULL
sakila staff 1 mysql-master InnoDB 2 3c08fd6c4482c4e48c2c692c731e57f991e21089 0 0 NULL NULL
sakila store 1 mysql-master InnoDB 2 9976f802fe471c5ac6c9c037a28557ff2b24da3f 0 0 NULL NULL
[root@domU-12-31-36-00-00-42 sakila-db]# mysql-table-checksum h=mysql-master,u=root,p=$MYSQLPASS h=mysql-slave
DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
mysql columns_priv 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql columns_priv 1 mysql-slave MyISAM 0 NULL 0 0 NULL NULL
mysql db 1 mysql-master MyISAM 2 c62b1e99966f5652a478847b624df26b4dfd1377 0 0 NULL NULL
mysql db 1 mysql-slave MyISAM 2 c62b1e99966f5652a478847b624df26b4dfd1377 0 0 NULL NULL
mysql event 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql event 1 mysql-slave MyISAM 0 NULL 0 0 NULL NULL
mysql func 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql func 1 mysql-slave MyISAM 0 NULL 0 0 NULL NULL
mysql general_log 1 mysql-master CSV 0 NULL 0 0 NULL NULL
mysql general_log 1 mysql-slave CSV 0 NULL 0 0 NULL NULL
mysql help_category 1 mysql-master MyISAM 37 9ef62663c76f84a5bd7e875f65ff3f63c2994479 0 0 NULL NULL
mysql help_category 1 mysql-slave MyISAM 37 9ef62663c76f84a5bd7e875f65ff3f63c2994479 0 0 NULL NULL
mysql help_keyword 1 mysql-master MyISAM 425 db02c9d0f5a3d3eab678af8cdbb557c84518b14c 0 0 NULL NULL
mysql help_keyword 1 mysql-slave MyISAM 425 db02c9d0f5a3d3eab678af8cdbb557c84518b14c 0 0 NULL NULL
mysql help_relation 1 mysql-master MyISAM 898 56c6eaa49e593718a3e15ccb6a8f5b3178b52466 0 0 NULL NULL
mysql help_relation 1 mysql-slave MyISAM 898 56c6eaa49e593718a3e15ccb6a8f5b3178b52466 0 0 NULL NULL
mysql help_topic 1 mysql-master MyISAM 478 0b15c5e97f5cec4b857550848ac5263170e3487e 0 0 NULL NULL
mysql help_topic 1 mysql-slave MyISAM 478 0b15c5e97f5cec4b857550848ac5263170e3487e 0 0 NULL NULL
mysql host 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql host 1 mysql-slave MyISAM 0 NULL 0 0 NULL NULL
mysql ndb_binlog_index 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql ndb_binlog_index 1 mysql-slave MyISAM 0 NULL 0 0 NULL NULL
mysql plugin 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql plugin 1 mysql-slave MyISAM 0 NULL 0 0 NULL NULL
mysql proc 1 mysql-master MyISAM 6 9f1b2aad83c5274e4d95881ea0e241933c141862 0 0 NULL NULL
mysql proc 1 mysql-slave MyISAM 6 9f1b2aad83c5274e4d95881ea0e241933c141862 0 0 NULL NULL
mysql procs_priv 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql procs_priv 1 mysql-slave MyISAM 0 NULL 0 0 NULL NULL
mysql servers 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql servers 1 mysql-slave MyISAM 0 NULL 0 0 NULL NULL
mysql slow_log 1 mysql-master CSV 0 NULL 0 0 NULL NULL
mysql slow_log 1 mysql-slave CSV 0 NULL 0 0 NULL NULL
mysql tables_priv 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql tables_priv 1 mysql-slave MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone 1 mysql-slave MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone_leap_second 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone_leap_second 1 mysql-slave MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone_name 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone_name 1 mysql-slave MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone_transition 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone_transition 1 mysql-slave MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone_transition_type 1 mysql-master MyISAM 0 NULL 0 0 NULL NULL
mysql time_zone_transition_type 1 mysql-slave MyISAM 0 NULL 0 0 NULL NULL
mysql user 1 mysql-master MyISAM 6 57c3425f2dcd64664840aa6e858916322fdf1cc0 0 0 NULL NULL
mysql user 1 mysql-slave MyISAM 6 57c3425f2dcd64664840aa6e858916322fdf1cc0 0 0 NULL NULL
DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
sakila actor 1 mysql-master InnoDB 200 e460daf0c941a35e07595f3b76686132d3994e49 0 0 NULL NULL
sakila actor 1 mysql-slave InnoDB 200 e460daf0c941a35e07595f3b76686132d3994e49 0 0 NULL NULL
sakila address 1 mysql-master InnoDB 603 175a2192effdad0b4a8c16ee5038de16e7b32223 0 0 NULL NULL
sakila address 1 mysql-slave InnoDB 603 175a2192effdad0b4a8c16ee5038de16e7b32223 0 0 NULL NULL
sakila category 1 mysql-master InnoDB 16 579c8b0ad142daf527bff6d52abbf904bb873985 0 0 NULL NULL
sakila category 1 mysql-slave InnoDB 16 579c8b0ad142daf527bff6d52abbf904bb873985 0 0 NULL NULL
sakila city 1 mysql-master InnoDB 600 3577100ea5241d5e6600c0f6ef0d31ce55787fa0 0 0 NULL NULL
sakila city 1 mysql-slave InnoDB 600 3577100ea5241d5e6600c0f6ef0d31ce55787fa0 0 0 NULL NULL
sakila country 1 mysql-master InnoDB 109 47146889e8990bc79d4b9aaf7344ffbf279eade1 0 0 NULL NULL
sakila country 1 mysql-slave InnoDB 109 47146889e8990bc79d4b9aaf7344ffbf279eade1 0 0 NULL NULL
sakila customer 1 mysql-master InnoDB 599 5109a39e50e0c29cee8b6805be3c8be2542bba74 0 0 NULL NULL
sakila customer 1 mysql-slave InnoDB 599 5109a39e50e0c29cee8b6805be3c8be2542bba74 0 0 NULL NULL
sakila film 1 mysql-master InnoDB 1000 3857ddc3f576d2246e35366b57bbf02d18455bac 0 0 NULL NULL
sakila film 1 mysql-slave InnoDB 1000 3857ddc3f576d2246e35366b57bbf02d18455bac 0 0 NULL NULL
sakila film_actor 1 mysql-master InnoDB 5462 4b2f6d38e7b75bab9cce775748c0a655643bdaad 0 0 NULL NULL
sakila film_actor 1 mysql-slave InnoDB 5462 4b2f6d38e7b75bab9cce775748c0a655643bdaad 0 0 NULL NULL
sakila film_category 1 mysql-master InnoDB 1000 45a4418ebcc3a9c4808512414112abbe0f5e64e8 0 0 NULL NULL
sakila film_category 1 mysql-slave InnoDB 1000 45a4418ebcc3a9c4808512414112abbe0f5e64e8 0 0 NULL NULL
sakila film_text 1 mysql-master MyISAM 1000 355c6e8af91556fcfba2af381539e285e368e2b0 0 0 NULL NULL
sakila film_text 1 mysql-slave MyISAM 1000 355c6e8af91556fcfba2af381539e285e368e2b0 0 0 NULL NULL
sakila inventory 1 mysql-master InnoDB 4581 59c10c2dce34a5b5132abe984e52b0b8b09ff6f9 1 0 NULL NULL
sakila inventory 1 mysql-slave InnoDB 4581 59c10c2dce34a5b5132abe984e52b0b8b09ff6f9 0 0 NULL NULL
sakila language 1 mysql-master InnoDB 6 95ef63c60bdc024031c746232262f77ad7ead342 0 0 NULL NULL
sakila language 1 mysql-slave InnoDB 6 95ef63c60bdc024031c746232262f77ad7ead342 0 0 NULL NULL
sakila payment 1 mysql-master InnoDB 16049 d63f0af5124c1598061e1d99af03785db0a600e3 1 0 NULL NULL
sakila payment 1 mysql-slave InnoDB 16049 d63f0af5124c1598061e1d99af03785db0a600e3 0 0 NULL NULL
sakila rental 1 mysql-master InnoDB 16044 047b7143e51a64348451dfe57d0c78c4da8950f9 1 0 NULL NULL
sakila rental 1 mysql-slave InnoDB 16044 047b7143e51a64348451dfe57d0c78c4da8950f9 1 0 NULL NULL
sakila staff 1 mysql-master InnoDB 2 3c08fd6c4482c4e48c2c692c731e57f991e21089 0 0 NULL NULL
sakila staff 1 mysql-slave InnoDB 2 3c08fd6c4482c4e48c2c692c731e57f991e21089 0 0 NULL NULL
sakila store 1 mysql-master InnoDB 2 9976f802fe471c5ac6c9c037a28557ff2b24da3f 0 0 NULL NULL
sakila store 1 mysql-slave InnoDB 2 9976f802fe471c5ac6c9c037a28557ff2b24da3f 0 0 NULL NULL
With BIT_XOR algorithm
mysql-table-checksum h=mysql-master,u=root,p=$MYSQLPASS h=mysql-slave --algorithm=BIT_XOR
DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
mysql columns_priv 1 mysql-master MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql columns_priv 1 mysql-slave MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql db 1 mysql-master MyISAM 2 924537BECA7F5E81CF29E64077F12AD3BCC5BB2B 0 0 NULL NULL
mysql db 1 mysql-slave MyISAM 2 924537BECA7F5E81CF29E64077F12AD3BCC5BB2B 0 0 NULL NULL
mysql event 1 mysql-master MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql event 1 mysql-slave MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql func 1 mysql-master MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql func 1 mysql-slave MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql general_log 1 mysql-master CSV 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql general_log 1 mysql-slave CSV 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql help_category 1 mysql-master MyISAM 37 7BA18C3681F17D236E38254D00CC43A8E1544AF8 0 0 NULL NULL
mysql help_category 1 mysql-slave MyISAM 37 7BA18C3681F17D236E38254D00CC43A8E1544AF8 0 0 NULL NULL
mysql help_keyword 1 mysql-master MyISAM 425 B746F8F8D7D50EA9810923704522CE769C81B494 0 0 NULL NULL
mysql help_keyword 1 mysql-slave MyISAM 425 B746F8F8D7D50EA9810923704522CE769C81B494 0 0 NULL NULL
mysql help_relation 1 mysql-master MyISAM 898 FC305F19C53B03FF2DA9670B97B6AEF0916FDE25 0 0 NULL NULL
mysql help_relation 1 mysql-slave MyISAM 898 FC305F19C53B03FF2DA9670B97B6AEF0916FDE25 0 0 NULL NULL
mysql help_topic 1 mysql-master MyISAM 478 96B15EF54DEB0510FA0006C4870E1A29634BB4BE 0 0 NULL NULL
mysql help_topic 1 mysql-slave MyISAM 478 96B15EF54DEB0510FA0006C4870E1A29634BB4BE 0 0 NULL NULL
mysql host 1 mysql-master MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql host 1 mysql-slave MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql ndb_binlog_index 1 mysql-master MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql ndb_binlog_index 1 mysql-slave MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql plugin 1 mysql-master MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql plugin 1 mysql-slave MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql proc 1 mysql-master MyISAM 6 6D5555085EF1DAA2B2B657DB9507EF5212924F96 0 0 NULL NULL
mysql proc 1 mysql-slave MyISAM 6 6D5555085EF1DAA2B2B657DB9507EF5212924F96 0 0 NULL NULL
mysql procs_priv 1 mysql-master MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql procs_priv 1 mysql-slave MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql servers 1 mysql-master MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql servers 1 mysql-slave MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql slow_log 1 mysql-master CSV 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql slow_log 1 mysql-slave CSV 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql tables_priv 1 mysql-master MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql tables_priv 1 mysql-slave MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql time_zone 1 mysql-master MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql time_zone 1 mysql-slave MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql time_zone_leap_second 1 mysql-master MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql time_zone_leap_second 1 mysql-slave MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql time_zone_name 1 mysql-master MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql time_zone_name 1 mysql-slave MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql time_zone_transition 1 mysql-master MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql time_zone_transition 1 mysql-slave MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql time_zone_transition_type 1 mysql-master MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql time_zone_transition_type 1 mysql-slave MyISAM 0 0000000000000000000000000000000000000000 0 0 NULL NULL
mysql user 1 mysql-master MyISAM 6 ECD344AA2367991CAFEFC28B94C057D894186EA6 0 0 NULL NULL
mysql user 1 mysql-slave MyISAM 6 ECD344AA2367991CAFEFC28B94C057D894186EA6 0 0 NULL NULL
DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
sakila actor 1 mysql-master InnoDB 200 157EB02E45E58007B07DA789953BA599973FDA63 0 0 NULL NULL
sakila actor 1 mysql-slave InnoDB 200 157EB02E45E58007B07DA789953BA599973FDA63 0 0 NULL NULL
sakila address 1 mysql-master InnoDB 603 6D7722328064948F07916D9AF3FADEB07952D988 0 0 NULL NULL
sakila address 1 mysql-slave InnoDB 603 6D7722328064948F07916D9AF3FADEB07952D988 0 0 NULL NULL
sakila category 1 mysql-master InnoDB 16 B051DA25BFD9CABE4D831ADC79D9B213B3F1E723 0 0 NULL NULL
sakila category 1 mysql-slave InnoDB 16 B051DA25BFD9CABE4D831ADC79D9B213B3F1E723 0 0 NULL NULL
sakila city 1 mysql-master InnoDB 600 482C25D7E0FD3434A5644ABB26963E1E880C5DE0 0 0 NULL NULL
sakila city 1 mysql-slave InnoDB 600 482C25D7E0FD3434A5644ABB26963E1E880C5DE0 0 0 NULL NULL
sakila country 1 mysql-master InnoDB 109 972AFF5C0601C571B80F895785FA6F70AAFE20EA 0 0 NULL NULL
sakila country 1 mysql-slave InnoDB 109 972AFF5C0601C571B80F895785FA6F70AAFE20EA 0 0 NULL NULL
sakila customer 1 mysql-master InnoDB 599 9B42642F2A15D9A0EC255CB6C5E8B9231A8BD04B 0 0 NULL NULL
sakila customer 1 mysql-slave InnoDB 599 9B42642F2A15D9A0EC255CB6C5E8B9231A8BD04B 0 0 NULL NULL
sakila film 1 mysql-master InnoDB 1000 5290C30D4589E69533FE037C1D1D61494968E3C7 0 0 NULL NULL
sakila film 1 mysql-slave InnoDB 1000 5290C30D4589E69533FE037C1D1D61494968E3C7 0 0 NULL NULL
sakila film_actor 1 mysql-master InnoDB 5462 66AB134AF212CBE2FDCF062C488552BD9FDA982A 0 0 NULL NULL
sakila film_actor 1 mysql-slave InnoDB 5462 66AB134AF212CBE2FDCF062C488552BD9FDA982A 0 0 NULL NULL
sakila film_category 1 mysql-master InnoDB 1000 4367D179ECB2168A85434DDB696E80A24FAA1FBF 0 0 NULL NULL
sakila film_category 1 mysql-slave InnoDB 1000 4367D179ECB2168A85434DDB696E80A24FAA1FBF 0 0 NULL NULL
sakila film_text 1 mysql-master MyISAM 1000 BFF6E20B7831A9083386629509C6E1A14D325ED9 0 0 NULL NULL
sakila film_text 1 mysql-slave MyISAM 1000 BFF6E20B7831A9083386629509C6E1A14D325ED9 0 0 NULL NULL
sakila inventory 1 mysql-master InnoDB 4581 95A1455B778287CC1F333ECACF08F1771F9178A1 1 0 NULL NULL
sakila inventory 1 mysql-slave InnoDB 4581 95A1455B778287CC1F333ECACF08F1771F9178A1 0 0 NULL NULL
sakila language 1 mysql-master InnoDB 6 94710D1DA433D122B9F47C526F10BEEC01AD67BF 0 0 NULL NULL
sakila language 1 mysql-slave InnoDB 6 94710D1DA433D122B9F47C526F10BEEC01AD67BF 0 0 NULL NULL
sakila payment 1 mysql-master InnoDB 16049 E8EBF724C16994BC5C499E3B45FB1AA5C0A1CFDD 0 0 NULL NULL
sakila payment 1 mysql-slave InnoDB 16049 E8EBF724C16994BC5C499E3B45FB1AA5C0A1CFDD 1 0 NULL NULL
sakila rental 1 mysql-master InnoDB 16044 8E6D5544BF044732ED3D5CC107A0797E92D27011 0 0 NULL NULL
sakila rental 1 mysql-slave InnoDB 16044 8E6D5544BF044732ED3D5CC107A0797E92D27011 1 0 NULL NULL
sakila staff 1 mysql-master InnoDB 2 5B1AC4028B339F3F2A303367CECCC8C83D9C0360 0 0 NULL NULL
sakila staff 1 mysql-slave InnoDB 2 5B1AC4028B339F3F2A303367CECCC8C83D9C0360 0 0 NULL NULL
sakila store 1 mysql-master InnoDB 2 CB0B4C8B4374925926117ADF8C8958CA43E8ACFF 0 0 NULL NULL
sakila store 1 mysql-slave InnoDB 2 CB0B4C8B4374925926117ADF8C8958CA43E8ACFF 0 0 NULL NULL
Creating checksum table so we can check for errors in checksum
CREATE TABLE checksum (
db char(64) NOT NULL,
tbl char(64) NOT NULL,
chunk int NOT NULL,
boundaries char(64) NOT NULL,
this_crc char(40) NOT NULL,
this_cnt int NOT NULL,
master_crc char(40) NULL,
master_cnt int NULL,
ts timestamp NOT NULL,
PRIMARY KEY (db, tbl, chunk)
) ENGINE=InnoDB;
mysql> use mysql
Database changed
mysql> CREATE TABLE checksum (
-> db char(64) NOT NULL,
-> tbl char(64) NOT NULL,
-> chunk int NOT NULL,
-> boundaries char(64) NOT NULL,
-> this_crc char(40) NOT NULL,
-> this_cnt int NOT NULL,
-> master_crc char(40) NULL,
-> master_cnt int NULL,
-> ts timestamp NOT NULL,
-> PRIMARY KEY (db, tbl, chunk)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
mysql-table-checksum h=mysql-master,u=root,p=$MYSQLPASS --replicate=mysql.checksum
DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
mysql columns_priv 1 mysql-master MyISAM 0 NULL 0 NULL NULL NULL
mysql db 1 mysql-master MyISAM 2 c62b1e99966f5652a478847b624df26b4dfd1377 0 NULL NULL NULL
mysql event 1 mysql-master MyISAM 0 NULL 0 NULL NULL NULL
mysql func 1 mysql-master MyISAM 0 NULL 0 NULL NULL NULL
You can't use usual read lock with log tables. Try READ LOCAL instead at line 790 while doing mysql.general_log
mysql help_category 1 mysql-master MyISAM 37 9ef62663c76f84a5bd7e875f65ff3f63c2994479 0 NULL NULL NULL
mysql help_keyword 1 mysql-master MyISAM 425 db02c9d0f5a3d3eab678af8cdbb557c84518b14c 0 NULL NULL NULL
mysql help_relation 1 mysql-master MyISAM 898 56c6eaa49e593718a3e15ccb6a8f5b3178b52466 0 NULL NULL NULL
mysql help_topic 1 mysql-master MyISAM 478 0b15c5e97f5cec4b857550848ac5263170e3487e 0 NULL NULL NULL
mysql host 1 mysql-master MyISAM 0 NULL 0 NULL NULL NULL
mysql ndb_binlog_index 1 mysql-master MyISAM 0 NULL 0 NULL NULL NULL
mysql plugin 1 mysql-master MyISAM 0 NULL 0 NULL NULL NULL
mysql proc 1 mysql-master MyISAM 6 9f1b2aad83c5274e4d95881ea0e241933c141862 0 NULL NULL NULL
mysql procs_priv 1 mysql-master MyISAM 0 NULL 0 NULL NULL NULL
mysql servers 1 mysql-master MyISAM 0 NULL 0 NULL NULL NULL
You can't use usual read lock with log tables. Try READ LOCAL instead at line 790 while doing mysql.slow_log
mysql tables_priv 1 mysql-master MyISAM 0 NULL 0 NULL NULL NULL
mysql time_zone 1 mysql-master MyISAM 0 NULL 0 NULL NULL NULL
mysql time_zone_leap_second 1 mysql-master MyISAM 0 NULL 0 NULL NULL NULL
mysql time_zone_name 1 mysql-master MyISAM 0 NULL 0 NULL NULL NULL
mysql time_zone_transition 1 mysql-master MyISAM 0 NULL 0 NULL NULL NULL
mysql time_zone_transition_type 1 mysql-master MyISAM 0 NULL 0 NULL NULL NULL
mysql user 1 mysql-master MyISAM 6 57c3425f2dcd64664840aa6e858916322fdf1cc0 0 NULL NULL NULL
DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
sakila actor 1 mysql-master InnoDB 200 e460daf0c941a35e07595f3b76686132d3994e49 0 NULL NULL NULL
sakila address 1 mysql-master InnoDB 603 175a2192effdad0b4a8c16ee5038de16e7b32223 0 NULL NULL NULL
sakila category 1 mysql-master InnoDB 16 579c8b0ad142daf527bff6d52abbf904bb873985 0 NULL NULL NULL
sakila city 1 mysql-master InnoDB 600 3577100ea5241d5e6600c0f6ef0d31ce55787fa0 0 NULL NULL NULL
sakila country 1 mysql-master InnoDB 109 47146889e8990bc79d4b9aaf7344ffbf279eade1 0 NULL NULL NULL
sakila customer 1 mysql-master InnoDB 599 5109a39e50e0c29cee8b6805be3c8be2542bba74 0 NULL NULL NULL
sakila film 1 mysql-master InnoDB 1000 3857ddc3f576d2246e35366b57bbf02d18455bac 0 NULL NULL NULL
sakila film_actor 1 mysql-master InnoDB 5462 4b2f6d38e7b75bab9cce775748c0a655643bdaad 0 NULL NULL NULL
sakila film_category 1 mysql-master InnoDB 1000 45a4418ebcc3a9c4808512414112abbe0f5e64e8 0 NULL NULL NULL
sakila film_text 1 mysql-master MyISAM 1000 355c6e8af91556fcfba2af381539e285e368e2b0 0 NULL NULL NULL
sakila inventory 1 mysql-master InnoDB 4581 59c10c2dce34a5b5132abe984e52b0b8b09ff6f9 0 NULL NULL NULL
sakila language 1 mysql-master InnoDB 6 95ef63c60bdc024031c746232262f77ad7ead342 0 NULL NULL NULL
sakila payment 1 mysql-master InnoDB 16049 d63f0af5124c1598061e1d99af03785db0a600e3 1 NULL NULL NULL
sakila rental 1 mysql-master InnoDB 16044 047b7143e51a64348451dfe57d0c78c4da8950f9 1 NULL NULL NULL
sakila staff 1 mysql-master InnoDB 2 3c08fd6c4482c4e48c2c692c731e57f991e21089 0 NULL NULL NULL
sakila store 1 mysql-master InnoDB 2 9976f802fe471c5ac6c9c037a28557ff2b24da3f 0 NULL NULL NULL
On Slave:
mysql> use mysql
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| checksum |
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)
mysql> select * from checksum;
+--------+---------------------------+-------+------------+------------------------------------------+----------+------------------------------------------+------------+---------------------+
| db | tbl | chunk | boundaries | this_crc | this_cnt | master_crc | master_cnt | ts |
+--------+---------------------------+-------+------------+------------------------------------------+----------+------------------------------------------+------------+---------------------+
| mysql | columns_priv | 1 | 1=1 | | 0 | | 0 | 2007-09-08 02:44:28 |
| mysql | db | 1 | 1=1 | c62b1e99966f5652a478847b624df26b4dfd1377 | 2 | c62b1e99966f5652a478847b624df26b4dfd1377 | 2 | 2007-09-08 02:44:28 |
| mysql | event | 1 | 1=1 | | 0 | | 0 | 2007-09-08 02:44:28 |
| mysql | func | 1 | 1=1 | | 0 | | 0 | 2007-09-08 02:44:28 |
| mysql | help_category | 1 | 1=1 | 9ef62663c76f84a5bd7e875f65ff3f63c2994479 | 37 | 9ef62663c76f84a5bd7e875f65ff3f63c2994479 | 37 | 2007-09-08 02:44:28 |
| mysql | help_keyword | 1 | 1=1 | db02c9d0f5a3d3eab678af8cdbb557c84518b14c | 425 | db02c9d0f5a3d3eab678af8cdbb557c84518b14c | 425 | 2007-09-08 02:44:28 |
| mysql | help_relation | 1 | 1=1 | 56c6eaa49e593718a3e15ccb6a8f5b3178b52466 | 898 | 56c6eaa49e593718a3e15ccb6a8f5b3178b52466 | 898 | 2007-09-08 02:44:28 |
| mysql | help_topic | 1 | 1=1 | 0b15c5e97f5cec4b857550848ac5263170e3487e | 478 | 0b15c5e97f5cec4b857550848ac5263170e3487e | 478 | 2007-09-08 02:44:28 |
| mysql | host | 1 | 1=1 | | 0 | | 0 | 2007-09-08 02:44:28 |
| mysql | ndb_binlog_index | 1 | 1=1 | | 0 | | 0 | 2007-09-08 02:44:28 |
| mysql | plugin | 1 | 1=1 | | 0 | | 0 | 2007-09-08 02:44:28 |
| mysql | proc | 1 | 1=1 | 9f1b2aad83c5274e4d95881ea0e241933c141862 | 6 | 9f1b2aad83c5274e4d95881ea0e241933c141862 | 6 | 2007-09-08 02:44:28 |
| mysql | procs_priv | 1 | 1=1 | | 0 | | 0 | 2007-09-08 02:44:28 |
| mysql | servers | 1 | 1=1 | | 0 | | 0 | 2007-09-08 02:44:28 |
| mysql | tables_priv | 1 | 1=1 | | 0 | | 0 | 2007-09-08 02:44:28 |
| mysql | time_zone | 1 | 1=1 | | 0 | | 0 | 2007-09-08 02:44:28 |
| mysql | time_zone_leap_second | 1 | 1=1 | | 0 | | 0 | 2007-09-08 02:44:28 |
| mysql | time_zone_name | 1 | 1=1 | | 0 | | 0 | 2007-09-08 02:44:28 |
| mysql | time_zone_transition | 1 | 1=1 | | 0 | | 0 | 2007-09-08 02:44:28 |
| mysql | time_zone_transition_type | 1 | 1=1 | | 0 | | 0 | 2007-09-08 02:44:28 |
| mysql | user | 1 | 1=1 | 57c3425f2dcd64664840aa6e858916322fdf1cc0 | 6 | 57c3425f2dcd64664840aa6e858916322fdf1cc0 | 6 | 2007-09-08 02:44:28 |
| sakila | actor | 1 | 1=1 | e460daf0c941a35e07595f3b76686132d3994e49 | 200 | e460daf0c941a35e07595f3b76686132d3994e49 | 200 | 2007-09-08 02:44:28 |
| sakila | address | 1 | 1=1 | 175a2192effdad0b4a8c16ee5038de16e7b32223 | 603 | 175a2192effdad0b4a8c16ee5038de16e7b32223 | 603 | 2007-09-08 02:44:28 |
| sakila | category | 1 | 1=1 | 579c8b0ad142daf527bff6d52abbf904bb873985 | 16 | 579c8b0ad142daf527bff6d52abbf904bb873985 | 16 | 2007-09-08 02:44:28 |
| sakila | city | 1 | 1=1 | 3577100ea5241d5e6600c0f6ef0d31ce55787fa0 | 600 | 3577100ea5241d5e6600c0f6ef0d31ce55787fa0 | 600 | 2007-09-08 02:44:28 |
| sakila | country | 1 | 1=1 | 47146889e8990bc79d4b9aaf7344ffbf279eade1 | 109 | 47146889e8990bc79d4b9aaf7344ffbf279eade1 | 109 | 2007-09-08 02:44:28 |
| sakila | customer | 1 | 1=1 | 5109a39e50e0c29cee8b6805be3c8be2542bba74 | 599 | 5109a39e50e0c29cee8b6805be3c8be2542bba74 | 599 | 2007-09-08 02:44:28 |
| sakila | film | 1 | 1=1 | 3857ddc3f576d2246e35366b57bbf02d18455bac | 1000 | 3857ddc3f576d2246e35366b57bbf02d18455bac | 1000 | 2007-09-08 02:44:28 |
| sakila | film_actor | 1 | 1=1 | 4b2f6d38e7b75bab9cce775748c0a655643bdaad | 5462 | 4b2f6d38e7b75bab9cce775748c0a655643bdaad | 5462 | 2007-09-08 02:44:28 |
| sakila | film_category | 1 | 1=1 | 45a4418ebcc3a9c4808512414112abbe0f5e64e8 | 1000 | 45a4418ebcc3a9c4808512414112abbe0f5e64e8 | 1000 | 2007-09-08 02:44:28 |
| sakila | film_text | 1 | 1=1 | 355c6e8af91556fcfba2af381539e285e368e2b0 | 1000 | 355c6e8af91556fcfba2af381539e285e368e2b0 | 1000 | 2007-09-08 02:44:28 |
| sakila | inventory | 1 | 1=1 | 59c10c2dce34a5b5132abe984e52b0b8b09ff6f9 | 4581 | 59c10c2dce34a5b5132abe984e52b0b8b09ff6f9 | 4581 | 2007-09-08 02:44:28 |
| sakila | language | 1 | 1=1 | 95ef63c60bdc024031c746232262f77ad7ead342 | 6 | 95ef63c60bdc024031c746232262f77ad7ead342 | 6 | 2007-09-08 02:44:28 |
| sakila | payment | 1 | 1=1 | d63f0af5124c1598061e1d99af03785db0a600e3 | 16049 | d63f0af5124c1598061e1d99af03785db0a600e3 | 16049 | 2007-09-08 02:44:29 |
| sakila | rental | 1 | 1=1 | 047b7143e51a64348451dfe57d0c78c4da8950f9 | 16044 | 047b7143e51a64348451dfe57d0c78c4da8950f9 | 16044 | 2007-09-08 02:44:30 |
| sakila | staff | 1 | 1=1 | 3c08fd6c4482c4e48c2c692c731e57f991e21089 | 2 | 3c08fd6c4482c4e48c2c692c731e57f991e21089 | 2 | 2007-09-08 02:44:30 |
| sakila | store | 1 | 1=1 | 9976f802fe471c5ac6c9c037a28557ff2b24da3f | 2 | 9976f802fe471c5ac6c9c037a28557ff2b24da3f | 2 | 2007-09-08 02:44:30 |
+--------+---------------------------+-------+------------+------------------------------------------+----------+------------------------------------------+------------+---------------------+
37 rows in set (0.00 sec)
Adding a row to slave (sakila.country)
mysql> insert into country values (110,'WoopWoop',now());
Query OK, 1 row affected (0.01 sec)
mysql> select * from country order by country_id desc limit 2;
+------------+----------+---------------------+
| country_id | country | last_update |
+------------+----------+---------------------+
| 110 | WoopWoop | 2007-09-08 02:50:18 |
| 109 | Zambia | 2006-02-15 04:44:00 |
+------------+----------+---------------------+
2 rows in set (0.00 sec)
On master
mysql-table-checksum h=mysql-master,u=root,p=$MYSQLPASS h=mysql-slave --algorithm=BIT_XOR
DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
...
sakila country 1 mysql-master InnoDB 109 972AFF5C0601C571B80F895785FA6F70AAFE20EA 0 0 NULL NULL
sakila country 1 mysql-slave InnoDB 110 EC435FA369E1AEC29DB08161E7D09320451F3D88 0 0 NULL NULL
...
Running mysql-table-checlsum with --replcheck option
mysql-table-checksum h=mysql-master,u=root,p=$MYSQLPASS --replicate=mysql.checksum --emptyrepltbl --replcheck
Results on mysql-master (Host=mysql-master,Server_id=1)
[root@domU-12-31-36-00-00-42 sakila-db]# echo $?
0
Need to set report-host in slave /etc/my.cnf and restart slave
report-host='mysql-slave'
mysql> show slave hosts\G
*************************** 1. row ***************************
Server_id: 2
Host: mysql-slave
Port: 3306
Rpl_recovery_rank: 0
Master_id: 1
1 row in set (0.00 sec)
Still didn't find the row difference using --replcheck however using SQL on slave did
mysql-table-checksum h=mysql-master,u=root,p=$MYSQLPASS --replicate=mysql.checksum --emptyrepltbl --replcheck
Results on mysql-slave:3306 (Port=3306,Master_id=1,Host=mysql-slave,Server_id=2)
Results on mysql-master (Host=mysql-master,Server_id=1)
[root@domU-12-31-36-00-00-42 sakila-db]# echo $?
0
SQL to run to check for issues with checksums:
SELECT db, tbl, chunk, this_cnt-master_cnt AS cnt_diff,
this_crc master_crc OR ISNULL(master_crc) ISNULL(this_crc) AS crc_diff
FROM checksum
WHERE master_cnt this_cnt OR master_crc this_crc
OR ISNULL(master_crc) ISNULL(this_crc);
mysql> use mysql
Database changed
mysql> SELECT db, tbl, chunk, this_cnt-master_cnt AS cnt_diff,
-> this_crc master_crc OR ISNULL(master_crc) ISNULL(this_crc) AS crc_diff
-> FROM checksum
-> WHERE master_cnt this_cnt OR master_crc this_crc
-> OR ISNULL(master_crc) ISNULL(this_crc);
+--------+---------+-------+----------+----------+
| db | tbl | chunk | cnt_diff | crc_diff |
+--------+---------+-------+----------+----------+
| sakila | country | 1 | 1 | 1 |
+--------+---------+-------+----------+----------+
1 row in set (0.00 sec)
mysql> use test
Database changed
mysql> create table t1 (name varchar(25)) engine=InnoDB;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 values ('Fred');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values ('John');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values ('Dimitri');
Query OK, 1 row affected (0.01 sec)
On Slave
mysql> use test;
Database changed
mysql> select * from t1;
+---------+
| name |
+---------+
| Fred |
| John |
| Dimitri |
+---------+
3 rows in set (0.00 sec)
Stop slave;
mysql> delete from t1 where name = 'Fred';
Query OK, 1 row affected (0.01 sec)
mysql> set global sql_slave_skip_counter = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+---------+
| name |
+---------+
| Fred |
| John |
| Dimitri |
+---------+
3 rows in set (0.00 sec)
Run mysql-table-checksum again
mysql-table-checksum h=mysql-master,u=root,p=$MYSQLPASS --replicate=mysql.checksum --emptyrepltbl
mysql> SELECT db, tbl, chunk, this_cnt-master_cnt AS cnt_diff,
this_crc master_crc OR ISNULL(master_crc) ISNULL(this_crc) AS crc_diff
FROM checksum
WHERE master_cnt this_cnt
OR master_crc this_crc
OR ISNULL(master_crc) ISNULL(this_crc);
+------+-----+-------+----------+----------+
| db | tbl | chunk | cnt_diff | crc_diff |
+------+-----+-------+----------+----------+
| test | t1 | 1 | 1 | 1 |
+------+-----+-------+----------+----------+
1 row in set (0.00 sec)
DBA Dojo 