Mysqldump несколько таблиц. Справочное руководство по MySQL
Делать дамп (бэкап) базы данных очень важно. Поэтому я сделал некоторые комментарии с примерами к такой отличной утилите, как 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 служит для резервирования и восстановления резервных копий (часто называются "дампами") баз данных и таблиц MySQL. Утилита предназначена для командной строки и работает под операционными системами Windows, FreeBSD и некоторыми другими. Mysqldump является свободнораспространяемым ПО, не требующим обязательной оплаты.
Для локального копирования баз данных MySQL с типом таблиц ISAM и MyISAM лучше использовать утилиту, написанную на Perl: mysqlhotcopy . Работает она намного быстрее, чем mysqldump, так как копирует файлы баз данных *.frm, *.myd и *.myi целиком, предварительно заблокировав таблицы. Также, для корректной работы mysqlhotcopy необходимо наличие Unix-утилит: cp или scp. Для таблиц типа InnoDB mysqlhotcopy не подойдет, так как не все файлы таблиц InnoDB хранятся в стандартной директории БД MySQL.
Примеры использования mysqldump
Основной синтаксис mysqldump таков:
mysqldump [
OPTIONS]
database [
tables]
mysqldump [
OPTIONS]
--
databases [
OPTIONS]
DB1 [
DB2 DB3...
]
mysqldump [
OPTIONS]
--
all-
databases [
OPTIONS]
Не указывая имена таблиц или используя параметры --databases или --all-databases, будет получен дамп всех баз данных находящихся в MySQL.
Записать дамп БД с удаленного сервера MySQL с адресом 127.0.0.2 на локальный компьютер в файл dump_db.sql:
mysqldump - uroot - h127.0.0.2 - p db_name > dump_db.sql
Восстановить базу MySQL db_name на удаленном сервере по адресу 127.0.0.2 с дампа, расположенного на локальном компьютере, в файле dump_db.sql:
mysql - uroot - h127.0.0.2 - p db_name < dump_db.sql
Записать дамп баз данных MySQL (database1, database2, database3), находящихся на удаленном сервере по адресу 127.0.0.2 в локальный файл dump_db.sql:
mysqldump - uroot - h127.0.0.2 - p - B database1 database2 database3 > dump_db.sql
Выборка не более 50 записей (напремер для тестирования не нужна полная копия БД):
Скопировать базы данных MySQL на удаленный компьютер:
mysqldump -- opt database | mysql -- host= remote- host - C database
Скопировать только структуру базы данных MySQL:
mysqldump - uroot - hh127.0.0.2 - p -- no- data my_dbname > my_db_structure.sql
Утилита mysqldump, используемая без опций --opt или --quick, перед тем, как сделать дамп результата выборки информации, запишет весь результат своей работы в память. Это может вызвать проблемы при получении дампа большой базы данных. Также следует учесть, что нет необходимости применять параметры --opt или -e, если предполагается использовать для получения дампа новую копию программы mysqldump, а затем воспроизводить его на очень старом MySQL-сервере.
Все параметры утилиты mysqldump можно просмотреть, запустив ее так:
mysqldump --help
Стандартный перечень параметров 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» будет заменена на 0×616263.
--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.
--tz-utc
при использовании данного параметра в дамп будет добавлен оператор вида SET TIME_ZONE="+00:00", который позволит обмениваться дампа в различных временных зонах.
--verbose, -v
Расширенный режим вывода. Вывод более детальной информации о работе программы.
--version, -V
Вывести информацию о версии программы.
--where="where-condition", -w "where-condition"
Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны: "--where=user="test"" "-wuserid>1" "-wuserid
--xml, -X
Представляет дамп базы данных в виде XML.
--first-slave, -x
Блокирует все таблицы во всех базах данных.
--debug=..., -#
Отслеживать прохождение программы (для отладки).
--help
Выводится справка и завершается работа программы.
Mysqldump - документация и примеры.
Идея статьи, создать качественное руководство по установке и работе с программой 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
делает
расширенную вставку.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