# How to verify data consistency for replication(master:window OS, slave:Ubuntu OS)? **URL:** \ **Category:** Percona Toolkit\ **Created:** [May 28, 2015, 6:06am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243 "2015-05-28T06:06:04Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![gpuser](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@gpuser](https://forums.percona.com/u/gpuser)\ **Post date:** [May 28, 2015, 6:06am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/1 "2015-05-28T06:06:04Z")
Hi, My replication solution mysql 5.5 version setup is using master as window 8 OS and slave is ubuntu OS. How can I verify my slave data consistency since percona seems like need to verify data from master (linux OS) Chern ---
**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\ **Post date:** [May 28, 2015, 12:22pm UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/2 "2015-05-28T12:22:39Z")
Hi gpuser; You’ll want to checkout pt-table-checksum, which does just what you are looking for: [url][https://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html[/url]](https://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html%5B/url%5D) -Scott ---
**Author:** ![gpuser](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@gpuser](https://forums.percona.com/u/gpuser)\ **Post date:** [May 28, 2015, 9:13pm UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/3 "2015-05-28T21:13:02Z")
Hi Scott, pt-table-checksum usually run on master machine in linux right? In my case (master in window, slave in ubuntu), how should i proceed ? ---
**Author:** ![martin.arrieta](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/martin.arrieta/32/16888_2.png) [@martin.arrieta](https://forums.percona.com/u/martin.arrieta)\ **Post date:** [May 28, 2015, 9:32pm UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/4 "2015-05-28T21:32:48Z")
You can run pt-table-checksum from another server and use the hostname to connect to the master. For example: pt-table-checksum [options] h=your\_master\_ip Regards, Martin. ---
**Author:** ![gpuser](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@gpuser](https://forums.percona.com/u/gpuser)\ **Post date:** [May 29, 2015, 12:23am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/5 "2015-05-29T00:23:10Z")
Hi Martin, For example: my master (window) IP is 192.168.1.100 my slave (ubuntu) IP is 192.168.1.101 your suggestion now is I use 3rd server to run the command? ---
**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\ **Post date:** [May 29, 2015, 11:40am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/6 "2015-05-29T11:40:55Z")
Hi gpuser; You can run pt-table-checksum from any Linux host, so you should be able to run it on the slave. The only caveat is that you must be able to connect to the master MySQL instance, so you have to have a database account that will allow the connection (i.e. the account host is “%” to allow a connection from anywhere, or it matches whatever host you are running pt-table-checksum from). If you want to check all the tables in a database, the command is fairly straight forward: pt-table-checksum --databases= --host= --port= --user= --password= If you do not want to put your password on the command line, you can use --ask-pass for it to task you when you run it. By default, pt-table-checksum will then connect to the master and run a “show processlist” to find your slave, and then will start doing the checksums and comparison. I would also recommend running the pt-table-checksum command in a screen session if your database is large, as it can take quite a while to run. -Scott ---
**Author:** ![gpuser](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@gpuser](https://forums.percona.com/u/gpuser)\ **Post date:** [June 2, 2015, 3:13am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/7 "2015-06-02T03:13:39Z")
Hi Scott, I want to check all the databases, the command should be " --databases=all"? 192.168.104.197 is my master IP When i run the command : pt-table-checksum --databases=all --host=192.168.104.197 --port=3306 --user=root --password= It returns error to me as below: Cannot connect to h=REPLICATION,p=…,u=root Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information. Please advise ---
**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\ **Post date:** [June 2, 2015, 10:53am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/8 "2015-06-02T10:53:07Z")
Hi gpuser; You would just leave off the --databases option if you actually want it to check all the databases. It looks like it is complaining about not being able to connect, so I would try to manually connect to mysql from the server you are trying to run pt-table-checksum on and see if you can actually connect or not. mysql --host=192.168.104.197 --port=3306 --user=root --password= -Scott ---
**Author:** ![gpuser](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@gpuser](https://forums.percona.com/u/gpuser)\ **Post date:** [June 3, 2015, 3:32am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/9 "2015-06-03T03:32:03Z")
Hi Scott, I able to run below checksum command pt-table-checksum --host=192.168.104.197 --port=3306 --user=root --password= Because of the database too large, I tried to save the output in a txt file but it return me error command: pt-table-checksum --host=192.168.104.197 --port=3306 --user=root --password=pass \> output.txt It return me error: Cannot connect to h=REPLICATION,p=…,u=root Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information. Any suggestion? ---
**Author:** ![gpuser](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@gpuser](https://forums.percona.com/u/gpuser)\ **Post date:** [June 3, 2015, 5:54am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/10 "2015-06-03T05:54:33Z")
Scott, One more inquiry need your advise. After a long run of checksum, can I just get a summary of the result? if yes, what is the command? ---
**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\ **Post date:** [June 3, 2015, 9:42am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/11 "2015-06-03T09:42:17Z")
Hi gpuser; To get a progress update, you can add: –progress=time,300 That will output progress every 5 minutes. As for why your command would fail after adding the redirection at the end (\>), I do not know. That should not affect the connection parameters so that is a bit strange. I just tried your same command with and without the output redirection and they both worked. So I would just verify that nothing is changing in your second command (i.e. the password). -Scott ---
**Author:** ![gpuser](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@gpuser](https://forums.percona.com/u/gpuser)\ **Post date:** [June 4, 2015, 2:47am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/12 "2015-06-04T02:47:08Z")
Hi Scott, I knew why like this already. whenever I run command: pt-table-checksum --host=192.168.104.197 --port=3306 --user=root --password= or pt-table-checksum --replicate=percona.checksums --create-replicate-table --empty-replicate-table --no-check-binlog-format h=192.168.104.197,u=root,p= ## It will output me then start the process. Cannot connect to h=REPLICATION,p=…,u=root 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 06-04T15:46:35 0 0 0 1 0 0.062 mysql.columns\_priv 06-04T15:46:35 0 0 47 1 0 0.058 mysql.db 06-04T15:46:35 0 0 0 1 0 0.051 mysql.event 06-04T15:46:35 0 0 0 1 0 0.050 mysql When I tried to the get summary of differences (if no differences were found this will come up empty) with command: pt-table-checksum --replicate=percona.checksums --replicate-check --replicate-check-only --no-check-binlog-format h=192.168.104.197,u=root,p= ## It return me output only because of no differences were found: ## Cannot connect to h=REPLICATION,p=…,u=root Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information In case differences are found I should run pt-table-sync like below? “h=localhost”, or put my slave IP? (i’m running command in slave only) pt-table-sync --print --replicate=percona.checksums --sync-to-master h=localhost,u=root,p= ## If no differences are found, it will return error? ## DBD::mysql::db selectall\_arrayref failed: Table ‘percona.checksums’ doesn’t exist [for Statement “SELECT db, tbl, CONCAT(db, ‘.’, tbl) AS `table`, chunk, chunk\_index, lower\_boundary, upper\_boundary, COALESCE(this\_cnt-master\_cnt, 0) AS cnt\_diff, COALESCE(this\_crc \<\> master\_crc OR ISNULL(master\_crc) \<\> ISNULL(this\_crc), 0) AS crc\_diff, this\_cnt, master\_cnt, this\_crc, master\_crc FROM percona.checksums WHERE master\_cnt \<\> this\_cnt OR master\_crc \<\> this\_crc OR ISNULL(master\_crc) \<\> ISNULL(this\_crc)”] at /usr/bin/pt-table-sync line 4957. Issuing rollback() due to DESTROY without explicit disconnect() of DBD::mysql::db handle ;host=localhost;mysql\_read\_default\_group=client at /usr/bin/pt-table-sync line 4957. Issuing rollback() due to DESTROY without explicit disconnect() of DBD::mysql::db handle ;host=192.168.104.197;port=3306;mysql\_read\_default\_group=client at /usr/bin/pt-table-sync line 4957. ---
**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\ **Post date:** [June 4, 2015, 11:03am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/13 "2015-06-04T11:03:05Z")
Hi gpuser; It appears it is not detecting the slave correctly, as the basic use case is pretty straight forward and does not require many of the options you are giving it. I would try creating the dsns table to specify your slave explicitly and see if that helps. On your master database: CREATE DATABASE percona; USE percona; CREATE TABLE `dsns` ( `id` int(11) NOT NULL AUTO\_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) ); INSERT INTO dsns VALUES (1,NULL,“h=\,P=,u=root,p=”); pt-table-checksum --progress=time,300 --host=192.168.104.197 --port=3306 --user=root --password= --recursion-method dsn=D=percona,t=dsns Give that a try and see if it will work. -Scott ---
**Author:** ![gpuser](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@gpuser](https://forums.percona.com/u/gpuser)\ **Post date:** [June 4, 2015, 11:49am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/14 "2015-06-04T11:49:42Z")
Hi Scott, I like simple and straight forward method too. 🙂 my master (192.168.104.197,window) my slave (192.168.104.192,linux) If I run the command from slave to check the data consistency pt-table-checksum --host=192.168.104.197 --port=3306 --user=root --password= It will go to my master and compare/checksum master and slave database/table? or it only checksum my master database and not comparing with my slave? In case, (pt-table-checksum --host=192.168.104.197 --port=3306 --user=root --password= ) will compare both master and slave DB, may i know what is the command to get summary of differences and also pt-table-sync if require? Or I must creating the dsns table to specify your slave explicitly as what your advice? If must create dsns table, after created based on your advice, I run below command at slave to checksum/compare both master and slave DB right? pt-table-checksum --progress=time,300 --host=192.168.104.197 --port=3306 --user=root --password= --recursion-method dsn=D=percona,t=dsns In this case, after run the checksum, what is the command to get summary of differences ? this command? pt-table-checksum --replicate=percona.checksums --replicate-check --replicate-check-only --no-check-binlog-format h=192.168.104.197,u=root,p= Sorry for stupid question. ---
**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\ **Post date:** [June 4, 2015, 11:57am UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/15 "2015-06-04T11:57:56Z")
Hi gpuser; The idea is that you are getting pt-table-checksum to connect to the master, detect the slave(s) (or you specify them explicitly via the dsns table), and then the tool will run the checksums on the master and the slave(s), and compare them to look for differences. The problem is that according to your errors, it is not auto-detecting the slave. So if you create the dsns table to explicitly tell it where your slave is, it should work then. Pt-table-checksum will then connect to the master, look in dsns table to find your slave, connect to your slave, and then run the comparisons. If any differences are found, it should output them. And if you add the --replicate=percona.checksums option like you did, it should also put the differences in that table. So if you create the dsns table on the master, you only have to run pt-table-checksum against the master, and it will connect to the slave. You do not run the tool against the slave in this case. -Scott ---
**Author:** ![gpuser](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@gpuser](https://forums.percona.com/u/gpuser)\ **Post date:** [June 4, 2015, 12:41pm UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/16 "2015-06-04T12:41:29Z")
Hi Scott, I created the table (on master) as per advice and run the command at slave. it return me output below, is it correct? replication@replication:/$ pt-table-checksum --progress=time,300 --host=192.168.104.197 --port=3306 --user=root --password= --recursion-method dsn=D=percona,t=dsns 06-05T02:07:42 Replication filters are set on these hosts: replication replicate\_do\_db = subs\_010415\_jpvby,subs\_050215\_glxkl,subs\_170315\_91g7o,subs\_240415\_6817i,subs\_240415\_xvxr4,subs\_270315\_26d96,subs\_270315\_2cao1,subs\_270315\_9nkd3,subs\_270315\_dbpxq,subs\_270315\_e3q9a,subs\_270315\_pcirb,subs\_270315\_xret9,subs\_270315\_yq5mq,subs\_300115\_0yh4k,subs\_300315\_6f3aq,subs\_300315\_hfh0d,subs\_300315\_id71e,subs\_300315\_ijs2h,subs\_300315\_lt3rp,subs\_300315\_u5ew2,subs\_300315\_yv2y1,subs\_m Please read the --check-replication-filters documentation to learn how to solve this problem. at /usr/bin/pt-table-checksum line 9502. replication@replication:/$ pt-table-checksum --host=192.168.104.197 --port=3306 --user=root --password= Cannot connect to P=3306,h=REPLICATION,p=…,u=root 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 06-05T02:09:39 0 0 0 1 0 0.052 mysql.columns\_priv 06-05T02:09:39 0 0 47 1 0 0.055 mysql.db 06-05T02:09:39 0 0 0 1 0 0.051 mysql.event 06-05T02:09:39 0 0 0 1 0 0.056 mysql.func ---
**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\ **Post date:** [June 4, 2015, 12:52pm UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/17 "2015-06-04T12:52:24Z")
Hi gpuser; Yes the first command looks like it connected that time, however you have replication filters setup, so it exited before doing anything. You can run it if you force it to ignore the replication filters, but as the manual suggests, that is dangerous and can break replication. [url][pt-table-checksum — Percona Toolkit Documentation](https://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html#cmdoption-pt-table-checksum--%5B/url%5D%5Bno%5Dcheck-replication-filters) Since you seem to be fairly new to this area, I would not recommend forcing it, because it could cause you a lot of headache if it does break replication. -Scott ---
**Author:** ![gpuser](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@gpuser](https://forums.percona.com/u/gpuser)\ **Post date:** [June 4, 2015, 1:11pm UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/18 "2015-06-04T13:11:30Z")
Hi Scott, So what should i do now since it exited before doing anything due to replication filters and it is not recommended to force it run? ---
**Author:** ![scott.nemes](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/scott.nemes/32/5_2.png) [@scott.nemes](https://forums.percona.com/u/scott.nemes)\ **Post date:** [June 4, 2015, 3:54pm UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/19 "2015-06-04T15:54:19Z")
Hi gpuser; You could: 1. Get rid of your replication filters. This is always advised, but requires knowledge as to why they are there to start with and what would need to be done to get rid of them. 2. Run pt-table-checksum on a single database at a time using the --databases option and --no-check-replication-filters and see what happens. 3. Not run the tool 4. Pay for support from Percona or another professional organization/dba Unfortunately what I can help you with is limited to general advice, but it sounds like you may need more involved hands-on help. -Scott ---
**Author:** ![gpuser](https://avatars.discourse-cdn.com/v4/letter/g/e9c0ed/32.png) [@gpuser](https://forums.percona.com/u/gpuser)\ **Post date:** [June 4, 2015, 8:38pm UTC](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243/20 "2015-06-04T20:38:40Z")
Hi Scott, Thanks for your advice. If I run the pt-table-checksum with no-check-replication-filters, the worst case will stop the replication only? it wont affect my master database? [Next page](https://forums.percona.com/t/how-to-verify-data-consistency-for-replication-master-window-os-slave-ubuntu-os/4243.md?page=2)