SXB: инкрементальный бэкап MySQL

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

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

Формат SXB предназначен для пользователей начального и среднего уровня. Для тех, кто не знаком (или не может использовать их на конкретном сайте) со средствами горячего бэкапа (бинарные логи, снимки файловой системы, Xtrabackup и т.п.). Грубо говоря, для тех, кто для бэкапа MySQL использует mysqldump и подобные программы, создающие SQL-дамп базы.

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

Почему не mysqldump?

Итак, одним из самых популярных вариантом для бэкапа баз данных MySQL является идущая в комплекте программа mysqldump. В интернете, можно встретить множество, как простых скриптов (в том числе, есть несколько статей на Хабре), так и отдельных программ бэкапа в которых используется mysqldump.

Так почему же не использовать его? Основные недостатки связаны с пресловутым «unix way» (когда каждая программа делает минимальную задачу и не подозревает о других программах).

В данном случае возникают следующие проблемы:

• Использование временного файла

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

• Плохая поддержка дедупликации

  Из-за того, что mysqldump вставляет комментарии и различные метаданные (типа значения auto_increment), а также из-за зависимости одних таблиц от других.

• Отсутствие навигации по дампу

  Чтобы достать данные только из одной таблицы нужно парсить дамп, пока не найдешь нужное место. Потому чаще всего восстанавливают весь дамп (возможно во временную базу данных).

• Отсутствие постпроцессинга

  Так как дамп представляет собой просто набор SQL запросов, а восстановление выполняет стандартная программа для выполнения любых запросов. То восстановление занимает дольше времени (из-за более сложного парсера), и нет возможности изменять запросы (например, использовать REPLACE вместо INSERT).

Часть из этих вопросов можно попробовать решить различными опциями mysqldump, но в результате усложняется восстановление дампа. Да и на практике таких решений не встречал. Я пробовал решить некоторые из этих проблем в Sypex Dumper 2. Там в SQL-дамп добавлены специальные метаданные. Но в новой версии решил пойти дальше.

Новые возможности бэкапа MySQL в формате SXB

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

В pro версии дампера, уже был использован SELECT OUTFILE для ускорения процесса бэкапа. Так как вполне понятно, что намного быстрее будет, если MySQL-сервер сам будет сохранять данные в файл, а не передавать данные разбитые на отдельные поля, каждое из которых еще нужно экранировать, добавить кавычки, скобки и т.п. Но при этом дамп всё же приводился к привычному SQL-виду.

В новой же версии, я задумался над тем, зачем тратить время на оформление дампа во время бэкапа, если на эти декорации уходит 30-50% времени. Учитывая, что бэкап обычно делается значительно чаще, чем восстановление. То естественно лучше перенести дополнительную нагрузку на процесс восстановления.

Работая над форматом SXB и блочной дедупликацией для файлов, появилась идея, что неплохо бы попробовать и дедупликацию для самой БД. Ведь в базе данных меняется не так много данных между бэкапами.
На первом этапе решил реализовать дедупликацию на блочном уровне, также как и для файлов. У такого варианта, конечно, есть недостатки по сравнению с дедупликацией на уровне строк. Но он будет значительно быстрее и менее требовательным к ресурсам.

Сами данные хранятся для каждой таблицы отдельно, в виде строк с табуляцией в качестве разделителей. Блоки таблиц не зависят, от других таблиц. Этим достигается компактность файла бэкапа, плюс расширенные возможности по постпроцессингу при восстановлении. Также отдельно хранится структура каждой таблицы. При этом из структуры таблиц вырезается значение AUTO_INCREMENT, чтобы не бэкапить постоянно структуру таблицы у которой только AUTO_INCREMENT меняется. А само значение AUTO_INCREMENT сохраняется в метаданных заголовка таблицы.

Для каждого блока считается идентификатор (CRC32 + MD5 + Размер блока), по этому идентификатору определяется уникальность блока. Два алгоритма хэширования используются для того, чтобы избежать коллизий. А сами алгоритмы выбраны, как наиболее быстрые. Также считается общий MD5-хэш для всей таблицы.

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

Протестировать инкрементальный бэкап MySQL на деле можно с помощью упрощенного скрипта, желательно поделиться данными.

Обязательно наличие прав доступа FILE у вашего MySQL пользователя и MySQL-сервер должен находиться на localhost. Если будет много желающих добавлю версию и с простыми селектами.

В результате работы получите такую таблицу (обрезал дату, чтобы влезло на страницу).

+--------+------+---------+--------+--------+----------+----------+----------+--------+ |  D & t | Tabs | Rows    | Blocks | Dubs   |   Size   | Dub.size | SXB.size | Time   | +--------+------+---------+--------+--------+----------+----------+----------+--------+ |  07:38 |   26 |  557180 |   4779 |      0 | 37.03 MB |        - | 11.43 MB | 3.8561 | |  08:06 |   26 |  557187 |   4779 |   4761 | 37.03 MB | 36.92 MB |  39.8 KB | 2.8214 | |  08:22 |   26 |  557187 |   4779 |   4775 | 37.03 MB |    37 MB | 12.22 KB | 2.5557 | |  08:37 |   26 |  557187 |   4779 |   4778 | 37.03 MB | 37.03 MB |  3.75 KB | 2.5052 | |  08:52 |   26 |  557193 |   4779 |   4768 | 37.03 MB | 36.96 MB | 24.57 KB | 2.6060 | |  09:10 |   26 |  557196 |   4779 |   4775 | 37.03 MB | 37.01 MB |  7.79 KB | 2.8218 | +--------+------+---------+--------+--------+----------+----------+----------+--------+ 

Первый раз делается полный бэкап, последующие бэкапы инкрементальные. «SXB.size» показывает размер файла с инкрементным бэкапом (т.е. измененные блоки).