Вы здесь

Утилита mysqldump и шпаргалка по параметрам

Создание дампа

Разберем пример простейшее использования, задампим базу данных "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

Развертывание дампа

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

mysql -uroot -h82.82.82.82 -p database < database.sql

Или через mysql-console:

mysql> use database;
mysql> source database.sql

Ну, а если у нас gz-архив к примеру, то:

zcat database.sql.gz | mysql -uroot -h82.82.82.82 -p database

Пример использование некоторых параметров

Например, нам нужны данные с "продакшен версии базы" для "версии разработчика", то есть нам нужна "песочница". Выбираем не более 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 --no-create-info --no-data --triggers --routines --events -uroot -p database | gzip > ~/database.sql.gz

Шпаргалка по параметрам

Приведу некоторые параметры, которые могут понадобится при работе с утилитой 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" будет заменена на 0x616263.

    --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.

    --events, -E
        Создается дамп событий. Смотрите MySQL Event Scheduler или встроенный диспетчер событий в MySQL.

    --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, рекомендую посмотреть! UPD: XtraBackup - резервное копирование для innoDB
Бин-лог и репликации

Для репликации "mysqldump" не предназначена, для этого есть бин-лог (--log-bin):

# mysqlbinlog binlog.[0-9]* | mysql

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

 

Материал взят отсюда