I am trying to figure out pt-table-checksum, as I am using it for the first time. It look likes the documentation is complex and not easy to understand stuff.
I have a complex replication topology setup: I have master-master active topology setup geographically separated and both syncing each other. Further a number of slaves under each master. Also, some of the slaves are using filter table replication under both masters.
The database I am using is around 200GB in size, and slaves are balancing reads. I need to make sure pt-table-checksum will not break replication and will not cause any loads on server which hurts app usage.
My requirements are
Connect on master1 and check master2 and all slaves under both masters and inform for any deficiences in between master slaves data.
Is it possible to check only specific tables' specific slaves ? e.g. check from master1 and all underlying slaves of master1 but do not touch master2 and its underlying slaves?
How to take care of replication filters ? e.g. master1 has slaves slave1 slave2 where slave2 replicating only some of the database tables while slave1 replicates the entire database. I need pt-table-checksum to check for differences on slave1 for entire database and check only tables that are replicating on slave2.
I am running pt-table-checksum (2.0.1) on test machines on an
employees database. I inserted some records in employees table with
sql_log_bin off to prevent them from replicating so I can test pt-table-checksum to report differences and sync on slave.
Created user on master and on slave with SUPER, REPLICATION SLAVE privilege. The user is
testpass being the password
master$ ./pt-table-checksum --replicate=test.checksum --create-replicate-table --nocheck-replication-filters --databases=employees localhost Cannot connect to h=slave-ip
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 07-09T13:48:38 0 0 9 1 0 0.082 employees.departments 07-09T13:48:39 0 0 331603 7 0 1.001 employees.dept_emp 07-09T13:48:39 0 0 24 1 0 0.040 employees.dept_manager 07-09T13:48:39 0 0 300028 1 0 0.577 employees.employees 07-09T13:48:48 0 0 2844047 13 0 8.006 employees.salaries 07-09T13:48:49 0 0 443308 1 0 0.989 employees.titles
You can see employees table has some extra records. Secondly I executed the following command.
master$ ./pt-table-checksum --replicate=test.checksum --replicate-check-only --databases=employees --host=slave-ip --user=testuser --password=testpass
Command executed without any slave. Nothing displayed. When I checked on slave
employees count is still old and not updated as master.