mysqldump – импорт/экспорт БД в MySQL

Создание дампа через mysqldump

Самый простой и эффективный способ создать дамп БД – утилита mysqldump

mysqldump -uroot -h82.82.82.82 -p database > database.sql

где:

• -u или -–user=… – имя пользователя
• -h или –host=… – удаленный хост (для локального хоста можно опустить этот параметр)
• -p или –password – запросить пароль
• database – имя базы данных
• database.sql – файл для дампа

Импорт базы данных MySQL

из дампа через терминале linux

Для импорта базы данных в MySQL или MariaDB зайдите на сервер по SSH.

Загрузите файл дампа базы данных в нужный вам каталог удобным для вас способом. Например: /var/www/html/

Для загрузки дампа в базу данных database выполним следующую команду:

mysql database_name < /var/www/html/dump.sql

где databaseimport — имя БД;

/var/www/html/dump.sql — путь к файлу с БД.

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

mysql -uroot -pPassword database_name < /var/www/html/dump.sql

аналог:

mysqldump -uroot -pPassword database_name < /var/www/html/dump.sql

где
root — имя пользователя для доступа к БД,
Password — пароль для доступа к БД,
database_name — имя базы данных.

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

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

$ mysql -u root –p
CREATE DATABASE database_name DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;
mysql> use database_name
mysql> show databases;
mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'db_user'@'localhost';
mysql> SELECT db, host, user FROM mysql.db WHERE db='database_name';
mysql> source db_name.sql

«Лёгкий» дамп MySQL

Иногда, требуется быстро получить структуру и 100—200 записей в каждой таблице из очень большой базы.

Для MySQL это делается так:

mysqldump mydatabase --where="true limit 200"  | gzip > mydatabase.sql.gz

Дамп только структуры БД

Или нам нужна только структура, без данных:

mysqldump -uroot -h82.82.82.82 -p –no-data database > database.sql

Импо

Параметры mysqldump

Некоторые параметры, которые могут понадобится при работе с утилитой mysqldump.

 

–add-drop-database

Добавляет оператор DROP DATABASE перед каждым оператором CREATE DATABASE.

–add-drop-table

Добавляет оператор DROP TABLE перед каждым оператором CREATE TABLE.

–add-locks

Добавляет оператор LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы (для ускорения доступа к MySQL).

–all-databases, -A

Сохраняет все таблицы из всех баз данных, которые находятся под управлением текущего сервера.

–allow-keywords

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

–comments, -i

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

–compact

Данный параметр требует от mysqldump создать дамп, используя как можно более компактный формат. Параметр является противоположным –comments.

–compatible=name

Параметр генерирует вывод, который совместим с другими СУБД или более старыми версиями MySQL. Вместо ключевого слова name можно использовать: “ansi”, “mysql323”, “mysql40”, “postgresql”, “oracle”, “mssql”, “db2”, “maxdb”, “no_key_options”, “no_table_options”, “no_field_options”. Можно использовать несколько значений, разделив их запятыми.

–complete-insert, -c

Используется полная форма оператора INSERT (с именами столбцов).

–create-options

Добавляет дополнительную информацию в операторы CREATE TABLE. Это может быть тип таблицы, начальное значение AUTO_INCREMENT и другие параметры.

–databases, -B

Параметр позволяет указать имена нескольких баз данных, для которых необходимо создать дамп.

–delayed

Использовать команду INSERT DELAYED при вставке строк.

–delete-master-logs

На главном сервере репликации автоматически удаляются бинарные логи (logbin) после того, как дамп был успешно создан при помощи mysqldump. Этот параметр автоматически включает параметр “–master-data”.

–disable-keys, -K

Для каждой таблицы, окружает оператор INSERT выражениями /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; и /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; в выводе результата дампа. Это ускорит загрузку данных на сервер для таблиц типа MyISAM, так как индексы создаются после внесения всех данных.

–extended-insert, -e

Использовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода).

–flush-logs, -F

Записать на диск данные системного журнала из буфера MySQL-сервера перед началом выполнения дампа.

–force, -f

Продолжать даже если в процессе создания дампа произошла ошибка.

–hex-blob

Параметр позволяет представить бинарные данные в полях типа BINARY, VARBINARY, BLOB и BIT в шестнадцатеричном формате. Так последовательность “abc” будет заменена на 0x616263.

