В качестве проложения поста AWS: Database Migration Service, часть 1 — обзор и пример миграции self-hosted MariaDB в AWS Aurora RDS – описание утилиты mysqldbcompare, которая предназначена для сравнения двух баз данных.
См. документацию тут>>>.
Содержание
Установка
На Arch Linux можно установить из AUR:
[simterm]
$ yay -S mysql-utilities
[/simterm]
Либо скачать и установить вручную.
Загружаем архив:
[simterm]
$ 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
[/simterm]
Пробуем установить:
[simterm]
$ 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'
[/simterm]
Добавляем Python-модуль configparser:
[simterm]
$ yay -S python-configparser
[/simterm]
И ещё раз установка:
[simterm]
$ 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'
[/simterm]
Та что ж ты…
Но проблема старая, известная – открываем на редактирование файл setup.py, в импортах меняем:
... import ConfigParser ...
На:
... import configparser ...
И ещё раз запускаем сборку:
[simterm]
$ cd mysql-utilities-1.6.5 $ python2 setup.py build
[/simterm]
Проверяем каталог buid:
[simterm]
$ 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
[/simterm]
Устанавливаем:
[simterm]
$ 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 ...
[/simterm]
Опции mysqldbcompare
mysqldbcompare может выполнить сравнение одной базы на разных серверах, разных баз на разных серверах, или одной базы на одном и том же сервере.
Общий синтаксис:
[simterm]
mysqldbcompare --server1=<DB_HOST_1> --server=<DB_HOST_2> <DB_NAME_1>:<DBNAME_2>
[/simterm]
Если имя баз данных на обоих хостах одинаковое – можно указать только <DBNAME_1>.
mysqldbcompare выполнит сравнение таблиц, views, триггеров, процедур, функций и events в каждой из заданных баз:
- проверяется наличие указанных баз на обоих хостах
- object-compare: проверка наличия всех объектов в обеих базах
- diff: сравнение CREATE объектов в обехи базах
- row-count: проверка количества записей в таблицах
- 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
[simterm]
$ 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.
[/simterm]
mysqldbcompare обнаружил первое несоответствие – разные кодировки для баз, и сразу остановил дальнейшую проверку.
Добавляем --run-all-tests или -a:
[simterm]
$ 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 ...
[/simterm]
Теперь мы дошли до проверки таблицы country_prices, у которой поле is_published в одном случае создано как “NOT NULL DEFAULT 0“, а во втором – “NOT NULL DEFAULT '0'“, при этом направление проверки – “--changes-for=server1“.
Изменим на --changes-for=server, что бы видеть что надо выполнить на таргет-сервере (AWS RDS Aurora) для того, что бы данные стали идентичны:
[simterm]
$ 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; ...
[/simterm]
Теперь видим, что на server2 для таблицы cron не совпадает AUTO_INCREMENT.
Проверяем в исходной базе:
[simterm]
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
[/simterm]
В целевой:
[simterm]
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
[/simterm]
Исходная база:
[simterm]
MariaDB [new-eat]> select id from cron; +----+ | id | +----+ | 1 | | 3 | | 5 | +----+
[/simterm]
Целевая база:
[simterm]
MySQL [new-eat]> select id from cron; +----+ | id | +----+ | 1 | | 3 | | 5 | +----+
[/simterm]
Данные одинаковые, но следующий инкремент различается.
Аналогично с другими таблицами, например в исходной таблице order_accessories:
[simterm]
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)
[/simterm]
И целевой:
[simterm]
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
[/simterm]
Снова в таргете на один меньше.
Меняем номер для таблицы cron на 7:
[simterm]
MySQL [new-eat]> ALTER TABLE cron AUTO_INCREMENT = 7; Query OK, 0 rows affected (0.173 sec) Records: 0 Duplicates: 0 Warnings: 0
[/simterm]
И заодно кодировку базы:
[simterm]
MySQL [new-eat]> ALTER DATABASE `new-eat` CHARACTER SET utf8 COLLATE = utf8_general_ci; Query OK, 1 row affected (0.135 sec)
[/simterm]
Повторяем проверку:
[simterm]
$ 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'; ...
[/simterm]
Теперь вопросов к кодировке и AUTO_INCREMENT нет.
Но на всякий случай – пересоздал дамп с опциями --skip-lock-tables и --single-transaction, залил – теперь всё получилось ровно. Видимо в прошлый раз какая-то запись добавилась во время создания дампа.
Попробуем.
Дропаем базу в таргете:
[simterm]
MySQL [(none)]> drop database `new-eat`;
[/simterm]
Перездаём её с CHARACTER utf8:
[simterm]
MySQL [(none)]> create database `new-eat` CHARACTER SET utf8 COLLATE = utf8_general_ci;;
[/simterm]
Создаём новый дамп:
[simterm]
$ 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
[/simterm]
Проверяем авто-инкремент в новой базе:
[simterm]
MySQL [new-eat]> SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'new-eat' AND TABLE_NAME = 'cron'; +----------------+ | AUTO_INCREMENT | +----------------+ | 7 | +----------------+
[/simterm]
Теперь везде одинаково.
Повторяем, добавим --difftype=differ --format=vertical --width=150:
[simterm]
$ 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
...
[/simterm]
Так сообщение станет ещё более наглядным:
... - `is_published` tinyint(1) NOT NULL DEFAULT '0', ? - - + `is_published` tinyint(1) NOT NULL DEFAULT 0, ...
В одном случае, в старой базе, у нас CREATE выполняется с DEFAULT '0', а в другом – без кавычек, DEFAULT 0.
Уже было, не критично, можно пропустить.
Аналогично – проверяем все остальные таблицы и данные в них.
