← Все записи

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

mysql-dump

Утилита 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

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

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

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

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

Резервирование данныс в MySQL 6.x

С версии MySQL 6.x доступен online-backup, вот слайд объясняющий нововведения:

online\_backup

Комментарии 45

Архив обсуждения. Новые комментарии закрыты.

adw0rd

mra, да, вроде вы правы. Я просто такой метод (с перенаправлением потока) не использую, я все использую “source”.

Дмитрий

adw0rd, еще раз спасибо :) полезные вещи публикуете.

Andrey

Чтобы пароль не запрашивался, нужно писать его сразу после -p, то есть без пробелов.

Tommy

Я мануал почитал, но что получается противоречий много или мой кривой английский.

скажем у нас есть база данных

site //база данный
     posts //таблица
     users //таблица
     comment //таблица

--lock-tables когда начнется дамп заблокируют всю базу “site” или будет по отдельности когда дампит таблицу “posts” блокирует ей -> потом разблокирует. и дальше тоже самое со всеми таблицами.
просто в писалось что может быть рассинхронизация, что мол в пост мы записали. а в узер не успели.

И да еще вопрос как бы посоветовал реализовать механизм. что когда начинается дамп на сайте пользователям выводилось сообщение: “Сайт заработает через несколько минут.”

adw0rd

При –lock-tables будет лочится только одна таблица и т.д. Обратите внимание на параметр:

--lock-all-tables

о которой тоже есть упоминание в статье

adw0rd

И да еще вопрос как бы посоветовал реализовать механизм. что когда начинается дамп на сайте пользователям выводилось сообщение: «Сайт заработает через несколько минут.»

Ну делать надо дамп в часы минимальной нагрузки, ставить высокий приоритет процессу mysqldump (см. nice) и самое простое:
* либо смотреть процессы (ps ax|grep mysqldump|grep -v grep)
* либо при таймауте подключения к БД ловить эксепшен и выводить страничку

Но вообще лучше юзать бинлоги или спец утилиты типа: mysqlhotcopy, xtrabackup и т.д.

Tommy

На тему –lock-all-tables

mysqldump -uroot -h82.82.82.82 --lock-all-tables -p database > database.sql

здесь будет заблокировано только database? просто написано при всех базах данных и блокировка всех.

mysqlhotcopy тоже же придется то блокировать таблицу.
Да я думаю уже в строну поднятия slave сервера. ибо там проблем не будет. просто не хотелось бы это крутить на одном сервера. а сервер для бэкапа слабый. боюсь что slave не будет успевать за master.

за идейки спасибо.

Курганский лис

Я настроил ежедневный бэкап базы данных на 4 часа утра местного времени (в этот момент, меньше всего число посетителей). Но все равно, каждую ночь в логе mysql появляются сообщения о медленных запросах (sloq queries). Проанализировав ситуацию понял, что медленные запросы генерируются при запросах поисковых ботов.
Попробовал в момент дампа выводить страницу о временной недоступности сайта. Но сейчас понял, что это может отрицательно повлиять на сайт, с точки зрения присутствия в поисковиках.
Какой вариант действий в такой ситуации?

adw0rd

Перевелите все таблицы в InnoDB и используйте –single-transaction, либо xtrabackup

single-transaction will force running a backup within a transaction. This allows backing up of transactional tables without imposing a lock howerver will NOT properly backup non-transacitonal tables. Auto-detect will choose single-transaction unless Holland finds non-transactional tables in the backup-set. None will completely disable locking. This is generally only viable on a MySQL slave and only after traffic has been diverted, or slave ervices suspended.

Курганский лис

В обозримом будущем собираюсь прикрутить Sphinx к базе. Но перевести все таблицы в в InnoDB не получится - я использую полнотекстовый поиск. Пока ситуация именно такая.
Надеялся что есть какой-то способ временного решиныя моей проблемы.

adw0rd

Используйте кеширование, например memcached, думаю вам это поможет

Александр

Если возможность при помощи mysqldump разбить данные одной таблицы на несколько дампов? Бывает нужно, когда файл большой.

SkyAls

Здравствуйте. Подскажите пожалуйста.
Я сделал бэкап командой
mysqldump -uroot -p –all-databases > dump.txt
Как мне теперь развернуть базы данных на новом сервере.
mysql -uroot -p database < dump.txt
Что указать в параметре “database”, когда восстанавливается вся структура?
Заранее спасибо.

Suvoroff

Переносил базу форума, кодировка utf, при развертывании весь русский текст исчезает, в чем проблема?
Я то в конце концов дамп развернул - создал средствами phpmyadmin, а развернул через ssh, но на будущее хотелось бы узнать. Кто-нибудь подскажет?

