В качестве проложения поста AWS: Database Migration Service, часть 1 — обзор и пример миграции self-hosted MariaDB в AWS Aurora RDS — описание утилиты mysqldbcompare
, которая предназначена для сравнения двух баз данных.
См. документацию тут>>> .
Установка
На Arch Linux можно установить из AUR:
Либо скачать и установить вручную.
Загружаем архив:
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 в каждой из заданных баз:
проверяется наличие указанных баз на обоих хостах
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
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
.
Уже было, не критично, можно пропустить.
Аналогично — проверяем все остальные таблицы и данные в них.