АРХИВ СТАТЕЙ ЖУРНАЛА «МОЙ КОМПЬЮТЕР» ЗА 2003 ГОД

БД на марше

• Все журналы \ Номер 46/269 `2003 от 17.11.2003 \ БД на марше

Александр ЛОЗОВЮК info@sinergetix.com

Разрабатывая или поддерживая сайты, основанные на популярной OpenSource БД MySQL, пользователи неоднократно сталкиваются с проблемами резервирования, восстановления и переноса баз с одного компьютера на другой. Например, при смене хостинговой площадки надо быстро и без потерь данных перенести одну или несколько баз на новый хостинг. Или при разработке программист использует дома MySQL для Windows, а потом надо переносит свою разработку на реальный UNIX-сервер. Как это сделать корректно и быстро? Об этом наш дальнейший рассказ.

Чем отличается резервное копирование или перенос БД от обычных запросов? Как минимум тем, что переносится обычно вся база, вместе со всеми таблицами (которых может быть много), индексами, а то и несколько баз. А это значит, что, например, ручная выборка всех записей из каждой таблицы будет очень продолжительной. Поэтому даже в базовой поставке MySQL есть специальные средства для облегчения и автоматизации таких действий. Кроме того, большинство front-end интерфейсов (обычно графических, так как сама MySQL — консольное приложение) предлагают свои решения, зачастую функционально более насыщенные.

Средства языка SQL

Сам язык SQL (тот, который поддерживает БД MySQL) имеет несколько операторов, которые помогают при перенесении базы с одного сервера на другой. Оператор LOAD DATA позволяет загрузить данные с локального или удаленного файла в определенную таблицу. Применяя его при загрузке данных вместо команды INSERT, вы можете достичь большой экономии времени. Синтаксис и принцип работы оператора довольно сложен, поэтому мы опишем только самые нужные опции.

LOAD DATA LOCAL INFILE “data.sql” — ключевое слово LOCAL уведомляет сервер, что нужный файл данных находится на машине клиента, и его сначала нужно считать и передать по сети. Используйте этот режим осторожно, так как передача большого файла данных может занять много времени, а при обрыве связи или ошибке сервера все придется начинать сначала. Рекомендуется сначала загрузить файл на сервер (например, через FTP, в директорию /home/data.sql), а потом вызывать команду загрузки без опции LOCAL, только с указанием пути к файлу —LOAD DATA INFILE “/home/data.sql”. Путь можно задавать как абсолютный, так и относительный. Сервер сам решает, где искать файл. Если задано только имя файла, он должен находиться в директории, где находится БД. Если же путь содержит несколько частей, он воспринимается как путь относительно каталога БД (опция --datadir при старте сервера).

Ключевые слова IGNORE | REPLACE указывают, как поступать при обнаружении дублирующих значений в полях с уникальными значениями. Эта опция важна при загрузке данных в таблицу, где уже есть информация. Должно быть определено либо одно, либо другое слово, иначе при встрече такой записи процесс загрузки остановится, и все дальнейшие данные не будут помещены в таблицу.

Отдельно надо остановится на определении полей, вернее, их разделителей. Цифровые поля особых затруднений не вызывают, проблемы возникают при различных кодировках в текстовых полях —VARCHAR, CHAR, TEXT. Поэтому введена опция FIELDS, в которой можно задать следующие символы:

TERMINATED BY ‘xx’ — определяет символ, разграничивающий значения в строке;

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

ESCAPED BY ‘xx’ — определяет символ, который указывает на то, что следующий символ в строке — специальный;

еще одна опция —LINES TERMINATED BY ‘xx’, определяет символ окончания строки (это необходимо при переносе данных БД с сервера UNIX на Windows или наоборот). Например, при переносе файла, созданного в Windows команда будет иметь следующий вид:

Если значения разделены запятыми, а поля заключены в двойные кавычки, то оператор загрузки будет иметь такой вид (классический вариант при переносе с домашней Windows-машины на рабочий сервер):

Для создания файла с данными (резервирование рабочей таблицы) применяется команда SELECT в следующем виде:

Для этой команды применимы все вышеописанные опции задания разделителей строки и полей. Как видите, SELECT * INTO OUTFILE идеально сочетается с LOAD DATA INFILE. Но и ограничения этих операторов очевидны — они позволяют переносить только одну таблицу за раз. Зато и возможности велики — ведь в операторе выборки SELECT можно использовать предложение WHERE. Кроме того, индексы не переносятся, к тому же не сохраняется информация о структуре таблицы — предназначенная для переноса таблица уже должна существовать на сервере.

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

Утилиты mysqldump и mysqlimport

Утилита mysqldump входит в стандартную конфигурацию MySQL и работает с командной строки. Перед началом работы убедитесь, что пользователи не обращаются с запросами к БД, которую вы резервируете. Кроме того, убедитесь, что на диске есть достаточно места — результатом работы утилиты mysqldump является простой текстовый файл, который может быть довольно значительным по объему. Несмотря на обилие опций, работа с mysqldump очень проста:

Можно указать базу, а также отдельные таблицы внутри базы, которые надо скопировать. Опция --databases base1 base2 позволяет скопировать несколько БД, а опция --all-databases копирует все размещенные на сервере базы.

Опции задаются перед описанием копируемых баз, а наиболее полезными являются:

--add-drop-table. В выходной файл будет добавляться выражение DROP TABLE IF EXISTS и существующие таблицы будут перезаписаны;

--lock-tables. Блокирует от изменений все таблицы, с которыми работает mysqldump.

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

--force. Позволяет продолжить работу, игнорируя ошибки.

Для форматирования можно применять все опции оператора LOAD DATA, только их синтаксис меняется — опции начинаются с символа --, а вместо пробела —-; значение опции присваивается знаком =. Например, --lines-terminated-by=”\r\n”.

В паре с этой утилитой работает утилита mysqlimport. Она является интерфейсом командной строки к оператору LOAD DATA и применяет все те же опции, что и сам оператор. Следует отметить, что файлы, содержащие данные таблиц, должны иметь имена те же, что и у таблиц (расширение может быть любым, оно игнорируется), так как утилита воспринимает заданное название таблиц как название файлов. Это значит, что для таблицы basetbl файл для импорта должен называться basetbl.xxx (с любым расширением).

При работе с утилитой кроме опций форматирования, описанных выше, можно использовать такие опции:

--delete. Очищает таблицы перед вставкой данных;

--force. Продолжать работу, несмотря на ошибки;

--local. По умолчанию утилита считает, что файлы для импорта находятся на сервере. Эта опция позволяет работать с локальными файлами, а утилита сама передаст их на сервер. Это может существенно замедлить работу, а также привести к повторной отправке всех файлов, если встретилась ошибка, и опция --force не задана;

--lock-tables. Блокирует таблицы на период загрузки данных. Рекомендую всегда использовать эту опцию для избежания повреждения данных;

--replace. Заменяет дубликаты строк.

Резервирование и перенос таблиц методом прямого копирования

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

Узнать, какой формат имеет таблица, можно по расширению. Файл, хранящий данные таблицы, имеет расширение .isd, а файлы индексов —.ism. Для формата MyISAM это .myd и .myi соответственно.

Естественно, в процессе копирования надо блокировать доступ к таблицам — лучше всего просто временно остановить сервер.

Хотя при таком методе переноса отпадает необходимость в соблюдении формата полей и т.д., возникают другие проблемы, уже на уровне файловой системы — например, регистрозависимость имен файлов для Linux/Unix и игнорирование регистров в Windows, что может потребовать переименование всех или части файлов. Также необходимо следить за другими ограничениями, например за размером файлов (на сервере отдельная таблица может иметь объем и более 2 Гб, а вот на локальной машине FAT может не поддерживать такой размер файла).

Резервирование и перенос в phpMyAdmin

Очень популярная визуальная оболочка phpMyAdmin предустановлена почти на всех хостингах с поддержкой MySQL. Написанная на PHP, она работает на web-сервере, доступна через обычный web-браузер и позволяет просто и наглядно работать с таблицами и базами, администрировать и управлять сервером БД. Возможно, я расскажу об этой системе отдельно, сейчас же нас интересует, как выполнить копирование и вставку отдельных таблиц и целой БД посредством оболочки phpMyAdmin.

Сначала выбираем необходимую нам базу (если на сервере их несколько). Пока оболочка позволяет за раз копировать только одну базу (Рис. 1). Переходим на вкладку Экспорт, где и сосредоточены все нужные функции (Рис. 2). Можно отметить только нужные таблицы или все сразу. Как видите, есть пять разных форматов, в которых можно получить дамп, не считая сжатия. Думаю, что для наших нужд подходит формат SQL. Для переноса базы на Windows-компьютер и последующего импорта в MS Excel выберите формат CSV для данных MS Excel.

Справа предлагаются некоторые опции. Добавить удаление таблицы соответствует опции --add-drop-table утилиты mysqldump, Обратные кавычки в названиях таблиц и полей заключает, соответственно, в кавычки имена полей. Хотя по умолчанию опция включена, рекомендую ее убрать — меньше придется настраивать, если для экспорта вы применяете одни средства, а импортируете данные другим методом.

Опции Полная вставка и Расширенные вставки отличаются тем, что первая формирует операторы INSERT INTO для каждого ряда, а вторая использует один оператор INSERT, который вставляет сразу несколько строк. Это аналогично применению опций --extended-insert. Для повышения быстродействия (особенно в таблицах, где есть индексы) рекомендуется применять расширенный оператор INSERT, поскольку в таком случае индексы обновляются один раз, после всей вставки, и сам оператор работает быстрее, а при пересылке команд по сети экономит трафик.

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

После описанных манипуляций получаем то, что изображено на Рис. 3. Обратное восстановление происходит очень просто. Переходим на вкладку SQL (Рис. 4).

Если дамп выводится в окно браузера, скопируйте текст в поле SQL-запроса и выполните его. Если вы имеете данные в виде файла, нажмите Обзор и укажите файл, он будет закачан на сервер, и данные будут обработаны. Это аналогично исполнению оператора LOAD DATA LOCAL INFILE. Тут же можно указать, сжат ли файл (или сервер сам определит). При наличии сжатия и необходимых библиотек файл будет разархивирован сервером самостоятельно.

И напоследок еще одно существенное замечание. Внимательно проверяйте кодировки! В частности, после запуска на первой странице phpMyAdmin выберите нужную кодировку; если вы переносите данные с домашнего сервера на рабочий, выставьте одинаковые кодировки на обоих компьютерах еще до начала создания копии и загрузки данных. Лучше всего посмотреть, с какими опциями запускается сам сервер MySQL (опция --language при загрузке или в ini-файле конфигурации). Несовпадение кодировок при создании дампа и загрузке приведет к тому, что данные после выборки нужно будет еще раз перекодировать, а иногда даже phpMyAdmin может отказаться работать, вернее, не сможет корректно отобразить ваши данные.

Идёт загрузка...

Данную страницу никто не комментировал. Вы можете стать первым.

Ваше имя:
Ваша почта:
Комментарий:
Введите символы или вычислите пример: * Обновить