Резервное копирование данных в MySQL

    Резервное копирование базы данных — это такая штука, которую вечно приходится настраивать для уже работающих проектов прямо на «живых» production-серверах.
    Подобная ситуация легко объяснима. В самом начале любой проект еще пуст и там просто нечего копировать. В фазе бурного развития головы немногочисленных разработчиков заняты исключительно прикручиванием фишек и рюшек, а также фиксом критических багов с дедлайном «позавчера». И только когда проект «взлетит», приходит осознание, что главная ценность системы — это накопленная база данных, и её сбой станет катастрофой.
    Эта обзорная статья — для тех, чьи проекты уже достигли этой точки, но жареный петух ещё не клюнул.

    1. Копирование файлов базы


    Базу данных MySQL можно скопировать, если временно выключить MySQL-сервер и просто скопировать файлы из папки /var/lib/mysql/db/. Если сервер не выключить, по очевидным причинам вероятна потеря и порча данных. Для больших нагруженных баз эта вероятность близка к 100%. Кроме того, при первом запуске с «грязной» копией базы данных MySQL-сервер начнет процесс проверки всей базы, который может затянуться на часы.

    В большинстве «живых» проектов регулярное выключение сервера БД на длительное время неприемлемо. Для решения этой проблемы применяется трюк, основанный на снэпшотах файловой системы. Снэпшот — это что-то вроде «фотографии» файловой системы на определенный момент времени, сделанный без реального копирования данных (и потому быстро). Аналогичным образом работает «ленивое копирование» объектов во многих современных языках программирования.
    Общая схема действий такова: блокируются все таблицы, сбрасывается файловый кэш БД, делается снэпшот файловой системы, разблокируются таблицы. После этого файлы спокойно копируются из снэпшота, после чего он уничтожается. «Блокирующая» часть такого процесса занимает время порядка секунд, что уже терпимо. В качестве расплаты на какое-то время, пока «жив» снэпшот, снижается производительность файловых операций, что в первую очередь бьет по скорости операций записи в базу.

    Некоторые файловые системы, например, ZFS, поддерживают снятие снэпшотов нативно. Если вы не пользуетесь ZFS, но на вашем сервере стоит менеджер томов LVM, вы также сможете скопировать базу MySQL через снэпшот. Наконец, под *nix можно воспользоваться драйвером снэпшотов R1Soft Hot Copy, но этот способ не заработает в контейнере openvz (процесс бэкапа MySQL описан здесь).

    Для баз MyISAM существует официальная бесплатная утилита mysqlhotcopy, которая «правильно» копирует файлы баз MyISAM без остановки сервера. Существует аналогичная утилита для InnoDB, но она платная, хотя и возможностей в ней больше.

    Копирование файлов — самый быстрый способ перебросить базу данных целиком с одного сервера на другой.

    2. Копирование через текстовые файлы


    Для того, чтобы считать в бэкап данные из production-базы, необязательно дергать файлы. Можно выбрать данные запросом и сохранить их в текстовый файл. Для этого используется SQL-команда SELECT INTO OUTFILE и парная ей LOAD DATA INFILE. Выгрузка производится построчно (можно отобрать для сохранения только нужные строки, как в обычном SELECT). Структура таблиц нигде не указывается — об этом должен заботиться программист. Он также должен позаботиться о включении команд SELECT INTO OUTFILE в транзакцию, если это необходимо для обеспечения целостности данных. На практике SELECT INTO OUTFILE используется для частичного бэкапа очень больших таблиц, которые нельзя скопировать никаким другим образом.

    В большинстве случаев намного более удобна созданная Игорем Романенко утилита mysqldump. Утилита mysqldump формирует файл, содержащий все SQL-команды, необходимые для полного восстановления БД на другом сервере. Отдельными опциями можно добиться совместимости этого файла с практически любой СУБД (не только MySQL), кроме того, существует возможность выгрузки данных в форматах CSV и XML. Для восстановления данных из таких форматов существует утилита mysqlimport.

    Утилита mysqldump консольная. Существуют её надстройки и аналоги, позволяющие управлять бэкапом через веб-интерфейс, например, украинская тулза Sypex Dumper (их представитель zapimir есть на хабре).

    Недостатки универсальных утилит бэкапа в текстовые файлы — это относительно невысокая скорость работы и отсутствие возможности делать инкрементные бэкапы.

    3. Инкрементные бэкапы


    Традиционно рекомендуют держать 10 бэкапов: по одному на каждый день недели, а также бэкапы двухнедельной, месячной и квартальной давности — это позволит достаточно глубоко откатиться в случае порчи каких-либо данных.
    Храниться бэкапы должны точно не на том же диске, что и живая база, и не на том же сервере. На случай пожаров и прочих катаклизмов лучше всего арендовать пару юнитов в соседнем дата-центре.

    Эти требования могут стать проблемой для больших баз. Прокачка бэкапа 100-гигабайтной базы по 100-мбитной сети займет часа три, на которые полностью забьет канал.
    Частично решить эту проблему позволяют инкрементные бэкапы, когда полный бэкап делается, скажем, только по воскресеньям, а в остальные дни пишутся только данные, добавленные или измененные за прошедшие сутки. Сложность в том, как выявить эти самые «данные, изменившиеся за сутки».

    Здесь практически вне конкуренции система Percona XtraBackup, которая содержит модифицированный движок InnoDB, анализирует двоичные логи MySQL и вытаскивает из них необходимую информацию. Почти такими же возможностями обладает платная InnoDB Hot Backup, упомянутая выше.

    Общая проблема с любыми бэкапами в том, что они всегда отстают. В случае фатального сбоя основного сервера восстановить систему можно будет только с некоторым «откатом» по времени, что очень и очень разочарует её пользователей. Если в системе так или иначе были затронуты финансовые потоки, подобный «откат» может в прямом смысле влететь в копеечку.

    4. Репликация


    Избежать откатов призвана система репликации MySQL. Идея репликации основана на том, что кроме «главного» сервера («Мастера») постоянно работают ведомые сервера MySQL («слейвы»), которые получают инкрементные бэкапы с мастера в режиме реального времени. Таким образом, время отката уменьшается почти до сетевого лага. В случае краха Мастера можно оперативно назначить «новым Мастером» один из слейвов и перенаправить клиентов на него. Кроме того, слейвы могут обрабатывать запросы на чтение данных (SELECT-ы); это можно использовать для выполнения каких-то расчетов или снижения нагрузки на мастера. MySQL поддерживает репликацию «из коробки», процесс настройки репликации в MySQL хорошо описан юзером whisk. Существует возможность запуска конфигураций Master-Master, а с помощью внешних аппаратно-программных систем — и балансировки нагрузки между мастерами. Только не нужно забывать про ограничения, накладываемые CAP-теоремой.

    Репликация — это очень здорово, только использовать её нужно по назначению. Реплика — это полная копия базы, но это не резервная копия! Очевидно же, что если на мастере выполнить DROP TABLE или UPDATE users SET password=«Haha!», изменения будут тут же скопированы на слейв, и откатить их назад станет невозможно.

    Репликацию можно совместить с бэкапом на уровне файлов базы, останавливая слейв, а не мастера.

    Вместо заключения


    Главное помнить, что бэкап в каждом случае индивидуален. Его нельзя настроить один раз и забыть. Администратор обязан хотя бы раз в месяц пытаться восстановить базу с нуля из бэкапов. В живых развивающихся проектах структура БД постоянно меняется, поэтому рано или поздно какие-то данные перестанут попадать в бэкап просто потому, что забыли поправить скрипты. Если это обнаружится в момент падения основной базы, ценность таких бэкапов окажется равной нулю.
    Метки:
    Поделиться публикацией
    Комментарии 52
    • –1
      большое спасибо за статью, интересно!
      • +1
        Спасибо. Повторение — мать учения:

        Средства создания горячих BackUp`ов MySQL
        habrahabr.ru/blogs/mysql/63394/

        Удаленный шифрованный бэкап MySQL при помощи LVM snapshot
        habrahabr.ru/blogs/personal/102738/

        Много о LVM:

        habrahabr.ru/search/?q=lvm
        • +1
          К сожалению, ваша статья не гуглится. Поэтому в своё время я её на нашел.
          • +1
            *в смысле первая приведенная вами
            • +1
              Свежие комментарии — свежие мысли. Всегда хорошо.
          • +1
            Простой скрипт, заодно реализующий и шифрование:

            nice -n 10 mysqldump -v -u USERNAME -p«PASSWORD»
            --add-drop-table --add-locks --create-options --single-transaction \
            --disable-keys --extended-insert --quick DATABASENAME | \
            gzip -c \
            | openssl aes-128-cbc -salt -kfile /home/USERNAME/.aes_pass -out /home/sql-dumps/base_`date "+%Y-%m-%d_%H-%M-%S"`_full.sql.gz.aes

            • 0
              добавить бы сюда ещё --master-data=2 (или 1, если настроена репликация) — тогда можно будет в экстренных случаях или при экстренном переносе данных с одного сервера на другой (без репликации на последнем) накатить бэкап, а не него — бинлоги с точки, которая запишется в файл
        • +1
          На не очень больших базах неплохая штука
          • +3
            Sypex Dumper отличная, кстати, штука. Простая, как газета Гудок.
            В особенности выручает на всяких недохостингах, где SSH не доступен.
            • +1
              Реплика мастер-мастер и группы слейвов покрывают 99% потерь данных. Криворукость администраторов никто пока не отменял.
              • +1
                Использую репликацию на сервер бекапов и уже оттуда снимаю бэкап раз 2 в часа и храню последнюю неделю :) для моих нужд достаточно :)
                • +1
                  Ну раз уж тут реплику рассматривали, то стоило бы упомянуть и бинлоги, которые позволяют откатывать базу с ювелирной точностью (вплоть до секунд), при условии, что бинлоги введутся как минимум с момента последнего бэкапа. Даже howto по восстановлению есть (:
                  Однако, стоит помнить, что бинлоги так же не являются полноценным бэкапом как минимум потому, что он должен ввестись на удалённом хосте.
                  • +4
                    Для горячего копирования InnoDB/XtraDB есть XtraBackup, бесплатный в percona-форке mysql.
                    http://www.percona.com/doc/percona-xtrabackup/
                    • 0
                      поддерживаю! Отличная софтина, прекрасно выполняет бэкап в очень короткое время по сравнению со стандартными средствами!
                    • НЛО прилетело и опубликовало эту надпись здесь
                      • +3
                        Обычно в связке с mysqldump для импорта созданного бекапа я использую стандартный консольный клиент mysql: mysql dbname < dump.sql
                        В чём преимущества утилиты mysqlimport, о которой вы упомянули?
                        • 0
                          ну или
                          mysql> \. /path/to/file.sql
                          если файл на том же сервере

                          mysqlimport вам нужен если вы импортите с csv, например. По сути это интерфейс для LOAD DATA INFILE INTO TABLE
                          • +1
                            к стати, никогда не использовал никакие GUI-дамперы — они мне просто не нужны, когда есть консольные тулзы
                            mysql
                            mysqlimport
                            mysqldump

                            Про Sypex Dumper, упомянутый в статье, слышал, — установил даже как-то, посмотрел, удалил — консоль ближе.
                            • +1
                              он выручает когда консоль недоступна
                              • 0
                                это когда? Опишите конкретные примеры ситуация.
                                • 0
                                  это когда консоль недоступна

                                  например на дешевых/бесплатных хостингах
                                  • 0
                                    Все-равно не понятно. Вы с ssh не путаете случайно? Хостер может не предоставлять ssh-доступ, но почему я при помощи утилиты mysql не могу подрубиться к серверу? Шелл-то для этого не нужен…
                                    • 0
                                      потому что адрес базы localhost, пароль — root
                                      • 0
                                        может потому что мускуль слушается чаще всего либо через сокет либо локально либо по жестким ограничениям
                              • 0
                                Ещё очень удобно дамп на другой сервак лить с помощью mysqldump и mysql через пайп.
                              • 0
                                mysqlhotcopy это совсем не тоже самое что mysqldump, это как раз реализация вашего варианта номер один. Сбрасываем и блокируем таблицы и копируем файлы.
                                • +1
                                  Разве snapshot файловой системы гарантирует консистентность data-файлов DB?
                                  • +2
                                    разумеется нет. необходимо базу либо остановить, либо «заморозить». первый вариант проще и может применятся, например, в комбинации с репликой — реплику переодически стопать и делать снапшот.
                                  • +1
                                    Статья из разряда «добавлю в закладки».
                                    Спасибо, буду реализовывать через недельку.
                                    • +1
                                      кстати, наличие снапшота вовсе не обязательно влечет за собой падение перформанса. все зависит от реализации снапшотов.
                                      • 0
                                        а есть какие-то утилиты, чтобы сделать diff двух баз, не выгружая перед этим в текстовый формат?
                                        • +1
                                          Так можно прямо у гугла и спросить про mysqldiff. А вообще, насколько знаю, недавно mysqlidff добавили в инсталляцию MySQL Workbench.
                                          • 0
                                            я имел ввиду сравнение данных, а mysqldiff сравнивает, вроде, только структуру.
                                            просто иногда возникает ситуация, когда хотелось бы узнать, насколько сломана репликация (и вычислять кем).
                                          • 0
                                            наверно уже не актуально, но отвечу для тех кто придёт сюда из гугла: сравнить чексуммы таблиц можно с помощью утилиты pt-table-checksum, которая входит в состав набора percona-toolkit.
                                          • 0
                                            Храниться бэкапы должны точно не на том же диске, что и живая база, и не на том же сервере. На случай пожаров и прочих катаклизмов лучше всего арендовать пару юнитов в соседнем дата-центре.

                                            Арендовать юниты для бэкапов? Может экономически выгоднее взять один ВДС в соседнем ДЦ, так как производительность процессора и объём ОЗУ здесь для задачи совершенно неважен?
                                            • 0
                                              Ну если на этом ВДС вам выделят пару зеркалированных терабайт под бэкапы, то почему нет? (Ну я рассматриваю нормальных размеров проекты. Понятно что бложик с двумя сотнями спамкомментов можно хоть карандашом на бумажку бэкапить)
                                              • 0
                                                Нормальных размеров проекты — не занимаются арендой чужого оборудования. А ставят на колоь свои сервера. Экономически выгоднее, imho…
                                                • 0
                                                  Я про это и говорил, когда писал «арендовать пару юнитов». Имелись в виду места в стойках.
                                                  Установка своего сервера на коллокейшн обычно окупается за 8-12 месяцев (по сравнению с арендой такого же железа у хостера).
                                            • +3
                                              В большинстве «живых» проектов регулярное выключение сервера БД на длительное время неприемлемо.


                                              1. Master-Slave репликация (вероятно с >1 слейвом)
                                              2. Выключаем слейв
                                              3. Делаем бекап со слейва

                                              4. PROFIT!
                                            • +1
                                              innodb hot backup и mysqlhotcopy это ни в коем случае не «через текстовые файлы».
                                              innodb hot backup — практически то же самое, что и percona xtrabackup.
                                              mysqlhotcopy — просто копирует MYD/MYI файлы
                                              • 0
                                                Спасибо за поправки, внёс в статью.
                                              • –3
                                                не понятен смысл этого поста
                                                автор открыл Америку чтоли?
                                                • +1
                                                  Со связкой php-mysql в силу её простоты часто работают люди, которые всё изучают в стиле «погугли», то есть вместо нормального последовательного обучения от азов к суровым вещам изучают всё по чуть-чуть. Для них статья вполне может стать и откровением. Но в любом случае, лишний раз заставить задуматься про необходимость бекапов не лишнее.
                                                  • –1
                                                    вряли тут для кого то откровение, а атких статей в нете куча, автор тупо пишет ниочем новом.
                                                • 0
                                                  спасибо. отличная статья-overview
                                                  • 0
                                                    MySQL database management — clean, import and export data (ещё одна надстройка над mysqldump, нацеленная на скорость работы)
                                                    code.google.com/p/mysql-backup/
                                                    • 0
                                                      Ещё поправочка: InnoDB HotBackup теперь называется MySQL Enterprise Backup.
                                                      • 0
                                                        У меня может и несколько неэффективный способ, но проблем с ним не испытывал. Сразу скажу, что особенность такова, что к таблицам запрос на DELETE не идет. Все данные удаляют кроны по истечению «срока давности». На основном и резервном серверах в каждой таблице есть поле updated с аттрибутами on update current timestamp. php-скрипт-крон раз в определенный промежуток времени (в зависимости от частоты наполнения таблиц) тащит из резервной таблицы последний по значению updated и проверяет в основной базе все записи с большим значением этого поля. Если таковые имеются, добавляет в резервную через replace into. Очищают основная и резервная база каждая себя сама по одним и тем же алгоритмам и тоже используя поле updated. Способ, возможно, несколько кустарный, зато работает. И основное в нем преимущество — кроны можно запускать даже ежеминутно и ничего не приходится останавливать при этом.
                                                        • 0
                                                          Под Windows — пользуемся MySqlBackupFTP — надстройка над mysqldump с простым интерфейсом, FTP, шифрованием и подтверждениями по емайлу. Одна из полезных фишек – позволяет делать бэкапы через phpMyAdmin когда нет другого выхода.
                                                          • +1
                                                            > Общая схема действий такова: блокируются все таблицы, сбрасывается файловый кэш БД, делается снэпшот файловой системы, разблокируются таблицы. После этого файлы спокойно копируются из снэпшота, после чего он уничтожается.

                                                            А что происходит при восстановлении с незавершенными на момент снепшота транзакциями?
                                                            • 0
                                                              А подскажите, существует ли способ оперативно посмотреть (или получить sql-дамп) базы с файлов бекапа, созданных по методу «1. Копирование файлов базы»?

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

                                                              Мы имеем в распоряжении только файлы .FRM, .MYD, .MYI, которые если только подсовывать рабочему серверу, но для этого нужны рутовые права. Хотелось бы как-то конвертнуть эти файлы в sql-дамп, но софта такого не нашёл.

                                                              Сообщите кто каким образом решает такие проблемы при бекапе mysql через файлы (снапшоты)?

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