Учебники

MySQL — экспорт базы данных

Декабрь 28, 2018

Самый простой способ экспорта табличных данных в текстовый файл — использование оператора SELECT … INTO OUTFILE, который экспортирует результат запроса непосредственно в файл на хосте сервера.

Экспорт данных с помощью оператора SELECT … INTO OUTFILE

Синтаксис этого оператора объединяет обычную команду SELECT с именем файла INTO OUTFILE в конце. Формат вывода по умолчанию такой же, как и для команды LOAD DATA. Итак, следующий оператор экспортирует таблицу tutorials_tbl в /tmp/tutorials.txt в виде файла с разделителями табуляцией и строкой, заканчивающейся строкой . 

mysql> SELECT * FROM tutorials_tbl 
   -> INTO OUTFILE '/tmp/tutorials.txt';

Вы можете изменить формат вывода, используя различные параметры, чтобы указать, как заключать в кавычки и разделять столбцы и записи. Чтобы экспортировать таблицу tutorial_tbl в формате CSV со строками, оканчивающимися CRLF, используйте следующий код. 

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.txt'
   -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
   -> LINES TERMINATED BY '\r\n';

SELECT … INTO OUTFILE имеет следующие свойства —

  • Выходной файл создается непосредственно сервером MySQL, поэтому имя файла должно указывать, куда вы хотите записать файл на хост сервера. ЛОКАЛЬНОЙ версии оператора, аналогичной ЛОКАЛЬНОЙ версии LOAD DATA, не существует .

  • У вас должна быть привилегия MySQL FILE для выполнения инструкции SELECT … INTO .

  • Выходной файл не должен уже существовать. Это препятствует тому, чтобы MySQL забивал файлы, которые могут быть важны.

  • У вас должна быть учетная запись на хосте сервера или какой-то другой способ получить файл с этого хоста. В противном случае команда SELECT … INTO OUTFILE , скорее всего, не будет иметь для вас никакого значения.

  • В UNIX файл создается для чтения всем миром и принадлежит серверу MySQL. Это означает, что, хотя вы сможете прочитать файл, вы не сможете удалить его.

Выходной файл создается непосредственно сервером MySQL, поэтому имя файла должно указывать, куда вы хотите записать файл на хост сервера. ЛОКАЛЬНОЙ версии оператора, аналогичной ЛОКАЛЬНОЙ версии LOAD DATA, не существует .

У вас должна быть привилегия MySQL FILE для выполнения инструкции SELECT … INTO .

Выходной файл не должен уже существовать. Это препятствует тому, чтобы MySQL забивал файлы, которые могут быть важны.

У вас должна быть учетная запись на хосте сервера или какой-то другой способ получить файл с этого хоста. В противном случае команда SELECT … INTO OUTFILE , скорее всего, не будет иметь для вас никакого значения.

В UNIX файл создается для чтения всем миром и принадлежит серверу MySQL. Это означает, что, хотя вы сможете прочитать файл, вы не сможете удалить его.

Экспорт таблиц в виде необработанных данных

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

Чтобы вывести таблицу в виде файла данных, вы должны указать опцию —tab, которая указывает каталог, в который вы хотите, чтобы сервер MySQL записал файл.

Например, чтобы выгрузить таблицу tutorials_tbl из базы данных TUTORIALS в файл в каталоге / tmp , используйте команду, как показано ниже. 

$ mysqldump -u root -p --no-create-info \
   --tab=/tmp tutorials tutorials_tbl
password ******

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

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

$ mysqldump -u root -p TUTORIALS tutorials_tbl > dump.txt
password ******

Это создаст файл с содержимым, как показано ниже. 

-- MySQL dump 8.23
--
-- Host: localhost    Database: TUTORIALS
---------------------------------------------------------
-- Server version       3.23.58

--
-- Table structure for table `tutorials_tbl`
--

CREATE TABLE tutorials_tbl (
   tutorial_id int(11) NOT NULL auto_increment,
   tutorial_title varchar(100) NOT NULL default '',
   tutorial_author varchar(40) NOT NULL default '',
   submission_date date default NULL,
   PRIMARY KEY  (tutorial_id),
   UNIQUE KEY AUTHOR_INDEX (tutorial_author)
) TYPE = MyISAM;

--
-- Dumping data for table `tutorials_tbl`
--

INSERT INTO tutorials_tbl 
   VALUES (1,'Learn PHP','John Poul','2007-05-24');
INSERT INTO tutorials_tbl 
   VALUES (2,'Learn MySQL','Abdul S','2007-05-24');
INSERT INTO tutorials_tbl 
   VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

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

$ mysqldump -u root -p TUTORIALS > database_dump.txt
password ******

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

$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

Опция —all-database доступна в версии MySQL 3.23.12. Этот метод может быть использован для реализации стратегии резервного копирования базы данных.

Копирование таблиц или баз данных на другой хост

Если вы хотите скопировать таблицы или базы данных с одного сервера MySQL на другой, используйте mysqldump с именем базы данных и именем таблицы.

Выполните следующую команду на исходном хосте. Это сбросит всю базу данных в файл dump.txt . 

$ mysqldump -u root -p database_name table_name > dump.txt
password *****

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

Теперь загрузите файл ftp dump.txt на другом хосте и используйте следующую команду. Перед выполнением этой команды убедитесь, что вы создали имя_базы_данных на конечном сервере. 

$ mysql -u root -p database_name < dump.txt
password *****

Другой способ сделать это без использования промежуточного файла — это отправить выходные данные mysqldump напрямую по сети на удаленный сервер MySQL. Если вы можете подключиться к обоим серверам с хоста, на котором находится исходная база данных, используйте следующую команду (убедитесь, что у вас есть доступ на обоих серверах). 

$ mysqldump -u root -p database_name \
   | mysql -h other-host.com database_name

В mysqldump половина команды подключается к локальному серверу и записывает вывод дампа в канал. Оставшаяся половина команды подключается к удаленному серверу MySQL на other-host.com. Он читает канал для ввода и отправляет каждое утверждение на сервер other-host.com.

154
Share: