Резервное копирование mysql, программа Mysqldump

В одной и предыдущих статей, обсуждались штатные средства операционной системы FreeBSD для резервного копирования и восстановления данных файловой системы, программ: Dump и Restore. В данном материале разговор пойдет о программе Mysqldump, использующейся для резервного копирования баз данных MySQL, а так-же о восстановлении из резервных копий с помощью консольного клиента, программы mysql.

Утилита командной строки Mysqldump, предназначена для создания резервных копий, одной, нескольких выбранных, или всех имеющихся баз данных, на случай аварийной ситуации или для переноса баз на другой сервер. Резервная копия представляет из себя текстовый файл, содержащий SQL инструкции для создания необходимых таблиц и заполнения их информацией ( можно делать дампы, содержащие только структуру таблиц ).

Создание резервных копий баз данных с помощью Mysqldump

В общем случае, команда для создания резервной копии базы данных, с помощью программы Mysqldump, выглядит следующим образом:

vds-admin/#  mysqldump -u [ имя пользователя ] -p [ пароль ] --databases [ название базы данных ] > [ файл резервной копии ]

-u [ имя пользователя ] - имя пользователя с правами, достаточными для создания резервной копии.
-p [ пароль ] - пароль пользователя, указывается без пробела перед ним
--databases [ название базы данных ] - Короткий вариант -B, название одной или нескольких баз данных для дампа. При создании резервной копии одной базы данных, опцию --databases, писать не обязательно, здесь я использовал ее для большей наглядности и однозначности. То есть название базы данных можно прописать сразу за паролем пользователя.
> - перенаправление вывода команды mysqldump, в файл дампа.

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

vds-admin/# mysqldump -u dbadmin  -pdbadminpass -B workdb > /backup/workdb.sql

Обратите внимание на отсутствие пробела после опции -p, сразу указываем пароль. В данном случае будет создан файл резервной копии, содержащий структуру и данные, /backup/workdb.sql. Если вам не нужны данные из этой базы, а достаточно просто сохранить структуру таблиц, можно воспользоваться следующим вариантом:

vds-admin/# mysqldump -u dbadmin  -pdbadminpass --no-data -B workdb > /backup/structure_workdb.sql

Ключ --no-data ( сокращенный вариант -d ), указывает mysqldump, не скидывать данные в дамп.

Так-же есть возможность создавать дампы, только необходимых таблиц, определенной базы данных:

vds-admin/# mysqldump -u dbadmin -pdbadminpass -B drupal --tables access users > /backup/drupal2tables.sql

Вышеприведенная команда, создает резервную копию таблиц access и users, указанных вслед за опцией --tables, из базы данных drupal и сохраняет в файл /backup/drupal2tables.sql.

Для создания дампа только структуры таблиц, без данных, используется та-же опция --no-data.

vds-admin/# mysqldump -u dbadmin -pdbadminpass --no-data -B drupal --tables access users > ./drupal2tables_nodata.sql

Для создания резервной копии нескольких баз данных, достаточно перечислить их названия после опции --databases ( -B ):

vds-admin/# mysqldump -u dbadmin -pdbadminpass -B drupal cacti > /backup/drupal_cacti.sql

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

vds-admin/# mysqldump -u dbadmin -pdbadminpass -A > /backup/all_databases.sql

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

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

Восстановить базу данных или таблицу из сохраненного ранее дампа, еще проще, чем этот дамп создать. Для этого достаточно воспользоваться стандартной программой-клиентом mysql, перенаправив в нее файл резервной копии. Делается это так:

vds-admin/# mysql -u dbadmin -pdbadminpass < /backup/all_databases.sql

После выполнения команды, будут восстановлены все базы данных, из резервной копии, которую мы создали несколькими строками выше. Таким-же способом происходит и восстановление отдельных баз данных или таблиц. Обратите внимание, при восстановлении таблиц, нужно указать в какую именно базу данных вы ее хотите поместить.

vds-admin/# mysql -u dbadmin -pdbadminpass test < test_table.sql

В приведенном примере, мы восстанавливаем из резервной копии, таблицу test_table, помещая ее в базу test.

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

vds-admin/# mysqldump -u dbadmin -pdbadminpass --no-data -B test --tables test_table > /struct_test_table.sql

Скидываем в дамп структуру таблицы, test_table из базы данных test.

