Бэкапы 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 ГБ
Бэкап таблицы GeoNames около 1 ГБ
