Ein Überblick zu pt-table-checksum
pt-table-checksum führt auf MySQL-Servern eine Online-Konsistenzüberprüfung der Replikate einer Datenbank durch, indem es Prüfsummen-Abfragen auf dem Master ausführt, was bei Replikaten, die mit dem Master inkonsistent sind, zu abweichenden Resultaten führt.
Die Grenzen von pt-table-checksum
- Das Tool überprüft keine Abweichungen in der Datenbank-/Tabellenstruktur von MySQL und geht davon aus, dass diese identisch sind.
- Das Tool setzt voraus, dass auf dem Master “binlog_format = STATEMENT” gesetzt ist (was die Standardeinstellung ist), und alle Replikate der Datenbank müssen das gleiche binlog_format haben, weil Abfragen sonst für die restliche Kette an Replikaten nicht weiter repliziert werden. Auf dieses Problem können wir nur stoßen, wenn wir eine Kette von Replikaten haben: Master -> Replikat 1 -> Replikat2. Beim gebräuchlichen Setup der Form Master -> Replikat besteht dieses Problem nicht, weil wir es mit ‘–no-check-binlog-format’ umgehen können.
- Es ist hierbei sehr wichtig zu verstehen, dass dieses Skript, weil es auf Prüfsummen zurückgreift, zu der Situation führen kann, dass sich die Daten zwar unterscheiden, das Skript aber anzeigt, dass die Daten in Ordnung sind. Das passiert wegen einer Hash- Kollision (Was eine Hash-Kollision in der Praxis bedeutet: https://stackoverflow.com/questions/45795637/what-exactly-is-hash-collision).
pt-table-checksum: Eine einfache Fallstudie
Bei dieser Methode kann der MySQL-Server dynamisch zwischen einer Statement-basierten Replikation und einer zeilenbasierten Replikation wählen, was von verschiedenen Bedingungen abhängt. Zu diesen Bedingungen gehören die Verwendung einer benutzerdefinierten Funktion (UDF), die Verwendung eines INSERT-Befehls mit DELAYED-Klausel, die Verwendung temporärer Tabellen und die Verwendung eines Statements, das Systemvariablen verwendet.
master [localhost:24028] {msandbox} ((none)) > create database sync;
Query OK, 1 row affected (0.000 sec)
master [localhost:24028] {msandbox} ((none)) > use sync
Database changed
master [localhost:24028] {msandbox} (sync) > create table ok(id integer auto_increment, value varchar(30), dtm datetime, primary key(id));
Query OK, 0 rows affected (0.056 sec)
master [localhost:24028] {msandbox} (sync) > create table fail(id integer auto_increment, value varchar(30), dtm datetime, primary key(id));
Query OK, 0 rows affected (0.048 sec)
#
# Generiere ein paar Daten, indem du in beiden Tabellen folgende Statements benutzt: ok, failed
#
master [localhost:24028] {msandbox} (sync) > insert into ok(value, dtm) values( hex(rand()*0xFFFFFFFF), now());
Query OK, 1 row affected (0.011 sec)Nun werden wir die folgende Inkonsistenz auf dem Replikat einfügen:
- eine einzelne Zeile löschen
- eine einzelne Zeile aktualisieren
- eine neue Zeile einfügen.
An diesem Punkt nehmen wir einmal an, dass die Tabelle mit dem Namen “ok” die gleichen Daten auf dem Master und dem Replikat hat, während die Tabelle “fail” die oben erwähnte Inkonsistenz aufweist. Hier ist der Inhalt der “fail”-Tabelle:
master [localhost:24028] {msandbox} (sync) > select * from fail;
+----+----------+---------------------+
| id | value | dtm |
+----+----------+---------------------+
| 1 | A15F0C16 | 2021-03-18 11:51:09 |
| 2 | 16C13FA0 | 2021-03-18 11:51:09 |
| 3 | 8DA91A62 | 2021-03-18 11:51:10 |
| 4 | 8009C58E | 2021-03-18 11:51:10 |
| 5 | D7358D23 | 2021-03-18 11:51:10 |
| 6 | B3EE718A | 2021-03-18 11:51:11 |
| 7 | FE0790CF | 2021-03-18 11:51:12 |
| 8 | DA5A7FF3 | 2021-03-18 11:51:12 |
+----+----------+---------------------+
8 rows in set (0.001 sec)
slave1 [localhost:24029] {msandbox} (sync) > select * from fail;
+----+----------+---------------------+
| id | value | dtm |
+----+----------+---------------------+
| 1 | A15F0C16 | 2021-03-18 11:51:09 |
| 2 | 16C13FA0 | 2021-03-18 11:51:09 |
| 3 | 8DA91A62 | 2021-03-18 11:51:10 | ← die nächste Zeile wurde gelöscht
| 5 | D7358D23 | 2021-03-18 11:51:10 |
| 6 | B3EE718A | 2021-03-18 11:51:11 |
| 7 | changed | 2021-03-18 11:51:12 | ← die aktualisierte Zeile
| 8 | DA5A7FF3 | 2021-03-18 11:51:12 |
| 9 | A44B677A | 2021-03-18 11:53:39 | ← die eingefügte Zeile
+----+----------+---------------------+
8 rows in set (0.000 sec)Lass uns nun pt-table-checksum auf dem Master ausführen. Ich habe das binlog_format auf beiden Servern für den Test auf ROW gesetzt.
>> pt-table-checksum --user=msandbox --password=msandbox --port 24028 --host=127.0.0.1 --databases="sync"
Checking if all tables can be checksummed ...
Starting checksum ...
Cannot connect to P=24030,h=node-3,p=...,u=msandbox
Cannot connect to P=24029,h=node-2,p=...,u=msandbox
Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.
CRC32 never needs BIT_XOR optimization at /usr/bin/pt-table-checksum line 6124.Hier haben wir versucht, pt-table-checksum auszuführen, haben aber die Fehlermeldung erhalten, dass wir wegen fehlender Berechtigung keine Verbindung mit den Replikaten herstellen können. Um das zu lösen, können wir der offiziellen Anleitung folgen und folgende Tabelle anlegen:
master [localhost:24028] {msandbox} (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`)
-> );
Query OK, 0 rows affected (0.060 sec)Alle Tabellen sollten DSNs haben, die Informationen darüber liefern, wie man sich in meinem speziellen Testfall mit allen Datenbanken verbinden kann.
master [localhost:24028] {msandbox} (percona) > insert into dsns values(1, null, "h=127.0.0.1,P=24029,u=msandbox,p=msandbox");
Query OK, 1 row affected (0.008 sec)
master [localhost:24028] {msandbox} (percona) > insert into dsns values(2, null, "h=127.0.0.1,P=24030,u=msandbox,p=msandbox");
Query OK, 1 row affected (0.007 sec)Lass uns pt-table-checksum erneut aufführen, um zu sehen, was passiert:
>> pt-table-checksum --user=msandbox --password=msandbox --port 24028 --host=127.0.0.1 --databases="sync" --no-check-slave-tables --recursion-method='dsn=h=127.0.0.1,D=percona,t=dsns'
Checking if all tables can be checksummed ...
Starting checksum ...
Replica slave1 has binlog_format ROW 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.
Replica slave2 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.Wie du siehst, sagt die Anwendung, dass wir entweder das binlog_format ändern oder diese Überprüfung überspringen sollen (was wir tun werden, weil wir hier keine Replikationskette haben. In einigen Versionen von MariaDB könntest du eine Fehlermeldung bezüglich CRC bekommen und diese umgehen, indem du ‘–function MD5’ eingibst). Ich werde “–chunk-size=2” eingeben, um die Chunk-Size zu verkleinern und zu zeigen, wie man herausfindet, wo es eine Inkonsistenz gibt.
>> pt-table-checksum --user=msandbox --password=msandbox --port 24028 --host=127.0.0.1 --databases="sync" --no-check-slave-tables --recursion-method='dsn=h=127.0.0.1,D=percona,t=dsns' --no-check-binlog-format --chunk-size=2
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
03-18T12:39:51 0 3 8 6 0 0.240 sync.fail
03-18T12:39:51 0 0 5 5 0 0.195 sync.ok
Was wir hier sehen ist, dass die Tabelle “sync.ok” keine Probleme macht, während es bei der Tabelle “fail” ein paar Probleme gibt. Lass uns den Inhalt der Tabelle “percona.checksums” genauer betrachten, in der dieses Skript die Ergebnisse der Ausführung abspeichert (die Tabelle ist hier klein und als Bild eingefügt, weil sie sehr breit ist und nicht als Tabelle angezeigt werden kann).
Lass uns jetzt die wichtigen Informationen genauer betrachten:
slave1 [localhost:24029] {msandbox} ((none)) > select chunk, lower_boundary, upper_boundary, this_crc, master_crc, this_cnt, master_cnt from percona.checksums where tbl='fail' and( this_crc <> master_crc or this_cnt <> master_cnt)\G
*************************** 1. row ***************************
chunk: 2
lower_boundary: 3
upper_boundary: 4
this_crc: 6c6eee37756ea6e6e9581fe3c11218cf
master_crc: 1c8de6ca86846fd0dbc3ca84de1e9d39
this_cnt: 1
master_cnt: 2
*************************** 2. row ***************************
chunk: 4
lower_boundary: 7
upper_boundary: 8
this_crc: 466afe5823613919b3be7a8058e97afb
master_crc: b9e0bb85309d42c382bc4b458ea9eb7e
this_cnt: 2
master_cnt: 2
*************************** 3. row ***************************
chunk: 6
lower_boundary: 8
upper_boundary: NULL
this_crc: 0
master_crc: 0
this_cnt: 1
master_cnt: 0
3 rows in set (0.000 sec)Hier haben wir die Daten nach zwei Bedingungen gefiltert:
- this_cnt <> master_cnt: Diese Bedingung hat die Zeilen herausgefiltert, wo wir in jedem Chunk die gleiche Menge Zeilen haben, d. h. es wurden keine Zeilen gelöscht oder eingefügt.
- this_crc <> master_crc: Diese Bedingung hat die Zeilen herausgefiltert, die einen abweichenden CRC-Wert haben, d. h. es wurden einige Zeilen verändert.
Lass uns die Ausgabe genauer anschauen und die Chunks einzeln betrachten.
Chunk #2
Wir sehen, dass die Zahl der Zeilen unterschiedlich ist, und wir sollten diesen Chunk manuell überprüfen. Da this_cnt kleiner ist als master_cnt, wurden Zeilen auf dem Replikat gelöscht.
Chunk #4
Die Zahl der Zeilen ist identisch, aber crc ist unterschiedlich, also haben wir hier einige Zeilen aktualisiert.
Chunk #6
Hier haben wir eine unterschiedliche Anzahl an Zeilen (crc ist leer, weil es diesen Chunk auf dem Master nicht gibt) und auf dem Master haben wir weniger als auf dem Replikat. Also haben wir hier etwas hinzugefügt.
Abschlussbemerkung
Weil wir hier einen ziemlich einfachen Fall einer Datenbank haben, wissen wir, was die Unterschiede zwischen dem Master und den Replikaten sind und können leicht herausfinden, worin die Inkonsistenz besteht.
In der Realität könnten wir aber mit einer Situation konfrontiert sein, in der die Zeilenanzahl um 1 abweicht, jedoch 5 Zeilen gelöscht und 6 Zeilen eingefügt wurden, sodass insgesamt 11 Inkonsistenzen angezeigt werden.
pt-table-checksum in der Produktion
Dieses Skript sollte in einer Produktionsumgebung mit Vorsicht angewendet werden.
Falls du als Administrator irgendeine Art von Wartungsfenster hast, solltest du dieses benutzen, um pt-table-checksum auszuführen.
Ist das nicht der Fall, solltest du dieses Skript lieber außerhalb der Hauptlastzeiten ausführen, und du kannst zusätzlich die Datenmenge, die pro Ausführung überprüft werden soll, reduzieren. Der Schlüssel hierbei ist, einen zuverlässigen Workflow zu erstellen, um die gesamte Tabelle über mehrere Ausführungen verteilt zu überprüfen.
Es gibt verschiedene Parameter, die dabei helfen könnten, die Menge der Zeilen zu reduzieren, die pro Ausführung überprüft werden:
--where
Do only rows matching this WHERE clause. You can use this option to limit the checksum to only part of the table. This is particularly useful if you have append-only tables and don't want to constantly re-check all rows; you could run a daily job to just check yesterday's rows, for instance.
Example:
pt-table-checksum --where "updated_dtm >= '2021-03-18 00:00:00' and updated_dtm < '2021-03-19 00:00:00'"
--tables
Checksum only this comma-separated list of tables. Table names may be qualified with the database name.
--tables-regex
Checksum only tables whose names match this Perl regex.
Example:
pt-table-checksum --tables 'transactions,users'
