Статьи

Проверка соответствия определения таблицы с MySQLdiff

Январь 5, 2018

[Эта статья была написана Мигелем Анхелем Ньето]

Несоответствия данных в средах репликации довольно распространены. Есть много постов, которые объясняют, как исправить те, которые используют  pt-table-checkum  и  pt-table-sync . Обычно мы заботимся только о данных, но время от времени мы получаем этот вопрос в  поддержку :

Как я могу проверить согласованность определений таблиц между серверами?

Репликация также позволяет нам иметь различные определения таблиц между ведущим и ведомым. Например, в некоторых случаях вам нужны некоторые индексы на ведомых устройствах для запросов, но на самом деле они не нужны на ведущем устройстве. Есть и другие случаи, когда эти различия являются просто ошибкой, которую необходимо исправить.

mysqldiff, включенный в Oracle  MySQL Utilities , может помочь нам найти эти различия и получить информацию, необходимую для их устранения. В этом посте я собираюсь показать вам, как использовать его с примером.

Найти несоответствия в определении таблицы

mysqldiff позволяет нам находить эти несоответствия, проверяя различия между таблицами на одном сервере (разные базы данных) или на разных серверах (также возможно в разных базах данных). В этом примере я собираюсь искать различия в определениях таблиц между двумя разными серверами, server1 и server2.

Командная строка довольно проста. Это используется для сравнения таблиц в «тестовой» базе данных:

mysqldiff --server1=user@host1 --server2=user@host2 test:test

Если имя базы данных отличается:

mysqldiff --server1=user@host1 --server2=user@host2 testdb:anotherdb

Если имя таблицы отличается:

mysqldiff --server1=user@host1 --server2=user@host2 testdb.table1:anotherdb.anothertable

Теперь я хочу проверить соответствие определения таблицы между двумя серверами. База данных называется «сотрудники»:

# mysqldiff --force --server1=root:msandbox@127.0.0.1:21489 --server2=root:msandbox@127.0.0.1:21490 employees:employees
# WARNING: Using a password on the command line interface can be insecure.
# server1 on 127.0.0.1: ... connected.
# server2 on 127.0.0.1: ... connected.
# Comparing `employees` to `employees`                             [PASS]
# Comparing `employees`.`departments` to `employees`.`departments`   [FAIL]
# Object definitions differ. (--changes-for=server1)
#
 
