Вопрос или проблема
У меня есть производственный кластер баз данных (innodb, если это имеет значение), который необходимо регулярно архивировать и импортировать в среду разработки.
В настоящее время я использую простой mysqldump для создания *.sql файла, который передается в среду разработки и импортируется.
Проблема заключается в том, что размер файла сейчас составляет около 3,9 Гб, и его импорт занимает более часа. Должен быть более быстрый способ сделать это, так как расширение кластера innodb занимает всего несколько минут и выполняет полное зеркалирование базы данных.
Я пробовал использовать метод mysqldump --tab=dir, но он экспортирует все таблицы по сути в алфавитном порядке, и при попытке импорта таблиц *.sql файлов возникает ошибка из-за внешних ключей.
Есть ли способ заставить mysqldump правильно экспортировать таблицы и данные отдельно, чтобы их можно было импортировать?
Есть ли лучший способ обработки этого переноса данных, чтобы для импорта в нашу среду разработки не требовалось более 1 часа?
RomeoNinov уже предложил один способ — асинхронная репликация, но это не сработает, если вы начнете разрешать обновления в среде разработки.
Вы можете сэкономить время, если будете делать mysqldump реже, а затем вручную копировать журналы бинарных данных из производственной среды для применения откатов, например, вместо ежедневного применения mysqldumps вы делаете это раз в неделю, затем на стороне разработки удаляете текущую базу данных, импортируете (возможно устаревший) экспорт и применяете бинарные журналы из производственной среды.
Часть проблемы с вашим текущим механизмом заключается в том, что он выполняется в одном потоке как на исходном, так и на целевом узле. Если у вас есть несколько баз данных, вы можете запускать каждую в отдельном потоке.
Самый быстрый механизм для создания свежей копии — это использование снимков — у вас может не быть в настоящее время хранилища, которое это поддерживает (например, SAN), но вы можете добавить snapraid или DRBD без сильных нарушений (возможно, имеет смысл применить это к новому реплике в производственной среде в качестве источника).
Менее сложная версия этого — просто скопировать файлы базы данных.
Как копирование файлов, так и создание снимков действительно требуют, чтобы источник DBMS был отключен, когда делается снимок. Восстановление MySQL после сбоев работает очень хорошо, но не идеально.
поэтому при попытке импорта таблиц *.sql файлов возникает ошибка из-за внешних ключей.
Хмм, я думаю, вы путаете 2 проблемы здесь.
Импорт не удастся, если вы просто попытаетесь последовательно импортировать последовательную резервную копию данных для каждой таблицы без обертывания её в SET FOREIGN_KEY_CHECKS=0; … SET FOREIGN_KEY_CHECKS=1 (mysqlbackup должен делать это автоматически, если вы указываете ему осуществлять резервное копирование базы данных).
Если вы уже делаете это и все еще наблюдаете проблемы, тогда вам нужно обратить внимание на вашу изоляцию транзакций.
Ответ или решение
Вопрос, касающийся экспорта и восстановления большой базы данных MySQL в среде разработки, требует тщательного обдумывания и применения принципов оптимизации. В условиях, когда текущий метод использования mysqldump приводит к созданию файлов большого размера (около 3.9 Гб) и требует более часа на импорт, необходимо искать более эффективные подходы. Давайте более подробно рассмотрим различные решения, которые можно применить.
Теория
Сначала рассмотрим природу проблемы. Вашей целью является регулярное резервное копирование производственной базы данных и ее импорт в среду разработки. Основная проблема в том, что текущий процесс медленный и неэффективный в условиях роста объема данных.
-
Однопоточное выполнение:
mysqldumpпо умолчанию выполняется в однопоточном режиме, что ограничивает скорость как экспорта, так и импорта данных. -
Порядок экспорта и внешний ключи: При использовании
mysqldump --tab=dirданные экспортируются в алфавитном порядке, что может вызывать проблемы с внешними ключами при импорте. -
Частота операций: Частое выполнение операций
mysqldumpи последующий импорт требуют много времени и ресурсов.
Пример
Рассмотрим другие подходы, которые могут решить эти проблемы:
-
Асинхронная репликация: Хотя это может нарушить целостность данных в Dev-среде при наличии записей, это эффективное решение, так как репликация осуществляется в фоновом режиме и поддерживает актуальное состояние базы.
-
Инкрементальные бэкапы: Использование механизма бинарных журналов (binlogs) MySQL. Выполнение полного экспорта (
mysqldump) реже, но с дальнейшим применением инкрементальных изменений через binlogs, что сократит объем дублируемых данных. -
Файловые снимки (Snapshots): Если ваша инфраструктура поддерживает это, например, через SAN, Snapraid или DRBD, создание моментальных снимков может быть наиболее быстрым методом, однако требует временного отключения базы данных.
-
Параллельный экспорт: Экспорт и импорт относящихся к разным базам данных в многопоточном режиме может ускорить процесс.
-
Отключение внешних ключей: При импорте данных можно временно отключить проверки внешних ключей, используя
SET FOREIGN_KEY_CHECKS=0;, что позволит избежать ошибок импортирования из-за порядка строк.
Применение
Для внедрения вышеизложенных теорий в вашу среду, можно принять следующие меры:
Инфраструктурные изменения:
- Убедитесь, что у вас есть возможность использовать Point-in-Time снапшоты, или внедрите системы, такие как DRBD или Snapraid, которые позволят практически мгновенно копировать состояние базы данных.
- Рассмотрите модернизацию серверного оборудования или сети передачи данных для работы на SSD, что значительно повысит скорость ввода-вывода при операциях с большими объемами данных.
Настройки MySQL:
- Исследуйте возможность разбить
mysqldumpна несколько потоков. Существуют кастомные скрипты и утилиты, которые могут помочь с параллельным экспортом и импортом. - Реализуйте дополнительные скрипты для управления binlogs. Периодическое использование
mysqlbinlogдля регистрация изменений позволит поддерживать актуальность данных без полного экспорта.
Планирование и автоматизация:
- Автоматизируйте процесс резервного копирования и восстановления с помощью crontab или других планировщиков задач, что гарантирует регулярное выполнение операций в оптимальное время и минимизирует влияние на пользовательскую нагрузку.
- Внедрите мониторинг и оповещения об успешности или сбоях операций, чтобы своевременно реагировать на проблемы.
Подход к экспорту и импорту данных с использованием данных методов значительно сократит время простоя и повысит эффективность работы с корпоративной базой данных MySQL. Однако выбор конкретного решения должен зависеть от доступных ресурсов и специфики вашего проекта.