Продолжение поста AWS: Database Migration Service — обзор и пример миграции self-hosted MariaDB в AWS Aurora RDS.
И всё было хорошо, пока мы не запустили первые тесты, которые начали отваливаться с ошибками:
[2020-08-28 17:13:02] local.ERROR: SQLSTATE[HY000]: General error: 1364 Field ‘id’ doesn’t have a default value
Начинаем проверять таблицы, и видим, что поле ID потеряло свой атрибут auto_increment.
Содержание
AWS Database Migration Service и AUTO_INCREMENT
Посмотрим таблицу cron_log в старой базе:
[simterm]
MariaDB [new-eat]> desc cron_log; +-----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | ...
[/simterm]
И та же таблица и колонка id в новой базе:
[simterm]
MySQL [new-eat]> desc cron_log; +-----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | PRI | NULL | | ...
[/simterm]
В поле Extra атрибута auto_increment нет.
Начинаем разбираться.
Читаем документацию High-level view of AWS DMS:
AWS DMS creates the target schema objects necessary to perform the migration. However, AWS DMS takes a minimalist approach and creates only those objects required to efficiently migrate the data. In other words, AWS DMS creates tables, primary keys, and in some cases unique indexes, but doesn’t create any other objects that are not required to efficiently migrate the data from the source. For example, it doesn’t create secondary indexes, nonprimary key constraints, or data defaults.
…
If you are performing a homogeneous migration (between two databases of the same engine type), you migrate the schema by using your engine’s native tools to export and import the schema itself, without any data.
В двух словах – DMS при копировании базы создаёт таблицы, первичные ключи, и некоторые индексы, но не создаёт внешние ключи и другие данные, которые не требуются для выполнения самой миграции.
Далее, читаем в How to Migrate from MySQL to Amazon Aurora using AWS SCT and AWS DMS:
You can use the tool to export the source database object definitions such as schema, views, stored procedures, and functions from MySQL. However, for version-compatible homogenous migration, we recommend exporting the database object definitions from MySQL using native tools. These include mysqldump and the MySQL Workbench Data Export and Import Wizard.
Т.е. – рекомендуется сначала сдампить схему базы и связанные объект используя инструменты типа mysqldump.
Попробуем.
Что нам вообще надо мигрировать? Попробуем вспомнить, что ещё есть в базе:
- views
- procedures
- triggers
- events
- indexes
auto_increments– см. https://www.techonthenet.com/mysql/auto_increment.php и https://mariadb.com/kb/en/auto_increment/
Проверим, что у нас есть в старой базе.
Source database check
Views
Наши вьюшки в старой базе – пусто:
[simterm]
MariaDB [information_schema]> SELECT TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION
-> FROM information_schema.VIEWS
-> WHERE TABLE_SCHEMA LIKE 'new-eat';
Empty set (0.110 sec)
[/simterm]
Procedurs
Тоже пусто:
[simterm]
MariaDB [information_schema]> SHOW PROCEDURE STATUS WHERE db = 'new-eat'; Empty set (0.108 sec)
[/simterm]
Triggers
Тоже ничего:
[simterm]
MariaDB [information_schema]> select trigger_schema, trigger_name, action_statement from information_schema.triggers; Empty set (0.110 sec)
[/simterm]
Events
И тут пусто:
[simterm]
MariaDB [information_schema]> SHOW EVENTS\G Empty set (0.108 sec)
[/simterm]
Indexes
А вот индексы, разумеется, есть.
Ищем в старой базе:
[simterm]
MariaDB [information_schema]> USE information_schema; Database changed MariaDB [information_schema]> SELECT * FROM `statistics` WHERE TABLE_SCHEMA LIKE 'new-eat' ORDER BY TABLE_SCHEMA ASC, TABLE_NAME ASC, INDEX_NAME ASC; +---------------+--------------+---------------------------+------------+--------------+------------------------------+--------------+-------------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | INDEX_COMMENT | +---------------+--------------+---------------------------+------------+--------------+------------------------------+--------------+-------------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+ | def | new-eat | acl_permissions | 0 | new-eat | name_unique | 1 | name | A | 26 | NULL | NULL | | BTREE | | | | def | new-eat | acl_permissions | 0 | new-eat | PRIMARY | 1 | id | A | 26 | NULL | NULL | | BTREE | | | | def | new-eat | acl_permissions_acl_roles | 1 | new-eat | fk_acl_pivot_permissions | 1 | acl_permission_id | A | 49 | NULL | NULL | | BTREE | | | | def | new-eat | acl_permissions_acl_roles | 0 | new-eat | PRIMARY | 1 | acl_role_id | A | 4 | NULL | NULL | | BTREE | | ...
[/simterm]
Сравним с базой, которую мигрировал DMS в предыдущей попытке:
[simterm]
MariaDB [information_schema]> SELECT * FROM `statistics` WHERE TABLE_SCHEMA LIKE 'new-eat' ORDER BY TABLE_SCHEMA ASC, TABLE_NAME ASC, INDEX_NAME ASC; +---------------+--------------+---------------------------+------------+--------------+------------------------------+--------------+-------------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT | INDEX_COMMENT | +---------------+--------------+---------------------------+------------+--------------+------------------------------+--------------+-------------------+-----------+-------------+----------+--------+----------+------------+---------+---------------+ | def | new-eat | acl_permissions | 0 | new-eat | PRIMARY | 1 | id | A | 26 | NULL | NULL | | BTREE | | | | def | new-eat | acl_roles | 0 | new-eat | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | | | def | new-eat | country_prices | 0 | new-eat | PRIMARY | 1 | id | A | 1 | NULL | NULL | | BTREE | | | | def | new-eat | cron | 0 | new-eat | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | | | def | new-eat | cron_log | 0 | new-eat | PRIMARY | 1 | id | A | 409 | NULL | NULL | | BTREE | | | | def | new-eat | customers | 0 | new-eat | PRIMARY | 1 | id | A | 19946044 | NULL | NULL | | BTREE | | ...
[/simterm]
А тут у нас только PRIMARY ключи.
AUTO_INCREMENT
Уже смотрели в начале поста, но для полноты картины – ещё раз в старой базе:
[simterm]
MySQL [(none)]> desc new_eat_fit_dev_schema_only.cron_log; +-----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
[/simterm]
И, конечно – auto_increment есть у почти всех таблиц, где используется ID.
mysqldump --no-data
Сделаем дамп схемы без данных, который потом накатим на новую базу, что бы создать таблицы, индексы, ключи.
Что умеет mysqldump:
[simterm]
$ mysqldump --help | grep -i dump ... -E, --events Dump events. ... -R, --routines Dump stored routines (functions and procedures). ... --triggers Dump triggers for each dumped table. ...
[/simterm]
Нам сейчас тут надо только --no-data.
Запускаем:
[simterm]
$ mysqldump -h 104.***.***.2 -u root -p*** --no-data new-eat > new-eat-cloned-dev-schema.sql
[/simterm]
Проверяем содержимое файла дампа new-eat-cloned-dev-schema.sql, например – наша табличка cron_log в нём:
... DROP TABLE IF EXISTS `cron_log`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cron_log` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, ...
Вот наш AUTO_INCREMENT, окей – можно приступать к повторной миграции.
CloudWatch logs
И давайте зафиксим логи.
Создаём файл dmsAssumeRolePolicyDocument2.json с политикой:
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": "dms.amazonaws.com"
},
"Action": "sts:AssumeRole"
}
]
}
Создаём роль:
[simterm]
$ aws --profile arseniy iam create-role --role-name dms-cloudwatch-logs-role --assume-role-policy-document file://dmsAssumeRolePolicyDocument2.json
[/simterm]
Подключаем полиси AmazonDMSCloudWatchLogsRole к роли dms-cloudwatch-logs-role:
[simterm]
$ aws --profile arseniy iam attach-role-policy --role-name dms-cloudwatch-logs-role --policy-arn arn:aws:iam::aws:policy/service-role/AmazonDMSCloudWatchLogsRole
[/simterm]
Проверяем:
(полный дебаг излишен, конечно)
Миграция схемы
Имя базы с тире и “You have an error in your SQL syntax“
При использовании тире в имени базы возможны ошибки вроде такой:
[simterm]
MySQL [information_schema]> drop database new-eat; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-eat' at line 1
[/simterm]
Что бы избежать её – используем `:
[simterm]
MySQL [information_schema]> drop database `new-eat`; Query OK, 28 rows affected (0.592 sec)
[/simterm]
Создание схемы
Создаём её заново:
[simterm]
MySQL [information_schema]> create database `new-eat`; Query OK, 1 row affected (0.132 sec) MySQL [information_schema]> use new-eat; Database changed MySQL [new-eat]> show tables; Empty set (0.125 sec)
[/simterm]
ОК – база пустая, ничего нет.
Загружаем в неё наш дамп без данных:
[simterm]
$ mysql -h dms-test-aurora-instance-1.***.us-east-2.rds.amazonaws.com -u admin -p*** new-eat < /tmp/new-eat-cloned-dev-schema.sql
[/simterm]
Проверяем:
[simterm]
MySQL [new-eat]> show tables; +---------------------------+ | Tables_in_new-eat | +---------------------------+ | acl_permissions | | acl_permissions_acl_roles | | acl_roles | | contents | | cron | | cron_log | ...
[/simterm]
Таблицы появились, проверим поле id таблицы cron_log:
[simterm]
MySQL [new-eat]> desc cron_log; +-----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | ...
[/simterm]
Автоинкремент на месте.
Индексы – другим запросом:
select index_schema,
index_name,
group_concat(column_name order by seq_in_index) as index_columns,
index_type,
case non_unique
when 1 then 'Not Unique'
else 'Unique'
end as is_unique,
table_name
from information_schema.statistics
where table_schema like'new-eat'
group by index_schema,
index_name,
index_type,
non_unique,
table_name
order by index_schema,
index_name;
Выполняем:
[simterm]
MySQL [new-eat]> select index_schema,
-> index_name,
-> group_concat(column_name order by seq_in_index) as index_columns,
-> index_type,
-> case non_unique
-> when 1 then 'Not Unique'
-> else 'Unique'
-> end as is_unique,
-> table_name
-> from information_schema.statistics
-> where table_schema like'new-eat'
-> group by index_schema,
-> index_name,
-> index_type,
-> non_unique,
-> table_name
-> order by index_schema,
-> index_name;
+--------------+-----------------------------+-------------------------------+------------+------------+---------------------------+
| index_schema | index_name | index_columns | index_type | is_unique | table_name |
+--------------+-----------------------------+-------------------------------+------------+------------+---------------------------+
| new-eat | acl_roles_unique | name | BTREE | Unique | acl_roles |
| new-eat | code_unique | code | BTREE | Unique | prices |
| new-eat | code_unique | code | BTREE | Unique | products |
| new-eat | fk_acl_pivot_permissions | acl_permission_id | BTREE | Not Unique | acl_permissions_acl_roles |
| new-eat | fk_cron_log_cron | cron_id | BTREE | Not Unique | cron_log |
...
[/simterm]
Индексы на месте.
Запуск Database migration task
Переходим к DMS.
Выбираем созданную ранее Database migration task, выбираем Modify, или создаём новую.
В Task settings переключаем Target table preparation mode в Do nothing – у нас уже есть все таблицы, нам нужны только данные:
Сохраняем, и повторяем миграцию:
DMS Table error и “Cannot add or update a child row: a foreign key constraint fails“
И тут же статус задачи меняется на Running with errors, и некоторые таблицы выпадают в статус Table error:
Останавливаем задачу, и проверяем логи CloudWatch:
Ищем по имени таблицы, например acl_permissions_acl_roles:
2020-08-31T12:35:49 [TARGET_LOAD ]E: RetCode: SQL_ERROR SqlState: HY000 NativeError: 1452 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-5.6.10]Cannot add or update a child row: a foreign key constraint fails (`new-eat`.`acl_permissions_acl_roles`, CONSTRAINT `fk_acl_pivot_roles` FOREIGN KEY (`acl_role_id`) REFERENCES `acl_roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) [1022502] (ar_odbc_stmt.c:4734)
В общем-то понятно – данные в таблицах ещё неполные, и MySQL ругается на заданный ему CONSTRAINT:
[simterm]
MySQL [new-eat]> show create table acl_permissions_acl_roles; ... CONSTRAINT `fk_acl_pivot_permissions` FOREIGN KEY (`acl_permission_id`) REFERENCES `acl_permissions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_acl_pivot_roles` FOREIGN KEY (`acl_role_id`) REFERENCES `acl_roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ...
[/simterm]
“Наша песня хороша – начинай сначала” (с)
Дропаем базу, создаём пустую:
[simterm]
MySQL [(none)]> drop database `new-eat`; Query OK, 25 rows affected (1.181 sec) MySQL [(none)]> create database `new-eat`; Query OK, 1 row affected (0.131 sec)
[/simterm]
Заново накатываем схему из дампа:
[simterm]
$ mysql -h dms-test-aurora-instance-1.***.us-east-2.rds.amazonaws.com -u admin -p*** new-eat < new-eat-cloned-dev-schema.sql
[/simterm]
Отключаем проверку ограничений:
[simterm]
MySQL [new-eat]> SET GLOBAL FOREIGN_KEY_CHECKS=0; ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
[/simterm]
Ага, счаз)
Гуглим, находим пример для RDS тут>>>, пробуем:
[simterm]
MySQL [new-eat]> CALL mysql.rds_set_fk_checks_off(); ERROR 1305 (42000): PROCEDURE mysql.rds_set_fk_checks_off does not exist
[/simterm]
Nope.
Снова гуглим, находим документ Migrating Your Databases to Amazon Aurora:
Note: If you have foreign key constraints in your source schema, when creating your target endpoint you need to enter the following for Extra connection attributes in the Advanced section: initstmt=SET FOREIGN_KEY_CHECKS=0
Редактируем Target endpoint, в Ednpoint-specific settings добавляем initstmt=SET FOREIGN_KEY_CHECKS=0:
Перезапускаем задачу, и все данные прошли, таблицы синхронизированы:
Готово.
