the first step is to checksum difference and record into percona.checksum table pt-table-checksum h=xx,P=xx,u=xx,p=xx method=dsn=h=xx,D=xx,t=xx --databases=kobe the second step is to print the repair SQL into a file pt-table-sync --print --sync-to-master h=xx,P=xx,u=xx,p=xx >repair.sql and I need to take this repairsql file to developers,show them how many records are difference, difference detail, and let them make sure。Ater that I execute the repair sql so my question is what I execute this old repair SQL, the new data is inserting ,and the new difference is creating, maybe this old repair SQL will cover the latest data ! can anybody help me ? thank you !

We have following Multi master replication scenario. We are using Mariadb 10.1.34 and percona 3.0.11 - Master: Name: db-master1 (192.168.14.5) - Slave: Name: db-slave2 (192.168.15.5) Replica Connection name: master1 (192.168.14.5) SHOW SLAVE 'master1' STATUS\G on slave server is working fine. On execution of following pt-table-checksum command on DB Master (db-master1) pt-table-checksum --replicate=percona.checksums --ignore-databases mysql,Quotient h=localhost,u=checksum_user,p=checksum_password --recursion-method=dsn=D=percona,t=dsns --no-check-binlog-format Checking if all tables can be checksummed ... Starting checksum ... Replica db-slave2 is stopped. Waiting. Replica db-slave2 is stopped. Waiting. On Master1-db, the percona.dsns table have entry for db-slave2 The Above Waiting information is continue in infinite loop. Look forward any support

This is the master status

mysql> show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000039 | 796685393 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

This is the slave status.

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: ---------
                  Master_User: secondary
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000039
          Read_Master_Log_Pos: 796683447
               Relay_Log_File: ---------------
                Relay_Log_Pos: 488478421
        Relay_Master_Log_File: mysql-bin.000039
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 796682447
              Relay_Log_Space: 488478639
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 677f82ac-de9f-11ea-85db-fa163ee8b7b4
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

Seconds_Behind_master is 0, there is a difference in binlog positions. Out of 35 tables in the database, 4 have a one-row difference in the pt-table-checksum result. I want to write a script to see if the replication is working and if there is no data drift. Is having 1 difference ok? If not, how can I reach the ideal 0 difference?

The set up I work with is MySQL 5.5 in a master-slave replication configuration, but we have to use "MIXED" replication (statement and row-based) because we use an application that requires it, and it happens to be on our same server. We had replication stop recently, and I want to make sure we have recovered properly. I downloaded and installed the Percona Toolkit, but it doesn't look like I'll be able to use pt-table-checksum in this case, specifically because of the row-based replication: > **2.27.5 LIMITATIONS** Replicas using row-based replication pt-table-checksum requires statement-based replication, and it sets > binlog_format=STATEMENT on the master, but due to a MySQL limitation > replicas do not honor this change. Therefore, checksums will not > replicate past any replicas using row-based replication that are > masters for further replicas. The tool automatically checks the > binlog_format on all servers. See > --[no]check-binlog-format . (Bug 899415) Are there any other options for doing a checksum on row-based replication tables? I have found this on the Percona Forum which says it *will* work, but I cannot have it change my cnf file as it claims it will do. It also warns, when I attempt to run it, the following: > Replica mysql-b.example.com has binlog_format MIXED which could cause > pt-table-checksum to break replication. Please read "Replicas using > row-based replication" in the LIMITATIONS section of the tool's > documentation. If you understand the risks, specify > --no-check-binlog-format to disable this check.

