MySQLDUMP на примерах

by Опубликовано

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

Ну, просто поднадоело мне постоянно гуглить ключи, когда они мне «вдруг» понадобятся.

Немного теории..

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

Источники

Опубликовано p.b.Leo

WTF ?