Обзор средств синхронизации баз данных MySQL

При разработке современных веб-приложений сложно недооценить пользу от использования систем контроля версий. Применительно к файлам разрабатываемого продукта, мы способны отследить любые этапы производства в любой момент, начиная с первой ревизии. Инструменты, помогающие нам в этом, на сегодняшний день популяризированы, считаются хорошим тоном при разработке, а во многих случаях успешное производство без их применения невозможно в принципе. А какие возможности мы имеем, когда возникает необходимость проследить изменения не в файлах, а в базах данных проекта? Под катом я поделюсь информацией о существующих средствах, с которыми мне пришлось ознакомиться.

1. PHP SQLDIFF, a.k.a. SQLDiff

(http://phpsqldiff.sourceforge.net/)

PHP-скрипт, позволяющий увидеть полные различия (как в структуре, так и в данных) между любыми таблицами двух БД. В инструменте отсутствуют какие-либо средства по автоматической синхронизации структуры или данных – предоставляется лишь визуальная информация. Еще из существенных недостатков – возможность подключения только к БД, к которым возможен доступ напрямую (не через ssh-тоннель). Медленная скорость работы на больших объемах данных (работа через pear-модуль, который не блещет ни новизной, ни скоростью). Считаю данный скрипт весьма полезным для разработчика в случаях, когда необходимо понимание и визуальное представление различий между разными таблицами — имеет удобный интерфейс, быстрая настройка. Охарактеризую скорее как полезную карманную утилиту для быстрого получения понимания о рассинхронизации таблиц, которые в теории должны быть идентичны, нежели как серьезный инструмент, который можно применить для автоматизации процессов синхронизации.

2. LIQUIBASE

(http://www.liquibase.org/)

Удобный многофункциональный и простой в использовании мигратор структуры БД на java. Вижу для себя в этом плюс, если использовать в связке с Jenkins.
Пример (host1 — сервер, с которого необходимо копировать структуру БД; host2 — сервер, на который необходимо перенести структуру с host1):

java -jar liquibase.jar --driver=com.mysql.jdbc.Driver \
      --classpath=mysql-connector-java-5.1.xx-bin.jar \
      --logFile=db.ExampleChangelog.xml \
      --url="jdbc:mysql://host2" \
      --defaultSchemaName=db_name \
      --username=username \
      --password="password" \
      --referenceUrl=jdbc:mysql://host1 \
      --referenceUsername=username \
      --referencePassword="password" \
      diffChangeLog > ChangeSet.xml

Формирует changeset в формате xml, дальнейшая миграция которого приводит структуру бд на host2 в состояние, идентичное host1.

Запуск миграции:

java -jar liquibase.jar --driver=com.mysql.jdbc.Driver \
     --classpath=/path/to/classes \
     --changeLogFile=ChangeSet.xml \
     --url="jdbc:mysql://host2" \
     --username=user \
     --password="password" \
     migrate

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

Changeset также можно формировать и в других форматах — в sql, в json (не только в xml). Формирование changeset'а в sql будет полезным в тех случаях, когда для миграции используются средства другой утилиты.

3. schemasync

(http://schemasync.org/)

Инструмент для синхронизации структуры БД. Для работы необходим python и соответствующий интерфейс для mysql.
Из существенных различий с liquibase:
— schemasync создает не только ченжсет, но и файл, позволяющий откатить изменения (самое важное и самое ценное преимущество, хотя, на мой взгляд, не избавляет от необходимости делать backup перед синхронизацией)
— liquibase позволяет не только получить ченжсет, но и сразу же запустить миграцию средствами самой утилиты. Может быть, не киллер-фича, но все равно удобно и полезно

schemasync работает только с sql – никаких промежуточных xml и аналогов – вижу для себя в этом как преимущества, так и недостатки.
Очень лаконичный синтаксис, минимум настроек. Позволяет не синхронизировать комментарии и автоинкремент (настраивается) — безусловный плюс.

Пример использования:

schemasync mysql://user:pass@dev-host:3306/dev_db mysql://user:pass@prod-host:3306/production_db

4. MAATKIT data sync

(http://www.maatkit.org/doc/)
mk-table-sync — утилита для синхронизации данных таблиц. Сразу же упомяну, что maatkit — это целый комплекс средств для работы с MySQL, который предоставляет возможности, не заложенные в оригинальном MySQL. Это и не удивительно, учитывая, что данный продукт создан Percona – мы уже привыкли видеть от них продукты а-ля «Мы возьмем MySQL и добавим в него то, что в нем уже давно должно было быть».
Эффективно работает с таблицами только при наличии первичного ключа или уникального индекса (что, в общем-то, оправданно).
Имеет внушительное количество опций и настроек, позволяет синхронизировать master-slave, master-master конфигурации. Позволяет запускать автоматическую синхронизацию, но нас интересует в первую очередь не этим, а возможностью создать именно лог изменений. Думаю, по этой утилите можно написать отдельную статью, поэтому не буду углубляться в настройки – акцентирую лишь на том, что опций действительно много и они дарят разработчику возможность очень гибкой настройки синхронизации, позволяя учитывать многие нюансы. Есть смысл читать оригинальную документацию (http://www.maatkit.org/doc/mk-table-sync.html).

Пример использования:

mk-table-sync
    --verbose
    --print
    --charset=DB_CHARSET,
        h=host,
        P=port1,
        u=user1,
        p=password1,
        t=table1,
        D=db1
        h=host2,
        P=port2,
        u=user2,
        p=password2,
        D=db2
    > /__path__/ChangeLogQueries.sql

Дополнение 1:

Упомянутые инструменты имеют существенный недостаток, который менее важен при деплое на production-сервер, но постоянно о себе напоминает в процессе разработки – скорость выполнения. Изначально была задача построить механизм, который позволит автоматизировать сихнронизацию между площадками в цепочке «разработчики — тестовые сервера — stage — production». Начиная (в упомянутой выше цепочке) с тестовых серверов все довольно просто – из ресурсов требуется только время, нагрузка минимальна. Если процесс выполняется автоматически по расписанию по ночам, то важно не то, за сколько времени выполнилось, а то, чтобы не создать нагрузку и чтобы к утру задача была полностью завершена. Если синхронизация регулярная, то списки изменений никогда не будут чрезмерно большими, а нагрузка будет ничтожной. Другое дело, когда речь идет о синхронизации между площадкой разработчика и тестовым сервером. В таком случае, синхронизация, выполняющаяся 1 час, всегда будет ощутимой и будет создавать неудобства. Эти обстоятельства и натолкнули на средство синхронизации №5:

5. «Полуавтоматическая синхронизация».

В описанных выше утилитах большую (чуть менее, чем полностью) часть времени занимает именно формирование списка различий. Применить же потом скрипт по устранению различий – действие достаточно быстрое (опять же, не аксиома, но в большинстве случаев в процессе разработки). Это наталкивает на мысли, что можно поработать над ручным формированием списка различий, а автоматизировать только его применение. Очевидных способов нашлось только два:
а) в любом продукте существуют методы для работы с БД — не важно, используете ли вы популярный фреймворк или у вашего продукта самодельное ядро. Достаточно фиксировать все необходимые запросы (если только стуктура интересует — DDL, если данные тоже — update и delete, к примеру) в собственное хранилище;
б) если ваш фреймворк (а еще больше этим грешат cms) не имеет возможности расширить методы класса, работающего с БД и даже не имеет обработчика событий в методе, выполняющем запрос к БД, можно парсить лог всех запросов (надеюсь, возможность включить такой лог имеет весь целевой софт) и фиксировать только необходимые действия из этого лога.

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

Дополнение 2:

(updated)
К сожалению, почти все упомянутые мной средства обладают существенным недостатком (и я буду рад, если ошибаюсь, но опровержений моим словам я не нашел) — все они требуют удаленного доступа к БД по tcp/ip. Учитывая, что нормальной практикой является разрешение доступа к БД только с локального хоста и запрет удаленного доступа, добиться синхронизации лишь средствами упомянутых утилит не удастся.
Большое спасибо пользователю DarkByte за предложенное решение с пробросом портов — это решает проблему.

Удачного применения и не забывайте делать бэкапы!