pt-table-checksum - 驗證 MySQL 複製完整性

進出口：

$ 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，並創建適當的權限，以便它也可以檢查從伺服器。可能是授權問題，可能是權限問題（缺少權限），可能是不正確的從屬檢測（您可以更改檢測方法），但是使用該輸出，您並沒有以記錄的方式使用該工具。

不同的表校驗和表明您在每台伺服器上執行獨立的校驗和，或者它們沒有正確執行。您可能仍然存在架構差異，因為似乎一個正在使用主鍵，而另一個沒有（因此不同的塊）。

引用自：https://dba.stackexchange.com/questions/78924