mysqldump параметры командной строки
Как сделать дамп базы MySQL / MariaDB
В статье рассмотрим общие принципы выполнения резервного копирования СУБД MySQL или MariaDB. Также рассмотрим некоторые примеры часто используемых ключей и параметров резервирования.
Синтаксис и базовая команда
Создание дампа выполняется из командной строки Linux или Microsoft. Общий синтаксис:
Пример базовой команды для резервирования базы:
* в данном примере мы создадим резервную копию базы base и поместим его в папку /tmp, назвав сам файл dump.sql. Подключение к базе происходит от пользователя root. Это самый простой пример создания дампа MySQL.
Базовые параметры команды mysqldump:
* полный перечень параметров смотрите в официальном руководстве.
Примеры создания дампа MySQL
1. С последующим архивированием
Или с подробным выводом информации на экран (дольше по времени):
2. Для одновременно нескольких баз
3. Для всех баз одной командой
4. Резервирование только структуры базы
5. Создание копии определенной таблицы
Для этого после базы через пробел перечисляем названия таблиц
6. Резервирование прав доступа на СУБД
Позволяет выгрузить все учетные записи с паролями. Удобно для переноса СУБД на новый сервер без потери доступа к нему.
* после восстановления этого дампа, необходимо в sql shell выполнить команду flush privileges;
7. Проигнорировать определенную таблицу
Выполняется при помощи ключа ignore-table:
Скрипт для резервного копирования
Для повседневных операций по резервному копированию MySQL рекомендуется написать скрипт и запускать его через cron.
Резервное копирование в phpMyAdmin
В качестве графического инструмента для работы с MySQL/MariaDB используется phpMyAdmin. Разберем, как с его помощью сделать экспорт данных.
В верхней части меню кликаем по Экспорт:
В разделе «Способ экспорта» ставим переключатель в положение Обычный:
* обычный режим откроет дополнительные опции для резервного экспорта данных.
Выбираем компрессию, например, zip:
И в нижней части окна нажимаем OK.
Начнется загрузка файла с резервной копией на компьютер.
Для восстановления базы читайте инструкцию Как восстановить базу MySQL
mysqldump — бекап/дамп данных в Mysql
Mysqldump — это одна из утилит, входящих в пакет с клиентскими программами mysql-client. Используется для создания дампа одной или нескольких баз данных, отдельных таблиц или только их структуры с целью резервирования нужной информации и дальнейшего его восстановления в будущем. Дамп содержит в себе набор SQL-команд, которые выполняются последовательно при разворачивании.
В самом обычном и стандартном случае используется:
Вывести справочную информацию и выйти из программы.
Еще пару слов о бекапе в MySQL
mysqlhotcopy для MyISAM
Для быстрого резервирования БД с типом таблиц ISAM и MyISAM можно использовать «mysqlhotcopy», которая скопирует файлы *.frm, *.MYD и *.MYI:
Для InnoDB не подойдет данный способ, потому что при этом типе не обязательно все файлы будут храниться в директории базы данных.
xtrabackup для InnoDB
Для InnoDB есть xtrabackup, рекомендую посмотреть! UPD: XtraBackup — резервное копирование для innoDB
Бин-лог и репликации
Для репликации «mysqldump» не предназначена, для этого есть бин-лог (—log-bin):
Для полной репликации необходимо вести бин-лог с самого начала работы БД, то есть еще до создания структур и данных.
Читайте другие интересные статьи
Понравилась статья, расскажи о ней друзьям, нажми кнопку!
MySQLDUMP на примерах
Этот пост содержит наиболее часто используемые команды для создания или развертывания резервных копий баз данных MySQL.
Ну, просто поднадоело мне постоянно гуглить ключи, когда они мне «вдруг» понадобятся.
Кстати, есть еще такая утилита, как MySqlHotCopy, которую лучше всего использовать для создания горячих резервных копий, так как она ставит базу на блокировку и копирует файлы БД в нужное место. Но эта штука будет работать только если ее запускать на самом сервере, работает только с MyISAM и Archive-таблицами и больше подойдет для больших БД.
Восстановить данные можно путем копирования сохраненных файлов в каталог данных MySQL.
Но, в общем-то речь не о ней… может напишу о «горячей копии» в другой статье…
Создание резервной копии базы данных
Начнем с самой распространенной команды создания дампа сайта site.ru в файл site.ru:
- Подробнее о ключах:
Чтобы сделать резервную копию нескольких БД, можно воспользоваться ключом -B и указать несколько БД, вот пример:
Если ситуация не дает времени на раздумья, и нужно делать резервную копию всех баз данных, то в данной ситуации можно воспользоваться ключом —all-databases, вот пример:
Если вы делайте резервную копию базы данных на работающем, и причем, активно используемом сервере, то вы рискуете получить нарушение логических связей. Есть пара способов избежать этого.
Первый способ заключается в блокировке таблиц, т.е. можно воспользоваться параметром —lock-tables, вот пример:
Но в момент создания резервной копии запросы клиентов будут подвешены.. а следовательно могут быть тайм-ауты.
Восстановление резервной копии базы данных
Тут уже будет править утилита «mysql». Вот пример:
Еще один способ, более педантичный:
Ну а если у вас БД сохранена в gz-архив, то можно скомбинировать команды mysql и zcat вот так:
Еще варианты использования mysqldump
Например нам нужна база данных на dev зону, так сказать песочницу, а размер основной БД очень велик. Можно воспользоваться ключом —where=»true limit 150″, которому мы явно укажем выборку данных не более 150 записей. Вот пример:
Если нам нужна только структура без данных, то можно воспользоваться ключом —no-data, вот пример
Если нам нужна копия только одной таблицы, то вот пример команды:
Если нам нужна копия триггеров, процедур и событий (встроенного планировщика), то вот пример:
Еще можно сразу создать заархивированный дамп базы. Сделать это можно вот так:
И еще можно указать дату создания архива, вот так:
Ключи для использования mysqldump
Ниже будут приведены наиболее популярные ключи mysqldump:
Утилита mysqldump и шпаргалка по параметрам
Утилита mysqldump позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.
Так же mysqldump имеет возможность развертывания баз данных из созданного sql-файла.
Создание дампа
Разберем пример простейшее использования, задампим базу данных «database» при помощи перенаправления потока в файл «database.sql»:
Для того чтобы сделать дамп несколько баз данных, необходимо использовать параметр —databases (или сокращенно -B), пример:
А для того чтобы сделать дамп всех баз данных, необходимо использовать параметр —all-databases (или сокращенно -A), пример:
Развертывание дампа
Перенаправляем поток в обратную сторону и развертываем базу данных:
Или через mysql-console:
Ну, а если у нас gz-архив к примеру, то:
Пример использование некоторых параметров
Например, нам нужны данные с «продакшен версии базы» для «версии разработчика», то есть нам нужна «песочница». Выбираем не более 100 записей:
Или нам нужна только структура, без данных:
Делаем дамп только триггеров, процедур и событий:
Шпаргалка по параметрам
Приведу некоторые параметры, которые могут понадобится при работе с утилитой mysqldump.
—add-drop-database
Добавляет оператор DROP DATABASE перед каждым оператором CREATE DATABASE.
—add-drop-table
Добавляет оператор DROP TABLE перед каждым оператором CREATE TABLE.
—add-locks
Добавляет оператор LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы (для ускорения доступа к MySQL).
—allow-keywords
Разрешить создавать имена столбцов, которые совпадают с ключевыми словами. Отсутствие конфликтов обеспечивается прибавлением имени таблицы в качестве префикса к имени каждого столбца.
—compact
Данный параметр требует от mysqldump создать дамп, используя как можно более компактный формат. Параметр является противоположным —comments.
—compatible=name
Параметр генерирует вывод, который совместим с другими СУБД или более старыми версиями MySQL. Вместо ключевого слова name можно использовать: «ansi», «mysql323», «mysql40», «postgresql», «oracle», «mssql», «db2», «maxdb», «no_key_options», «no_table_options», «no_field_options». Можно использовать несколько значений, разделив их запятыми.
—create-options
Добавляет дополнительную информацию в операторы CREATE TABLE. Это может быть тип таблицы, начальное значение AUTO_INCREMENT и другие параметры.
—delayed
Использовать команду INSERT DELAYED при вставке строк.
—delete-master-logs
На главном сервере репликации автоматически удаляются бинарные логи (logbin) после того, как дамп был успешно создан при помощи mysqldump. Этот параметр автоматически включает параметр «—master-data».
—hex-blob
Параметр позволяет представить бинарные данные в полях типа BINARY, VARBINARY, BLOB и BIT в шестнадцатеричном формате. Так последовательность «abc» будет заменена на 0x616263.
—ignore-table=db_name.tbl_name
Позволяет игнорировать таблицу tbl_name базы данных db_name при создании дампа. Если из дампа необходимо исключить несколько таблиц, необходимо использовать несколько параметров «—ignore-table», указывая по одной таблице в каждом из параметров.
—insert-ignore
Добавляет ключевое слово IGNORE в оператор INSERT.
—no-autocommit
Включает все операторы INSERT, относящиеся к одной таблице, в одну транзакцию, что приводит к увеличению скорости загрузки данных.
—order-by-primary
Указание параметра приводит к тому. что каждая таблица сортируется по первичному ключу или первому уникальному индексу.
—protocol=
Параметр позволяет задать протокол подключения к серверу.
—replace
Добавляет ключевое слово REPLACE в оператор INSERT. Данный параметр впервые появился в MySQL 5.1.3.
—single-transaction
Параметр создает дамп в виде одной транзакции.
—skip-comments
Данный параметр позволяет подавить вывод в дамп дополнительной информации.
—tables
Перекрывает действия параметра —databases (-B). Все аргументы, следующие за этим параметром, трактуются как имена таблиц.
—triggers
Создается дамп триггеров. Этот параметр включен по умолчанию. для его отключения следует использовать параметр —skip-triggers.
—tz-utc
при использовании данного параметра в дамп будет добавлен оператор вида SET TIME_ZONE=’+00:00′, который позволит обмениваться дампа в различных временных зонах.
—help
Вывести справочную информацию и выйти из программы.
Еще пару слов о бекапе в MySQL
mysqlhotcopy для MyISAM
Для быстрого резервирования БД с типом таблиц ISAM и MyISAM можно использовать «mysqlhotcopy», которая скопирует файлы *.frm, *.MYD и *.MYI:
Для InnoDB не подойдет данный способ, потому что при этом типе не обязательно все файлы будут храниться в директории базы данных.
xtrabackup для InnoDB
Бин-лог и репликации
Для репликации «mysqldump» не предназначена, для этого есть бин-лог (—log-bin):
Для полной репликации необходимо вести бин-лог с самого начала работы БД, то есть еще до создания структур и данных.
Резервирование данныс в MySQL 6.x
С версии MySQL 6.x доступен online-backup, вот слайд объясняющий нововведения:
MySQLdump: справочник опций и параметров
Справочник опций и параметров
В приложении mysqldump можно использовать следующие опции и параметры:
—add-drop-database
Перед каждым оператором CREATE DATABASE, выполняет добавление оператора DROP DATABASE
—add-drop-table
Аналогично предыдущему. Добавляет оператор DROP TABLE перед каждым CREATE TABLE.
—add-locks
Вставляет операторы LOCK TABLE перед таблицей и UNLOCK TABLE после каждой таблицы (для ускорения доступа к MySQL).
Делает дамп всех баз данных и соответственно всех таблиц на запрашиваемом сервере.
—allow-keywords
Разрешает создавать имена столбцов в таблице, которые совпадают с ключевыми словами (служебными словами). Это реализуется с помощью добавления префикса с именем таблицы в имя столбца.
Данный параметр добавляет дополнительные комментарии в которых указывается: версия mysqldump, версия mysql, имя хоста и другие параметры сервера.
—compact
—compatible=name
Параметр генерирует вывод, который совместим с другими СУБД или более старыми версиями MySQL. Вместоключевогослова name можноиспользовать: «ansi», «mysql323», «mysql40», «postgresql», «oracle», «mssql», «db2», «maxdb», «no_key_options», «no_table_options», «no_field_options». Можно использовать несколько значений, разделив их запятыми.
Обеспечивает полную форму оператора insert, включая в него имена столбцов.
—create-options
Заполняет операторы CREATE TABLE дополнительной информацией, такой как, начальное значение AUTO_INCREMENT, тип таблицы и другие параметры.
Этот параметр позволяет выбрать одну или несколько баз данных для создания дампа.
—delayed
При выполнении вставки строк, использует команду INSERT DELAYED.
—delete-master-logs
После успешного выполнения дампа удаляет бинарные логи на главном сервере.
В каждой таблице оператор INSERT окружен выражениями /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; и /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; в выводе результата дампа. Это ускоряет загрузку таблиц типа MyISAM.
Команда INSERT задается в новом многострочном синтаксисе, что позволяет сделать дамп более компактным.
Перед выполнением дампа записывает данные из буфера в системный журнал на диске.
При ошибке выполнения, все равно продолжать дамп.
—hex-blob
Параметр позволяет представить бинарные данные в полях типа BINARY, VARBINARY, BLOB и BIT в шестнадцатеричном формате. Так последовательность «abc» будет заменена на 0×616263.
—ignore-table=db_name.tbl_name
—insert-ignore
Будет вставлено ключевое слово IGNORE в оператор INSERT.
Выполняет блокировку всех таблиц во всех базах данных на время создания дампа.
Блокирует таблицу базы данных, для которой создается дамп.
—no-autocommit
Выполняет ввод операторов INSERT в одну транзакцию для каждой таблицы, это способствует повышению скорости выполнения дампа.
Подавляет создание операторов INSERT. Что позволяет создавать только структуру базы данных.
—order-by-primary
Сортирует каждую таблицу по первичному ключу.
Номер TCP порта, для подключения к хосту.
Задает протокол подключения к серверу.
Начинает формировать дамп, не дожидаясь полной загрузки данных из сервера, это значительно экономит время выполнения дампа.
Помещает имена баз данных, таблиц и столбцов в обратные апострофы `. Начиная с MySQL 4.1, данный параметр включен по умолчанию.
—replace
Добавляет ключевое слово REPLACE в оператор INSERT. Данный параметр впервые появился в MySQL 5.1.3.
Параметр направляет дамп в файл file. Этот параметр особенно удобен в Windows, без использования командной строки. когда можно перенаправить результат в файл при помощи последовательностей > и >>.
Создает дамп хранимых процедур и функций.
—single-transaction
Дамп создается в виде одной транзакции.
—skip-comments
Не выводит в дампе дополнительную информацию в виде комментариев, это также увеличивает скорость выполнения дампа.
Указывается путь к файлу сокета для выполнения подключения к хосту.
—tables
Выполняет дамп только некоторых таблиц, имена которых, перечисляются после данного параметра.
—triggers
Создается дамп триггеров. Параметр включен по умолчанию.
—tz-utc
Позволяет обмениваться дампами в различных временных зонах, добавляя при этом специальные атрибуты в дамповый файл.
Выводит детальную информацию о работе программы.
Выводит версию программы.
Выполняет дамп только некоторых записей. Важно: кавычки обьязательны.
Создает дамп в виде XML.
Выполняет блокировку всех таблиц во всех базах данных.
Отслеживает прохождение программы при выполнении дампа. Используется для отладки.
—help
Как уже упоминалось в начале статьи, выводит всю справочную информацию по текущей версии mysqldump.