Резервное копирование баз MySQL

методика и утилиты для создания резервных копий баз данных и таблиц MySQL

Для современных сайтов базы данных стали неотъемлемой частью. Информация, которая в них хранится, очень важна для работы сайта или сервиса, иногда перекрывая по стоимости весь сайт и даже аппаратное обеспечение. Поэтому создание резервных копий информации из баз данных является важной задачей для владельца или администратора. В случае администрирования сервера хостинг-провайдером услуга резервирования является базовой и осуществляется для всего сервера по расписанию — к примеру, раз в сутки. Но часто возникает потребность самому создать копию информации с базы данных. Так как наиболее часто используется сервер баз данных MySQL, то далее мы будем считать, что именно он установлен на сайте.

Есть несколько методов создания резервных копий базы данных:

• Метод прямого копирования;
• Создание дампа таблицы посредством операторов SQL;
• Служебными утилитами MySQL;
• Через менеджер БД (к примеру, phpMyAdmin);
• Используя специальные программы.

Прямое копирование — самый быстрый метод

Метод прямого копирования — самый простой. MySQL хранит каждую базу данных в отдельном каталоге (/data/), а каждая таблица хранится в одном или нескольких файлах (в действительности это три файла с разными расширениями: в одном хранится структура информации, в другом — непосредственно сами данные таблицы, в третьем – индексы). Поэтому самый очевидный путь (и самый быстрый) – это скопировать все файлы с каталога базы (если у вас одна база), или только необходимые таблицы и каталоги БД. Но существует ряд ограничений. Методом простого копирования можно переносить только таблицы формата MyISAM, которые не зависят от платформы. Иначе таблицы, созданные в других форматах, будут работать только на компьютерах одной архитектуры (наиболее распространенный вариант – х86). Это значит, что если у вас компьютер x86, то вы можете перенести таблицы только на сервер такой же архитектуры. Кроме указанных ограничений, есть и налагаемые самой операционной системой – ограничения на размер файла, зависимость от регистра имен файлов (имя файла с данными таблицы – это название таблицы, а MySQL сможет правильно трактовать только идентичные файлы). А самое важное – таким образом нельзя скопировать открытые таблицы (вернее можно, но целостность информации не гарантируется), для резервирования желательно остановить сервер БД, а это может быть неприемлемым.

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

В синтаксисе языка SQL, который применяется в MySQL, предусмотрена специальная форма оператора выборки данных SELECT, которая выбирает данные из таблицы и записывает их в текстовый файл. Оператор выглядит таким образом:

SELECT * INTO OUTFILE "data.sql" FIELDS TERMINATED BY «,» FROM datatbl

В запросе можно указать дополнительные опции — в частности, как разделять текстовые поля — VARCHAR, CHAR и TEXT:

TERMINATED BY «xx» – определяет символ, разграничивающий значения в строке;
[OPTIONALLY] ENCLOSED BY «xx» – определяет символ кавычек для ограничения значения поля. Опция OPTIONALLY применяется для указания, что в кавычках заключены только поля CHAR и VARCHAR;
ESCAPED BY «xx» – определяет символ, который указывает на то, что следующий символ в строке — специальный;

Ограничения такого метода также существенны: можно переносить только одну таблицу за раз. Хотя с большими возможностями – ведь в операторе выборки SELECT можно использовать предложение WHERE. Кроме того, индексы не переносятся, и, к тому же, не сохраняется информация о структуре таблицы – для переноса обратно таблица уже должна существовать на сервере.

mysqldump входит в дистрибутив

Метод резервирования с использованием служебных утилит MySQL аналогичен применению команд SQL, так как эти команды — всего лишь надстройка к утилитам командной строки. Сама программа называется mysqldump, а результатом ее работы является простой текстовый файл, который может быть довольно значительным по объему. Несмотря на обилие опций, работа с mysqldump очень простая.

mysqldump mydase [tables] "base_dumps.sql"

Можно указать базу, а также отдельные таблицы внутри базы, которые надо скопировать. Опция --databases base1 base2 позволяет скопировать несколько БД, а опция --all-databases копирует все размещенные на сервере базы. Рекомендуется всегда задавать опцию оптимизации процесса --opt (который может быть долгим и серьезно загружать сервер БД). При задании этой опции самостоятельно больше ничего делать не надо, сервер сам заблокирует от изменений все таблицы и скопирует их, восстановив впоследствии доступ. Для форматирования вывода можно применять все опции оператора LOAD DATA, только их синтаксис меняется – опции начинаются с символа "--", а вместо пробела – "-"., значение опции присваивается знаком "=". Например, --lines-terminated-by="\r\n".

phpMyAdmin позволяет легко скопировать отдельные таблицы и базы

Если для управления базами вы используете популярный менеджер phpMyAdmin (он присутствует как стандартный скрипт на большинстве хостингов), то процесс резервирования упрощается. Через графический интерфейс вы можете настроить все необходимые параметры резервирования и выбрать формат готового файла. Самое интересное – phpMyAdmin может самостоятельно заархивировать полученный дамп базы и выдать его вашему браузеру для закачки. Эту возможность желательно использовать всегда, так как по умолчанию вся информация выводит в окно браузера, что при больших объемах приводит к замедлению работы и зависанию браузера.

MySQL Export имеет очень мощные опции форматирования и экспорта

И, наконец, самый легкий путь – воспользоваться специальными утилитами для импорта/экспорта информации. К примеру, известная компания EMS имеет в своем наборе утилит программу MySQL Export. Утилита может экспортировать таблицы MySQL во множество разных форматов – как в таблицы других БД (Access, DBF), так и форматы RTF, XML, MS Word, CSV и другие. Определенным недостатком является то, что одновременно можно работать только с одной базой, размещенной на локальном компьютере, или удаленным сервером. Но удобство и богатство настроек экспорта сразу выводят эту программу в лидеры – ни один другой способ резервирования не позволяет так тонко настроить все параметры. Однако отсутствие встроенной возможности архивировать полученные данные затрудняют работу с большими данными.

Для периодического резервирования на стороне сервера идеально подходит утилита mysqldump — один раз настроив все параметры, ее можно запускать по расписанию через cron. Если же надо срочно получить копию базы или отдельных таблиц, для этого подойдет установленный на сервере пакет phpMyAdmin, тем более что такое решение независимо от пользователя – он может работать как с дома или рабочего места, так и с компьютерного клуба, лишь бы Интернет был. Но когда необходимо постоянно работать с информацией из баз, переносить ее между разными платформами и приложениями, то утилита EMS MySQL Export является лучшим выбором.