Изменения схемы MySQL в реальном времени на Amazon RDS с Percona - пошаговое руководство

Изменения схемы MySQL в реальном времени на Amazon RDS с Percona - пошаговое руководство

В dubizzle OLX мы недавно столкнулись с препятствием в проекте, когда нам пришлось изменить схему примерно для 130 таблиц в нашей производственной базе данных с репликацией главный / подчиненный. Самым простым возможным решением было воссоздать те же таблицы с новыми схемами, скопировать данные и обновить наше приложение, чтобы оно указывало на новые. Это было бы невозможно без значительного простоя всего веб-сайта, что было недопустимо. Итак, мы использовали Percona Toolkit для MySQL, чтобы выполнить эту работу, и мы хотели бы поделиться своими знаниями в этом посте.

Что такое Percona Toolkit для MySQL?

Percona Toolkit - это набор инструментов командной строки с открытым исходным кодом для выполнения различных задач базы данных MySQL. Еще раз подумайте о проблеме, о которой мы говорим, и о том, как мы могли бы решить ее вручную.

• Восстановите около 130 таблиц с обновленной схемой.
• Выключите веб-сайт или переведите его в режим только для чтения, чтобы обеспечить нулевое расхождение данных между старыми и новыми таблицами.
• Скопируйте все из старых таблиц в новые.
• Восстановите внешние ключи.
• Переименуйте новые и старые таблицы или обновите приложение, чтобы оно указывало на новые.
• Повторно разверните веб-сайт.

Другое решение заключалось в настройке сложного набора триггеров, операций копирования / чтения / записи и наличии плана отката. Все это звучит очень рискованно, управлять им самостоятельно, не ожидая простоя, и именно здесь Percona Toolkit подходит для выполнения этой работы за вас.

Percona Toolkit содержит набор инструментов для различных задач. В этом посте мы собираемся обсудить pt-online-schema-change.

Подготовка к большому дню

Просто протестировать инструмент на локальном компьютере перед тем, как приступить к работе, - это вообще не план. В вашем распоряжении должна быть аналогичная копия продукции, чтобы проверить, как будет работать изменение. По этой причине мы экспериментировали pt-online-schema-change на установке, подобной производственной, которую мы называем промежуточной средой. Убедиться, что каждый параметр / конфигурация в промежуточной базе данных точно такой же, как и в производственной среде, является очень важной частью тестирования того, как миграция будет выполняться в производственной среде.

Примечание об экземплярах AWS RDS MySQL: AWS RDS не дает вам СУПЕР-привилегий для ваших экземпляров MySQL, поэтому вам придется поэкспериментировать и настроить группу параметров БД. Самый важный параметр в контексте этого сообщения - log_bin_trust_function_creators. Значение по умолчанию для этого в большинстве случаев - 0 (отключено).

Цитата из документации MySQL здесь: эта переменная применяется, когда включено двоичное ведение журнала. Он контролирует, можно ли доверять создателям сохраненных функций, чтобы они не создавали сохраненные функции, которые вызовут запись небезопасных событий в двоичный журнал. Если установлено значение 0 (по умолчанию), пользователям не разрешается создавать или изменять сохраненные функции, если у них нет привилегии SUPER в дополнение к привилегии CREATE ROUTINE или ALTER ROUTINE. Значение 0 также накладывает ограничение на то, что функция должна быть объявлена ​​с характеристикой DETERMINISTIC, либо с READS SQL DATA, либо с характеристикой NO SQL. Если переменная установлена ​​в 1, MySQL не применяет эти ограничения на создание сохраненных функций. Эта переменная также применяется при создании триггера.

Поскольку pt-online-schema-change не может получить привилегию SUPER для экземпляров RDS, необходимо включить log_bin_trust_function_creators для создания триггеров и других подпрограмм. Подробнее об этом вы можете прочитать в MySQL docs.

Что вам нужно знать о pt-online-schema-change

Ранее мы обсуждали, как одним из решений может быть установка сложного набора триггеров и операций копирования / чтения / записи для выполнения изменений схемы. Внутренне инструмент pt-online-schema-change работает примерно так же. Давайте посмотрим, что он для этого делает.

• Создает пустую копию старой таблицы с новой схемой.
• Создает триггеры в старых таблицах для обновления соответствующих строк в новых.
• Скопируйте все записи из старых таблиц в новую.
• Восстановите отношения.
• Поменяйте местами старые / новые таблицы, выполнив атомарную операцию RENAME TABLE.
• Отбросьте старые таблицы (поведение по умолчанию).

Примечание. Перечисленные операции не обязательно выполняются в одном и том же порядке.

Типичная pt-online-schema-change команда для изменения схемы будет выглядеть примерно так:

