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)

Advertisements

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

MySQL DBT2 Benchmark on EC2 part 1

In the last couple of articles I have been using the load simulator/generator tool provided with the MySQL 5.1 install called mysqlslap.

I read around on some other blogs and thought it might be also useful to use a benchmarking tool. DBT2 is a TPC-C like benchmark tool provided by OSDL. You can download the software from the DBT sourceforge site.

The TPC-C is a online transaction test benchmark and the overall measure is in Transactions per Minute on TPM. The higher the better.

I chose to use the DBT2 benchmark as this is the tool that MySQL AB themselves have used to benchmark their MySQL Cluster. I also found plenty of useful information of Peter Zaitsev’s MySQLPerformance blog, specifically this presentation (PDF).

On with the show, once again, we need to test a plain standalone MySQL database. Once that is done we can build on that to test MySQL replication (Master-Slave) and MySQL NDB Clusters.

The documentation for the tool is essentially the README and README-MYSQL files in the tarball, the README mentioned a user manual which unfortunately I couldn’t find anywhere.
There is scant mention of prerequisites either, hopefully this article will help fill the void for a while.
I will probably need to create a proper HOWTO document for this as there were a bunch of conflicting information in the README files which was different to the actually files available.

Prerequisites:

  1. Perl 5.8
  2. Perl Modules: Chart::Graph::Gnuplot, Test::Parser, Test::Reporter, XML::Simple, XML::Twig
  3. Linux packages: gcc gnuplot sysstat
  4. MySQL 5.0 for stored procedures.

Comments:

  • I used CentOS 4.4 again as the base linux distro and added the required packages.
  • use CPAN to install the perl modules as it will handle any dependencies.
  • Some of the names are different from the README files.
  • The TPM results for 20 warehouses and 20 concurrent sessions scaled ok based on the number of threads (Terminal Threads). The lower the number of threads the lower the TPM.
  • For the most part the benchmark was constrained by IO waits. I was using the /mnt mountpoint on EC2, and whilst a test write of a 100M file using dd if=/dev/zero of=/mnt/data/test1 count=1 bs=100M was quick, the random nature of the IO was a killer.
  • Innodb logfiles where separated from the ibdata file.

Results:

  • Using an optimized my.cnf based on my-heavy-innodb-4G.cnf file (see listing below)
  • Data generated 20 warehouses which equated to a 3 Gigabyte database
  • Benchmark settings of 100 terminal threads, 20 concurrent sessions, 20 warehouses
  • Duration of 30 minutes.
  • 2016.02 TPM (transactions per minute)

As I have time I will try and replicate the size (200 warehouses) of Peter Zaitsev’s article, however the data generation step took a fair amount of time.

Have Fun

Paul



Get and install the prerequisites


cd /mnt
wget http://optusnet.dl.sourceforge.net/sourceforge/osdldbt/dbt2-0.40.tar.gz
tar -xzvf dbt2-0.40.tar.gz
yum install gnuplot gcc sysstat
cd dbt2-0.40
./configure --with-mysql --with-mysql-libs=/usr/local/mysql/lib/ \
--with-mysql-includes=/usr/local/mysql/includes
make

Generate the 20 warehouse dataset

mkdir -p /mnt/data
src/datagen -w 20 -d /mnt/data --mysql

warehouses = 20
districts = 10
customers = 3000
items = 100000
orders = 3000
stock = 100000
new_orders = 900

Output directory of data files: /mnt/data

Generating data files for 20 warehouse(s)...
Generating item table data...
Finished item table data...
Generating warehouse table data...
Finished warehouse table data...
Generating stock table data...
Finished stock table data...
Generating district table data...
Finished district table data...
Generating customer table data...
Finished customer table data...
Generating history table data...
Finished history table data...
Generating order and order-line table data...

Finished order and order-line table data...
Generating new-order table data...
Finished new-order table data...

Load the dataset after adding an additional index as suggested by Zaitsev presentation

cd /mnt/dbt2-0.40/scripts/mysql

vi build_db.sh

Modified the CREATE TABLE add index to NEW_ORDER table

