export/import:
$ mysqldump -h6dhdbm01 -p clee2 users > 6dhdbm01-clee2.users.sql
Enter password:
$ echo $?
0
$ mysql clee2 < 6dhdbm01-clee2.users.sql
$ echo $?
0
$
pt-table-checksum
:
$ pt-table-checksum --databases=clee2 --tables=users
Error setting innodb_lock_wait_timeout: DBD::mysql::db do failed: Variable 'innodb_lock_wait_timeout' is a read only variable [for Statement "SET SESSION innodb_lock_wait_timeout=1"]. The current value for innodb_lock_wait_timeout is 50. If the variable is read only (not dynamic), specify --set-vars innodb_lock_wait_timeout=50 to avoid this warning, else manually set the variable and restart MySQL.
Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
10-14T08:39:00 0 0 100679 6 0 1.717 clee2.users
$ pt-table-checksum --databases=clee2 --tables=users --ask-pass 6dhdbm01
Enter MySQL password:
Error setting innodb_lock_wait_timeout: DBD::mysql::db do failed: Variable 'innodb_lock_wait_timeout' is a read only variable [for Statement "SET SESSION innodb_lock_wait_timeout=1"], <STDIN> line 1. The current value for innodb_lock_wait_timeout is 120. If the variable is read only (not dynamic), specify --set-vars innodb_lock_wait_timeout=120 to avoid this warning, else manually set the variable and restart MySQL.
Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
10-14T08:39:12 0 0 100679 9 0 2.804 clee2.users
$
query:
$ mysql -e"SELECT * FROM percona.checksums WHERE db = 'clee2' AND tbl = 'users';"
+-------+-------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| db | tbl | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts |
+-------+-------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| clee2 | users | 1 | 0.071199 | PRIMARY | 0 | 1491 | b3c4d418 | 1000 | b3c4d418 | 1000 | 2014-10-14 08:38:58 |
| clee2 | users | 2 | 0.089819 | PRIMARY | 1492 | 9015 | b50c2d1b | 7022 | b50c2d1b | 7022 | 2014-10-14 08:38:59 |
| clee2 | users | 3 | 0.13534 | PRIMARY | 9016 | 38573 | b8bcb3ec | 27133 | b8bcb3ec | 27133 | 2014-10-14 08:38:59 |
| clee2 | users | 4 | 0.221181 | PRIMARY | 38574 | 108035 | 239edb5d | 65524 | 239edb5d | 65524 | 2014-10-14 08:38:59 |
| clee2 | users | 5 | 0.066098 | PRIMARY | NULL | 0 | 0 | 0 | 0 | 0 | 2014-10-14 08:38:59 |
| clee2 | users | 6 | 0.06616 | PRIMARY | 108035 | NULL | 0 | 0 | 0 | 0 | 2014-10-14 08:39:00 |
+-------+-------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
$ mysql -h6dhdbm01 -p -e"SELECT * FROM percona.checksums WHERE db = 'clee2' AND tbl = 'users';"
Enter password:
+-------+-------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| db | tbl | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts |
+-------+-------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| clee2 | users | 1 | 0.083038 | PRIMARY | 0 | 1491 | b3c4d418 | 1000 | b3c4d418 | 1000 | 2014-10-14 08:39:09 |
| clee2 | users | 2 | 0.228184 | PRIMARY | 1492 | 7932 | 26fd4c48 | 6021 | 26fd4c48 | 6021 | 2014-10-14 08:39:10 |
| clee2 | users | 3 | 0.249667 | PRIMARY | 7933 | 20618 | 89d9fbdb | 11655 | 89d9fbdb | 11655 | 2014-10-14 08:39:10 |
| clee2 | users | 4 | 0.383064 | PRIMARY | 20619 | 40104 | 17c8b21e | 17895 | 17c8b21e | 17895 | 2014-10-14 08:39:10 |
| clee2 | users | 5 | 0.42216 | PRIMARY | 40105 | 63159 | 1beb0525 | 20747 | 1beb0525 | 20747 | 2014-10-14 08:39:11 |
| clee2 | users | 6 | 0.466877 | PRIMARY | 63160 | 86949 | 41efafd2 | 22408 | 41efafd2 | 22408 | 2014-10-14 08:39:11 |
| clee2 | users | 7 | 0.432634 | PRIMARY | 86950 | 108035 | ccc6ead0 | 20953 | ccc6ead0 | 20953 | 2014-10-14 08:39:12 |
| clee2 | users | 8 | 0.033889 | PRIMARY | NULL | 0 | 0 | 0 | 0 | 0 | 2014-10-14 08:39:12 |
| clee2 | users | 9 | 0.035318 | PRIMARY | 108035 | NULL | 0 | 0 | 0 | 0 | 2014-10-14 08:39:12 |
+-------+-------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
$
How can I force pt-table-checksum
to have SINGLE chunk vs multiple? or at least same chunks, so I can actually compare.
Best Answer
That means that you are using the tool incorrectly -a single run must be done so that it creates checksums that are eventually consistent for the master and the slave.
Run
pt-table-checksum
only directed to the master, and create the appropriate permissions so that it can check the slave, too. Maybe it is a problem of authorization, maybe permissions (the lack of it), maybe the unproper slave detection (and you can change the detection method), but with that output, you are not using the tool in the documented way.The different table checksums suggests that you are doing independent checksums on each server, or that they are not being performed correctly. You may still have schema differences, as it seems that one is using the primary key, while the other is not (hence the different chunks).