войти зарегистрироваться

MySQL whois

индекс
202,61

Средства создания горячих BackUp`ов MySQL

Доброго времени суток. Недавно я задался вопросом о том, как делать горячие BackUp`ы MySQL-серверов — ниже компиляция из прочитанного. Заранее хочу сказать, что данный пост является скорее большой заметкой, чем полноценной статьёй. Я намеренно уклоняюсь от описания синтаксиса — на эту тему уже немало написано — я же ставил перед собой другую цель — составить краткий обзор основных методов с характерными особенностями:

1. C помощью утилиты mysqldump. Данная программка крайне популярна среди пользователей веб-хостингов. Читая содержимое таблиц, она создаёт файл с SQL-инструкциями для последующего заполнения. Но, как правило, при использовании люди забывают про три ключевых момента:
  • Если не использовать блокировку таблиц, вполне можно получить нарушение логических связей между содержимым таблиц(если в процессе создания копии кто-то решит оставить запись в базе). Здесь косвенно может помочь накатывание части bin-log`a после восстановления из дампа. Так что если по каким-то причинам не блокируете таблицы — применяйте ключ --flush-log — при его использовании старый лог будет закрыт и начат новый. Если кто-то что-то запишет в процессе создания бэкапа — это отразится в начале журнала и вы без проблем перенесёте это изменение в базу. Я бы советовал после окончания бэкапа так же выполнить mysqladmin -flush-logs и положить в бэкап помимо dump-файла предпоследний бинарный журнал.
  • При использовании ключа --lock-tables все таблицы получают блокировку записи, запросы встают в очередь. Это может привести к таймаутам на стороне клиента.
  • Стоит так же иметь ввиду, что подъём(как и создание дампа) большой базы, сохраненной таким образом может изрядно затянуться — в первом случае вы сгребаете из базы все записи, а при обратном варианте — скармливаете их ей. Тем не менее, это один из немногих способов сбэкапить базу из консоли, не имея root-доступа.

Восстановление: путём скармливания dump-файла утилите mysql через STDIN.

2. С помощью утилиты mysqlhotcopy. Еще одно средство из штатного набора MySQL. Идея такова: база ставится на блокировку, после чего средствами cp или scp её файлы копируются в другое место.
  • В отличие от предыдущего варианта сохраняются именно табличные файлы, а не набор инструкций по воссозданию базы, то есть скорость ограничивается лишь операционной системой и вашими железками.
  • По моему разумению — вполне подойдёт для бэкапа больших баз.
  • Работает только с MyISAM и ARCHIVE-таблицами.
  • Выполняется только с сервера на котором лежит база, при условии наличия прав к файлам с таблицами MySQL.

Восстановление: путём копирования сохраненных файлов в каталог данных MySQL.

3. С помощью LVM.
LVM — дополнительный слой между файловой системой и самим жестким диском. Одной из примечательных особенностей LVM является возможность снять на лету образ с тома. Схема действий будет следующей: заблокировать все таблицы базы, снять snapshot с тома, разблокировать таблицы.
  • Данный метод подразумевает предварительный FLUSH с блокировкой всех таблиц(лучше скриптик написать для этих целей).
  • Для применения этого метода необходимо, чтобы данные MySQL(для Linux они скорее всего будут храниться в директории /var/lib/mysql) находились на LVM-томе(желательно отдельном, дабы не бэкапить лишнее).
  • Учитывая, что мы говорим о горячем бэкапе — если вы собираетесь применять данный метод — решение о размещении лучше принять на этапе конфигурации сервера.

Восстановление: путём копирования сохраненных с образа файлов в каталог данных MySQL.

4. С помощью репликации. Несмотря на то, что этот вариант многие считают геморроем, мне такой способ резервирования кажется самым правильным. Логика такого подхода заключается в постоянной синхронизации основного(master) сервера с вторичным(slave). Подробней о репликации можно почитать здесь.
  • Требуется конфигурация отдельного MySQL-сервера. Причем желательно — на автономном железе.
  • Остановка slave-сервера не сыграет никакой роли на master`е — можно делать «холодный» бэкап.
  • В случае падения master`a можно в кратчайшие сроки(было бы разумно автоматизировать этот процесс) перевести всю нагрузку на slave, а после восстановления отсинхронизировать с ним master и вернуть всё на прежние места.
  • Slave может стать по совместительству площадкой для хранения бэкапов.
  • Важно! Существование реплики не освобождает вас от создания бэкапов. Выполнение какого-нибудь DROP'а коснётся обоих серверов!

Восстановление: вывод slave-сервера на место master`a, либо восстановление одним из вышеуказанных методов(в зависимости от выбранного).