NEW_ORDER="CREATE TABLE new_order (
no_o_id int(11) NOT NULL default '0',
no_d_id int(11) NOT NULL default '0',
no_w_id int(11) NOT NULL default '0',
PRIMARY KEY (no_d_id,no_w_id,no_o_id),
KEY ix_no_wid_did (no_w_id,no_d_id)
)"

Load the dataset

sh build_db.sh -d dbt2 -f /mnt/data -s /tmp/mysql.sock -u root -p $MYSQLPASS

Loading of DBT2 dataset located in /mnt/data to database dbt2.

DB_ENGINE: INNODB
DB_SCHEME: OPTIMIZED
DB_HOST: localhost
DB_USER: root
DB_SOCKET: /tmp/mysql.sock

Creating table STOCK
Creating table ITEM
Creating table ORDER_LINE
Creating table ORDERS
Creating table NEW_ORDER
Creating table HISTORY
Creating table CUSTOMER
Creating table DISTRICT
Creating table WAREHOUSE

Loading table customer
Loading table district
Loading table history
Loading table item
Loading table new_order
Loading table order_line
Loading table orders
Loading table stock
Loading table warehouse

Edit the MySQL stored procedures to fix delimiter (replacing |; with |)

cd /mnt/dbt2-0.40/storedproc/mysql
sed -i -e 's/|\;/|/' *.sql
mysql -u root -p$MYSQLPASS -D dbt2 &lt new_order.sql
mysql -u root -p$MYSQLPASS -D dbt2 &lt new_order_2.sql
mysql -u root -p$MYSQLPASS -D dbt2 &lt order_status.sql
mysql -u root -p$MYSQLPASS -D dbt2 &lt payment.sql
mysql -u root -p$MYSQLPASS -D dbt2 &lt stock_level.sql

Check that MySQL is ready to go

mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use dbt2
Database changed
mysql> show tables;
+----------------+
| Tables_in_dbt2 |
+----------------+
| customer |
| district |
| history |
| item |
| new_order |
| order_line |
| orders |
| stock |
| warehouse |
+----------------+
9 rows in set (0.00 sec)

mysql> show table status
-> ;
+------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------+
| customer | InnoDB | 10 | Compact | 603562 | 665 | 401604608 | 0 | 46907392 | 0 | NULL | 2007-08-31 03:18:15 | NULL | NULL | latin1_swedish_ci | NULL | | InnoDB free: 229376 kB |
| district | InnoDB | 10 | Compact | 43 | 1524 | 65536 | 0 | 0 | 0 | NULL | 2007-08-31 03:18:15 | NULL | NULL | latin1_swedish_ci | NULL | | InnoDB free: 229376 kB |
| history | InnoDB | 10 | Compact | 600452 | 83 | 49889280 | 0 | 0 | 0 | NULL | 2007-08-31 03:18:15 | NULL | NULL | latin1_swedish_ci | NULL | | InnoDB free: 229376 kB |
| item | InnoDB | 10 | Compact | 100160 | 110 | 11026432 | 0 | 0 | 0 | NULL | 2007-08-31 03:18:15 | NULL | NULL | latin1_swedish_ci | NULL | | InnoDB free: 229376 kB |
| new_order | InnoDB | 10 | Compact | 152246 | 51 | 7880704 | 0 | 3686400 | 0 | NULL | 2007-08-31 03:18:15 | NULL | NULL | latin1_swedish_ci | NULL | | InnoDB free: 229376 kB |
| order_line | InnoDB | 10 | Compact | 5697509 | 95 | 545259520 | 0 | 0 | 0 | NULL | 2007-08-31 03:18:15 | NULL | NULL | latin1_swedish_ci | NULL | | InnoDB free: 229376 kB |
| orders | InnoDB | 10 | Compact | 600343 | 60 | 36257792 | 0 | 27885568 | 0 | NULL | 2007-08-31 03:18:15 | NULL | NULL | latin1_swedish_ci | NULL | | InnoDB free: 229376 kB |
| stock | InnoDB | 10 | Compact | 2000025 | 382 | 764411904 | 0 | 0 | 0 | NULL | 2007-08-31 03:18:15 | NULL | NULL | latin1_swedish_ci | NULL | | InnoDB free: 229376 kB |
| warehouse | InnoDB | 10 | Compact | 20 | 819 | 16384 | 0 | 0 | 0 | NULL | 2007-08-31 03:18:15 | NULL | NULL | latin1_swedish_ci | NULL | | InnoDB free: 229376 kB |
+------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+------------------------+
9 rows in set (0.77 sec)

