MYSQLDUMP НА ПРИМЕРАХ

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

На выходе мы получаем .sql файл с дампом базы данных. В данном файле содержится Sql код в виде текста, т.е. его всегда можно открыть текстовым редактором, чтобы посмотреть, отредактировать и т.д…
Восстановить такой дамп можно с помощью утилиты mysql через STDIN.

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

Создание резервной копии базы данных

Начнем с самой распространенной команды создания дампа сайта site.ru в файл site.ru:

mysqldump -uroot -h10.30.30.10 -p site_ru > site_ru.sql
    Подробнее о ключах:

  • -u (—user=…) это имя пользователя БД;
  • -h (—host=…) это хост, на котором располагается сам сервер. Если сервер локальный, то данных параметр можно или не использовать или прописать туда localhost. Так же имейте в виду, что ip сервера после ключа нужно писать без пробела;
  • -p (—password=…) это пароль пользователя. Если этот ключ не использовать, то подключение к БД будет возможно в том случае, если пароля нет. Так же никто не запрещает вписать пароль в команду, причем после ключа не должно быть пробела (например: -p1234567890, где 1234567890 это пароль);
  • site_ru это имя БД на MySql сервере;
  • site_ru.sql это файл для дампа.

Чтобы сделать резервную копию нескольких БД, можно воспользоваться ключом -B и указать несколько БД, вот пример:

mysqldump -uroot -h10.30.30.10 -p site_ru site2_ru site3_ru > sites.sql

Если ситуация не дает времени на раздумья, и нужно делать резервную копию всех баз данных, то в данной ситуации можно воспользоваться ключом —all-databases, вот пример:

mysqldump -uroot -h10.30.30.10 -p -A > all-db.sql

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

Первый способ заключается в блокировке таблиц, т.е. можно воспользоваться параметром —lock-tables, вот пример:

mysqldump -uroot -h10.30.30.10 -p —lock-tables site_ru > site_ru.sql

Но в момент создания резервной копии запросы клиентов будут подвешены.. а следовательно могут быть тайм-ауты.

Второй способ это использование ключа —flush-log при создании резервной копии. Этот ключ закроет старый лог действий и создаст новый. Если кто-то что-то запишет в процессе создания копии — это отразится в начале журнала и можно будет перенести это изменение в базу. Далее, чтобы наверняка, после окончания создании резервной копии, нужно выполнять команду mysqladmin -flush-logs и оставлять копию предпоследнего бинарного журнала.

Восстановление резервной копии базы данных

Тут уже будет использоваться утилита «mysql».  Вот пример:

mysql -uroot -h10.30.30.10 -p site_ru < site_ru.sql

Еще один способ, более педантичный:

mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.6.35-1+deb.sury.org~xenial+0.1 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> use site_ru;
mysql> source site_ru.sql

Ну а если у вас БД сохранена в gz-архив, то можно скомбинировать команды mysql и zcat вот так:

zcat site_ru.sql.gz | mysql -uroot -h10.30.30.10 -p site_ru

Еще варианты использования mysqldump

Например нам нужна база данных на dev зону, так сказать песочницу, а размер основной БД очень велик. Можно воспользоваться ключом —where=»true limit 150″, которому мы явно укажем выборку данных не более 150 записей. Вот пример:

mysqldump -uroot -h10.30.30.10 -p --where="true limit 150" site_ru > site_ru.sql

Если нам нужна только структура без данных, то можно воспользоваться ключом —no-data, вот пример

mysqldump -uroot -h10.30.30.10 -p --no-data site_ru > site_ru.sql

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

mysqldump -u root -p testdb tablename > testdb_table_backup.sql

Если нам нужна копия триггеров, процедур и событий (встроенного планировщика), то вот пример:

mysqldump --no-create-info --no-data --triggers --routines --events -uroot -p site_ru | gzip > ~/database.sql.gz

Еще можно сразу создать заархивированный дамп базы. Сделать это можно вот так:

mysqldump -uroot -p site_ru | gzip > /path/to/site_ru.sql.gz

И еще можно указать дату создания архива, вот так:

mysqldump -uroot -p site_ru | gzip > `date +/path/to/site_ru.sql.%Y%m%d.%H%M%S.gz`

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

Источник