Итого: Как видно, у каждого метода есть свои плюсы и минусы: вряд ли есть смысл бэкапить маленький форум с применением реплик, а базы, где счет идёт на гигабайты вряд ли будет удобно поднимать из файлов, сделанных mysqldump`ом — каждый метод хорош в определенных условиях.

На этом этом я заканчиваю своё повествование, надеюсь оно будет вам полезно. Спасибо за внимание и до новых встреч в эфире. :)
+49
2 июля 2009, 03:11
mixermsk

комментарии (47)

  • НЛО прилетело и опубликовало эту надпись здесь.
    • Если честно, с кластером еще поработать не успел, хотя тема мне крайне интересна(как и любой HighLoad). С альфой 6й ветки тоже. Как только опробую технологию — могу отписаться о содеянном, заодно и тему бэкапа освещу :)
  • Репликация идет в один поток и slave будет отставать от мастера
    • тут палка о двух концах:
      1. заблокировать бд и получить скорее мёртвое, чем живое приложение
      2. отцепить от репликации слейва и снять дамп с актуальностью «10 минут назад»
      в зависимости от условий выбирается одно из решений.
      что не так? :-)
    • В смылсе? На совсем хай-лоаде может и будет, при штатных нагрузках — не отстает.
      Тем более в 5.1 есть row-based репликация.
  • У меня mysqldump почему-то из-под крона не желает исполнятся. Уже голову себе всю сломал :(
    mysqldump --user=юзернейм --default-character-set=cp1251 --password='пасс' датабейз-нейм > file.log
    Причем руками все работает ок…
    • mysqldump лежит вне PATH с которым работает крон?
    • А разве крон не говорит, почему?
    • надо указать полный путь для mysqldump. В кроне будет выглядеть вот так (путь для FreeBSD):

      /usr/local/bin/mysqldump --user=юзернейм --default-character-set=cp1251 --password='пасс' датабейз-нейм > file.log
      • Или дописать в строку PATH файла /etc/crontab путь до бинарника
      • А еще желательно указывать полный путь к файлу, куда будет дамп литься.
    • Никаких сообщений в логе крона не появлялось. Прописал полный путь — посмотрим.
      Спасибо за помощь.
  • Раз уж сказали про репликацию и LVM хорошо было бы упомянуть drbd и кластеры.
    А так заметка в самый раз.
  • я бы еще добавил что если мастер+слейв, то бекапы лучше делать со слейва. угу?
    • Да, только надо хорошо мониторить работу слейва — чтобы не получить дамп с кривыми данными.
  • > LVM — дополнительный слой между файловой системой и самым жестким диском

    А на основании чего проводится ранжирование дисков по жесткости? ;)
    • Пофиксил, спасибо :)
  • Не знаю как сейчас, но раньше mysqlhotcopy был платным инструментом.
    • Никогда не был. Вы путаете с инструментом от innodb. да и тот потерял актуальность в связи с недавним появлением xtrabackup
      • Совершенно верно. В свое время еще пришлось написать свой аналог mysqlhotcopy для innodb. Запамятовал за давностью лет, бывает.
        • аналог? :) или вы просто глобальную блокировку ставили и .ibd копировали?
  • ЕДИНСТВЕННЫЙ вариант на самом деле «горячего» бекапа это реплика и снятие бекапа с нее. Любые другие методы либо требуют блокировки таблиц (а вы побекапте 4-5 миллионов строк с блокировкой, время ощутимое), либо не гарантируют целостности. Говорить о бекапе через LVM это приблизительно то же самое что о бекапе через RAID или Norton Ghost.
    • На самом деле — в случае если у вас таблицы, не поддерживающие транзакции(MyISAM тот же), даже используя реплику вы можете остановить slave в неподходящее время и оставить какую-нибудь запись без связки с другой таблицей. Я это к тому, что очень многое зависит от реализации конкретно взятого сервера.
      В то же время, например в случае с маленьким форумом у вас вероятнее всего ничего не случится если вы поймаете момент минимальной нагрузки на сервер. С действительно большими базами такой фокус не прокатит, да — к слову, я об этом писал. Думаю, по аналогии с первым пунктом, люди поймут, чем грозит блокировка живой базы.
      Касательно RAID`а — сама по себе избыточность данных не спасает от главного момента(про который я уже говорил) — когда кто-то с похмелья выполняет на сервере DROP. RAID в этом случае покорно снесёт файлы со всех дисков, входящих в том. LVM же здесь упоминается именно из-за возможности налету сделать дамп раздела
      • а вам не кажется, что термин «на лету» и «горячий бекап» подразумевает бекап без какого-либо влияния на сервер? т.е. никаких flush, lock etc.
        • Бэкап при помощи LVM занимает считанные секунды (а иногда и считанные миллисекунды). По сравнению с репликацией он имеет то преимущество, что не ломается без конца (а репликация — конструкция достаточно шаткая) и не требует отдельной машины (и почти не требует ресурсов). Вот примерная последовательность команд:

          mysql: flush (5-10 секунд, не блокирует ничего, так что не считается)
          — mysql: lock (мгновенно)
          mysql: flush (меньше секунды)
          lvm: сделать snapshot (меньше секунды)
          mysql: unlock (мгновенно)
          lvm: отпустить snapshot
          • ghost@isolde:/var/lib/mysql# du -h --summarize
            14.1G.

            я честно говоря не делал lvcreate --snapshot ни разу, просто думаю что на таком объеме это не миллисекунды.
            • Насколько я понимаю, операция создания снапшота в него ничего не записывает. Это просто указание системе «начиная с этого момента все данные записывай, пожалуйста, не на основной диск, а в снапшот». Поэтому создается снапшот очень быстро. Ну а flush выполняется быстро потому, что он идет почти сразу после первого (долгого) flush-а.
              • кажется, снапшот — это слепок текущего состояния диска, и система при этом оставляет снап в нормальном состоянии и продолжает писать на диск.
                • когда кажется, надо креститься, а не комментировать
          • Каким образом подбирается размер снапшота?
            Обязательно ли он должен быть таким же размером как и база?
            • экспериментально. 25% большинству хватает. если вы имеете ввиду зарезервированное место для блоков новой информации при использовании этих самых снапшотов.
  • zloe.org.ua/?cat=1
  • В статье обойдён вниманием самый интересный вариант бэкапа — инкрементальный. Базу в несколько гигабайт никакими методами быстро не пробэкапить, да и хранить полные копии сложно. Возможно, поможет какое-то средство типа использования бинарных логов (если есть транзакции) или какого-то прокси типа NDB или mysqlproxy?
    • ИМХО инкрементность на основе бинлога — слишком неустойчиво.
      rdiff-backup.nongnu.org не спасет ли?
      • Как раз только на основе бинарных логов инкрементальное копирование и можно сделать по-настоящему, но классический mysql пообще без транзакций живёт, так что это отпадает. А вот на proxy вполне можно было бы включить журнал изменений.

        rduff-backup я пробовал на маленьких базах, работает. Но если он должен найти отличия в гигабайтных файлах — это само по себе долго, если на время сравнения включать блокировку базы, то можно выйти за таймауты.
        • каким образом наличие транзакций влияет на binlog? Туда все равно (для транзакционных engines) только закоммиченые транзакции попадают.
          • На транзакционных СУБД бинарные логи — побочный продукт жизнедеятельности, они есть всегда, поэтому нет накладных расходов на их поддержку. MySQL поддерживает бинарные логи, и в частности для инкрементального копирования, но их надо включить и смириться с небольшой потерей производительности.
            • А о какой потере производительности вы говорите? Какие нибудь цифры можете привести?

              • Не могу, но это же в два раза больше операций записи на диск.
  • В первом пункте все же три ключевых момента, а не два. Время выполнения операции, думаю, стоит считать важным фактором.
    • Поправил, спасибо. Постил ночью — местами ляпы. Выше в комментах обсуждался «самый жесткий диск», например. :)
  • если размер БД не очень большой, можно попробовать Sypex Dumper
    • Насколько я помню, это более медленный аналог первого варианта.
      • Это не аналог, это уникальный живучий скрипт жизненно необходимый в условиях унылого виртуального хостинга с массой ограничений.
        Я стыжусь его использовать, но приходится.
  • Репликация может быть и master-master.
    А для особых извращенцев: master-master-master. :)
  • для lvm backup никаких новых скриптов писать не надо, они уже есть https://launchpad.net/mylvmbackup
  • есть еще решения для backup — например https://launchpad.net/percona-xtrabackup — non-blocking hot backup для innodb

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