mysqldump: игнорирование данных в определённых таблицах | Блог ГО

[…] mysqldump: игнорирование данных в определённых таблицах 18 февраля 2012; Рубрика: mysql; Теги: bash, mysql, mysqldump; (картинку увёл с adw0rd.ru) […]

Резервное копирование баз MySQL Резервное копирование баз MySQL &laquo; Notes About Nothing

[…] И очень хорошая статья о mysqldump […]

Резервное копирование баз MySQL Резервное копирование баз MySQL &laquo; Notes About Nothing

[…] И очень хорошая статья о mysqldump Share this:TwitterFacebookLike this:НравитсяБудьте первым, кому понравился этот . By a1iante, on Февраль 21, 2012 at 11:19, under MySQL. Метки: Backup, MySQL. 1 комментарий Добавить комментарий или оставить обратную ссылку: URL обратной ссылки. « OTRS + LDAP OPM пакеты для OTRS 3 на CentOS. » […]

димон

спс - пол-дня искал решение для mysql-консоли в putty/ фигею как хреново описаы функции по putty-mysql в инете((

user

димон, а при чем тут putty?

Ваша фраза звучит как “пол дня искал решение по ремонту ламборджини на трассе москва-кузбасс” как хреново описаны функции по ламборджини-москва-кузбас в инете(("

Всё, что происходит внутри консоли Mysql (когда вы подключаетесь к серверу) - обработки внутри сервера и никак не зависят от того, через putty вы подключились, или другой клиент

ОМ

Практически при любых параметрах выдает похожее.

mysql> mysqldump -u root -h localhost -p -A -Q -v > all.sql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ‘mysql
dump -u root -h localhost -p -A -Q -v > all.sql’ at line 1

Как сделать дамп.

adw0rd

mysqldump - это утилита ОС, а не синтаксис MySQL. Выйдите из консоли MySQL, и вызовите mysqldump

Oleg

Спасибо за source от начинающего! Долго искал как залить дамп через консоль mysql.

cross

Доброго дня, подскажите пожалуйста, а можно ли сделать дамп бд таким способом -
mysqldump -uroot -proot database > all.sql;
а потом из этого дампа вытищить определенную таблицу?
Заранее спасибо.

alexkn

Хочу повторить вопрос
SkyAls 25 июля 2011 г. 9:59

Здравствуйте. Подскажите пожалуйста.
Я сделал бэкап командой
mysqldump -uroot -p –all-databases > dump.txt
Как мне теперь развернуть базы данных на новом сервере.
mysql -uroot -p database < dump.txt
Что указать в параметре “database”, когда восстанавливается вся структура?
Заранее спасибо.

Вы ответили:
Название БД

Так а если база не одна, названий несколько? Какое название ставить?

karpo518

Из статьи непонятно, как импортировать дамп из архива, перед этим удалив создаваемые (или все) таблицы

adw0rd

@karpo518, статье почти 10 лет, надо проверить насколько данные вообще актуальны. Но если прочитать статью, то следует, что вам надо использовать сначала вот эти флаги:

--add-drop-table
--add-drop-database

а разворачивать дамп так:

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

всё это написано в статье

karpo518

Я всё перепутал. Предположил, что флаги от mysqldump нельзя добавить в команду mysql, но это и не требуется. Флаги добавляются на этапе создания дампа, а не при разворачивании. Спасибо. Вопрос исчерпан

Лёхандр

Столкнулся с такой хухнёй - в Windows файлы с дампом создаются с разделителем строк CRLF, а в UNIX с LF. У меня файлы дампы находятся под контролем Git. Из-за разных разделителей строк Git думает, что файл изменен полностью, а не частично. Хто знает как в mysqldump настроить разделитель строк в создаваемых файлах?

Лёхандр

Короче, нашёл этот параметр - это –result-file, нужно имя файла задавать через него.
Было:
mysqldump -u “login” -p “pass” wordpress_db “wp_commentmeta” –skip-comments –host localhost > wp_commentmeta.sql
Исправил на:
mysqldump -u “login” -p “pass” wordpress_db “wp_commentmeta” –skip-comments –host localhost result-file=wp_commentmeta.sql

В мануале пишут про эту опцию:
This option should be used on Windows to prevent newline \n characters from being converted to \r\n carriage return/newline sequences.

adw0rd

@Даниил так установите с помощью пакетного менеджера, например в Ubuntu для этого надо установить mysql-client:

sudo apt update
sudo apt install mysql-client