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
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]
-Scott
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 ?
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.
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?
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
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
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
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?
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?
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
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.
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=<slave IP/hostname>,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
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.
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
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
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
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
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?
Hi gpuser;
You could:
- 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.
- Run pt-table-checksum on a single database at a time using the --databases option and --no-check-replication-filters and see what happens.
- Not run the tool
- 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
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?