ANTICHAT - Умный бэкап MySQL

http://habrastorage.org/storage1/b96...3/3e5401f3.jpg

Думаю, у многих в жизни возникала ситуация, когда есть полный бэкап базы MySQL, и нужно восстановить только несколько таблиц из него. Или, еще хуже, нужно восстановить старые записи в этих таблицах, не затерев при этом новых. А в случае если дамп занимает сотни мегабайт, процесс не очень вдохновляет.

В этой статье я опишу концепцию умного бэкапа, реализованную в Sypex Dumper. А также приведу примеры использования.

Вступление

В процессе разработки Sypex Dumper 2 были проанализированы многие решения для бэкапа MySQL. И в первую очередь, естественно, исходники комплектного mysqldump.

В mysqldump создается файл с набором SQL-запросов, которые при восстановлении просто разбиваются по разделителю и «скармливаются» MySQL. Такая схема, в принципе, давно работает и, по сути, она стала стандартом. Но у такой схемы есть следующие недостатки:

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

В общем, решил поэкспериментировать с новой концепцией бэкапа.

Умный бэкап в теории

Были определены следующие принципы:

Дамп делается в формате, содержащем только необходимый минимум информации.
Минимум опций бэкапа, для максимальной скорости выполнения.
Все вспомогательные SQL-запросы (DROP TABLE и т.п.) убрать из дампа и добавлять их автоматически в процессе восстановления.
Добавление расширенных настроек при восстановлении дампа.
Парсер SQL заточенный исключительно на дампы, и умеющий разбивать длинные INSERT-запросы на более короткие.
Добавление в сам файл метаинформации.
Автоматическая и главное корректная работа с кодировками.

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

Например, скорость парсинга проверялась на RAM-диске, файл 860 МБ проглатывался за 0,5 секунды, у phpMyAdmin на аналогичный файл уходили десятки минут (выполнение запросов в обоих случаях было выключено).

Умный бэкап на практике

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

Например, если удалена или изменена часть данных в таблице, и их нужно восстановить, но при этом с момента последнего бэкапа в таблицу были добавлены новые строки, и нужно, чтобы они не пострадали. В таком случае достаточно в дампере выбрать нужную таблицу и режим восстановления REPLACE. В результате дампер восстановит только те строки, которые есть в дампе, а новые строки останутся нетронутыми. Если же нужно восстановить только удаленные строки, то можно воспользоваться режимом INSERT IGNORE.

http://habrastorage.org/storage1/162...c/20f60118.png

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

Еще одна полезная фишка — возможность восстановления таблиц с заменой префикса в названиях.

Все настройки восстановления (как и бэкапа) можно сохранить, и потом выполнять в пару кликов либо по крону (удобно для демо-сайтов).

Кодировки

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

Sypex Dumper имеет довольно развитые функции работы с кодировками. Он спокойно в автоматическом режиме «переваривает» таблицы с разными кодировками в одном дампе, при этом не конвертирует все данные в UTF-8. Также в нем предусмотрены функции по коррекции кодировок.

Удивляет, что очень много софта, в том числе известных коммерческих скриптов до сих пор не умеют толком работать с кодировками (например, тот же vBulletin 4). Да, они делают таблицы в кодировке UTF-8, делают кодировку в заголовках и шаблонах UTF-8, но при этом забывают устанавливать кодировку соединения к MySQL в UTF-8. В итоге MySQL думает, что данные пришли в latin1, и пытается их конвертировать из latin1 в UTF-8. И самое плохое в таких «косяках», что их не сразу и заметишь, так как внешне форум выглядит нормально, разве что поиск и сортировка по тексту, не очень правильно работают.

C помощью дампера можно эти проблемы довольно просто решать. Но, это уже тема для отдельной статьи.

zapimir

http://habrahabr.ru/blogs/mysql/131797/