I have this scenario. Data files are transferred from multiple stores (transactions) to the head office as they happen. For each store the table in question is pretty big in size. At the head office, that same table contains the same info from all stores which makes it huge. The data is transferred through files as there isn't any direct connections between the HO and the stores. I'm trying to find an efficient way to make sure that the data from each store has made it safe a sound to the head office table. To do this I have tried to do a checksum_AGG of a checksum for the fields that should match between the store and the HO for a specific Day for a specific store. But the performance was poor. I've also tried the sum of specific numeric columns and do the same at host for a specific Date and store to compare but the performance seems to be poor also. I've tried to use a persisted computed column to calculate a row's hashbytes and maybe use that but unfortunately, I couldn't, since the table was created with ansi_nulls off, which is a restriction of the persisted computed columns. And it's unthinkable to fix that issue on a very large scale of stores and HOs. I also thought of creating a trigger to fill any type of checksum per row, but the reality is that I will need to do this on multiple tables (maybe 50 tables) and to add triggers on every table will be very heavy, especially in OLTP system at the stores. I have verified the fragmentation of the indexes and all is good. Ideally it would be great if I transfer all the data from the store line by line and compare it against the HO but it's unthinkable since it's way too big. I'm looking for ideas that I can explore to see if performance is acceptable. The idea is to get some type of checksum of a table per date (store/date) from a store and compare it with the same operation at the HO. This means that I would only need to transfer a checksum value per store and date to the HO. The solution must be compatible from SQL Server 2008 up until the latest SQL Server version. **More info as requested** The sample table that I've been working with is one from a HO for my tests... Size: 160Gb for 105 stores and 633 days (200 columns... I know... not ideal but it's what it is and practically impossible to change for all stores and HOs) Example of queries tried to create a daily checksum per store: select [trsDate],[store],CHECKSUM_AGG(checksum(amt,qty,total)) from BigTable where store='111' AND trsDate BETWEEN '2016-01-01' and '2016-01-30' group by [trsDate],[store] order by [trsDate],[store] SELECT trsDate, store,'BigTable', amt=SUM(ISNULL(amt,0)), qty=SUM(ISNULL(qty,0)), total=SUM(ISNULL(total,0)) FROM BigTable WHERE trsDate BETWEEN '2016-01-01' and '2016-01-30' AND store='111' GROUP BY trsDate,store select [trsDate],[store],CHECKSUM_AGG(checksum([trsDate],[store],terminal,trsNumb,linenumb)) from BigTable where store='111' AND trsDate BETWEEN '2016-01-01' and '2016-01-30' group by [trsDate],[store] order by [trsDate],[store] SELECT [trsDate],[store], HASHBYTES('SHA2_512', concat(COUNT(*),CHAR(26), SUM(amt+qty+total)) from BigTable where store='111' AND trsDate BETWEEN '2016-01-01' and '2016-01-30' group by [trsDate],[store] order by [trsDate],[store] Execution plan shows an index Seek 100%... Adding an index to Store\Date and include amt,qty and total has helped drastically but I have to investigate the impact of this index for the thousands of transaction processed and transfer. Ideally I would love to have a checksum on all columns but the more I dig the more I realise that it will practically be impossible (otherwise I would need to double the size of each table by putting all columns in the indexe's include)

I'm try to use pt-table-checksum tool. I have Master/Slave tables, and I add some rows to the slave to check it. pt-table-checksum --ignore-databases mysql S=/tmp/5.6.22_3306_Master/data/mysql.sock --user=SlaveUser --password=1234 produce this result: TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 07-21T16:47:35 0 0 8 1 0 0.008 test.Countries 07-21T16:47:35 0 0 0 1 0 0.006 test.Persons When I run this query (SELECT * FROM percona.checksums WHERE db not like 'mysql';) on the **slave** I can see the difference: How I can see the right result and the diff with pt-table-checksum? Thanks!

I'm trying to find a way of comparing two tables in two databases (master and slave), as I have applied some slightly different settings to one, and need to see what affect it has (if any) on the data etc. Ordinarily I would use MySQL Utilities dbcompare which has worked well in the past, but this doesn't seem to have the option for a single table, and mysqldiff is too limited. So I downloaded percona-toolkit to my Debian Server (the slave) and am looking at pt-table-checksum. But the Page on the Percona website is very little help, instead filled with all manner of options, but seemingly no examples. I set up a test schema, with one table, and diffent data. So far I have got as far as: pt-table-checksum h=1.2.3.4,P=3306,u=xxxx,p=xxxx, -h1.2.3.5 --databases=test --tables=t1 --no-check-binlog-format (apparently there is some problem with Row Based Replication [?] hence --no-check-binlog-format) But this returned: # A software update is available: # * The current version for Percona::Toolkit is 2.2.14. TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 08-17T09:54:32 0 1 4 1 0 0.291 test.t1 Not exactly what I had in mind. Am I missing something, or have I completely misunderstood what pt-table-checksum does? I'm not sure why I didn't get the latest version either. I ran sudo apt-get update followed by sudo apt-get install percona-toolkit. (as you may have gathered, I'm not really a Linux person)

While **pt-table-checksum** seems to work well for most tables, a few of the big ones get this: > Error checksumming table mydb.mytable: Redundant argument in printf > at /usr/bin/pt-table-checksum line 8106. Versions: > pt-table-checksum 2.2.11 Servers (MariaDB acting as both slave to MySQL and master to another MariaDB, happens in both cases): > mysql Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using EditLine > wrapper mysql Ver 15.1 Distrib 10.1.24-MariaDB, for Linux (x86_64) > using readline 6.3

My environment: # cat /etc/redhat-release Red Hat Enterprise Linux Server release 6.8 (Santiago) # uname -a Linux A.B.C 2.6.32-642.4.2.el6.x86_64 #1 SMP Mon Aug 15 02:06:41 EDT 2016 x86_64 x86_64 x86_64 GNU/Linux # rpm -q mysql-server percona-toolkit mysql-server-5.1.73-7.el6.x86_64 percona-toolkit-2.2.19-1.noarch # I'm running pt-table-checksum and I'm getting following error: > Skipping chunk X of Y.Z because MySQL chose no index instead of the > word_sid_typeindex. mysql> DESCRIBE search_index; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | word | varchar(50) | NO | MUL | | | | sid | int(10) unsigned | NO | MUL | 0 | | | type | varchar(16) | YES | | NULL | | | score | float | YES | | NULL | | +-------+------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> SHOW TABLE STATUS WHERE Name='search_index'\G; *************************** 1. row *************************** Name: search_index Engine: InnoDB Version: 10 Row_format: Compact Rows: 3155432 Avg_row_length: 51 Data_length: 163250176 Max_data_length: 0 Index_length: 415514624 Data_free: 839909376 Auto_increment: NULL Create_time: 2016-09-01 21:14:58 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.13 sec) ERROR: No query specified mysql> Please advice.

export/import: $ mysqldump -h6dhdbm01 -p clee2 users > 6dhdbm01-clee2.users.sql Enter password: $ echo $? 0 $ mysql clee2 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.

I am using pt-table-checksum to ensure master-slave is synchronized. When I run pt-table-checksum, I see some differences on employee_profile.trigger_log but when I use pt-table-sync with --print, I see nothing. I resolved some differences on some tables with pt-table-sync but when I re-test with pt-table-checksum, this tool still says there is a difference. So what is the problem with pt-table-checksum ? Is is accuracy ? [UPDATE] I use pt-table-checksum -h -u -p '' --recursion-method dsn=h=,P=3306,u=,p=,D=check_db,t=dsns --nocheck-binlog-format --set-vars innodb_lock_wait_timeout=50 > ~/stats_check_db_.txt After that, I run command to filter: cat ~/stats_check_db_.txt | awk '{print "*"$3"*" " -- " $8}' | grep -Fv "*0*" This is output: *DIFFS* -- TABLE *1* -- mysql.user *1* -- employee_profile.entry *1* -- employee_profile.ip *1* -- employee_profile.thread When I run pt-table-sync: pt-table-sync --print h=,P=3306,u=,p= --sync-to-master --databases employee_profile --tables entry I see nothing. After a while, when I re-check with pt-table-checksum, I don't see difference on employee_profile.entry For a resolved table, I still see some differences on next re-check. These are disappeared from result of pt-table-checksum on the third checksum. [UPDATE 2] I am using percona toolkit version 2.2.13 - lastest version on http://www.percona.com/ I reviewed all implementations of db server that I did checksum. These are old implementation of mysql or mariadb that someone built them. I see the problem just appear when the implementation between master and slave is different such as master is MariaDB 5.5.34 and slave is MariaDB 5.5.37 (I see false positive error: Cannot nibble table because MySQL chose no index instead of the PRIMARY index - just ignore it and then execute pt-table-sync) or master is mysql 5.5.35 and slave is mysql 5.5.37 (I see pt-table-checksum says there is a difference but pt-table-sync --print says nothing - just re-test for sure). I think it is a reason for false positive errors. Thank you so much !

I was curious to hear what everyone's experiences are with using mysqldbcompare vs pt-table-checksum( from Percona Toolkit ) to find the differences between two MySQL databases. I have heard that pt-table-checksum is pretty dead-on accurate in figuring out if two DBs are different, but it looks like mysqldbcompare is able to show the actual row differences better and more clearly in its output. However, how do we know whether mysqldbcompare is as accurate as pt-table-checksum or is it more/less ? Thanks IS

I've got a question. I'm doing a simple script right now and it isn't working like I want. #!/bin/bash cat /etc/backup.conf | egrep -v "(^#.*|^$|^--.*)" > /tmp/databases while read line; do pt-table-checksum [options] --databases $line --quiet done < /tmp/databases rm /tmp/databases As you see I put names of databases to file /tmp/databases. Then I want to use pt-table-checksum for every database in that file line by line. This script works but only for first database name in file. Then it stops. Any ideas?