MySQL: mysqldbcompare — сравнение двух баз

Автор: | 09/09/2020
Нет комментариев
 

В качестве проложения поста AWS: Database Migration Service, часть 1 — обзор и пример миграции self-hosted MariaDB в AWS Aurora RDS — описание утилиты mysqldbcompare, которая предназначена для сравнения двух баз данных.

См. документацию тут>>>.

Установка

На Arch Linux можно установить из AUR:

yay -S mysql-utilities

Либо скачать и установить вручную.

Загружаем архив:

wget http://cdn.mysql.com/Downloads/MySQLGUITools/mysql-utilities-1.6.5.tar.gz
tar xfp mysql-utilities-1.6.5.tar.gz
ll mysql-utilities-1.6.5
total 140
-rw-r--r-- 1 setevoy setevoy 37814 Jan 17  2017 CHANGES.txt
drwxr-xr-x 3 setevoy setevoy  4096 Jan 17  2017 docs
-rw-r--r-- 1 setevoy setevoy  6680 Jan 17  2017 info.py
-rw-r--r-- 1 setevoy setevoy 17987 Jan 17  2017 LICENSE.txt
drwxr-xr-x 4 setevoy setevoy  4096 Jan 17  2017 mysql
-rw-r--r-- 1 setevoy setevoy   928 Jan 17  2017 PKG-INFO
-rw-r--r-- 1 setevoy setevoy 34819 Jan 17  2017 README.txt
drwxr-xr-x 2 setevoy setevoy  4096 Jan 17  2017 scripts
-rw-r--r-- 1 setevoy setevoy 14232 Jan 17  2017 setup.py
drwxr-xr-x 2 setevoy setevoy  4096 Jan 17  2017 unit_tests

Пробуем установить:

python mysql-utilities-1.6.5/setup.py
Traceback (most recent call last):
File "mysql-utilities-1.6.5/setup.py", line 21, in <module>
import ConfigParser
ModuleNotFoundError: No module named 'ConfigParser'

Добавляем Python-модуль configparser:

yay -S python-configparser

И ещё раз установка:

python mysql-utilities-1.6.5/setup.py
Traceback (most recent call last):
File "mysql-utilities-1.6.5/setup.py", line 21, in <module>
import ConfigParser
ModuleNotFoundError: No module named 'ConfigParser'

Та что ж ты…

Но проблема старая, известная — открываем на редактирование файл setup.py, в импортах меняем:

...
import ConfigParser
...

На:

...
import configparser
...

И ещё раз запускаем сборку:

cd mysql-utilities-1.6.5
python2 setup.py build

Проверяем каталог buid:

ll build/
total 8
drwxr-xr-x 3 setevoy setevoy 4096 Sep  2 10:04 lib
drwxr-xr-x 2 setevoy setevoy 4096 Sep  2 10:04 scripts-2.7

Устанавливаем:

sudo python2 setup.py install
...
running install_scripts
...
copying build/scripts-2.7/mysqldiff -> /usr/bin
copying build/scripts-2.7/mysqldbcompare -> /usr/bin
...
copying build/scripts-2.7/mysqlrplshow -> /usr/bin
...

Опции mysqldbcompare

mysqldbcompare может выполнить сравнение одной базы на разных серверах, разных баз на разных серверах, или одной базы на одном и том же сервере.

Общий синтаксис:

mysqldbcompare --server1=<DB_HOST_1> --server=<DB_HOST_2> <DB_NAME_1>:<DBNAME_2>

Если имя баз данных на обоих хостах одинаковое — можно указать только <DBNAME_1>.

mysqldbcompare выполнит сравнение таблиц, views, триггеров, процедур, функций и events в каждой из заданных баз:

  1. проверяется наличие указанных баз на обоих хостах
  2. object-compare: проверка наличия всех объектов в обеих базах
  3. diff: сравнение CREATE объектов в обехи базах
  4. row-count: проверка количества записей в таблицах
  5. data-check: сравнение целостности записей в таблицах обеих баз

Любая из проверок может быть пропущенна используя --skip-<test-name>.

