Создание дампа в mysql для нескольких таблиц. MySQLdump: скачать, работа с mysqldump, примеры. Примеры использования mysqldump
Делать дамп (бэкап) базы данных очень важно. Поэтому я сделал некоторые комментарии с примерами к такой отличной утилите, как mysqldump.
mysqldump - утилита позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер баз данных (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.
mysqldump -u root -p -f name_database >
Этой командой мы делаем бэкап базы данных под именем name_database на диск С в файл mydb_backup_name_database.txt
Файл можно не создавать, MySQL создаст его сам.
mysql -u root -p -f name_database < C:\mydb_backup_name_database.txt
этой командой мы импортируем данные бэкапа из файла C:\mydb_backup_name_database.txt
Примечание: -f, --force - опция, которая указывает продолжать даже при получении ошибки SQL, т.е. игнорировать ошибки. Например, если в таблице уже существует во всем идентичная строка.
Чтобы пароль не запрашивался, нужно писать его сразу после -p, то есть без пробелов. Если пароль Pwd , то пример выгладит так:
mysqldump -u root -pPwd -f name_database > C:\mydb_backup_name_database.txt
Если вы часто используете данную команду, то лучше сделать отдельного пользователя с необходимыми правами, дабы поменьше светить пароль root-а
Рассмотрим более тонкие настройки mysqldump:
--databases позволяет сделать так, что mysqldump включит в сценарий восстановления команды CREATE DATABASE /*!33333 IF NOT EXISTS*/ DBNAME и USE DBNAME. Это позволит создавать рабочие базы "с нуля". То есть, без использования --databases подразумевается, что пользователь восстанавливает одну базу данных и явно указывает, куда нужно помещать восстанавливаемые данные. Если же backup создается с целью сделать полностью рабочую копию данных, например, на другом MySQL-сервере, то нужно использовать этот ключ;
--all-databases позволяет сделать копии всех баз данных, которые существуют на данном MySQL-сервере. Если же нужно сделать копии только некоторых баз, нужно просто указать их через пробел при вызове mysqldump из командной строки (см. выше);
Ключ --help . Программа mysqldump имеет множество версий. Посмотреть, какие возможности поддерживаются конкретно Вашей версией, можно с помощью этого ключа;
--add-drop-table - ключ, который заставит mysqldump добавлять в итоговый сценарий команду drop table перед созданием таблиц. Это позволит избежать некоторых ошибок при восстановлении базы из резервной копии. Конечно, нужно учитывать то, что таблицы, находящиеся в рабочей копии (если таблицы с таким же именем существуют в backup), перед восстановлением из резервной копии будут удалены из основной базы и пересозданы из backup;
--no-data . С помощью этого ключа можно быстро сделать копию структуры таблицы/баз без самих данных. Например, Вы создали сложную таблицу и хотели бы сохранить на будущее ее структуру, а сами данные, которые находятся в этой таблице, Вам в резервной копии не нужны;
--result-file=... - этот ключ можно использовать для перенаправления вывода в файл. Можно использовать обычное unix-перенаправление командой ">", а можно - вот этот ключ. Кому что нравится;
Еще один очень полезный совет по использованию mysqldump в хостинговой среде. Как правило, при использовании хостинга на пользователя налагаются некоторые ограничения. Например, нельзя занять больше некоторого количества физической памяти (RAM, ОЗУ). mysqldump по умолчанию помещает все полученные от MySQL-сервера данные в память, а потом записывает все это на диск. Соответственно, если провайдер дает Вам занять, например, 30Мб памяти, а база, копию которой Вы делаете с помощью mysqldump, занимает 50Мб, конечно, тут возникнет ошибка - mysqldump не сможет отработать корректно и завершится аварийно, о чем Вам сообщит. Чтобы "заставить" mysqldump писать данные сразу на диск, а не хранить их, пусть даже и временно, в памяти, используйте ключ --quick . Это решит проблему.
Приведем еще пару полезных примеров:
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 {} \;
Тем самым Вы будете удалять архивы, которые "старше" семи дней.
Идея статьи, создать качественное руководство по установке и работе с программой mysqldump. Применяя, множество примеров и собрав все полезные опции и параметры по работе с этой утилитой. Все в одну статью сложно вместить, поэтому все будет кратко и по сути. Если кто-то, чего-то недопонимает, обращаемся в комментарии к статье.
Разделы:
Установка mysqldump:
Что такое mysqldump?
MySQLdump – это серверное приложение, которое позволяет делать резервное копирование (далее дамп) баз данных и сохранять их в отдельном файле. При этом можно осуществлять гибкие настройки дампа: несколько или все базы данных, архивация в gzip, добавление команд lock, drop и многое другое. Также возможнен обратный импорт резервных копий БД. Осуществлять можно с помощью PHP, но это неприемлемо для больших проектов, которые имеют большой вес данных.
Эта программа очень полезна при реализации экспорта и импорта данных с БД. Она может быть стандартно установленной на вашем хостинге (точнее mysql сервере). Но для того, чтобы отточить мастерство работы с mysqldump и научится устанавливать, можно поставить ее на denwer. Что мы сейчас и сделаем.
Скачать mysqldump
Вы всегда можете скачать программу mysqldump у нас на сайте, при этом скачивание приложения совершенно бесплатно. Скачайте mysqldump по прямой ссылке ниже.
Как установить mysqldump?
Устанавливать будем на локальный сервер Denwer. Установить приложение легко и просто, для этого следуйте ниже предоставленным инструкциям и скриншотам.
1.Копируем файл mysqldump.exe в папку с денвером:
D:\WebServers\usr\local\mysql5\bin\
При этом у вас может быть немного другое название папки mysql5, например mysql-5.1 или немного
видоизмененное. Поэтому, для большей ясности, ниже предоставлен скриншот:
2.Запускаем денвер
Наверняка вы и сами знаете как запустить Denwer.
3.Запускаем консоль:
Пуск->Выполнить->
cmd.
exe
или в ОС Windows
7: Пуск->Поиск->Вводим
cmd.
exe->
Enter
, как
показано на скриншоте:
4.Тестируем:
С помощью команд в консоле, переходим на виртуальных диск денвера (у меня W:\) и в папку с приложением mysqldump. Для подтверждения выполнения команды жмем Enter.
Вводим команды:
W: - заходим на виртуальный диск денвера
cd usr\ local\ mysql5\ bin – заходим в папку с приложением
mysqldump -uroot имя_вашей_бд>имя_файла.sql – тестируем, делаем дамп произвольной базы данных в файл, который сохранится в папку bin.
У меня установка выполнена успешно, надеюсь у вас тоже. В папке bin находим файл резервной копии БД. Чтобы научится более широко использовать программу, читайте следующие разделы статьи.
Начало работы: экспорт и импорт БД
Экспорт базы данных
Мы уже установили приложение и научились пользоваться консолем. Тестовый дамп мы уже делали. Теперь сделаем простой дамп базы данных в нужный нам каталог. Для этого я создал ранее используемую БД под именем “test”. Она находится на локальном сервере denwer. Ниже предоставлены пошаговые команды консоля для дампа бд test в нужную папку и нужный файл.
W: cdusr\local\mysql5\bin mysqldump -uroot test>D:\test\easydump.sql
На скриншоте ниже показан дамповый файл в папке тест:
Экспорт выполнен успешно. Теперь попробуем импортировать этот файл обратно на наш сервер.
Импорт базы данных
Для импорта БД, очищаем БД в phpmyadmin, и пользуемся следующей командой в cmd.exe:
Mysql -uroot test Важное замечание: если при экспорте мы использовали mysqldump…
, то при импорте нужно начинать команду с mysql
. В этом примере заключается базовое использование
приложения mysqldump, для
создания резервных копий (бэкапа) баз данных. Еще более команд и примеров, вы
найдете в разделе и статье . Ниже предоставлены наиболее используемые примеры
использования mysqldump.
С помощью которых можно не только делать бэкап, но и добавлять некоторые
параметры резервного копирования: сжатие с помощью gzip, добавление даты бэкапа, делать
дамп только нескольких таблиц или структуры БД, использовать гибкие настройки.
Эти параметры позволяют увеличить скорость выполнения дампа и экономно
использовать место дискового пространства. -u
или --user=...
- имя пользователя -h
или --host=...
- удаленный хост (для
локального хоста можно опустить этот параметр) -p
или --password
- запросить пароль database
- имя
экспортируемой базы данных /path/to/file/dump.sql
- путь и файл для
дампа Делаем дамп нескольких баз данных, для этого используем
атрибут --databases или
сокращенно –B, смотрите
на примере ниже: Mysqldump -uroot -h82.82.82.82 -p -B database1 database2 database3 > databases.sql
Если вы желаете создать дамп всех баз данных, необходимо использовать
параметр –all-databases или
–А в сокращенном виде, смотрим пример: Mysqldump -uroot -h82.82.82.82 -p -A > all-databases.sql
Для этого необходимо использовать параметр --no-data как
показано на примере ниже: Mysqldump --no-data - uUSER -pPASSWORD DATABASE > /path/to/file/schema.sql
Как уже упоминалось раньше, эти атрибуты уменьшают итоговый
размер файла и ускоряют процесс резервного копирования. А точнее: -Q
оборачивает
имена обратными кавычками -c
делает полную
вставку, включая имена колонок -e
делает
расширенную вставку. Данная утилита позволяет получить дамп (``моментальный снимок"")
содержимого базы данных или совокупности баз для создания резервной копии
или пересылки данных на другой SQL-сервер баз данных (не обязательно
MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или
заполнения таблиц. Если же резервная копия создается на сервере, то вместо описываемой
утилиты следует использовать mysqlhotcopy . См.раздел See section 4.8.6 mysqlhotcopy , Копирование баз данных и таблиц MySQL . Shell> mysqldump database
или mysqldump --databases DB1
или mysqldump --all-databases
Если не указывать имена таблиц или использовать параметры --databases или
--all-databases , то будет получен дамп базы данных в целом (соответственно
- всех баз данных). Перечень опций, поддерживаемых вашей конкретной версией утилиты mysqldump ,
можно получить, выполнив команду mysqldump --help . Следует иметь в виду, что утилита mysqldump , используемая без опций
--quick или --opt , перед тем, как сделать дамп результата выборки
информации, загрузит весь результат в память. Это может создать проблемы
при получении дампа большой базы данных. Учтите, что не следует применять параметры --opt или -e , если вы
собираетесь использовать для получения дампа новую копию программы
mysqldump , а затем воспроизводить его на очень старом MySQL-сервере. Утилита mysqldump поддерживает следующие опции: Add-locks
Добавить команды LOCK TABLES перед выполнением и UNLOCK TABLE после
выполнения каждого дампа таблицы (для ускорения доступа к MySQL).
--add-drop-table
Добавить команду DROP TABLE перед каждой командой CREATE TABLE .
-A, --all-databases
Произвести дамп всех баз данных. Аналогично опции --databases с указанием
всех баз данных.
-a, --all
Включить все опции создания объектов, специфичные для MySQL.
--allow-keywords
Разрешить создавать имена столбцов, которые совпадают с ключевыми словами.
Отсутствие конфликтов обеспечивается прибавлением имени таблицы в качестве
префикса к имени каждого столбца.
-c, --complete-insert
Использовать полные команды INSERT (с именами столбцов).
-C, --compress
Использовать сжатие всей информации между клиентом и сервером, если они
оба поддерживают сжатие.
-B, --databases
Выполнить дамп нескольких баз данных. Обратите внимание на разницу в
использовании: в этом случае таблицы не указываются. Все имена аргументов
рассматриваются как имена баз данных. Оператор USE db_name; включается в
вывод перед каждой новой базой данных.
--delayed
Использовать команду INSERT DELAYED при вставке строк.
-e, --extended-insert
Использовать команду INSERT с новым многострочным синтаксисом (повышает
компактность и быстродействие операторов ввода).
-#, --debug[=option_string]
Отслеживать прохождение программы (для отладки).
--help
Вывести справочную информацию и выйти из программы.
--fields-terminated-by=...
--fields-enclosed-by=...
--fields-optionally-enclosed-by=...
--fields-escaped-by=...
--lines-terminated-by=...
Эти опции используются совместно с параметром -T и имеют то же самое
значение, что и соответствующие операторы для LOAD DATA INFILE . См.
раздел See section 6.4.9 Синтаксис оператора LOAD DATA INFILE .
-F, --flush-logs
Записать на диск данные системного журнала из буфера MySQL-сервера перед
началом выполнения дампа.
-f, --force,
Продолжать даже при получении ошибки SQL при выполнении дампа таблицы.
-h, --host=..
Выполнить дамп данных MySQL сервера на указанном хосте. Значение хоста по
умолчанию - localhost .
-l, --lock-tables.
Заблокировать все таблицы перед началом выполнения дампа. Таблицы
блокируются оператором READ LOCAL , чтобы разрешить параллельные записи
для MyISAM -таблиц. Следует отметить, что при выполнении дампа совокупности
баз данных опция --lock-tables блокирует таблицы каждой базы по
отдельности. Таким образом, использование этого параметра не гарантирует,
что таблицы будут логически непротиворечивы в пределах этих баз данных. В
различных базах данных при выполнении дампа таблицы могут находиться в
совершенно разных состояниях.
-K, --disable-keys
Добавляет выражение
/*!40000 ALTER TABLE tb_name DISABLE KEYS */;
и /*!40000 ALTER TABLE tb_name ENABLE KEYS */;
в выводе результата. Это
ускорит загрузку данных на сервер MySQL 4.0, так как индексы создаются
после внесения всех данных.
-n, --no-create-db
В выводе результата выражение CREATE DATABASE /*!32312 IF NOT EXISTS*/
db_name; будет отсутствовать. Данная строка будет добавлена в любом случае
при использовании опций --databases или --all-databases .
-t, --no-create-info
Не записывать информацию о создании таблицы (команда CREATE TABLE).
-d, --no-data
Не записывать информацию из строк таблицы. Это очень полезно для получения
дампа структуры таблицы!
--opt
То же, что и --quick --add-drop-table --add-locks --extended-insert
--lock-tables . Должно дать наиболее быстрый дамп для чтения на
MySQL-сервере.
-pyour_pass, --password[=your_pass]
Используемый пароль при подключении к серверу. Если аргумент =your_pass не
введен, mysqldump предложит ввести пароль.
-P port_num, --port=port_num
Номер порта TCP/IP, используемого для подключения к хосту (применяется при
подсоединении к хостам, отличным от localhost , для которого используются
сокеты Unix).
-q, --quick
Выводить дамп непосредственно на стандартный вывод stdout без буферизации
запроса. Для этого используется функция mysql_use_result() .
-Q, --quote-names
Взять в кавычки имена таблиц и столбцов без символов ``" .
-r, --result-file=...
Прямой вывод указанного файла. Этот опцию следует использовать в MS DOS,
так как она предотвращает преобразование символа новой строки "\n" в
последовательность "\n\r" (новая строка + возврат каретки).
--single-transaction
Данная опция выдает SQL-команду BEGIN перед выполнением дампа данных с
сервера. Наиболее часто используется с InnoDB -таблицамии и уровнем
изоляции транзакций READ_COMMITTED , так как именно в этом режиме можно
получить дамп с непротиворечивым состоянием базы данных после выполнения
команды BEGIN без блокирования каких-либо приложений. Используя эту опцию,
необходимо помнить, что при выполнении дампа только транзакционные таблицы
будут находиться в непротиворечивом состоянии, т.е. некоторые MyISAM - или
HEAP -таблицы при использовании данной опции могут все же изменить свое
состояние. Опция --single-transaction добавлена в версии 4.0.2. Она
является взаимоисключающей по отношению к опции --lock-tables , так как
команда LOCK TABLES прерывает выполнение предыдущей транзакции.
-S /path/to/socket, --socket=/path/to/socket
Файл сокета для подсоединения к localhost (значение хоста по умолчанию).
--tables
Перекрывает параметр --databases (-B).
-T, --tab=path-to-some-directory
Для каждой заданной таблицы создает файл a `table_name.sql" , содержащий
SQL CREATE команды для создания таблицы, и файл `table_name.txt" с данными
таблицы. Файл `.txt" имеет формат в соответствии с параметрами --fields-xxx
и --lines--xxx . Примечание
: Этот параметр работает только при условии,
что утилита mysqldump запущена на том же компьютере, что и демон mysqld ,
причем пользователь/группа, запустившие данный поток mysqld (обычно это
пользователь mysql и группа mysql), должны иметь право
создавать/записывать файл по указанному адресу.
-u user_name, --user=user_name
Имя пользователя MySQL-сервера, используемое при подключении к серверу.
Значением по умолчанию является имя пользователя Unix.
-O var=option, --set-variable var=option
Установить значения переменных. Доступные для использования переменные
перечислены ниже.
-v, --verbose
Расширенный режим вывода. Вывод более детальной информации о работе
программы.
-V, --version
Вывести информацию о версии и выйти из программы.
-w, --where="where-condition"
Выполнить дамп только выбранных записей. Обратите внимание, что кавычки
обязательны.
"--where=user="jimf"" "-wuserid>1" "-wuserid
-X, --xml
Представляет дамп базы данных в виде XML.
-x, --first-slave
Блокирует все таблицы во всех базах данных.
-O net_buffer_length=#, where # Чаще всего утилита mysqldump используется для получения резервной копии
всех баз данных. See section 4.4.1 Резервное копирование баз данных .
mysqldump --opt database > backup-file.sql
Mysql database
Mysql -e "source /patch-to-backup/backup-file.sql" database
Данная утилита достаточно часто используется и для переноса информации из
базы данных на другой MySQL-сервер: Mysqldump --opt database | mysql --host=remote-host -C database
Вполне возможно получить дамп нескольких баз данных с помощью одной
команды: Mysqldump --databases database1 > my_databases.sql
Если необходим дамп всех баз данных, можно использовать: Mysqldump --all-databases > all_databases.sql
MySQLdump примеры
Создание дампа
mysqldump –uUSER -h82.82.82.82 -pPASSWORD DATABASE > /path/to/file/dump.sql
Создаем структуру
базы без данных
Создаем дамп только
одной или нескольких таблиц БД
mysqldump -uUSER -pPASSWORD DATABASE TABLE1 TABLE2 TABLE3 > dump.sql
Создаем дамп и
архивируем его в
gzip
mysqldump -u USER -pPASSWORD DATABASE | gzip > /path/to/outputfile.sql.gz
Создаем дамп с
указанием даты в имени файла
mysqldump -uUSER -pPASSWORD DATABASE | gzip > `date +dump.sql.%Y%m%d.%H%M%S.gz`
Используем
дополнительные атрибуты
mysqldump -Q -c -e -uUSER -pPASSWORD DATABASE > /path/to/file/dump.sql