Статьи Умный бэкап MySQL

Discussion in 'Статьи' started by d3l3t3, 3 Nov 2011.

  1. d3l3t3

    d3l3t3 Banned

    Joined:
    3 Dec 2010
    Messages:
    1,771
    Likes Received:
    98
    Reputations:
    10
    [​IMG]

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

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

    Вступление

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

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

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

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

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

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

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

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

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

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

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

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

    [​IMG]

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

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

    Кодировки

    Отдельный абзац посвящу кодировкам, так как довольно много людей обращается с проблемами.
    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/
     
Loading...