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

image

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



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 за предложенное решение с пробросом портов — это решает проблему.

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

Подробнее
Реклама

Комментарии 26

    +3
    Через SSH можно пробросить порт, например, используя PuTTY.
      +1
      Спасибо, отредактировал пост. Поставил бы плюсик, если бы мог.
      +1
      Посмотрите SymmetricDS, для многих он стал стандартом де-факто. А для ручной синхронизации удалённых БД стоит смотреть в сторону SQLyog, там и туннели и визуальное сопоставление и копирование в пару кликов.
        0
        Хотелось бы добавить немного по MAATKIT. Как вы правильно заметили MAATKIT теперь полностью мигрировал под percona и теперь percona занимается его развитием, вот ссылка на список www.percona.com/doc/percona-toolkit/

        Нужно добавть одну особенность по mk-table-sync/ pt-table-sync: данная функия работает в комплекте с mt-table-checksum / pt-table-checksum:
        1. скрипт *-table-checksum находит разницу в таблицах,
        2. скрипт *-table-sync синхронизирует данные наденные *-table-checksum

        Вот пример скрипта который я использовал для себя (это только отрывок, без всех всех опций) для проверки MASTER-MASTER сихронизации

        # Проверка таблиц базе $DB_TO_CHECK и записать результат в таблицу $CHECKSUM
        # $CHECKSUM=replicate_db.checksum (обазятельно должна участовать в репликации)
        pt-table-checksum -q \
            --replicate $CHECKSUM --create-replicate-table --databases $DB_TO_CHECK \
            h=$MASTER_HOST,u=$USR,p=$PWD 2>&1
        
        # используя флаг -q мы переключаем скрипт в тихий режим и тогда скрирт веренте return code 0 если все ок и 
        # больше нуля если pt-table-checksum нашел разницу в таблицах
        if [ $? -gt 0 ]; then
            # если pt-table-checksum вернул не нулевой результать запускаем pt-table-sycn чтобы синхронизировать 
            # chunks с ошибками
            pt-table-sync --execute --sync-to-master --verbose \
                --replicate $CHECKSUM \
                --databases $DB_TO_CHECK \        
                h=$SLAVE_HOST,u=$USR,p=$PWD 2>&1
        fi  
        


        Запустив вот этот запрос на сервере 2 можно можно получить список кусочков (chunk) которые содержат ошибки \ не синхронизированны

        SELECT 
            db, tbl, count(chunk) AS chunks
        FROM replicate_db.checksum 
        WHERE this_crc <> master_crc
        GROUP BY db,tbl
        ORDER BY db,tbl;
        
        0
        Простой способ сравнить структуры 2-х бд:

        mysqldump --no-data --skip-comments -u dbuser -p dbname > db1.sql
        mysqldump --no-data --skip-comments -u dbuser -p dbname > db2.sql
        diff db1.sql db2.sql
        
          0
          На крупных базах данных бэкап одной бд может занять большое количество времени.
            0
            По идее, не должен — мы же используем ключ --no-data.
              0
              Ну сравнить структуру мы сравнили, а patch и revert делать в ручную?!
                0
                Что посоветуете?
                  +1
                  schemasync, автор про него писал маленькая и быстрая утилита написанная на python
                    0
                    Спасибо, ознакомимся.
            0
            Ещё такая тулза есть: dev.mysql.com/doc/workbench/en/mysqldiff.html
              0
              У неё есть одна проблема — она показывает только ALTER'ы. Если добавилась/убавилась таблица/вьюшка, то тулза выкинет warning и на этом посчитает свою работу выполненной.
              Кроме того, предлагает переименовать одну из двух сравниваемых БД.

              Вот пример:
              mysqldbcompare --skip-data-check --skip-row-count --run-all-tests --server1=root:***@192.168.1.133 --changes-for=server2 --difftype=sql compare1:compare2

              выхлоп:
              # server1 on 192.168.1.133: ... connected.
              # Checking databases compare1 on server1 and compare2 on server2
              #
              
              # WARNING: Cannot generate SQL statements for these objects.
              # Check the difference output for other discrepencies.
              --- `compare2`
              +++ `compare1`
              @@ -1 +1 @@
              -CREATE DATABASE `compare2` /*!40100 DEFAULT CHARACTER SET cp1251 */
              +CREATE DATABASE `compare1` /*!40100 DEFAULT CHARACTER SET cp1251 */
              
              # WARNING: Cannot generate SQL statements for these objects.
              # Check the difference output for other discrepencies.
              --- `compare2`
              +++ `compare1`
              @@ -1 +1 @@
              -CREATE DATABASE `compare2` /*!40100 DEFAULT CHARACTER SET cp1251 */
              +CREATE DATABASE `compare1` /*!40100 DEFAULT CHARACTER SET cp1251 */
              
              # WARNING: Objects in server1.compare1 but not in server1.compare2:
              #        TABLE: table2
              #         VIEW: view1
              #
              #                                                   Defn    Row     Data   
              # Type      Object Name                             Diff    Count   Check  
              # ------------------------------------------------------------------------- 
              # TABLE     table1                                  FAIL    SKIP    SKIP    
              #
              # Transformation for --changes-for=server2:
              #
              
              ALTER TABLE `compare2`.`table1` 
                CHANGE COLUMN data data varchar(46) NULL;
              
              
              
              Databases are consistent given skip options specified.
              #
              # ...done
              
                0
                О, спасибо!

                > Кроме того, предлагает переименовать одну из двух сравниваемых БД.

                Это как посмотреть: вдруг вы её хотите переименовать? =)

                А что хочется вместо:

                # WARNING: Objects in server1.compare1 but not in server1.compare2:
                # TABLE: table2
                # VIEW: view1

                увидеть?
                  0
                  Ну вообще при сравнении двух БД вместо ворнингов об отсутствующих таблицах/вьюшках хочется увидеть CREATE/DROP TABLE/VIEW. По-моему вполне ожидаемо, не?
                    0
                    Ну вот иногда глаз замыливается и очевидного-то и не замечаешь =)

                    Можете открыть feature request на bugs.mysql.com?
              0
              А как вы решаете вопросы в которой будет следующее:

              db1.table1
              — index key1
              — index key2

              db2.table1
              — index key2
              — index key1

              т.е. ключи идут в разном порядке?
                0
                Никак. Будут в разном порядке.

                Я в комментарии указал "простой способ сравнить структуры 2-х бд". Не супер-мега-всех-заткнул-за-пояс-скрипт, а именно простой способ. Свалились на Вас с неба две базы, а Вам стало интересно есть ли между ними разница в структуре. Не регулярно сверять базы и синхронизировать, а просто сравнить. Раз в полгода.
                  0
                  Тогда да, ваш способ имет право на жизнь. Просто сталкнулся с тем что у меня была такая сиутация и я был малость в замешательстве как это исправить :(
              0
              Обратите ещё внимание на недавно появившиеся MySQL Workbench utilities.

              Например, mysqlserverclone (http://dev.mysql.com/doc/workbench/en/mysqlserverclone.html) клонирует базу, mysqldiff (http://dev.mysql.com/doc/workbench/en/mysqldiff.html) сравнивает структуру двух баз, mysqldbcompare (http://dev.mysql.com/doc/workbench/en/mysqldbcompare.html) — помимо структуры, сравнивает и данные, mysqlreplicate (http://dev.mysql.com/doc/workbench/en/mysqlreplicate.html) — разворачивает репликацию.
                0
                На самом деле, GUI приложения, с большими объемами не справляются. Из всех утилит которые я попробовал (без gui) на средних объемах БД 3-4 ГБ sсhemasync и maatkit справились достаточно быстро.
                MySQL Workbench очень тормознутый инструмент который нуждается в большом напильнике
                  0
                  MySQL Workbench Utilities, несмотря на название, — это набор command line tools, написаных на Python, имеющий к MySQL Workbench только то отношение, что они поставляются вместе с ним и могут быть вызваны непосредственно из него. А могут использоваться и самостоятельно. Вот здесь отдельно их можно загрузить: dev.mysql.com/downloads/tools/utilities/
                    0
                    Спасибо, на досуге попробую.
                0
                > К сожалению, почти все упомянутые мной средства обладают существенным недостатком (и я буду рад, если ошибаюсь, но опровержений моим словам я не нашел) — все они требуют удаленного доступа к БД по tpc/ip. Учитывая, что нормальной практикой является разрешение доступа к БД только с локального хоста и запрет удаленного доступа, добиться синхронизации лишь средствами упомянутых утилит не удастся.

                Я не думаю, что это недостаток, потому что иначе же нельзя к удалённой машине присоединиться.

                Помимо предложенной переброски портов можно сделать ещё аккаунт, предназначенный специально для процедуры синхронизации и жёстко прописать хост, с которого он может соединяться плюс добавить опцию REQUIRE SSL.
                  0
                  Немного не по теме, но для ручного сравнения баз, лучше DBForge for MySQL еще ничего не придумали. И умеет работать через SSH- и HTTP-туннели. Если разработчики DBForge читают Хабр, то низкий им поклон!

                  Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                  Самое читаемое