По-умолчанию mysqldbcompare остановит выполнение при первом найденном несоответствии. Что бы выполнить все тесты, игнорируя ошибки — используем --run-all-tests.

Опция --changes-for определяет направление проверок — для какой из баз будут выводить сообщения о необходимых трансформациях.

Если на сервере баз данных, учавствующем в проверке, включен binlog для репликации, и вы не хотите, что бы операции mysqldbcompare не попали в этот лог — используйте --disable-binary-logging.

Запуск и примеры

Итак, имеется два сервера баз данных, на каждом находится база, которая после миграции и репликации с помощью AWS Database Migration Service должна быть идентична базе на другом сервере.

При использовании баз с тире в имени — экранируйем из с помощью `, а для bash — символ ` экранируем с помощью \.

В примере ниже мы используем:

  • source DB — исходный сервер, с которого копировали базу — --server1=root:pass@104.***.***.2
  • target DB — целевой сервер, на который копировали базу — --server2=admin:pass@aurora-web-dev-cluster.***.us-east-2.rds.amazonaws.com
mysqldbcompare --server1=root:pass@104.***.***.2 --server2=admin:pass@aurora-web-dev-cluster.***.us-east-2.rds.amazonaws.com \`new-eat\`
WARNING: Using a password on the command line interface can be insecure.
server1 on 104.***.***.2: ... connected.
server2 on aurora-web-dev-cluster.***.us-east-2.rds.amazonaws.com: ... connected.
Checking databases new-eat on server1 and new-eat on server2
Object definitions differ. (--changes-for=server1)
--- `new-eat`
+++ `new-eat`
@@ -1 +1 @@
-CREATE DATABASE `new-eat` /*!40100 DEFAULT CHARACTER SET utf8 */
+CREATE DATABASE `new-eat` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */
ERROR: The object definitions do not match.

mysqldbcompare обнаружил первое несоответствие — разные кодировки для баз, и сразу остановил дальнейшую проверку.

Добавляем --run-all-tests или -a:

mysqldbcompare --run-all-tests --server1=root:password@104.***.***.2 --server2=dbadmin:password@aurora-web-dev-cluster.***.us-east-2.rds.amazonaws.comm \`new-eat\`
WARNING: Using a password on the command line interface can be insecure.
server1 on 104.***.***.2: ... connected.
server2 on aurora-web-dev-cluster.***.us-east-2.rds.amazonaws.com: ... connected.
Checking databases new-eat on server1 and new-eat on server2
Object definitions differ. (--changes-for=server1)
--- `new-eat`
+++ `new-eat`
@@ -1 +1 @@
-CREATE DATABASE `new-eat/*!40100 DEFAULT CHARACTER SET utf8 */
+CREATE DATABASE `new-eat` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */
Defn    Row     Data
Type      Object Name                             Diff    Count   Check
-------------------------------------------------------------------------
TABLE     acl_permissions                         pass    pass    -
- Compare table checksum                                pass
TABLE     acl_permissions_acl_roles               pass    pass    -
- Compare table checksum                                pass
TABLE     acl_roles                               pass    pass    -
- Compare table checksum                                pass
TABLE     country_prices                          FAIL    pass    -
- Compare table checksum                                pass
Object definitions differ. (--changes-for=server1)
--- `new-eat`.`country_prices`
+++ `new-eat`.`country_prices`
@@ -6,6 +6,6 @@
`language_code` varchar(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`price` decimal(10,2) NOT NULL,
`ccy` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
-  `is_published` tinyint(1) NOT NULL DEFAULT 0,
+  `is_published` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
...

Теперь мы дошли до проверки таблицы country_prices, у которой поле is_published в одном случае создано как «NOT NULL DEFAULT 0«, а во втором — «NOT NULL DEFAULT '0'«, при этом направление проверки — «--changes-for=server1«.

Изменим на --changes-for=server, что бы видеть что надо выполнить на таргет-сервере (AWS RDS Aurora) для того, что бы данные стали идентичны:

mysqldbcompare --run-all-tests --changes-for=server2 --server1=root:password@104.***.***.2 --server2=dbadmin:password@aurora-web-dev-cluster.***.us-east-2.rds.amazonaws.com \`new-eat\` --difftype=sql
WARNING: Using a password on the command line interface can be insecure.
server1 on 104.***.***.2: ... connected.
server2 on aurora-web-dev-cluster.***.us-east-2.rds.amazonaws.com: ... connected.
Checking databases new-eat on server1 and new-eat on server2
Transformation for --changes-for=server2:
ALTER DATABASE new-eat CHARACTER SET utf8 COLLATE = utf8_general_ci;
Defn    Row     Data
Type      Object Name                             Diff    Count   Check
-------------------------------------------------------------------------
TABLE     acl_permissions                         pass    pass    -
- Compare table checksum                                pass
TABLE     acl_permissions_acl_roles               pass    pass    -
- Compare table checksum                                pass
TABLE     acl_roles                               pass    pass    -
- Compare table checksum                                pass
...
TABLE     cron                                    FAIL    pass    -
- Compare table checksum                                pass
Transformation for --changes-for=server2:
ALTER TABLE `new-eat`.`cron`
CHANGE COLUMN description description varchar(255) NOT NULL DEFAULT '\'\'',
CHANGE COLUMN created_at created_at timestamp NULL DEFAULT 'NULL',
CHANGE COLUMN updated_at updated_at timestamp NULL DEFAULT 'NULL',
AUTO_INCREMENT=7;
...

Теперь видим, что на server2 для таблицы cron не совпадает AUTO_INCREMENT.

Проверяем в исходной базе:

MariaDB [new-eat]> SELECT `AUTO_INCREMENT` FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'new-eat' AND   TABLE_NAME   = 'cron'\G
*************************** 1. row ***************************
AUTO_INCREMENT: 7

В целевой:

MySQL [new-eat]> SELECT `AUTO_INCREMENT` FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'new-eat' AND   TABLE_NAME   = 'cron'\G
*************************** 1. row ***************************
AUTO_INCREMENT: 6

Исходная база:

MariaDB [new-eat]> select id from cron;
+----+
| id |
+----+
|  1 |
|  3 |
|  5 |
+----+

Целевая база:

MySQL [new-eat]> select id from cron;
+----+
| id |
+----+
|  1 |
|  3 |
|  5 |
+----+

Данные одинаковые, но следующий инкремент различается.

Аналогично с другими таблицами, например в исходной таблице order_accessories:

MariaDB [new-eat]> SELECT `AUTO_INCREMENT` FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'new-eat' AND   TABLE_NAME   = 'order_accessories'\G
*************************** 1. row ***************************
AUTO_INCREMENT: 41273
1 row in set (0.107 sec)

И целевой:

MySQL [new-eat]> SELECT `AUTO_INCREMENT` FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'new-eat' AND   TABLE_NAME   = 'order_accessories'\G
*************************** 1. row ***************************
AUTO_INCREMENT: 41272

Снова в таргете на один меньше.

Меняем номер для таблицы cron на 7:

MySQL [new-eat]> ALTER TABLE cron AUTO_INCREMENT = 7;
Query OK, 0 rows affected (0.173 sec)
Records: 0  Duplicates: 0  Warnings: 0

И заодно кодировку базы:

MySQL [new-eat]> ALTER DATABASE `new-eat` CHARACTER SET utf8 COLLATE = utf8_general_ci;
Query OK, 1 row affected (0.135 sec)

Повторяем проверку:

mysqldbcompare --run-all-tests --changes-for=server2 --server1=root:password@104.***.***.2 --server2=dbadmin:password@aurora-web-dev-cluster.***.us-east-2.rds.amazonaws.com \`new-eat\` --difftype=sql
WARNING: Using a password on the command line interface can be insecure.
server1 on 104.***.***.2: ... connected.
server2 on aurora-web-dev-cluster.***.us-east-2.rds.amazonaws.com: ... connected.
Checking databases new-eat on server1 and new-eat on server2
Type      Object Name                             Diff    Count   Check
-------------------------------------------------------------------------
TABLE     acl_permissions                         pass    pass    -
- Compare table checksum                                pass
TABLE     acl_permissions_acl_roles               pass    pass    -
- Compare table checksum                                pass
TABLE     acl_roles                               pass    pass    -
- Compare table checksum                                pass
TABLE     country_prices                          FAIL    pass    -
- Compare table checksum                                pass
Transformation for --changes-for=server2:
...
TABLE     cron                                    FAIL    pass    -
- Compare table checksum                                pass
Transformation for --changes-for=server2:
ALTER TABLE `new-eat`.`cron`
CHANGE COLUMN description description varchar(255) NOT NULL DEFAULT '\'\'',
CHANGE COLUMN created_at created_at timestamp NULL DEFAULT 'NULL',
CHANGE COLUMN updated_at updated_at timestamp NULL DEFAULT 'NULL';
...

Теперь вопросов к кодировке и AUTO_INCREMENT нет.

Но на всякий случай — пересоздал дамп с опциями --skip-lock-tables и --single-transaction, залил — теперь всё получилось ровно. Видимо в прошлый раз какая-то запись добавилась во время создания дампа.

Попробуем.

Дропаем базу в таргете:

MySQL [(none)]> drop database `new-eat`;

Перездаём её с CHARACTER utf8:

MySQL [(none)]> create database `new-eat` CHARACTER SET utf8 COLLATE = utf8_general_ci;;

Создаём новый дамп:

mysqldump -h 104.***.***.2 -u root -ppassword --no-data --skip-lock-tables --single-transaction new-eat > new-eat-prod-cloned.sql
[simterm]
У него по-прежнему 7:
...
-- Table structure for table `cron`
--
DROP TABLE IF EXISTS `cron`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cron` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
...
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
...
Загружаем в новую базу:
[simterm]
mysql -h aurora-web-dev-cluster.***.us-east-2.rds.amazonaws.com -u dbadmin -ppassword new-eat < EAT-DBs/new-eat-prod-cloned.sql

Проверяем авто-инкремент в новой базе:

MySQL [new-eat]> SELECT `AUTO_INCREMENT` FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'new-eat' AND   TABLE_NAME   = 'cron';
+----------------+
| AUTO_INCREMENT |
+----------------+
|              7 |
+----------------+

Теперь везде одинаково.

Повторяем, добавим --difftype=differ --format=vertical --width=150:

mysqldbcompare --run-all-tests --changes-for=server2 --server1=root:password@104.***.***.2 --server2=dbadmin:password@aurora-web-dev-cluster.***.us-east-2.rds.amazonaws.com \`new-eat\` --difftype=differ --format=vertical --width=150
WARNING: Using a password on the command line interface can be insecure.
server1 on 104.***.***.2: ... connected.
server2 on aurora-web-dev-cluster.***.us-east-2.rds.amazonaws.com: ... connected.
Checking databases new-eat on server1 and new-eat on server2
Defn    Row     Data
Type      Object Name                                                                                                        Diff    Count   Check
----------------------------------------------------------------------------------------------------------------------------------------------------
TABLE     acl_permissions                                                                                                    pass    pass    -
- Compare table checksum                                                                                                           pass
TABLE     acl_permissions_acl_roles                                                                                          pass    pass    -
- Compare table checksum                                                                                                           pass
TABLE     acl_roles                                                                                                          pass    pass    -
- Compare table checksum                                                                                                           pass
TABLE     country_prices                                                                                                     FAIL    pass    -
- Compare table checksum                                                                                                           pass
Object definitions differ. (--changes-for=server2)
CREATE TABLE `country_prices` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
`country_code` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
`language_code` varchar(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`price` decimal(10,2) NOT NULL,
`ccy` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
-   `is_published` tinyint(1) NOT NULL DEFAULT '0',
?                                              - -
+   `is_published` tinyint(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
...

Так сообщение станет ещё более наглядным:

...
-   `is_published` tinyint(1) NOT NULL DEFAULT '0',
?                                              - -
+   `is_published` tinyint(1) NOT NULL DEFAULT 0,
...

В одном случае, в старой базе, у нас CREATE выполняется с DEFAULT '0', а в другом — без кавычек, DEFAULT 0.

Уже было, не критично, можно пропустить.

Аналогично — проверяем все остальные таблицы и данные в них.


Similar posts