pt-online-schema-change --dry-run --nocheck-replication-filters --recursion-method="dsn=D=<database>,t=dsns" --chunk-size=2000 --alter-foreign-keys-method=rebuild_constraints --alter 'add column other_id INT DEFAULT NULL, add column item_hash VARCHAR(255) DEFAULT NULL, add column json_data TEXT DEFAULT NULL, add column item_id VARCHAR(255) DEFAULT NULL, add column from_other_item TINYINT DEFAULT NULL' h=<dbhost>,D=<database>,t=<table_name>

Параметры и пояснения команд:

Примечание. Часть описания была дословно скопирована из pt-online-schema-change docs для краткости.

пробный прогон: создайте и измените новую таблицу, но не создавайте триггеры, не копируйте данные и не заменяйте исходную таблицу. Безопасный механизм, позволяющий увидеть, как может выглядеть фактическая миграция. Обратите внимание, что он не может воспроизвести точный производственный сценарий, например сценарий миграции. Думайте об этом как о пробном тесте.

nocheck-replication-filters: Прервать, если на каком-либо сервере установлен какой-либо фильтр репликации. Инструмент ищет параметры сервера, которые фильтруют репликацию, например binlog_ignore_db и replicate_do_db. Если он находит такие фильтры, он прерывается с ошибкой.

Если реплики настроены с любыми параметрами фильтрации, вы должны быть осторожны, чтобы не изменять какие-либо базы данных или таблицы, существующие на главном сервере, а не на репликах, поскольку это может привести к сбою репликации. Для получения дополнительной информации о правилах репликации см. Http://dev.mysql.com/doc/en/replication-rules.html target.

Значение по умолчанию для этого параметра - yes, поэтому, если вы не собираетесь его использовать, убедитесь, что на вашем ведомом устройстве нет фильтров репликации. Фильтры репликации - это правила, по которым принимаются решения о том, следует ли выполнять операторы или игнорировать их. Для вашего ведомого устройства эти правила определяют, какие операторы, полученные от ведущего устройства, должны быть выполнены или проигнорированы. Теперь рассмотрим случай, когда у вас есть фильтр на подчиненном устройстве, который говорит, что не выполняйте никаких ALTER операторов для table_a. Когда вы изменяете схему для table_a на главном устройстве, подчиненное устройство никогда не увидит этого изменения. В конце концов, это могло привести к сбою репликации после RENAME операции. По этой причине значение по умолчанию - yes. Если вы решите изменить его на no, узнайте о фильтрах репликации, которые у вас есть на подчиненных устройствах, прежде чем вы попадете в ситуацию, когда репликация начинает давать сбой для одной из ваших таблиц.

метод-рекурсии: определяет методы, которые инструмент использует для поиска подчиненных хостов. Методы следующие:

METHOD       USES
===========  ==================
processlist  SHOW PROCESSLIST
hosts        SHOW SLAVE HOSTS
dsn=DSN      DSNs from a table
none         Do not find slaves

Однако по разным причинам ваш экземпляр RDS может быть настроен так, чтобы не передавать правильную информацию о подчиненном хосте pt-online-schema-change, как это было в случае с нашей настройкой. Наиболее конкретный способ сделать это - создать таблицу DSN (имя источника данных), из которой pt-online-schema-change будет считывать информацию. Для этого создайте таблицу со следующей структурой:

CREATE TABLE `dsns` (`id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`));

После этого вы можете указать свои подчиненные хосты, создав для них такие записи:

INSERT INTO dsns(dsn) VALUES('h=<slave_host>,P=3306');

Указав recursion-method="dsn=D=mydatabase,t=dsns", вы сообщаете percona, что нужно найти ведомые устройства в таблице с именем dsns в базе данных mydatabase.

alter-external-keys-method: Это требуется только в том случае, если у вас есть дочерние таблицы, которые ссылаются на таблицы, которые будут изменены как часть изменения схемы. Рекомендуемый метод - rebuild_constraints, который использует ALTER TABLE в дочерних таблицах для удаления и повторного добавления ссылок на внешние ключи, которые ссылаются на новую таблицу. Два других более рискованных варианта - это drop_swap и none, и если вам случится их использовать, убедитесь, что вы знаете все тонкости. Вы можете прочитать о них в pt-online-schema-change docs.

размер фрагмента: инструмент выполняет операцию копирования небольшими фрагментами данных (фрагмент - это набор строк). Этот параметр регулирует количество строк, выбранных для операции копирования, и переопределение значения по умолчанию отключит динамическое изменение размера блока. Этот параметр не работает с таблицами без первичных ключей или уникальных индексов, поскольку они используются для разделения таблиц.

Во время изменения схемы может возникнуть ситуация, когда количество строк в блоке на одной из ваших реплик будет больше, чем на главном сервере. Это могло произойти из-за задержки реплики, и вам придется настроить chunk-size для этого. Обратите внимание, что больший размер блока означает большую нагрузку на вашу базу данных.

