Тестируем новый тип бэкапа MySQL

    Бэкапы MySQL бывают 2 основных разновидностей это:

    Логический бэкап

    Создается текстовый дамп из SQL-запросов, как в mysqldump или Sypex Dumper.

    Физический бэкап

    Делаются точные копии файлов таблиц, типичный представитель mysqlhotcopy.

    В процессе работы над новой версией Sypex Dumper и Sypex Backuper, пришел к еще одному интересному варианту горячего бэкапа MySQL. Который представляет собой, что-то среднее между двумя этими вариантами.

    Но для начала рассмотрим основные достоинства и недостатки. Кто вместо теории хочет сразу перейти к практике — внизу поста найдете ссылку на тестовый скрипт.

    Логический бэкап


    Преимущества логического бэкапа:

    • полученный дамп может быть восстановлен на любой системе;
    • бэкап удаленного MySQL-сервера;
    • бэкап любых табличных движков (включая MEMORY);
    • бэкап создается на работающем сервере, не останавливая его работу.

    Из основных недостатков:

    • логический бэкап делается значительно медленнее физического, так как нужно все данные преобразовать в человекопонятные SQL-запросы;
    • больше размер файла из-за текстового формата бэкапа.

    Физический бэкап


    Преимущества физического бэкапа:

    • максимальная скорость бэкапа, так как просто копируются файлы;
    • небольшой размер файла (так как используется бинарный формат);
    • можно бэкапить лог-файлы сервера.

    Из основных недостатков:

    • бэкап только локального сервера;
    • могут быть сложности с переносом бэкапа на другую машину/систему.
    • нельзя создавать бэкап таблиц MEMORY (так как нет физических файлов);
    • не всегда можно восстановить отдельные таблицы (например, таблицы InnoDB могут храниться в одном файле).

    Sypex MySQL RAW бэкап


    При анализе логических способов бэкапа было замечено, что основная потеря скорости происходит при получении пакетов данных от сервера, разборе их и преобразовании к текстовому формату. К тому же этот разбор обычно делается libmysql либо в случае новых PHP-версий mysqlnd, и приводит к дополнительному оверхеду.

    Поэтому решил попробовать избавиться от лишних преобразований, и написал тестовый скрипт который подключается напрямую к MySQL (по TCP или к UNIX-сокету) без использования стандартных MySQL-драйверов, используя MySQL Client/Server Protocol. Скрипт сохраняет в файл данные в виде бинарных пакетов полученных от MySQL-сервера (ProtocolBinary::Resultset). Таким образом не тратится время на разбор пакетов, полей, экранирование данных. А разбор пакетов и формирование SQL-запросов происходит уже при восстановлении бэкапа.

    В итоге скорость бэкапа многократно увеличилась, в зависимости от структуры таблицы. Также дампы весьма компактны. Можно сравнить скорость RAW бэкапа, с бэкапом с помощью mysqldump и SELECT… INTO OUTFILE.

    Несколько результатов выполнения на стандартных таблицах форумов IPB и phpBB.


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

    Скачать скрипт для тестирования можно здесь.
    Скрипт является демонстратором технологии, а не конечным продуктом.

    О результатах отписывайтесь в комментах. Только учтите, что SELECT… INTO OUTFILE работает только на localhost, плюс у MySQL пользователя должны быть права доступа FILE и у каталога backup должны быть выставлены права доступа 777.

    UPD. По просьбам трудящихся, еще несколько тестов с таблицами побольше:

    Бэкап одной из таблиц wikipedia (categorylinks) около 1,3 ГБ
    image

    Бэкап таблицы GeoNames около 1 ГБ
    image
    Поделиться публикацией

    Похожие публикации

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

      –16
      Израильский SQL сервер отвечает запросом на запрос.
        +4
        Тестируем новый тип бэкапа

        Во-первых, что тут нового? Во-вторых, это не бэкап базы, а экспорт таблицы. Разница в наборе выгружаемых данных, целостности и штатной восстанавливаемости.
          0
          1. Какой инструмент делает бэкап аналогично?
          2. Это как бы тестовый скрипт, который проверяет концепцию. Или вы думаете сложно добавить запрос который делает запрос в MySQL для получение списка таблиц и других объектов? Тут основная фишка проверить насколько это быстро происходит. По сути он будет делать всё тоже, что и mysqldump только (те же запросы), только не будет приводить ответы сервера в текстовый вид. Что касается штатной восстанавливаемости, то да я писал об этом как об одном из недостатков, но тут уже нужно смотреть насколько плюсы метода будут превосходить этот недостаток.
          –1
          А Вы сшепшот файловой системы не пробывали делать?! А так велосипед имхо…
            0
            Снепшоты это несколько другой тип бэкапа, это физический бэкап, а мой вариант это всё же логический, т.е. при восстановлении он заполняет базу обычными SQL запросами, кроме того есть возможность восстановление отдельных объектов из бэкапа, вплоть даже до отдельных строк таблицы (так как этот бэкап еще и значительно проще парсить, чем SQL-файл).
            +2
            А процедуры из базы куда деваются при этом?
              0
              А Вы разве не делаете отдельно бекап данных и структуры?
                +4
                Нет, мне и xtrabackup за глаза хватает.
                  0
                  xtrabackup это физический бэкап, а рассмотренный в теме бэкап это, как ускоренный mysqldump.
                    –11
                    Откуда такие придурки берутся? :))))))))))))))))))
                      +2
                      В чем конкретно претензия? Или Вы просто из-за комплексов решили самоутвердиться путем оскорбления других? Ну тогда в какой-то детсадовский форум, а не на Хабр :)
                0
                Процедуры точно также бэкапятся, просто вызвать SHOW CREATE PROCEDURE слишком быстрая и простая операция, поэтому не добавлял в тестовый скрипт.
                +1
                Всё, что необходимо знать о бэкапах для MySQL давно уже находится здесь:
                www.percona.com/live/mysql-conference-2013/sessions/backup-today-saves-you-tomorrow :)
                  0
                  И что? К примеру описанные в презентации mydumper и Xtrabackup появились относительно недавно, да и сам mysqldump изначально написан сторонним разработчиком.
                    0
                    Что именно вы хотели сказать своим комментарием?)
                      +3
                      Я хотел сказать, что наличие старых инструментов, не означает, что не нужно разрабатывать новые :)
                  0
                  Немного не по теме: никто не подскажет: есть ли утилиты для репликации mysql с горизонтальной/вертикальной фрагментацией? Стандартная репликация же позволяет только 1 к 1 копию получать.
                    0
                    стандартная репликация позволяет выборочно реплицировать таблицы. вы можете сделать шардирование, пользуясь разными таблицами
                      0
                      Мне надо бы, как в MS SQL, выбрать строки/столбцы из существующей таблицы по определенному критерию, типа WHERE. В одном месте хранится полный набор данных, в другом — частичный.

                    0
                    Вы в скрипт посмотрите что предлагается для скачивания :)
                      0
                      И что там? Это же не готовый скрипт, а так сказать демонстратор технологии.
                      0
                      Я в своей практике использую Перконовскую xtrabackup (а также инкрементальный бекап).
                      В Амазон-облаке — RDS/EBS снапшоты. так проще.
                        +1
                        > Основной недостаток способа, естественно, невозможность восстановления стандартными методами, т.е. нужен специальный скрипт для восстановления.

                        А о целостности данных он заботится? Что произойдёт, если во время бэкапа кто-то изменит таблицу? Есть ли опция, аналогичная --master-data mysqldump-а?
                          0
                          По сути этот метод точно такой же как mysqldump, отправляет в MySQL те же самые запросы. Так что в этом плане он от mysqldump ничем не отличается. Основное отличие в том как он сохраняет результат выполнения этих запросов.

                          В случае mysqldump происходит следующее:

                          1. mysqldump с помощью libmysql делает SELECT * FROM table (используется команда COM_QUERY);
                          2. сервер отвечает, сначала приходит описание столбцов, потом пакет EOF, а дальше идут уже сами данные в текстовом протоколе, т.е. в каждом пакете одна строка, сами поля в виде LengthEncodedString (для каждого поля сначала указывается размер), также числа из бинарного вида переводятся в текстовый;
                          3. libmysql разбирает каждый пакет и отдает в mysqldump уже строку в виде массива полей
                          4. mysqldump в зависимости от типа экранирует каждое поле и оформляет каждую строку в виде SQL запроса (в минимальном виде, это заключение в скобки с разделителем запятой)
                          5. ну и после этого уже запись в файл
                          6. повторяем пункт 3


                          В таком варианте на этот самый разбор пакетов, экранирование и оформление в виде SQL уходит довольно много времени.

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

                          Получается так:

                          1. делаем запрос SELECT * FROM table напрямую к серверу, при этом используем не COM_QUERY, а COM_STMT_PREPARE и COM_STMT_EXECUTE (этот вариант быстрее);
                          2. разбираем описание столбцов, а сами данные уже приходят в ProtocolBinary::ResultsetRow (в таком виде цифры не преобразуются к тестовому виду, а также NULL поля в виде битовой карты передаются), в общем это быстрее и компактнее;
                          3. пишем пакеты полученные из сокета сразу в файл, без малейших преобразований, до тех пор пока не увидим EOF пакет
                            +1
                            mysqldump как бы сначала делает либо LOCK TABLES, либо START TRANSACTION. Вы это делаете?
                              0
                              В данном тестовом скрипте нет, так как задача этого скрипта проверить идею, а в конечном продукте естественно будет блокировка. У mysqldump же открытые исходники, так что не проблема выполнять те же запросы.
                            0
                            А формат этого самого бинарного протокола неизменен (например от версии к версии)?
                              0
                              Сейчас используется клиент-серверный протокол версии 10, он не менялся с выхода MySQL 4.1. Бывают его расширения, как в последних версиях MySQL, так и в форках, той же MariaDB. Но они в основном касаются добавления новых команд, или каких-либо расширенных статусов выполнения команд.

                              В любом случае, во время начального хендшейка, клиент передает флаги возможностей которые клиент поддерживает, и если клиент не умеет обрабатывать новые фишки, то сервер просто не будет их передавать клиенту.

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

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