MySQLtoolkit: mysql-table-checksum

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)

Future Posting Roadmap: 2007

Posting has been lighter than normal, as I have had the flu and it is footy finals time in Australia.

However I sat down the other day and thought about which areas I hope to cover over the next couple of months.
Given this is a blog and comments are on, if you want something covered which is not on the list feel free to add a comment with a request.

Topics:

Replication:

1. Determine the IO/sec where the slave starts falling behind.
2. Test mysqlslap running write workload on master and read workload on slave.
3. Test running mysqlslap 3rd machine and connection to master and slave.
4. Test running mysqlslap on replication with auto-increment column so show the effect of increment locking on master and slave.
5. mysqlslap vs MySQL 5.1 row based replication
   

The end result is a performance report which answers the following questions:

1. how many concurrent users causes the slave to fall behind
2. When does network bandwidth become an issue. (test simple scp to get raw bandwidth)
3. how does reading from the slave affect the slave's ability to keep up.

MySQL Cluster:

1. mysqlslap vs MySQL 5.1 Cluster
2. running ndb Sizing script against normal database
3. mysqlslap vs MySQL 5.1 Cluster Replication
4. benchmarking and performance.
   

MySQL Toolkit and related:

1. One article per mysqltoolkit tool.
2. Article on using innotop

Misc:

1. Testing mysql partitioning, and maintenance tasks.
2. How to design your database for your application (using load tables, federated storage)
3. Using tmpdir as /dev/shm to help with temp table issues.
4. Point in Time recovery with MySQL. Using slave (start slave until)
5. Backups using LVM. => Done
6. innodb_flush_method=O_DIRECT. Reason: see what the difference is, on a standalone instance and replication.

PostgreSQL:

1. performance
2. clustering options for redundancy

Sharding:

1. Using MySql
2. using OracleXE
3. using PostgreSQL
4. using Hibernate

Have Fun

Paul

MySQL Backups using LVM snapshots

There are a couple of options available to get consistent backups from MySQL.

1. Use mysqldump with FLUSH TABLES WITH READ LOCK
2. Use a slave with STOP SLAVE and your favourite backup tool.
   
3. For innodb, use the commerical backup tool ibbackup
4. Use LVM (Logical Volume Manager) snapshots with FLUSH TABLES WITH READ LOCK
5. Shutdown the database.

We are going to demostrate how to use LVM snapshots to reduce time required to hold MySQL consistent. I used these two good articles here and here as a basis for this one.

To use LVM on EC2, you need to umount the /mnt partition and create a physical volume (pvcreate) and then a bunch of logical volumes (lvcreate). Once you have the use of LVM, you can use lvcreate -s to create a snapshot of the logical volume.

The ease of taking backups like this means the time that the mysql database must be unable to handle writes (due to the read lock) is as short as the time takes for the lvcreate to finish.

As normal I have provided a cleared up screen dump of my terminal at the end of this post.

I was able to successfully take a backup, and tar the snapshot and create a slave from the backup.

Here is the procedure to take a backup using LVM.

1. mysql connection (left open): FLUSH TABLES WITH READ LOCK;
2. lvcreate -L16G -s -n dbbackup /dev/vg/myvmdisk1
3. mysql connection: UNLOCK TABLES;

Notes:

• You must wait for the FLUSH TABLES to finish, otherwise your backup will NOT be consistent. Peter Zaitsev mentions flushing individual tables can help the speed, followed by a final FLUSH TABLES
• Leave the mysql connection open, so the READ LOCK is held until you issue the UNLOCK
• lvcreate -s can fail with an error snapshot: Required device-mapper target(s) not detected in your kernel The solution: modprobe dm-snapshot
  

There is a perl script from Lenz Grimmer called mylvmbackup which I will check out soon.

Have Fun

Paul

This script with create two logical volumes /data1/mysql, /data2/mysql both 30 Gigabytes in size

###  BEGIN OF SCRIPT ###

#!/bin/sh
#
# Script to make EC2 /mnt into a LVM volume

umount /mnt
pvcreate /dev/sda2
vgcreate vg /dev/sda2
lvcreate -L30720M -n myvmdisk1 vg
mkfs -t ext3 /dev/vg/myvmdisk1
mkdir -p /data1/mysql/data
mount /dev/vg/myvmdisk1 /data1/mysql

lvcreate -L30720M -n myvmdisk2 vg
mkfs -t ext3 /dev/vg/myvmdisk2
mkdir -p /data2/mysql/data
mount /dev/vg/myvmdisk2 /data2/mysql

lvcreate -L30720M -n myvmdisk3 vg
mkfs -t ext3 /dev/vg/myvmdisk3
mkdir -p /backup/mysql/data
mount /dev/vg/myvmdisk3 /backup/mysql

lvdisplay

mkdir -p /data1/mysql/data
mkdir -p /data2/mysql/data

chown -R mysql:mysql /data1/mysql
chown -R mysql:mysql /data2/mysql
chown -R mysql:mysql /backup/mysql

