MySQL / 10. Импорт и экспорт данных

Экспорт содержимого таблиц или определений в SQL-формат

Задача
Вы хотите экспортировать таблицы или базы данных в SQL-предложения, чтобы упростить их последующий импорт.

Решение
Используйте утилиту mysqldump без опции --tab.

Обсуждение
Как уже упоминалось в рецепте 10.14, утилита mysqldump, запущенная с опцией --tab, заставляет сервер MySQL записывать таблицы в файлы с «сырыми» данными на хосте сервера. Если же опустить опцию --tab, сервер форматирует записи таблицы как предложения INSERT и возвращает их в mysqldump.

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

Чтобы экспортировать таблицу в файл в формате SQL, выполните такую команду:

% mysqldump cookbook states > dump.txt

Создается файл вывода dump.txt, содержащий как предложение CREATE TABLE, так и набор предложений INSERT:

# MySQL dump 8.16
#
# Host: localhost Database: cookbook
#--------------------------------------------------------
# Server version 3.23.46-log
#
# Table structure for table 'states'
#
CREATE TABLE states (name varchar(30) NOT NULL default '',
abbrev char(2) NOT NULL default '',
statehood date default NULL,
pop bigint(20) default NULL,
PRIMARY KEY (abbrev)
) TYPE=MyISAM;
#
# Dumping data for table 'states'
#
INSERT INTO states VALUES ('Alaska','AK','1959-01-03',550043);
INSERT INTO states VALUES ('Alabama','AL','1819-12-14',4040587);
INSERT INTO states VALUES ('Arkansas','AR','1836-06-15',2350725);
INSERT INTO states VALUES ('Arizona','AZ','1912-02-14',3665228);
INSERT INTO states VALUES ('California','CA','1850-09-09',29760021);
INSERT INTO states VALUES ('Colorado','CO','1876-08-01',3294394);
...

Для того чтобы вывести содержимое нескольких таблиц, укажите их имена после аргумента базы данных. Чтобы сделать дамп всей базы данных, ничего не указывайте после имени базы данных. Чтобы выполнить дамп всех таблиц всех баз данных, вызовите mysqldump так:

% mysqldump --all-databases > dump.txt

В этом случае файл вывода будет содержать предложения CREATE DATABASE и USE имя_БД, расположенные определенным образом, так что при последующем считывании файла каждая таблица будет создана в соответствующей базе данных. Опция --all-databases появилась в версии MySQL 3.23.12.

Доступны и другие опции, определяющие формат вывода:

--no-create-info

Не формировать предложения CREATE TABLE. Используйте опцию, если вы хотите выгрузить только содержимое таблицы.

--no-data

Не формировать предложения INSERT. Используйте опцию, если вы хотите выгрузить только определение таблицы.

--add-drop-table

Предварить каждое предложение CREATE TABLE предложением DROP TABLE. Это удобно для формирования файла, который может использоваться для повторного создания таблиц с нуля.

--no-create-db

Не формировать предложения CREATE DATABASE, которые обычно выводит опция --all-databases.

Теперь предположим, что вы используете mysqldump для создания файла дампа в формате SQL. Как осуществить обратный импорт в MySQL? Распространенной ошибкой является использование mysqlimport. Кажется логичным, что раз mysqldump экспортирует таблицы, mysqlimport должна их им-портировать, не так ли? К сожалению, нет. Может быть, это и логично, но не всегда правильно. Правда в том, что если вы используете опцию --tab для
mysqldump, то можете импортировать результирующие файлы данных при помощи mysqlimport. Но если вы выполняете дамп файла в формате SQL, mysqlimport не сможет корректно его обработать. Используйте лучше программу mysql. Конкретный способ выполнения операции зависит от того, что содержится в файле дампа. Если вы выгружали несколько баз данных при помощи --all-databases, то файл будет содержать соответствующие предложения USE имя_БД для выбора базы данных, к которой относится каждая таблица, и аргумент базы данных в командной строке не потребуется:

% mysql < dump.txt

Если таблицы выгружались из одной базы данных, необходимо указать mysql, в какую базу их следует импортировать:

% mysql имя_БД < dump.txt

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

Статьи по MySQL на эту тему:

Диагностическая утилита для LOAD DATA
Извлечение и перестановка столбцов файлов данных
Импорт XML в MySQL
Использование временных таблиц для преобразования дат
Использование дат с недостающими частями

Вернуться в раздел: MySQL / 10. Импорт и экспорт данных