vds-admin/# mysql -u dbadmin -pdbadminpass other_testdb < struct_test_table.sql

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

Параметры программы Mysqldump

--help, -?: Вывод справки по опциям и используемым переменным.
--add-drop-database: Добавляет команду, DROP DATABASE перед каждой командой CREATE DATABASE
--add-drop-table: Добавляет команду, DROP TABLE перед каждой командой CREATE TABLE
--add-locks: Обрамляет дамп командами LOCK TABLES и UNLOCK TABLES, для ускорения операций вставки.
--all-databases, -A: Создает полную резервную копию всех баз данных, текущего сервера.
--allow-keywords: Разрешить имена столбцов, совпадающие с зарезервированными ключевыми словами. К имени такого столбца, добавляется префикс таблицы.
--character-sets-dir= путь: Директории с установленными наборами символов кодировок
--comments, -i: Добавить в файл дампа, дополнительную информацию ( например, версию программы, версию MySQL, имя хоста ), отменяется опцией --skip-comments
--compact: Создает более компактный дамп. Использование данного параметра, автоматически включает опции: --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys и --skip-set-charset.
--compatible= имя: Данная опция, пытается повысить совместимость создаваемого дампа, с базой данных другого типа или с более старой версией MySQL. Возможные значения: ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options или no_field_options. Можно использовать несколько значений, разделенных запятыми.
--complete-insert, -c: Использовать полную форму оператора INSERT, включая имена столбцов.
--compress, -C: Использовать компрессию, при пересылке данных между клиентом и сервером, при условии, что они оба поддерживают компрессию.
--create-options: Включать все MySQL опции при использовании оператора CREATE TABLE.
--databases, -B: Делать дамп нескольких баз данных, перечисленных после данной опции. Без этого параметра, mysqldump, воспринимает в качестве имени базы данных, первое значение, остальные интерпретируются как имена таблиц.
--default-character-set= кодировка: Донная опция устанавливает кодировку по-умолчанию. Если не определена, используется utf8, в ранних версиях latin1.
--delayed-insert: Вместо оператора INSERT, использовать INSERT DELAYED.
--delete-master-logs: Удалять бинарный лог на основном сервере репликаций после создания дампа. При использовании этой опции, автоматически включается опция --master-data.
--disable-keys, -K: Оператор INSERT для каждой таблицы, обрамляется выражением /*!40000 ALTER TABLE tbl_name DISABLE KEYS */ и /*!40000 ALTER TABLE tbl_name ENABLE KEYS */. Данная опция ускоряет загрузку при восстановлении из дампа, для таблиц типа MyISAM, за счет того, что индексы создаются после вставки всех данных.
--dump-date: Если включена опция --comments, добавлять дату создания дампа.
--extended-insert, -e: Использовать многострочный синтаксис оператора INSERT. Уменьшает размер дампа и ускоряет последующую вставку данных.
--flush-logs, -F: Переоткрыть лог файлы, перед созданием резервной копии. Старый файл будет сохранен с суффиксом -old. При использовании с опцией --all-databases ( сокращенный вариант -A), будут переоткрыты лог-файлы каждой базы данных, для которой делается дамп.
--flush-privileges: Выполнять команду FLUSH PRIVILEGES после создания дампа базы данных.
--force, -f: Продолжать создание резервной копии даже в случае возникновения ошибки.
--host= имя_хоста, -h имя_хоста: Указывает хост MySQL сервера. По-умолчанию резервная копия делается для хоста localhost
--hex-blob: Представлять бинарные данные полей BINARY, VARBINARY, BLOB и BIT в шестнадцатиричном формате ( hex ).
--ignore-table= имя_базы.имя_таблицы: Не скидывать в дамп таблицу "имя_таблицы" из базы "имя_базы". Опцию нужно использовать повторно, для каждой игнорируемой таблицы.
--insert-ignore: Дописывать в оператор INSERT, опцию IGNORE.
--lock-tables, -l: Блокировка таблиц базы данных, на время создания резервной копии. При дампе всех баз данных с этой опцией, таблицы каждой базы блокируются отдельно. Для транзакционных таблиц, типа InnoDB и BDB, предпочтительней использовать опцию --single-transaction.
--log-error= имя_файла: Писать ошибки и предупреждения в файл "имя_файла".
--no-autocommit: Включает операторы INSERT для каждой таблицы, в операторы SET AUTOCOMMIT и COMMIT, для увеличения скорости выполнения большого количества запросов INSERT
--no-create-db, -n: Данная опция подавляет вывод в дамп оператора CREATE DATABASE, при использовании опций --databases и --all-databases.
--no-create-info, -t: Не писать оператор CREATE TABLE, для пересоздания каждой таблицы из резервной копии.
--no-data, -d: Не скидывать в дамп содержимое таблиц. Оставляет только операторы CREATE TABLE для создания структуры.
--opt: Групповая опция. Синоним включения опций, --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, --set-charset. Ускоряет общий процесс создания резервной копии, включена по-умолчанию. Отключается опцией --skip-opt.
--order-by-primary: Сортировать ряды таблиц по первичному ключу или по первому уникальному индексу, если индекс существует. Полезна в случае создания дампа таблиц MyISAM с последующей вставкой в таблицы типа InnoDB.
--password[=password], -p[password]: Пароль пользователя, для подключения к серверу. Не забывайте, что имя должно идти сразу за опцией, без разделяющего пробела. Если указаны только сама опция, без пароля, пароль будет запрошен из командной строки.
--port= номкр_порта, -P номкр_порта: Номер порта, для подключения к серверу по протоколу TCP/IP.
--protocol={TCP|SOCKET|PIPE|MEMORY}: Использовать для подключения к серверу MySQL, указанный протокол.
--quick, -q: Данная опция вынуждает Mysqldump, восстанавливать строки, по одной за раз, вместо того что-бы скидывать весь объем строк в буфер памяти и выписывать их оттуда. Очень полезна при создании резервных копий больших таблиц.
--quote-names, -Q: Обрамлять имена баз данных, таблиц и колонок, ковычками. Включена по-умолчанию.
--replace: Использовать оператор REPLACE вместо INSERT. Начиная с версии MySQL 5.1.3.
--result-file= имя_файла, -r имя_файла: Вывод результатов в указанный файл. Имейте в виду, если файл с таким именем уже существует, он будет перезаписан и в случае возникновения ошибки, данные могут быть потеряны.
--routines, -R: Записывать в дамп хранимые процедуры и функции. Для использования данной опции, необходимы права SELECT на таблицу proc, системной базы данных mysql. Дамп будет содержать операторы CREATE PROCEDURE и CREATE FUNCTION. При использовании этой опции, эти операторы не будут содержать атрибутов времени создания и модификации хранимых процедур и функций и после восстановления дампа они будут равны времени восстановления. Если вам необходимо сохранить исходные атрибуты времени, вместо использования данной опции, сделайте отдельный дамп таблицы mysql.proc, под именем пользователя, который имеет на это достаточные права. Опция появилась с версии MySQL 5.1.2.
--set-charset: Добавляет в дамп оператор SET NAMES со значением кодировки по-умолчанию. По-умолчанию, данная опция включена, что-бы подавить, используйте --skip-set-charset.
--single-transaction: Выполняет оператор BEGIN SQL, перед началом создания резервной копии. Опция используется только для транзакционных таблиц, типа InnoDB. Только этот тип таблиц может быть сохранен в дамп в актуальном состоянии, после выполнения BEGIN SQL, и без блокирования приложения. Например таблицы типа MyISAM или MEMORY, могут измениться в процессе создания резервной копии с использованием данной опции, в итоге, информация в дампе будет противоречивой, неактуальной. Опции --single-transaction и --lock-tables, являются взаимоисключающими.
--socket= путь_к_файлу_сокета, -S путь_к_файлу_сокета: Использовать файл unix-сокета, для подключения к localhost.
--tables: Имена идущие за этой опцией, считаются именами таблиц.
--triggers: Включать в резервную копию триггеры, для каждой таблицы. Отменить действие опции можно с помощью --skip-triggers.
--user= имя_пользователя, -u имя_пользователя: Имя пользователя для подключения к MySQL серверу.
--verbose, -v: Вывод служебной информации о ходе выполнения программы.
--where='where_условие', -w 'where_условие': Скидывать в дамп информацию, выбранную по условию WHERE.
--xml, -X: Создать дамп в формате XML

Рекомендую так-же полистать man mysqldump и man mysql, лишним никогда не будет.

Читать еще:

Dump и Restore, резервное копирование данных

Скрипт automysqlbackup для автоматизации резервного копирования