mysqldump
Post date: Jan 23, 2009 2:37:11 PM
mysqldump
mysql -u user -p -h localhost -o database dump.sql
Утилита mysqldump позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.
Так же mysqldump имеет возможность развертывания баз данных из созданного sql-файла.
Создание дампа
Разберем пример простейшее использования, задампим базу данных «database» при помощи перенаправления потока в файл «database.sql»:
mysqldump -uroot -h82.82.82.82 -p database > database.sql
где:
-u или -–user=... — имя пользователя
-h или --host=... — удаленный хост (для локального хоста можно опустить этот параметр)
-p или --password — запросить пароль
database — имя базы данных
database.sql — файл для дампа
Для того чтобы сделать дамп несколько баз данных, необходимо использовать параметр --databases (или сокращенно -B), пример:
mysqldump -uroot -h82.82.82.82 -p -B database1 database2 database3 > databases.sql
А для того чтобы сделать дамп всех баз данных, необходимо использовать параметр --all-databases (или сокращенно -A), пример:
mysqldump -uroot -h82.82.82.82 -p -A > all-databases.sql
Развертывание дампа
Перенаправляем поток в обратную сторону и развертываем базу данных:
mysqldump -uroot -h82.82.82.82 -p database < database.sql
Или через mysql-console:
mysql> use database;
mysql> source database.sql
Пример использование некоторых параметров
Например, нам нужны данные с «продакшен версии базы» для «версии разработчика», то есть нам нужна «песочница». Выбираем не более 100 записей:
mysqldump -uroot -h82.82.82.82 -p --where="true limit 100" database > database.sql
Или нам нужна только структура, без данных:
mysqldump -uroot -h82.82.82.82 -p --no-data database > database.sql
Примеры навеяны постом Александра Макарова — http://rmcreative.ru/blog/post/ljogkiy-damp-mysql
Шпаргалка по параметрам
Приведу некоторые параметры, которые могут понадобится при работе с утилитой 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/to/file, -T /path/to/file
При использовании этого параметра в каталоге 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'
Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны.
--xml, -X
Представляет дамп базы данных в виде XML.
--first-slave, -x
Блокирует все таблицы во всех базах данных.
--debug=..., -#
Отслеживать прохождение программы (для отладки).
--help
Вывести справочную информацию и выйти из программы.
Еще пару слов о бекапе в MySQL
mysqlhotcopy для MyISAM
Для быстрого резервирования БД с типом таблиц ISAM и MyISAM можно использовать «mysqlhotcopy», которая скопирует файлы *.frm, *.MYD и *.MYI:
# mysqlhotcopy db_name /path/to/dir
Для InnoDB не подойдет данный способ, потомучто при этом типе не обязательно все файлы будут храниться в директории базы данных.
xtrabackup для InnoDB
Для InnoDB есть xtrabackup, рекомендую посмотреть!
Бин-лог и репликации
Для репликации «mysqldump» не предназначена, для этого есть бин-лог (--log-bin):
# mysqlbinlog binlog.[0-9]* | mysql
Для полной репликации необходимо вести бин-лог с самого начала работы БД, то есть еще до создания структур и данных.
Резервирование данныс в MySQL 6.x
С версии MySQL 6.x доступен online-backup, вот слайд объясняющий нововведения:
Базы данных, Инструменты 07.06.2009