Перенос баз данных с одного сервера на другой

Одной из часто встречающихся задач является перенос баз данных с одного сервера на другой или просто создание резервной копии баз данных. В СУБД MySQL предоставляет для этого множество инструментов, однако чаще всего используется одно из двух решений:

• копирование бинарных файлов баз данных;
• создание SQL-дампа.

Копирование бинарных файлов

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

Примечание. Создание в каталоге данных нового каталога аналогично созданию новой базы данных.

В каталоге данных для каждой базы данных заводится свой подкаталог, а каждая таблица представлена тремя файлами, имена которых совпадают с именами таблицы, а расширения имеют следующий смысл:

• frm — определяет структуру таблицы, имена полей, их типы, параметры таблицы и т.д.;
• MYD — содержит данные таблицы (расширение образовано от сокращения MYData);
• MYI — содержит индексную информацию (расширение образовано от сокращения MYIndex).

Однако копирование данных непосредственно из каталога данных работающего сервера может привести к повреждению копий таблиц, причем это может случиться даже в том случае, если MySQL-сервер не обращался к копируемым таблицам в текущий момент. Поэтому перед копированием бинарных данных необходимо либо остановить сервер, либо блокировать таблицы на запись. Блокировку таблиц удобно осуществить при помощи оператора FLUSH TABLES.

Блокировка таблиц на запись

FLUSH TABLES WITH READ LOCK;

Для того, чтобы блокировка осталась в силе на время копирования, следует оставить клиент включенным и не выходить из него до тех пор, пока копирование каталога данных не будет завершено.

Для того, чтобы снять блокировку на запись, следует выполнить запрос UNLOCK TABLES.

Снятие блокировки

UNLOCK TABLES;

В дистрибутив MySQL входит скрипт горячего копирования бинарных файлов баз данных mysqlhotcopy. Данный скрипт действует по той же схеме, что описано ранее: блокирует таблицы базы данных base и копирует их бинарное представление по указанному пути /to/new/path.

Использование скрипта mysqlhotcopy

mysqlhotcopy base /to/new/path

Cоздание SQL-дампа

Копирование бинарных файлов не всегда удобно, иногда требуется развернуть базу данных в другой СУБД или СУБД MySQL более ранней версии, не поддерживающей нововведений поздних версий. В этом случае часто прибегают к созданию SQL-дампов. SQL-дамп — это текстовой файл с SQL-инструкциями, выполнение которых воссоздает базу данных.

Основным инструментом для создания SQL-дампов служит улита mysqldump. Для того, чтобы создать резервную копию базы данных, например wet, необходимо выполнить следующую команду.

Создание дампа базы данных wet

Улита mysqldump принимает имя пользователя при помощи параметра -u. Если учетная запись защищена паролем, следует добавить параметр -р. После всех параметров указывается имя базы данных wet, для которой производится создание дампа. Так как вывод данных осуществляется в стандартный поток(за которым по умолчанию закреплен экран монитора), его следует перенаправить в файл wet.sql. Перенаправление данных осуществляться при помощи оператора >. Если вместо оператора > использовать >>,то данные не будут перезаписывать уже существующий файл, а будут добавлены в конец файла. Созданный файл находится в каталоге bin MySQL. Откроем его.

Обратите внимание, что команда CREATE TABLE заканчивается описанием параметров таблицы:

ENGINE=InnoDB DEFAULT CHARSET=cp1251;

Первый из них ENGINE, указывает тип таблиц, к которому относится таблица author. Тип InnoDB применяется в сервере MySQL под Windows по умолчанию. Если создается таблица такого типа, то сервер производит проверку данных, вносимых в столбцы, помеченные как внешний ключ. Проверяется, соответствует ли тип данных этого столбца типу того столбца другой таблицы, с которой осуществляется связь. Кроме того, тип InnoDB поддерживает транзакции.

Для того, чтобы создать дамп сразу нескольких баз данных, надо воспользоваться параметром --databases (в сокращенной форме -В). Названия баз данных перечисляются через пробел.

Создание дампа нескольких баз данных

С:\mysql\bin>mysqldump -u root -p -B wet test > wet_test.sql
Enter password: *****

Команда, представленная в листинге, сохраняет дамп базы данных wet и test в файле wet_test.sql.

Если необходимо сохранить дамп всех баз данных MySQL-сервера, следует воспользоваться параметром --all-databases или в сокращенной форме -А.

Создание дампа всех баз данных MySQL-сервера

С:\mysql\bin>mysqldump -u root -p --all-databases > all-databases.sql
Enter password: *****

Развернуть SQL-дамп на другом сервере можно при помощи улиты mysql в пакетном режиме. Для этого нужно скопировать дамп базы данных в каталог bin MySQL-сервера и выполнить следующие действия:

Развертывание дампа базы данных с использованием mysql

mysql -u root test < wet.sql

В листинге данные из дампа wet.sql перенаправляются на стандартный вход улиты mysql, которая размещает таблицы базы данных wet в базе данных test.

Развертуть SQL-дамп можно не только в пакетном режиме, но и в диалоговом. Самый простой способ — это воспользоваться командой SOURCE, которая выполняет несколько SQL-инструкций, перечисленных в SQL-дампе.

Использование команды SOURCE

SOURCE wet.sql;

Примечание. Следует указывать путь к файлу в команде SOURCE или помещать SQL-дамп в каталог bin. В последнем случае можно использовать лишь имя файла.

Объемные текстовые файлы и многочисленные бинарные файлы таблиц зачастую неудобны для транспотрировки, и их обычно упаковывают в архив. В Windows для этого можно воспользоваться специализированными пакетами WinZip или WinRar.