--- `employees`.`departments`
+++ `employees`.`departments`
@@ -1,6 +1,6 @@
 CREATE TABLE `departments` (
   `dept_no` char(4) NOT NULL,
-  `dept_name` varchar(40) NOT NULL,
+  `dept_name` varchar(256) DEFAULT NULL,
   PRIMARY KEY (`dept_no`),
   UNIQUE KEY `dept_name` (`dept_name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
# Comparing `employees`.`dept_emp` to `employees`.`dept_emp`       [PASS]
# Comparing `employees`.`dept_manager` to `employees`.`dept_manager`   [PASS]
# Comparing `employees`.`employees` to `employees`.`employees`     [FAIL]
# Object definitions differ. (--changes-for=server1)
#
 
--- `employees`.`employees`
+++ `employees`.`employees`
@@ -5,5 +5,6 @@
   `last_name` varchar(16) NOT NULL,
   `gender` enum('M','F') NOT NULL,
   `hire_date` date NOT NULL,
-  PRIMARY KEY (`emp_no`)
+  PRIMARY KEY (`emp_no`),
+  KEY `last_name` (`last_name`,`first_name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
# Comparing `employees`.`salaries` to `employees`.`salaries`       [PASS]
# Comparing `employees`.`titles` to `employees`.`titles`           [PASS]
Compare failed. One or more differences found.

Есть как минимум два отличия. Один в таблице отделов, а другой в таблице сотрудников. Выход похож на diff. По умолчанию инструмент останавливается после нахождения первого различия. Вот почему мы используем –force, чтобы сообщить инструменту, что нужно продолжить проверку всех таблиц.

Это показывает нам, что в отделах dept_name это varchar (40) на server1 и varchar (256) на server2. Для таблицы «служащие» она имеет ключ KEY (last_name, first_name) на сервере server2, которого нет на сервере server1. Почему он принимает server2 в качестве ссылки? Из-за этой строки:

# Object definitions differ. (--changes-for=server1)

Итак, изменения, показанные в diff, относятся к server1. Если вы хотите, чтобы server2 был тем, который будет изменен, а server1 используется в качестве справочного, тогда  потребуется –changes-for = server2  .

В некоторых случаях вывод diff не очень полезен. Нам на самом деле нужен запрос SQL, чтобы внести изменения на сервере. Нам просто нужно добавить  –difftype = sql  в командную строку:

# mysqldiff --force --difftype=sql --server1=root:msandbox@127.0.0.1:21489 --server2=root:msandbox@127.0.0.1:21490 employees:employees
[...]
# Comparing `employees`.`departments` to `employees`.`departments`   [FAIL]
# Transformation for --changes-for=server1:
ALTER TABLE `employees`.`departments`
  DROP INDEX dept_name,
  ADD UNIQUE INDEX dept_name (dept_name),
  CHANGE COLUMN dept_name dept_name varchar(256) NULL;
[...]
# Comparing `employees`.`employees` to `employees`.`employees`     [FAIL]
# Transformation for --changes-for=server1:
#
ALTER TABLE `employees`.`employees`
  DROP PRIMARY KEY,
  ADD PRIMARY KEY(`emp_no`),
  ADD INDEX last_name (last_name,first_name);

Как видим, инструмент не идеален. Здесь есть две проблемы:

1- «Таблица отделов» сбрасывает УНИКАЛЬНЫЙ ключ, который присутствует на обоих серверах, только чтобы добавить его снова. Пустая трата времени и ресурсов.

2- На столе «служащих» он опускается и воссоздает ПЕРВИЧНЫЙ КЛЮЧ, опять то, что не нужно всем.

Я создал сообщение об  ошибке,  но это также дает нам хороший урок. Не просто копируйте и вставляйте команды без предварительной двойной проверки.

Какой mysqldiff работает под капотом?

В основном запросы на INFORMATION_SCHEMA. Это те, которые используются для проверки несоответствий в отделах:

SHOW CREATE TABLE `departments`;
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, AUTO_INCREMENT, AVG_ROW_LENGTH, CHECKSUM, TABLE_COLLATION, TABLE_COMMENT, ROW_FORMAT, CREATE_OPTIONS
  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'departments';
SELECT ORDINAL_POSITION, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE,
         COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT, COLUMN_KEY
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'departments';
SELECT PARTITION_NAME, SUBPARTITION_NAME, PARTITION_ORDINAL_POSITION,
         SUBPARTITION_ORDINAL_POSITION, PARTITION_METHOD, SUBPARTITION_METHOD,
         PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION, PARTITION_DESCRIPTION
  FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'departments';
SELECT CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_SCHEMA,
         REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'departments' AND
        REFERENCED_TABLE_SCHEMA IS NOT NULL;
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, AUTO_INCREMENT, AVG_ROW_LENGTH, CHECKSUM, TABLE_COLLATION, TABLE_COMMENT, ROW_FORMAT, CREATE_OPTIONS
  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'departments';
SELECT ORDINAL_POSITION, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE,
         COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT, COLUMN_KEY
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'departments';
SELECT PARTITION_NAME, SUBPARTITION_NAME, PARTITION_ORDINAL_POSITION,
         SUBPARTITION_ORDINAL_POSITION, PARTITION_METHOD, SUBPARTITION_METHOD,
         PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION, PARTITION_DESCRIPTION
  FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_SCHEMA = 'employees' AND TABLE_NAME = 'departments';

В качестве резюме, он проверяет разделы, формат строки, параметры сортировки, ограничения и так далее.

Вывод

Существуют разные инструменты для разных целей. Мы можем проверить согласованность данных с помощью pt-table-checkum / pt-table-sync, а также определения таблиц с помощью mysqldiff.

31
Share: