出口进口:
$ 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
$
询问:
$ 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 |
+-------+-------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
$
我如何强制pt-table-checksum
拥有单个块与多个块?或至少相同的块,所以我可以实际比较。
这意味着您错误地使用了该工具 - 必须完成一次运行才能创建最终对主从服务器一致的校验和。
只针对主控运行
pt-table-checksum
,并创建适当的权限,以便它也可以检查从属。也许是授权问题,也许是权限问题(缺少权限),也许是不正确的从属检测(并且您可以更改检测方法),但是对于该输出,您没有以记录的方式使用该工具。不同的表校验和表明您在每个服务器上执行独立的校验和,或者它们没有正确执行。您可能仍然存在架构差异,因为看起来一个正在使用主键,而另一个没有(因此不同的块)。