Создание резервной копии большой удалённой БД средствами YII2

Недавно мне довелось выполнить один маленький и интересный заказ Его суть заключалась в том, чтобы по нажатию кнопки с удалённого сервера БД скопировать БД (её приблизительный размер составляет почти 800Mb) на тестовый сервер и после выполнить ряд изменений в структурах таблиц.

Казалось бы, что нет ничего проще берём выполняем на удалённом сервере запрос вида:

mysqldump -uLogin -pPassword db_name > db_name.sql

и забрать потом файл, но в моём случае это оказался сервер БД без возможности выкачать оттуда файл, поэтому мне пришлось изловчиться и написать свой механизм экспорта из этой удалённой БД средствами YII2, с которым я был на тот момент не так сильно знаком.

Для начала мной был реализован простой интерфейс, описанный в файле views/db/index.php и представленный на Рисунке 1.

Рисунок 1. – Внешний вид

Для удобства все действия были разделены на 3 части:

1. Экспорт БД;
2. Импорт БД;
3. Применение изменений;
4. Удаление файлов бекапа.

Справа разместился блок для вывода информации о ходе выполнения операций.

Далее были описаны JQuery скрипты в файле web/js/common.js

Для определения необходимости в детальном логировании основных данных в консоль, была глобально определена константа DEBUG = false.

Переменные dbExportAll, dbImportAll также были определены глобально, для того чтобы можно было точно определить завершённость процесса импорта и экспорта.

Ещё для удобства были инициализированы константы URL_TABLES, URL_EXPORT, URL_IMPORT, URL_REMOVE, URL_MIGRATE. Из значения описаны в файле views/db/index.php:59

В служебных целях были описаны следующие JS функции:

1. isTrue – проверяет все ли элементы массивов dbExportAll, dbImportAll равны true;
2. startDB – предшествует началу обработки нажатий на кнопки, и блокирует все дальнейшие действия на странице;
3. finishFailDB – вызывается в случае невозможности выполнить действие;
4. finishSuccessDB — вызывается в случае успешного выполнения всех действий после нажатия на наши кнопки;
5. count – аналог одноимённой PHP функции;
6. logMess – выводит в лог массив;
7. logMessStr – выводит в лог строку.

Экспорт

Наконец мы подошли к тому зачем мы всё затевали, к функции,

$('body').on('click', '#dbExportAll', function () {});

В начале работы мы отправляем запрос на получение списка всех таблиц в удалённой БД и полученными данными заполняем массив dbExportAll. Далее мы проходимся по всему полученному списку таблиц и отправляем запросы на создание бекапов каждой таблицы отдельно, чтобы запросы не были слишком длинными во времени. Все полученные запросы обрабатываются в

DbController()->actionExport()

и

DbWrap::export($table, $date)

.

Пример длительности запросов приведён на Рисунке 2. Из Рисунка 2 видно, что на экспорт такой большой таблицы требуется минимум 134 секунды (2.2 минуты), и это без учёта того времени, которое запрос стоял в очереди на выполнение.

Рисунок 2. – Сведения об экспорте таблицы mis

Но из лога (Рисунок 3) видно, что на экспорт большинства таблиц требуется очень мало времени, менее секунды.

Рисунок 3. – Лог экспорта

А из Рисунка 4 видно, что на экспорт всех таблиц достаточно 7 минут.

Рисунок 4. – Тайминг экспорта

Импорт

Перед выполнением импорта необходимо выполнить экспорт, если ранее экспорт не выполнялся или файлы бекапа были удалены.

Работа импорта также начинается с отработки JS скрипта описанного в функции

$('body').on('click', '#dbImportAll', function () {});

Аналогично экспорту производится получение списка всех таблиц и инициализация переменной dbImportAll. Далее таблицы поштучно отправляются на импорт за который отвечают

DbController()->actionImport()

и

DbWrap::importAll($table)

Логика работы скрипта в этом месте простая, находим самый последний файл и выполняем команду вида «mysql -uroot -pPass db < file.sql» (если пароль пустой то команду отправляем без указания пароля).

Из Рисунка 5 видно, что на выполнение импорта хватает и 5 минут.

Рисунок 5. – Тайминг импорта

Применение изменений

Применение изменений начинается с отработки JS функции

$('body').on('click', '#dbMigrate', function () {...});

в которой мы отправляем запрос к контроллеру DbController и его методу actionMigrate, который в свою очередь вызывает DbWrap::migrate($mess). Функция migrate описывает транзакцию в рамках которой отправляется ряд запросов на изменение таблиц.

После отработки применения изменений выводится соответствующие сообщение с подробным описанием в случае ошибки (в нашем случае ошибка связана с тем что изменения уже были применены ранее), или с затраченным временем на выполнение (см. Рисунок 6).

Рисунок 6 – Миграция

Удаление

А удаление начинается с отработки JS функции

$('body').on('click', '# dbRemove, function () {});

в которой мы отправляем запрос к контроллеру DbController и его методу actionRemove, который в свою очередь вызывает

DbWrap::remove()

Функция m remove описывает определение семейства операционной системы, на которой запущен PHP скрипт и выполняет команды rm –rf или RD /S/q в зависимости от ОС.

Результат

В результате мы получили возможность быстро за 3 клика применить изменения на тестовом сервере, или поднять базу в случае её падения. Кроме этого у нас есть заготовка для выполнения других долгих запросов основным принципом которых является

Разделяй и властвуй

Справедливости ради стоит заметить, что все замеры были произведены на локальном сервере, работающем на PHP7 с относительно мощным процессором и 1.5Gb оперативной памяти. Поэтому производительность данного скрипта скорее всего будет ниже на слабых серверах и хостингах.

---

Вовремя вспомнил, для тех кто хочет детально ознакомиться с исходным, исходники размещены на Bitbucket

---

Литература:

1. http://sitear.ru/material/mysql-backups
2. http://www.yiiframework.com/doc-2.0/yii-db-connection.html
3. http://php.net/manual/ru/function.fopen.php
4. http://php.net/manual/ru/function.fwrite.php
5. http://php.net/manual/ru/function.shell-exec.php
6. https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
7. http://sc-blog.ru/импорт-экспорт-базы-mysql-консоль/

Автор: Тарас Викторович

Источник