7 июня 2009 г. MyISAM MySQL InnoDB Бэкап

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

Утилита 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, вот слайд объясняющий нововведения:

При попытке восстановления из скрипта содержащего только одну таблицу запрос не работает:
mysqldump -uroot -h82.82.82.82 -p database < table.sql
Видимо нужно использовать
mysql -uroot -h82.82.82.82 -p database < table.sql
PS. см http://blog.tsheets.com/2008/tips-tricks/mysql-restoring-a-single-table-from-a-huge-mysqldump-file.html

adw0rd 20 ноября 2009 г. 16:44

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

adw0rd 20 ноября 2009 г. 16:50

mra, спасибо, поправил с "mysqldump" на "mysql"

Дмитрий 21 марта 2010 г. 23:26

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

Andrew 15 июня 2010 г. 16:44

Реально помогло спасибо огромное

Andrey 26 октября 2010 г. 15:27

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

Tommy 28 декабря 2010 г. 13:53

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

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

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

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

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

adw0rd 28 декабря 2010 г. 14:06

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

--lock-all-tables

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

adw0rd 28 декабря 2010 г. 14:13

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

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

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

Tommy 28 декабря 2010 г. 14:24

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

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

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

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

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

Курганский лис 31 марта 2011 г. 13:20

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

adw0rd 31 марта 2011 г. 20:06

Перевелите все таблицы в 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.

Курганский лис 1 апреля 2011 г. 7:11

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

adw0rd 1 апреля 2011 г. 12:44

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

Александр 13 июля 2011 г. 16:40

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

SkyAls 25 июля 2011 г. 9:59

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

adw0rd 1 августа 2011 г. 18:41

Название БД

Suvoroff 18 сентября 2011 г. 22:18

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

vasa_c 18 февраля 2012 г. 15:02

Логотип сам рисовал?

adw0rd 18 февраля 2012 г. 15:43

ага

димон 27 февраля 2013 г. 9:55

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

user 26 мая 2013 г. 4:58

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

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

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

ОМ 1 августа 2013 г. 23:24

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

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 2 августа 2013 г. 19:03

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

ОМ 6 августа 2013 г. 21:06

adw0rd спасибо попробую

Oleg 20 января 2015 г. 11:26

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

cross 22 января 2015 г. 11:49

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

Никита 16 сентября 2015 г. 7:24

Лайк.

Александр 20 апреля 2016 г. 17:09

На заметку: при использовании опции flush-logs в некоторых версиях mysqldump, не создается дамп. Баг репорт https://mariadb.atlassian.net/browse/MDEV-6220

alexkn 27 ноября 2016 г. 12:29

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

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

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

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

adw0rd 29 ноября 2016 г. 10:22

@alexkn ничего не указывайте и все

mysql -uroot -p < dump.txt

karpo518 30 ноября 2018 г. 7:27

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

adw0rd 30 ноября 2018 г. 9:39

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

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

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

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

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

karpo518 30 ноября 2018 г. 10:01

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

Лёхандр 28 декабря 2019 г. 8:36

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

Лёхандр 28 декабря 2019 г. 10:04

Короче, нашёл этот параметр - это --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.

Жан 12 июня 2020 г. 9:00

очень полезная статья! то, что нужно! спасибо!

Владимир 26 сентября 2021 г. 16:14

Спасибо. Весьма подробно и по делу.

София 27 апреля 2022 г. 7:05

Как получить mysqldump

Даниил 5 мая 2022 г. 2:47

Нужен mysqldump

adw0rd 5 мая 2022 г. 22:11

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

sudo apt update
sudo apt install mysql-client

Оставьте свой комментарий