Генерируем «правильный» SQL дамп

    В процессе разработки с использованием MySQL часто приходится делать дамп базы данных для сохранения ее в репозиторий (деплоя на сервер и т.д.).
    Существуют разные клиенты для работы с MySQL:
    — MySQL Front
    — PHPMyAdmin
    — Aqua Data Studio
    — EMS SQL manager
    и так далее.

    Проблема


    В каждом из перечисленных существует функция экспорта схемы базы и её данных в файл. Попросту говоря — создания дампа БД. Но вот незадача! Каждый из иструментов генерирует SQL код со своим форматированием. К примеру, некоторые даже не вставляют ENGINE=MyISAM DEFAULT CHARSET=… в выражении CREATE TABLE, то же самое и с DROP TABLE IF EXISTS. Одним словом, наблюдается эффект «лебедь, рак и щука».

    Затруднения возникают, если в команде несколько разработчиков — вероятнее всего каждый из них привык пользоваться каким-то одним инструментом. Попытки заставить девелоперов применять какой-то определенный клиент могут привести к священным войнам («я к этому привык, я не хочу другое.., а это вообще отстой!»). Возможно, кому то проблема покажется надуманной, но в моей практике такое было. Да и что делать, если люди работают на разных операционных системах? SQL менеджеры у них тоже будут разные.

    Итак, нужен способ генерации дампа в каком то унифицированном формате. Ожидаемая выгода:
    • возможность легко сравнивать изменения sql файлов в репозитории (если ревизии в одном формате, это проще. Не так ли?)
    • возможность дальнейшего парсинга SQL кода (например, в инсталляторе приложения)
    • автоматизация сего действия
    • независимость от ОС
    • указание кодировки
    • возможно что-то еще:)

    Решение


    Отказаться от каких либо сторонних инструментов для генерации дампа.
    В дистрибутиве MySQL существует набор полезных утилит, в том числе и известная многим mysqldump. Ниже можно увидеть BAT-файл, который используя данную утилиту генерирует правильный SQL дамп.
    Правильный — в данном контексте означает «соответствующий оговоренному формату». В любом случае, не хочу подвергать сомнению корректность работы родной утилиты от MySQL :)

    @echo off
    rem author afi
    echo =========================================
    echo SQL generator
    echo Output files :
    echo scheme.sql - Scheme of database
    echo data.sql - Data for database
    echo =========================================

    rem по умолчанию UTF-8
    set ENCODING=utf8

    IF "%1" == "" goto ERROR
    IF "%2" == "" goto ERROR
    IF "%3" == "" goto ERROR
    IF "%4" == "" goto ERROR
    IF NOT "%5" == "" (
    set ENCODING="%5"
    )

    goto get

    :get
    echo Generating scheme for DB: %1
    mysqldump --host=%1 --password=%4 -u %3 --disable-keys --add-drop-table --default-character-set=%ENCODING% --no-data --result-file=scheme.sql %2

    echo Generating data for DB: %1
    mysqldump --host=%1 --password=%4 -u %3 --disable-keys --default-character-set=%ENCODING% --no-create-info --extended-insert=false --result-file=data.sql %2
    goto END

    :ERROR
    echo Please, define parameters. Example:
    echo gensql.bat host_name database_name mysql_user mysql_password [encoding]
    goto END

    :END
    echo =========================================

    @pause



    Теперь все заботы сводятся к запуску скрипта с параметрами:
    gensql.bat host_name database_name mysql_user mysql_password [encoding]

    , где encoding — выходная кодировка данных. Необязательный параметр, по умолчанию utf8.

    В результате, получаем 2 файла:
    scheme.sql — содержит схему БД, т.е. скрипт создания таблиц, ограничений, вьюшек и пр.
    data.sql — содержит дамп данных

    Сделать bash-вариант скрипта, думаю не составит труда тем, кому он может понадобиться.

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

      0
      Пришлось работать много с бекапами баз данных. При mysqldump, при переносе или восстановлении больших баз данных могут возникнуть проблемки, и чаще всего возникают. Это может быт таже кодировка, дблирование ключа, и т.д. и т.п. Тогда дамп приходится править в ручную.

      Для меня лучшее решение бекапа - это тар:
      mysqld stop
      tar -cvvf backup_xx_xx_2008.tar /home/mysql/
      mysqld start

      Теперь база сохраненна, и восстанавливается без проблем. Просто заливаем содержимое файла обратно в mysql/
        +1
        сохранение непосредственно файлов чревато проблемами при установке на другую версию mysql, да и остановка сервера не всегда приемлема. Но я уверен, для вас это лучшее решение :-)
          0
          Остановку mysql используем на репликации которая для бекапов, поэтому сама остановка не влияет. Иногда просто выключаем Slave. Для каждого случая - свое решение, но не mysqldump, точно.

          По поводу не совместимости разных версий: Это не так страшно на самом деле, по крайнем мере не страшнее чем когда проблема внутри дампа (mysqldump). Ни разу у нас проблем не было, хотя да, прыгали с версии на версию. И, да, подозреваю что с 4 на 5 могут быть проблемы. Но у нас просто таких резких переходов не может быть. И я это беру в расчет.
        0
        А я пользуюсь Sypex Dumper(http://sypex.net/) - удобная вещь.
          0
          в качестве mysql GUI советую sqlyog - есть фришная версия, делает бекапы совместимые с родными тулзами mysql

          PS если уж начали писать как забекапить, то логично завершить статью восстановлением данных. Так сказать для соблюдения целостности.
            +1
            всю жизнь делал дамп с помощью команды mysqldump указывая ключи --all --opt
              0
              Немного уточню: я описывал НЕ вариант бэкапа данных на продакшн-сервере (там, разумеется, не все так просто), а удобный способ именно для разработчика, например, когда база установлена локально. В начальный период в любом проекте схема базы меняется очень часто, то и дело добавляются тестовые данные...поэтому приходится часто выполнять подобные операции.

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

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