Шпарагалка по Mysqldump

mysql
mysqldump
шпаргалка

Утилита mysqldump позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.

Так же mysqldump имеет возможность развертывания баз данных из созданного sql-файла.

Создание дампа

Разберем пример простейшее использования, задампим базу данных “database” при помощи перенаправления потока в файл “database.sql”:

1

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

Развертывание дампа

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

1

mysql -uroot -h 82.82.82.82 -p database < database.sql

Или через mysql console:

1
2

mysql> use database;
mysql> source database.sql

Пример использование некоторых параметров

Например, нам нужны данные с “продакшен версии базы” для “версии разработчика”, то есть нам нужна “песочница”. Выбираем не более 100 записей:

1

mysqldump - uroot -h 82.82.82.82 -p --where="true limit 100" database > database.sql

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

1

mysqldump -u root -h 82.82.82.82 -p --no-data database > database.sql

Делаем дамп в архив:

1

mysqldump -u root -p database | gzip > ~/database.sql.gz

Шпаргалка по параметрам

Приведу некоторые параметры, которые могут понадобится при работе с утилитой 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 Создается дамп событий. Смотрите MySQL Event Scheduler или встроенный диспетчер событий в MySQL.
  • —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 Вывести справочную информацию и выйти из программы.