Актуализация реплики mysql с помощью pt-table-checksum и pt-table-sync

Содержание

Содержание
Введение
Установка percona-tools
Создание тестовой базы данных
Создание служебной базы данных
Сверка данных
Синхронизация данных
Повторная сверка данных
Источник

Введение

При использовании механизма репликации MySQL необходимо иметь идентичные наборы данных на всех серверах, участвующих в репликации данных.

Если появляется расхождение данных, необходимо его устранить. Для этого можно пересоздать реплику, что не всегда удобно, или воспользоваться утилитами pt-table-checksum и pt-table-sync.

Установка percona-tools

На всех серверах, участвующих в репликации данных, нужно установить пакет percona-toolkit в который входят утилиты pt-table-checksum и pt-table-sync:

# apt-get install percona-toolkit

Создание тестовой базы данных

Рассмотрим использование утилит на примере одного источника (node-master) и двух реплик (node-slave-1, node-slave-2).

Создаем тестовую базу данных:

mysql> CREATE DATABASE test;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE table1 (id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, name CHAR(5)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO table1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e'), (6, 'f'), (7, 'g'), (8, 'h'), (9, 'i'), (10, 'j');
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM table1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
|    7 | g    |
|    8 | h    |
|    9 | i    |
|   10 | j    |
+------+------+
10 rows in set (0.00 sec)

Теперь удалим часть данных на репликах:

node-slave-1

mysql> DELETE FROM table1 WHERE id > 7;
Query OK, 3 rows affected (0.00 sec)

mysql> SELECT * FROM table1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
|  6 | f    |
|  7 | g    |
+----+------+
7 rows in set (0.00 sec)

node-slave-2

mysql> DELETE FROM table1 WHERE id > 5;
Query OK, 4 rows affected (0.00 sec)

mysql> SELECT * FROM table1;
+----+------+
| id | name |
+----+------+
|  5 | e    |
|  6 | f    |
|  7 | g    |
|  8 | h    |
|  9 | i    |
| 10 | j    |
+----+------+
6 rows in set (0.00 sec)

Создание служебной базы данных

Для работы percona-toolkit на всех серверах нужно создать таблицу и пользователя:

mysql> CREATE DATABASE PERCONA;
Query OK, 1 row affected (0.00 sec)

mysql> USE PERCONA;
Database changed

mysql> CREATE TABLE checksums (
    ->    db             CHAR(64)     NOT NULL,
    ->    tbl            CHAR(64)     NOT NULL,
    ->    chunk          INT          NOT NULL,
    ->    chunk_time     FLOAT            NULL,
    ->    chunk_index    VARCHAR(200)     NULL,
    ->    lower_boundary TEXT             NULL,
    ->    upper_boundary TEXT             NULL,
    ->    this_crc       CHAR(40)     NOT NULL,
    ->    this_cnt       INT          NOT NULL,
    ->    master_crc     CHAR(40)         NULL,
    ->    master_cnt     INT              NULL,
    ->    ts             TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->    PRIMARY KEY (db, tbl, chunk),
    ->    INDEX ts_db_tbl (ts, db, tbl)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE,PROCESS,SUPER, SELECT ON *.* TO `checksum_user`@'%' IDENTIFIED BY 'checksum_password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON percona.* TO `checksum_user`@'%';
Query OK, 0 rows affected (0.03 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Сверка данных

После создания таблицы и пользователя, запускаем проверку целостности данных:

root@node-server:/# pt-table-checksum --replicate=percona.checksums --databases=test --host=localhost --user=checksum_user --password=checksum_password
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
09-01T14:07:26      0      1       10       1       0   0.013 test.table1

В результате видим, что значение в поле DIFFS отличается от 0. Можно подключиться к репликам и проверить, в каких таблицах данные повреждены.

На node-slave-1:

mysql> SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks
    -> FROM percona.checksums
    -> WHERE master_cnt <> this_cnt
    ->   OR master_crc <> this_crc
    ->   OR ISNULL(master_crc) <> ISNULL(this_crc)
    -> GROUP BY db, tbl;
+------+--------+------------+--------+
| db   | tbl    | total_rows | chunks |
+------+--------+------------+--------+
| test | table1 |          7 |      1 |
+------+--------+------------+--------+
1 row in set (0.00 sec)

На node-slave-2:

mysql> SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks
-> FROM percona.checksums
-> WHERE master_cnt <> this_cnt
->   OR master_crc <> this_crc
->   OR ISNULL(master_crc) <> ISNULL(this_crc)
-> GROUP BY db, tbl;
+------+--------+------------+--------+
| db   | tbl    | total_rows | chunks |
+------+--------+------------+--------+
| test | table1 |          6 |      1 |
+------+--------+------------+--------+
1 row in set (0.00 sec)

Синхронизация данных

На каждой реплике выполняем команду синхронизации данных.

На node-slave-1:

root@node-slave-1:/# pt-table-sync --print --replicate=percona.checksums --sync-to-master h=localhost,u=checksum_user,p=checksum_password

# A software update is available:
#   * The current version for Percona::Toolkit is 3.0.5

REPLACE INTO `test`.`table1`(`id`, `name`) VALUES ('8', 'h') /*percona-toolkit src_db:test src_tbl:table1 src_dsn:P=3306,h=192.168.5.56,p=...,u=checksum_user dst_db:test dst_tbl:table1 dst_dsn:h=localhost,p=...,u=checksum_user lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:3248 user:root host:node-slave-1*/;
REPLACE INTO `test`.`table1`(`id`, `name`) VALUES ('9', 'i') /*percona-toolkit src_db:test src_tbl:table1 src_dsn:P=3306,h=192.168.5.56,p=...,u=checksum_user dst_db:test dst_tbl:table1 dst_dsn:h=localhost,p=...,u=checksum_user lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:3248 user:root host:node-slave-1*/;
REPLACE INTO `test`.`table1`(`id`, `name`) VALUES ('10', 'j') /*percona-toolkit src_db:test src_tbl:table1 src_dsn:P=3306,h=192.168.5.56,p=...,u=checksum_user dst_db:test dst_tbl:table1 dst_dsn:h=localhost,p=...,u=checksum_user lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:3248 user:root host:node-slave-1*/;

На node-slave-2:

root@node-slave-2:/# pt-table-sync --print --replicate=percona.checksums --sync-to-master h=localhost,u=checksum_user,p=checksum_password

# A software update is available:
#   * The current version for Percona::Toolkit is 3.0.5

REPLACE INTO `test`.`table1`(`id`, `name`) VALUES ('1', 'a') /*percona-toolkit src_db:test src_tbl:table1 src_dsn:P=3306,h=192.168.5.56,p=...,u=checksum_user dst_db:test dst_tbl:table1 dst_dsn:h=localhost,p=...,u=checksum_user lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:2566 user:root host:node-slave-2*/;
REPLACE INTO `test`.`table1`(`id`, `name`) VALUES ('2', 'b') /*percona-toolkit src_db:test src_tbl:table1 src_dsn:P=3306,h=192.168.5.56,p=...,u=checksum_user dst_db:test dst_tbl:table1 dst_dsn:h=localhost,p=...,u=checksum_user lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:2566 user:root host:node-slave-2*/;
REPLACE INTO `test`.`table1`(`id`, `name`) VALUES ('3', 'c') /*percona-toolkit src_db:test src_tbl:table1 src_dsn:P=3306,h=192.168.5.56,p=...,u=checksum_user dst_db:test dst_tbl:table1 dst_dsn:h=localhost,p=...,u=checksum_user lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:2566 user:root host:node-slave-2*/;
REPLACE INTO `test`.`table1`(`id`, `name`) VALUES ('4', 'd') /*percona-toolkit src_db:test src_tbl:table1 src_dsn:P=3306,h=192.168.5.56,p=...,u=checksum_user dst_db:test dst_tbl:table1 dst_dsn:h=localhost,p=...,u=checksum_user lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:2566 user:root host:node-slave-2*/;

Ключ --print выводит запросы для актуализации данных без выполнения. Можно вручную выполнить их или запустить команду, заменив опцию --print на --execute.

Повторная сверка данных

После выполнения команды нужно проверить, что данные синхронизированы. Повторно запускаем проверку на источнике:

root@node-server:/# pt-table-checksum --replicate=percona.checksums --databases=test --host=localhost --user=checksum_user --password=checksum_password
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
09-01T14:15:17      0      0       10       1       0   1.015 test.table1

Источник