Главная > Блог > Web-разработка > PHP и MySQL >

Создание резервной копии БД

10.04.2013

Теги: MySQL • Web-разработка • БазаДанных

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

Создание резервной копии БД

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

mysqldump -u[user] -p[password] --databases [dbname] > [file]

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

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

mysqldump -uroot -pqwerty -B mydb > mydb.sql

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

mysqldump -uroot -pqwerty --no-data -B mydb > mydb-no-data.sql

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

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

mysqldump -uroot -pqwerty -B drupal --tables access users > drupal-access-users.sql

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

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

mysqldump -uroot -pqwerty --no-data -B drupal --tables access users > drupal-access-users-no-data.sql

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

mysqldump -uroot -pqwerty --no-data -B drupal joomla > drupal-joomla.sql

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

mysqldump -uroot -pqwerty -A > all-databases.sql

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

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

mysql -uroot -pqwerty < all-databases.sql

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

mysql -uroot -pqwerty mydb < mydb.sql

В приведенном примере мы восстанавливаем из резервной копии базу данных mydb. Обратите внимание — при восстановлении таблиц нужно указывать, в какую именно базу данных вы ее хотите поместить:

mysql -uroot -pqwerty mydb < mytable.sql

Здесь мы восстанавливаем из резервной копии отдельную таблицу mytable, помещая ее в базу mydb.

Параметры программы 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-all-tables, -x: Блокировка всех таблиц, во всех базах, на время создания резервной копии. Данная опция автоматически отключает --single-transaction и --lock-tables.
--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.

Поиск: MySQL • Web-разработка • База данных