### END OF SCRIPT ###

Do the snapshot backup

Flush the tables with read lock to dump to disk and get consistent state of data.

mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.20-beta-log

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.01 sec)

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

Try the create the snapshot...

lvcreate -L16G -s -n dbbackup /dev/vg/myvmdisk1
snapshot: Required device-mapper target(s) not detected in your kernel
lvcreate: Create a logical volume

modprobe dm-snapshot

lvcreate -L16G -s -n dbbackup /dev/vg/myvmdisk1
Logical volume "dbbackup" created

Check what logical volumes are available

lvdisplay
--- Logical volume ---
LV Name                /dev/vg/myvmdisk1
VG Name                vg
LV UUID                1IpGD0-c3e0-DauB-Xj8p-AZAk-VpNG-RRDbUP
LV Write Access        read/write
LV snapshot status     source of
                       /dev/vg/dbbackup [active]
LV Status              available
# open                 1
LV Size                30.00 GB
Current LE             7680
Segments               1
Allocation             inherit
Read ahead sectors     0
Block device           253:0

--- Logical volume ---
LV Name                /dev/vg/myvmdisk2
VG Name                vg
LV UUID                bIz7vN-zWy8-PT3N-j1GT-URwi-RN8p-o6eRFQ
LV Write Access        read/write
LV Status              available
# open                 1
LV Size                30.00 GB
Current LE             7680
Segments               1
Allocation             inherit
Read ahead sectors     0
Block device           253:1

--- Logical volume ---
LV Name                /dev/vg/dbbackup
VG Name                vg
LV UUID                c4GXbG-3dpv-7zEI-sm8r-eLFq-t3Ud-XdQHAx
LV Write Access        read/write
LV snapshot status     active destination for /dev/vg/myvmdisk1
LV Status              available
# open                 0
LV Size                30.00 GB
Current LE             7680
COW-table size         16.00 GB
COW-table LE           4096
Allocated to snapshot  0.00%
Snapshot chunk size    8.00 KB
Segments               1
Allocation             inherit
Read ahead sectors     0
Block device           253:4


mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |  3840558 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)


mkdir /mnt/backup
mount /dev/vg/dbbackup /mnt/backup

df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             7.9G  1.3G  6.3G  17% /
/dev/mapper/vg-myvmdisk1
                     30G  3.6G   25G  13% /data1/mysql
/dev/mapper/vg-myvmdisk2
                     30G   77M   28G   1% /data2/mysql
/dev/mapper/vg-dbbackup
                     30G  3.6G   25G  13% /mnt/backup

Make a tarball of /mnt/backup/data

cd /mnt/backup
tar -czvf mysql_backup_`date +%Y%m%d`.tar.gz data/ /etc/my.cnf

mysqlslap proves Innodb auto increment limits scalability

I was testing some mysqlslap runs which Brian 'Krow' Aker was running testing the effect of changing the commit interval. Whilst I was doing that I noticed that he had added the option to add an auto incrementing column to the generated table.

Having read elsewhere here and here that the way innodb handles auto incrementing columns can cause issues, I thought I would test that out. And as the picture shows, it certainly does limit scalability. Here is the mysql documentation on the subject of auto incrementing columns.

That is not the end of the story for auto incrementing columns, if you are looking at replication, in particular master-master replication you have to be very careful how to you handle them.
Auto incrementing and Replication
Auto increment increment and offset

Plus if you are looking at scaling out rather than scaling up, having natural primary keys rather than synthetic primary keys (read primary keys based on sequences or auto incrementing numbers) moving data between shards with natural keys will be a lot easier. Much less work required to prove the ID column in one shard is unique across all shards.

Have Fun

Paul

Commands:

mysqlslap --concurrency="1,25,50,75,100" --iterations=10 --number-int-cols=2 --number-char-cols=3 \
--auto-generate-sql --csv=/tmp/innodb-write-scale.csv \
--engine=$ENGINE --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=write \
--auto-generate-sql-execute-number=500

mysqlslap --concurrency="1,25,50,75,100" --iterations=10 \
--number-int-cols=2 --number-char-cols=3 --auto-generate-sql \
--csv=/tmp/test_concurr_noincr.csv --engine=innodb \
--auto-generate-sql-load-type=write --auto-generate-sql-execute-number=500 \
--user=root --password

Results:

cat /tmp/innodb-write-scale.csv

innodb,write,0.178,0.128,0.369,1,500
innodb,write,6.106,5.846,6.546,25,500
innodb,write,13.481,13.196,13.737,50,500
innodb,write,24.853,22.928,38.379,75,500
innodb,write,39.932,39.283,41.079,100,500

cat /tmp/test_concurr_noincr.csv

innodb,write,0.178,0.125,0.255,1,500
innodb,write,5.111,4.765,5.503,25,500
innodb,write,10.590,10.052,11.234,50,500
innodb,write,16.091,15.312,16.820,75,500
innodb,write,22.645,21.720,23.698,100,500