mysql> SHOW PROCEDURE STATUS
-> ;
+------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment |
+------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+
| dbt2 | delivery | PROCEDURE | root@localhost | 2007-08-30 21:21:11 | 2007-08-30 21:21:11 | DEFINER | |
| dbt2 | new_order | PROCEDURE | root@localhost | 2007-08-30 21:22:33 | 2007-08-30 21:22:33 | DEFINER | |
| dbt2 | new_order_2 | PROCEDURE | root@localhost | 2007-08-30 21:22:38 | 2007-08-30 21:22:38 | DEFINER | |
| dbt2 | order_status | PROCEDURE | root@localhost | 2007-08-30 21:22:44 | 2007-08-30 21:22:44 | DEFINER | |
| dbt2 | payment | PROCEDURE | root@localhost | 2007-08-30 21:22:51 | 2007-08-30 21:22:51 | DEFINER | |
| dbt2 | stock_level | PROCEDURE | root@localhost | 2007-08-30 21:22:56 | 2007-08-30 21:22:56 | DEFINER | |
+------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+
6 rows in set (0.00 sec)

Listing of my.cnf

grep -v "#" /etc/my.cnf|sed -e '/^$/d'

[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
back_log = 50
max_connections = 200
max_connect_errors = 10
table_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default_table_type =myISAM
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysql-bin
log_slow_queries
long_query_time = 2
log_long_format
server-id = 1
key_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 1G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_data_home_dir =/mnt/mysql/data/
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192

Benchmark runs:

sh run_workload.sh -c 20 -t 20 -d 300 -w 20 -u root -x $MYSQLPASS

MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
/mnt/dbt2-0.40/scripts/mysql/start_db.sh: illegal option -- p
************************************************************************
* DBT-2 test for mysql started
* *
* Results can be found in output/16 directory
************************************************************************
* *
* Test consists of 3 stages: *
* *
* 1. Start of client to create pool of databases connections *
* 2. Start of driver to emulate terminals and transactions generation *
* 3. Processing of results *
* *
************************************************************************

DATABASE SYSTEM: localhost
DATABASE NAME: dbt2
DATABASE USER: root
DATABASE PASSWORD: *******
DATABASE CONNECTIONS: 20
TERMINAL THREADS: 400
TERMINALS PER WAREHOUSE: 20
SCALE FACTOR(WAREHOUSES): 20
DURATION OF TEST (in sec): 300
1 client stared every 1000 millisecond(s)

Stage 1. Starting up client...
Sleeping 21 seconds

Stage 2. Starting up driver...
1000 threads started per millisecond
estimated rampup time: Sleeping 210 seconds
estimated rampup time has elapsed
estimated steady state time: Sleeping 300 seconds

Stage 3. Processing of results...
Killing client...
MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
run_workload.sh: line 548: [: -eq: unary operator expected
run_workload.sh: line 498: 10227 Terminated ${abs_top_srcdir}/src/driver ${DRIVER_COMMAND_ARGS} >${OUTPUT_DIR}/driver.out 2>&1
run_workload.sh: line 461: 10185 Terminated ${abs_top_srcdir}/src/client ${CLIENT_COMMAND_ARGS} >${OUTPUT_DIR}/client.out 2>&1
chmod: cannot access `/mnt/dbt2-0.40/scripts/output/16/db/log': No such file or directory
Test completed.
Results are in: /mnt/dbt2-0.40/scripts/output/16

Response Time (s)
Transaction % Average : 90th % Total Rollbacks %
------------ ----- --------------------- ----------- --------------- -----
Delivery 3.53 0.263 : 0.533 71 0 0.00
New Order 45.58 0.028 : 0.076 918 10 1.10
Order Status 3.67 0.071 : 0.173 74 0 0.00
Payment 41.46 0.035 : 0.102 835 0 0.00
Stock Level 5.76 0.003 : 0.004 116 37 46.84
------------ ----- --------------------- ----------- --------------- -----

503.55 new-order transactions per minute (NOTPM)
1.8 minute duration
0 total unknown errors
400 second(s) ramping up

sh run_workload.sh -c 20 -t 10 -d 900 -w 20 -u root -x $MYSQLPASS

MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
/mnt/dbt2-0.40/scripts/mysql/start_db.sh: illegal option -- p
************************************************************************
* DBT-2 test for mysql started
* *
* Results can be found in output/18 directory
************************************************************************
* *
* Test consists of 3 stages: *
* *
* 1. Start of client to create pool of databases connections *
* 2. Start of driver to emulate terminals and transactions generation *
* 3. Processing of results *
* *
************************************************************************

DATABASE SYSTEM: localhost
DATABASE NAME: dbt2
DATABASE USER: root
DATABASE PASSWORD: *******
DATABASE CONNECTIONS: 20
TERMINAL THREADS: 200
TERMINALS PER WAREHOUSE: 10
SCALE FACTOR(WAREHOUSES): 20
DURATION OF TEST (in sec): 900
1 client stared every 1000 millisecond(s)

Stage 1. Starting up client...
Sleeping 21 seconds

Stage 2. Starting up driver...
1000 threads started per millisecond
estimated rampup time: Sleeping 210 seconds
estimated rampup time has elapsed
estimated steady state time: Sleeping 900 seconds

Stage 3. Processing of results...
Killing client...
run_workload.sh: line 461: 13344 Terminated ${abs_top_srcdir}/src/client ${CLIENT_COMMAND_ARGS} >${OUTPUT_DIR}/client.out 2>&1
run_workload.sh: line 498: 13382 Terminated ${abs_top_srcdir}/src/driver ${DRIVER_COMMAND_ARGS} >${OUTPUT_DIR}/driver.out 2>&1
MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
run_workload.sh: line 548: [: -eq: unary operator expected
chmod: cannot access `/mnt/dbt2-0.40/scripts/output/18/db/log': No such file or directory
Test completed.
Results are in: /mnt/dbt2-0.40/scripts/output/18

1188549933
8597
Response Time (s)
Transaction % Average : 90th % Total Rollbacks %
------------ ----- --------------------- ----------- --------------- -----
Delivery 3.99 0.117 : 0.227 344 0 0.00
New Order 45.19 0.041 : 0.086 3900 33 0.85
Order Status 3.90 0.042 : 0.106 337 0 0.00
Payment 42.79 0.013 : 0.023 3693 0 0.00
Stock Level 4.13 0.003 : 0.003 356 0 0.00
------------ ----- --------------------- ----------- --------------- -----

254.19 new-order transactions per minute (NOTPM)
15.1 minute duration
0 total unknown errors
199 second(s) ramping up


sh run_workload.sh -c 20 -t 5 -d 900 -w 20 -u root -x $MYSQLPASS

MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
/mnt/dbt2-0.40/scripts/mysql/start_db.sh: illegal option -- p
************************************************************************
* DBT-2 test for mysql started
* *
* Results can be found in output/19 directory
************************************************************************
* *
* Test consists of 3 stages: *
* *
* 1. Start of client to create pool of databases connections *
* 2. Start of driver to emulate terminals and transactions generation *
* 3. Processing of results *
* *
************************************************************************

DATABASE SYSTEM: localhost
DATABASE NAME: dbt2
DATABASE USER: root
DATABASE PASSWORD: *******
DATABASE CONNECTIONS: 20
TERMINAL THREADS: 100
TERMINALS PER WAREHOUSE: 5
SCALE FACTOR(WAREHOUSES): 20
DURATION OF TEST (in sec): 900
1 client stared every 1000 millisecond(s)

Stage 1. Starting up client...
Sleeping 21 seconds

Stage 2. Starting up driver...
1000 threads started per millisecond
estimated rampup time: Sleeping 210 seconds
estimated rampup time has elapsed
estimated steady state time: Sleeping 900 seconds

Stage 3. Processing of results...
Killing client...
run_workload.sh: line 461: 14070 Terminated ${abs_top_srcdir}/src/client ${CLIENT_COMMAND_ARGS} >${OUTPUT_DIR}/client.out 2>&1
MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
run_workload.sh: line 548: [: -eq: unary operator expected
chmod: cannot access `/mnt/dbt2-0.40/scripts/output/19/db/log': No such file or directory
Test completed.
Results are in: /mnt/dbt2-0.40/scripts/output/19

1188551055
4260
Response Time (s)
Transaction % Average : 90th % Total Rollbacks %
------------ ----- --------------------- ----------- --------------- -----
Delivery 3.81 0.085 : 0.174 163 0 0.00
New Order 47.02 0.030 : 0.080 2011 17 0.85
Order Status 3.95 0.038 : 0.113 169 0 0.00
Payment 41.55 0.010 : 0.021 1777 0 0.00
Stock Level 3.67 0.002 : 0.003 157 0 0.00
------------ ----- --------------------- ----------- --------------- -----

130.21 new-order transactions per minute (NOTPM)
15.2 minute duration
0 total unknown errors
98 second(s) ramping up

Adjust ulimit and filesize

sh run_workload.sh -c 20 -t 200 -d 300 -w 20 -u root -x $MYSQLPASS

error: you're open files ulimit is too small, must be at least 8020

ulimit -a

core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) 10000
pending signals (-i) 13664
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 13664
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

ulimit -n 9000
ulimit -a

core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) 10000
pending signals (-i) 13664
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 9000
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 13664
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

sh run_workload.sh -c 20 -t 100 -d 3600 -w 20 -u root -x $MYSQLPASS

MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
/mnt/dbt2-0.40/scripts/mysql/start_db.sh: illegal option -- p
************************************************************************
* DBT-2 test for mysql started
* *
* Results can be found in output/20 directory
************************************************************************
* *
* Test consists of 3 stages: *
* *
* 1. Start of client to create pool of databases connections *
* 2. Start of driver to emulate terminals and transactions generation *
* 3. Processing of results *
* *
************************************************************************

DATABASE SYSTEM: localhost
DATABASE NAME: dbt2
DATABASE USER: root
DATABASE PASSWORD: *******
DATABASE CONNECTIONS: 20
TERMINAL THREADS: 2000
TERMINALS PER WAREHOUSE: 100
SCALE FACTOR(WAREHOUSES): 20
DURATION OF TEST (in sec): 3600
1 client stared every 1000 millisecond(s)

Stage 1. Starting up client...
Sleeping 21 seconds

Stage 2. Starting up driver...
1000 threads started per millisecond
estimated rampup time: Sleeping 210 seconds
estimated rampup time has elapsed
estimated steady state time: Sleeping 3600 seconds

Stage 3. Processing of results...
Killing client...
run_workload.sh: line 461: 14572 Terminated ${abs_top_srcdir}/src/client ${CLIENT_COMMAND_ARGS} >${OUTPUT_DIR}/client.out 2>&1
run_workload.sh: line 498: 14610 Terminated ${abs_top_srcdir}/src/driver ${DRIVER_COMMAND_ARGS} >${OUTPUT_DIR}/driver.out 2>&1
MySQL pid file 'yes/var/localhost.pid' does not exist.
MySQL was not stopped, if it was running.
run_workload.sh: line 548: [: -eq: unary operator expected
run_workload.sh: line 554: 18714 File size limit exceeded${abs_top_srcdir}/scripts/post-process --dir ${OUTPUT_DIR} --xml >${DRIVER_OUTPUT_DIR}/results.out
chmod: cannot access `/mnt/dbt2-0.40/scripts/output/20/db/log': No such file or directory
Test completed.
Results are in: /mnt/dbt2-0.40/scripts/output/20

Run the post-process step manually

./post-process --dir /mnt/dbt2-0.40/scripts/output/20 --xml

Use of uninitialized value at /usr/lib/perl5/site_perl/5.8.5/Test/Parser/Dbt2.pm line
Response Time (s)
Transaction % Average : 90th % Total Rollbacks %
------------ ----- --------------------- ----------- --------------- -----
Delivery 3.79 5.578 : 5.167 5309 0 0.00
New Order 43.84 6.023 : 5.492 61347 618 1.02
Order Status 3.74 5.270 : 5.021 5240 0 0.00
Payment 41.30 5.276 : 5.005 57799 1 0.00
Stock Level 7.33 5.004 : 5.513 10252 4883 90.95
------------ ----- --------------------- ----------- --------------- -----

2016.02 new-order transactions per minute (NOTPM)
29.8 minute duration
0 total unknown errors
2015 second(s) ramping up