Ниже приведены некоторые ресурсы, чтобы узнать, как Percona Toolkit обрабатывает фрагменты.

Связанные варианты: chunk-size-limit, chunk-time и alter-foreign-keys-method.

Дополнительные ресурсы:

• Как Percona Toolkit делит таблицы на части
• Команда размера блока

alter: изменение схемы, которое вы хотите применить.

Вы можете узнать больше об остальных параметрах здесь: pt-online-schema-change docs

План миграции

• Подготовьте полноценную реплику (назовите ее промежуточной) вашей производственной среды с точной репликацией главный / подчиненный. Убедитесь, что ваша постановка достаточно заполнена данными.
• Настоятельно рекомендуется создавать искусственную нагрузку на ваши промежуточные базы данных при тестировании изменений схемы в реальном времени. Для этого вы можете использовать такие инструменты, как Selenium и JMeter.
• Убедитесь, что группа параметров БД в AWS RDS в производственной и промежуточной среде совпадает, а для log_bin_trust_function_creators задано значение 1.
• Подготовьте набор сценариев для пробных прогонов и фактических изменений схемы. Это может включать список таблиц и сценарий оболочки, который будет запускать pt-online-schema-change и сохранять журналы для каждого запуска.
• Убедитесь, что вы храните журналы для каждого запуска (как промежуточного, так и производственного).
• Выполните пробные запуски постановки и производства, чтобы увидеть ожидаемый результат.
• Попросите кого-нибудь из вашей команды DevOps помочь вам в случае, если что-то начнет разваливаться во время производственной миграции.
• Выберите подходящее время для миграции. В идеале это должно быть наименее загруженное время для вашего сайта.
• Сделайте резервные копии продукции перед миграцией на случай, если вам нужно будет восстановить.

Выходной файл журнала

Вот образец журнала одной из наших миграций.

Found 3 slaves:
  ip-some-number
  ip-some-number
  ip-some-number
Will check slave lag on:
  ip-some-number
  ip-some-number
  ip-some-number
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `some_database`.`some_table`...
Creating new table...
Created new table some_database._some_table_new OK.
Altering new table...
Altered `some_database`.`_some_table_new` OK.
2015-10-21T02:35:03 Creating triggers...
2015-10-21T02:35:03 Created triggers OK.
2015-10-21T02:35:03 Copying approximately 10000 rows...
2015-10-21T02:35:05 Copied rows OK.
2015-10-21T02:35:05 Swapping tables...
2015-10-21T02:35:06 Swapped original and new tables OK.
2015-10-21T02:35:06 Dropping old table...
2015-10-21T02:35:06 Dropped old table `some_database`.`_some_table_old` OK.
2015-10-21T02:35:06 Dropping triggers...
2015-10-21T02:35:06 Dropped triggers OK.
Successfully altered `some_database`.`some_table`.

Ошибки, замеченные на производстве

2015-10-19T12:24:52 Error copying rows from `some_database`.`some_table` to `some_database`.`_some_table_new`: 2015-10-19T12:24:52 Cannot copy table `some_database`.`some_table` because on the master it would be checksummed in one chunk but on these replicas it has too many rows:   4130 rows on ip-some-number The current chunk size limit is 4000 rows (chunk size=1000 * chunk size limit=4.0).

Разрешение: увеличьте размер блока

Заключение и результаты

pt-online-schema-change от Percona работал с нами исключительно хорошо. Миграция была очень хорошо протестирована в нашей тестовой среде и работала почти так же в производственной среде.

Полезные команды:

• Чтобы получить список таблиц с определенным столбцом:

SELECT DISTINCT TABLE_NAME  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME NOT REGEXP "^test_.*(_archive)|(_new)|(_old)$" AND TABLE_NAME REGEXP "^test_.*" AND COLUMN_NAME = 'feed_id' AND TABLE_SCHEMA ='some_schema';

• Чтобы узнать количество подключений:

SHOW STATUS WHERE `variable_name` = 'Threads_connected';

• Чтобы узнать, заблокирована ли какая-либо таблица:

SHOW OPEN TABLES WHERE `Table` LIKE '%[TABLE_NAME]%' AND `Database` LIKE '[DBNAME]' AND In_use > 0;

• Чтобы найти все внешние ключи в таблице:

SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = '<table_name>';

Документация по инструментарию Percona (2.2+)

• Pt-online-schema-change docs

Другие ресурсы:

• Правила репликации MySQL
• MySQL Показать подчиненные хосты
• Контрольная сумма таблицы Percona
• Проверить и исправить несоответствия репликации MySQL

Примечания:
* Для этих миграций использовалась версия pt-online-schema-change 2.2.14
* Это сообщение также было опубликовано здесь и здесь тот же автор.