–ignore-table=db_name.tbl_name

Позволяет игнорировать таблицу tbl_name базы данных db_name при создании дампа. Если из дампа необходимо исключить несколько таблиц, необходимо использовать несколько параметров “–ignore-table”, указывая по одной таблице в каждом из параметров.

–insert-ignore

Добавляет ключевое слово IGNORE в оператор INSERT.

–lock-all-tables, -x

Указание этого параметра приводит к блокировке всех таблиц во всех базах данных на время создания полного дампа всех баз данных.

–lock-tables, -l

Указание этого параметра приводит к блокировке таблиц базы данных, для которой создается дамп.

–no-autocommit

Включает все операторы INSERT, относящиеся к одной таблице, в одну транзакцию, что приводит к увеличению скорости загрузки данных.

–no-create-db, -n

Подавляет создание в дампе операторов CREATE DATABASE, которые автоматически добавляются при использовании параметров –databases и –all-databases.

–no-data, -d

Подавляет создание операторов INSERT в дампе, что может быть полезно при создании дампа структуры базы данных без самих данных.

–opt

Параметр предназначен для оптимизации скорости резервирования данных и является сокращением, включающим следующие опции: –quick –add-drop-table –add-locks –create-options –disable-keys –extended-insert –lock-tables –set-charset. Начиная с MySQL 4.1, параметр –opt используется по умолчанию, т.е. все вышеперечисленные параметры включаются по умолчанию, даже если они не указываются. Для того чтобы исключить такое поведение, необходимо воспользоваться параметров –skip-opt

–order-by-primary

Указание параметра приводит к тому. что каждая таблица сортируется по первичному ключу или первому уникальному индексу.

–port, -P

Номер TCP порта, используемого для подключения к хосту.

–protocol={TCP|SOCKET|PIPE|MEMORY}

Параметр позволяет задать протокол подключения к серверу.

–quick, -q

Позволяет начать формирование дампа, не дожидаясь полной загрузки данных с сервера и экономя тем самым память.

–quote-names, -Q

Помещает имена баз данных, таблиц и столбцов в обратные апострофы `. Начиная с MySQL 4.1, данный параметр включен по умолчанию.

–replace

Добавляет ключевое слово REPLACE в оператор INSERT. Данный параметр впервые появился в MySQL 5.1.3.

–result-file=/path/to/file, -r /path/to/file

Параметр направляет дамп в файл file. Этот параметр особенно удобен в Windows, без использования командной строки. когда можно перенаправить результат в файл при помощи последовательностей > и >>.

–routines, -R

Данный параметр создает дамп хранимых процедур и функций. Доступен с MySQL 5.1.2.

–single-transaction

Параметр создает дамп в виде одной транзакции.

–skip-comments

Данный параметр позволяет подавить вывод в дамп дополнительной информации.

–socket=/path/to/socket, -S /path/to/socket

Файл сокета для подсоединения к localhost.

–tab=/path/, -T /path/

При использовании этого параметра в каталоге path для каждой таблицы создаются два отдельных файла: tbl_name.sql, содержащий оператор CREATE TABLE, и tbl_name.txt, который содержит данные таблиц, разделенные символом табуляции. Формат данных может быть переопределен явно с помощью параметров –fields-xxx и –lines-xxx.

–tables

Перекрывает действия параметра –databases (-B). Все аргументы, следующие за этим параметром, трактуются как имена таблиц.

–triggers

Создается дамп триггеров. Этот параметр включен по умолчанию. для его отключения следует использовать параметр –skip-triggers.

–events, -E

Создается дамп событий.

–tz-utc

при использовании данного параметра в дамп будет добавлен оператор вида SET TIME_ZONE=’+00:00′, который позволит обмениваться дампа в различных временных зонах.

–verbose, -v

Расширенный режим вывода. Вывод более детальной информации о работе программы.

–version, -V

Вывести информацию о версии программы.

–where=’where-condition’, -w ‘where-condition’

Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны.

–xml, -X

Представляет дамп базы данных в виде XML.

–first-slave, -x

Блокирует все таблицы во всех базах данных.

–debug=…, -#

Отслеживать прохождение программы (для отладки).

–help

Вывести справочную информацию и выйти из программы.