mysqldump - делаем дамп (бэкап) базы данных

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

Пример использования (отсутствие пробела после -p очень важно в данном случае):

mysqldump -h $(DB_HOST) -u $(DB_USER) -p$(DB_PASS) $(DB_NAME) > $(FILE_PATH)

Как выгрузить и одновременно сжать бд:

mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz

Разархивировать gz-файл можно так:

gzip -d [/path_to_file/DBNAME].sql.gz

Рассмотрим более тонкие настройки mysqldump:

--help. Программа mysqldump имеет множество версий. Посмотреть, какие возможности поддерживаются конкретно Вашей версией, можно с помощью этого ключа.

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

Пример игнорирования таблицы:

mysqldump -u [USERNAME] -p [DBNAME] --ignore-table=[DBNAME].my_table

Пример игнорирования нескольких таблиц:

mysqldump -u [USERNAME] -p [DBNAME] \
--ignore-table=[DBNAME].my_table \
--ignore-table=[DBNAME].my_table_2

Как игнорировать таблицы по префиксу (используйте %):

mysqldump -u [USERNAME] -p [DBNAME] --ignore-table=[DBNAME].my_%

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

 Делайте двухэтапный процесс:

• бэкапим схему (используя ключ --no-data )
• бэкапим данные (используя ключ --no-create-info )

--databases, -B позволяет сделать так, что mysqldump включит в сценарий восстановления команды CREATE DATABASE /*!33333 IF NOT EXISTS*/ DBNAME и USE DBNAME. Это позволит создавать рабочие базы "с нуля". То есть, без использования --databases подразумевается, что пользователь восстанавливает одну базу данных и явно указывает, куда нужно помещать восстанавливаемые данные. Если же backup создается с целью сделать полностью рабочую копию данных, например, на другом MySQL-сервере, то нужно использовать этот ключ;

--all-databases, -A позволяет сделать копии всех баз данных, которые существуют на данном MySQL-сервере. Если же нужно сделать копии только некоторых баз, нужно просто указать их через пробел при вызове mysqldump из командной строки (см. выше);

--result-file=/path/to/file, -r /path/to/file - этот ключ можно использовать для перенаправления вывода в файл. Можно использовать обычное unix-перенаправление командой ">", а можно - вот этот ключ. Кому что нравится. Этот параметр особенно удобен в Windows, без использования командной строки. когда можно перенаправить результат в файл при помощи последовательностей > и >>.

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

--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 (с именами столбцов).

--delayed Использовать команду INSERT DELAYED при вставке строк (работает только с таблицами типа ISAM и MyISAM.)

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

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

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

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

--insert-ignore Добавляет ключевое слово IGNORE в оператор INSERT. Использование данного синтаксиса удобно в случае случайного дублирования ключа при вставке. Таким образом, если в таблицу добавляется уже существующая строка, то вставка не будет произведена, при этом не будет прекращено выполнение.

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

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

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

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

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

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

--protocol={TCP|SOCKET|PIPE|MEMORY} Параметр позволяет задать протокол подключения к серверу.

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

В MySQL есть два расширения, которые позволяют атомарно вставить или обновить запись. Годятся они для штук с довольно высокой нагрузкой, когда без блокировок не работает типичный сценарий:

1. Выбрать запись.
2. Если выбралась — обновить.
3. Если пусто — вставить.

REPLACE INTO
  vote
SET
  user_id = 13,
  object_id = 42,
  value = 7

Аналог:

INSERT INTO
  vote
SET
  user_id = 13,
  object_id = 42,
  value = 7
ON DUPLICATE KEY UPDATE
  value = 7

Сходства

И то и то выражение в итоге даст примерно тот же результат. При нарушении ограничения на уникальность (то есть UNIQUE KEY или PRIMARY KEY) не будет никакой ошибки. Для ключа user_id, object_id в базе будет единственная запись с значением 7.

Отличия

Кроме очевидного отличия в синтаксисе есть и очень важно отличие в том, как всё это работает. REPLACE при срабатывании ограничения удаляет запись (и вызывает ON DELETE CASCADE) и затем вставляет новую, что не так быстро, как ON DUPLICATE KEY UPDATE, который обновляет запись без удаления.

--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=..., -# Отслеживать прохождение программы (для отладки).

Опции по-умолчанию:

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

--quick (по-умолчанию) - указывает писать бэкап сразу на диск, это очень полезно когда на пользователя налагаются некоторые ограничения. Например, нельзя занять больше некоторого количества физической памяти (RAM, ОЗУ). mysqldump по умолчанию помещает все полученные от MySQL-сервера данные в память, а потом записывает все это на диск. Соответственно, если провайдер дает Вам занять, например, 30Мб памяти, а база, копию которой Вы делаете с помощью mysqldump, занимает 50Мб, конечно, тут возникнет ошибка - mysqldump не сможет отработать корректно и завершится аварийно, о чем Вам сообщит.

--add-drop-table (по-умолчанию) - Добавляет оператор DROP TABLE перед каждым оператором CREATE TABLE. Это позволит избежать некоторых ошибок при восстановлении базы из резервной копии. Конечно, нужно учитывать то, что таблицы, находящиеся в рабочей копии (если таблицы с таким же именем существуют в backup), перед восстановлением из резервной копии будут удалены из основной базы и пересозданы из backup;

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

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

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

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

--lock-tables, -l (по-умолчанию). Указание этого параметра приводит к блокировке таблиц базы данных, для которой создается дамп.

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

Несколько полезных примеров:

mysqldump -u root -pPwd -f --default-character-set=cp1251 DBNAME | gzip -c > filename.txt.gz

распаковывать такой архив можно командой:

gunzip filename.txt.gz

Чтобы знать, за какое число был сделан бэкап, можно написать такую команду:

mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME | gzip -c > `date "+%Y-%m-%d"`.gz

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

set DBCHARACTER = utf8

set DBNAME = breach

mysqldump -u root -pPwd -f --default-character-set=$DBCHARACTER $DBNAME | bzip2 -c > sql.$DBNAME.`date "+%Y-%m-%d"`.bz2

Если Вы хотите автоматизировать удаление старых архивов, попробуйте воспользоваться cron и командой find, которая обычно есть в unix. Запуская периодически

find ~/каталог-с-архивами -name "*.gz" -mtime +7 -exec rm -f {} \;

Тем самым Вы будете удалять архивы, которые "старше" семи дней.