Для синхронизации файлов проекта, ведения история мы используем системы контроля версий, например, Git. Однако, когда у меня встал вопрос о контроле версий структуры базы MySQL — удовлетворяющего решения найти не удалось.
Замечу, во многих фреймворках и ORM существуют необходимые механизмы «из коробки» — миграции, версионность и т.д. А вот для нативной работы с MySQL — приходится все делать ручками. И пришла идея попытаться создать автоматическую систему для отслеживания изменений.
Задача
Хотелось менять структуру базы данных на development-сервере, автоматически обновлять ее на production-сервере, а также видеть историю всех изменений в Git, так как он уже использовался для контроля кода. И чтобы все бесплатно и просто!
Для этого необходимо получать информацию о всех запросах на изменение (CREATE, ALTER, DROP).
Решение, начало
MySQL поддерживает 3 способа ведения логов — это логи ошибок (error log), логи всех запросов (general log) и логи медленных запросов (slow log).
Первый вариант я пока не использовала, но есть идеи (подробности ниже). Теперь про два остальных варианта.
Логи можно записывать либо в таблицы mysql, либо в файлы. Формат файлов логов достаточно неудобный и я решила использовать таблицы.
Внимание, так как речь идет о ВСЕХ mysql-логах данное решение стоит использовать только на dev-сервере без нагрузки на MySQL!
Важным моментом является определение базы данных к которой идет запрос, так как в SQL-тексте самого запроса — этой информации может не быть.
CREATE TABLE /*DB_NAME.*/TABLE_NAME
Оказалось, что general log пишет только номер потока сервера и чтобы определить базу данных, пришлось бы искать запись для этого потока с указанием используемой БД. К тому же логи содержат информацию о подключении и отключении к серверу.
Структура mysql.general_log
А вот в slow_log как раз таки нашлось все что нужно: во-первых, логи содержат только информацию о запросах, во-вторых, запоминается название базы данных, в контексте которой идут запросы.
Структура mysql.slow_log
Настроить slow log для записи всех запросов очень просто в my.cnf
log-output=TABLE
slow_query_log = 1
long_query_time = 0
log_slow_admin_statements = 1
log_slow_admin_statements нужно для записи ALTER запросов.
Обработка логов
Итак, нам нужно постоянно забирать все запросы, выбирать из них запросы на изменение структуры БД и очищать все остальные.
Таблица mysql.slow_log не содержит ключевого поля, а также ее нельзя заблокировать (а значит частично удалять записи). Поэтому создадим таблицу, которая будет нас устраивать.
Структура change_structure_log
Для ротации логов небольшая процедура:
DELIMITER $$
CREATE PROCEDURE `change_structure_log_rotate`()
BEGIN
-- Definition start
USE mysql;
drop table if exists slow_log_copy;
CREATE TABLE slow_log_copy LIKE slow_log;
RENAME TABLE slow_log TO slow_log_old, slow_log_copy TO slow_log;
insert into change_structure_log (start_time,query_time,sql_text, db) select start_time, query_time, sql_text,db from slow_log_old where sql_text like "ALTER%" OR sql_text like "CREATE%" OR sql_text like "DROP%";
drop table slow_log_old;
-- Definition end
END
$$
А ее можно добавить в планировщик MySQL:
CREATE EVENT `event_archive_mailqueue`
ON SCHEDULE EVERY 5 MINUTE STARTS CURRENT_TIMESTAMP
ON COMPLETION NOT PRESERVE
ENABLE
COMMENT '' DO
call change_structure_log_rotate();
Итак, у нас есть таблица со всеми запросами на изменение структуры. Теперь напишем небольшой скрипт для ее обработки. Я не буду использовать какой-то конкретный язык (лично я пишу на PHP, но из-за большого количества зависимостей в коде смысла выкладывать код нет).
Итак:
1. Проходим в цикле все записи таблицы change_structure_log.
2. Для sql_text регуляркой вытаскиваем имя БД если оно, есть, например
^ALTERs+TABLEs+(?:(?:ONLINE|OFFLINE)s+)?(?:(?:IGNORE)s+)?(?:([^s.]+).s*)?([^s.]+)
3. Если в запросе не указано название db — используем его из поля db.
4. Записываем в папку проекта с Git все записи, соответствующих БД. Например, 20140508150500.sql.log. Для запросов без БД в начале пишем use $DB;
5. Удаляем все обработанные записи.
Итак, у нас в папке проекта появились новые файлы с запросами изменения БД, теперь мы можем закоммитить их в обычном режиме в нашем Git-клиенте.
Далее на production-сервере пишем скрипт отслеживающий появление новых файлов и исполняем их в mysql. Так, при обновлении git-репозитария на production-сервере вместе с кодом, мы изменяем базу данных до состояния на dev-сервере.
Сразу скажу, что данное решение достаточно примитивно и не поддерживает многих функций Git. Однако, основываясь на нем мы можем делать и более крутые вещи: по изменению конкретных таблиц — например, автоматически изменять файлы нашей ORM.
P.S. Если мы хотим также узнавать о медленных запросах — мы можем интегрировать это в нашу систему, для этого нужно убрать фильтр из процедуры и в нашем скрипте делать запрос на медленные запросы и сохранять их.
Автор: sferrka
