Средства создания горячих 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`ом — каждый метод хорош в определенных условиях.

    На этом этом я заканчиваю своё повествование, надеюсь оно будет вам полезно. Спасибо за внимание и до новых встреч в эфире. :)
    Share post
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 49

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

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

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

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

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

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

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

                                                0
                                                Добавьте в статью рядом с mysqlhotcopy ещё xtrabackup, т.к. mysqlhotcopy насколько я понимаю криво будет копировать innodb таблицы, а xtrabackup как раз этот недостаток восполняет.
                                                Но xtrabackup по-моему наоборот myisam не будет копировать, так что придётся наверно их в связке использовать.
                                                Странно, почему до сих пор не сделали общую тулзу которая сразу всё умеет бекапить ;(
                                                  0
                                                  Прошло 5 лет, так и не сделали. Innodb правда стал побыстрее, но по прежнему занимает больше места на диске. Появился движок Toku с компрессией данных (и их уже купила Percona), но утилита бэкапа для него платная.

                                                Only users with full accounts can post comments. Log in, please.