1

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.

  • Are you saying that you are running pt-table-checksum twice, once on the master and another time on the slave? – jynus Oct 10 '14 at 20:10
  • @jynus, sorry, i corrected my question. I hope it's clear now) – alexus Oct 11 '14 at 14:33
  • My question still stands. Do you run it on the master only or twice, once on the master and a second time on the slave? – jynus Oct 11 '14 at 17:38
  • I ran pt-table-checksum once on both nodes (master AND slave). – alexus Oct 11 '14 at 18:10
  • please provide the full command line and (relevant) output – jynus Oct 11 '14 at 18:19
2

"Diffs cannot be detected because no slaves were found."

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

  • while this very well may be correct answer, i can't find anywhere in documentation -a single run. – alexus Oct 14 '14 at 12:16
  • it is up there, pt-table-checksum, but you need the right permissions to access on the master and on the slave. Maybe you also need to change the access string: pt-table-checksum h=host,u=user,p=password or change the --recursion-method. I agree that the documentation is not great, though. – jynus Oct 14 '14 at 12:43
  • sorry, but "it is up there" is not enough, i search it and i cannot find it. I do not even need it to be master/slave, I just want to make sure they're identical. – alexus Oct 14 '14 at 18:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.