Как сделать дамп базы MySQL / MariaDB

В статье рассмотрим общие принципы выполнения резервного копирования СУБД MySQL или MariaDB. Также рассмотрим некоторые примеры часто используемых ключей и параметров резервирования.

Синтаксис и базовая команда

Создание дампа выполняется из командной строки Linux или Microsoft. Общий синтаксис:

mysqldump [опции] -p >

Пример базовой команды для резервирования базы:

mysqldump -v -uroot -p base > /tmp/dump.sql

* в данном примере мы создадим резервную копию базы base и поместим его в папку /tmp, назвав сам файл dump.sql. Подключение к базе происходит от пользователя root. Это самый простой пример создания дампа MySQL.

Базовые параметры команды mysqldump:

Параметр	Описание
-u	Учетная запись, от которой выполняется резервное копирование. Необходимо, чтобы у пользователя были соответствующие права.
-p	Пароль учетной записи. Его можно ввести в команде, например -p12345 (для скрипта) или оставить -p (безопаснее).

* полный перечень параметров смотрите в официальном руководстве.

Примеры создания дампа MySQL

1. С последующим архивированием

mysqldump -v -uroot -p base | gzip > /tmp/dump.sql.gz

2. Для одновременно нескольких баз

Просто перечисляем имена баз через пробел и добавляем параметр -B

mysqldump -v -uroot -p -B base1 base2 base3 > /tmp/dump_multiply_bases.sql

3. Для всех баз одной командой

Для этого ставим —all-databases, вместо имен баз

mysqldump -v -uroot -p —all-databases > /tmp/dump_all_bases.sql

4. Резервирование только структуры базы

Для уточнения, это создание копии только самих таблиц без данных. Делается добавлением параметра —no-data

mysqldump -v -uroot -p —no-data base1 > /tmp/dump_base1_nodata.sql

5. Создание копии определенной таблицы

Для этого после базы через пробел перечисляем названия таблиц

mysqldump -v -uroot -p base1 table1 table2 > /tmp/dump_base1_tables.sql

6. Резервирование прав доступа на СУБД

Позволяет выгрузить все учетные записи с паролями. Удобно для переноса СУБД на новый сервер без потери доступа к нему.

mysqldump -v -uroot -p mysql user > /tmp/mysql_user.sql

* после восстановления этого дампа, необходимо в sql shell выполнить команду flush privileges;

7. Проигнорировать определенную таблицу

Выполняется при помощи ключа ignore-table:

mysqldump -v -uroot -p base —ignore-table=base.logs > /tmp/base.sql

Скрипт для резервного копирования

Для повседневных операций по резервному копированию MySQL рекомендуется написать скрипт и запускать его через cron.

Резервное копирование в phpMyAdmin

В качестве графического инструмента для работы с MySQL/MariaDB используется phpMyAdmin. Разберем, как с его помощью сделать экспорт данных.

В верхней части меню кликаем по Экспорт:

В разделе «Способ экспорта» ставим переключатель в положение Обычный:

* обычный режим откроет дополнительные опции для резервного экспорта данных.

Выбираем компрессию, например, zip:

И в нижней части окна нажимаем OK.

Начнется загрузка файла с резервной копией на компьютер.

Для восстановления базы читайте инструкцию Как восстановить базу MySQL

Дамп MySQL базы данных

Несколько полезных приемов, которые позволят вам значительно ускорить работу с резервным копированием и восстановлением баз данных. Конечно дамп MySQL можно делать и с помощью стороннего ПО, того же phpMyAdmin, но встроенными утилитами самого сервера БД это удобнее и быстрее.

Делаем резервную копию (он же дамп, он же бекап)

Все данные одной базы

USER – логин пользователя, PASSWORD – пароль. Обратите внимание, что между -p и PASSWORD нет пробела.

Все данные нескольких определенных баз

Добавляем параметр —databases или -B

USER – логин пользователя, PASSWORD – пароль. Обратите внимание, что между -p и PASSWORD нет пробела.

Данные всех баз

Указываем ключ —all-databases или -A .

USER – логин пользователя, PASSWORD – пароль. Обратите внимание, что между -p и PASSWORD нет пробела.

Дамп только структуры базы данных MySQL

За это это отвечает параметр —no-data или -d .

Дамп определенных таблиц

Сразу архивируем дамп MySQL

Добавляем дату и время к дампу

Восстанавливаем дамп MySQL

Заливаем все данные

Заливаем данные в конкретную базу

Заливаем данные из заархивированного дампа

Распространенные ошибки, возникающие при создании дампа MySQL

Ошибка связана с тем, что не хватает времени для выполнения операции. Для исправления в конфигурационном файле /etc/my.cnf в секции [mysqldump] увеличьте значение параметров wait_timeout=600 и interactive_timeout=600 . Значение времени подберите под ваши условия.

Ошибка связана с тем, что объем вносимых данных превышает разрешенный. Решается 2-я способами:

1. добавить параметр —max_allowed_packet=128M к нашим командам
2. Добавить параметр max_allowed_packet=128M в секцию [mysqldump] конфигурационного файла /etc/my.cnf

Соответственно, размер данных указываем тот, который нам необходим.

Данная ошибка может быть связана, как с недостаточным значение параметра wait_timeout , так и с маленьким размером max_allowed_packet . Решения собственно уже приведены.

Читайте также

Не смотря на то, что Microsoft делает все возможное, что бы Skype стал как можно…

Оценить размер папки в Linux (занимаемого места) можно с помощью стандартной консольной команды du. Давайте…

90% книг написанных про Linux начинают знакомство с командной строкой с cat. По статистике (на…

powered by leo blog .

Этот пост содержит наиболее часто используемые команды для создания или развертывания резервных копий баз данных MySQL.

Ну, просто поднадоело мне постоянно гуглить ключи, когда они мне «вдруг» понадобятся.

MySQLDUMP это инструмент, который позволяет создавать резервные копии баз данных MySQL. На выхлопе мы получаем .sql файл с дампом базы данных. В данном файле содержится Sql код в виде текста, т.е. его всегда можно открыть текстовым редактором, чтобы посмотреть, отредактировать и т.д.
Восстановить такой дамп можно с помощью утилиты mysql через STDIN.

Кстати, есть еще такая утилита, как MySqlHotCopy, которую лучше всего использовать для создания горячих резервных копий, так как она ставит базу на блокировку и копирует файлы БД в нужное место. Но эта штука будет работать только если ее запускать на самом сервере, работает только с MyISAM и Archive-таблицами и больше подойдет для больших БД.
Восстановить данные можно путем копирования сохраненных файлов в каталог данных MySQL.

Но, в общем-то речь не о ней. может напишу о «горячей копии» в другой статье.

Создание резервной копии базы данных

Начнем с самой распространенной команды создания дампа сайта site.ru в файл site.ru:

Подробнее о ключах:

-u (—user=. ) это имя пользователя БД;

-h (—host=. ) это хост, на котором располагается сам сервер. Если сервер локальный, то данных параметр можно или не использовать или прописать туда localhost. Так же имейте в виду, что ip сервера после ключа нужно писать без пробела;

-p (—password=. ) это пароль пользователя. Если этот ключ не использовать, то подключение к БД будет возможно в том случае, если пароля нет. Так же никто не запрещает вписать пароль в команду, причем после ключа не должно быть пробела (например: -p1234567890, где 1234567890 это пароль);

site_ru это имя БД на MySql сервере;

site_ru.sql это файл для дампа.

Чтобы сделать резервную копию нескольких БД, можно воспользоваться ключом -B и указать несколько БД, вот пример:

Если ситуация не дает времени на раздумья, и нужно делать резервную копию всех баз данных, то в данной ситуации можно воспользоваться ключом —all-databases, вот пример:

Кстати, есть нюанс.

Если вы делайте резервную копию базы данных на работающем, и причем, активно используемом сервере, то вы рискуете получить нарушение логических связей. Есть пара способов избежать этого.

Первый способ заключается в блокировке таблиц, т.е. можно воспользоваться параметром —lock-tables, вот пример:

Но в момент создания резервной копии запросы клиентов будут подвешены.. а следовательно могут быть тайм-ауты.

Второй способ это использование ключа —flush-log при создании резервной копии. Этот ключ закроет старый лог действий и создаст новый. Если кто-то что-то запишет в процессе создания копии — это отразится в начале журнала и можно будет перенести это изменение в базу. Далее, чтобы наверняка, после окончания создании резервной копии, нужно выполнять команду mysqladmin -flush-logs и оставлять копию предпоследнего бинарного журнала.

Восстановление резервной копии базы данных

Тут уже будет править утилита «mysql». Вот пример:

Еще один способ, более педантичный:

Ну а если у вас БД сохранена в gz-архив, то можно скомбинировать команды mysql и zcat вот так:

Еще варианты использования mysqldump

Например нам нужна база данных на dev зону, так сказать песочницу, а размер основной БД очень велик. Можно воспользоваться ключом —where=»true limit 150″, которому мы явно укажем выборку данных не более 150 записей. Вот пример:

Если нам нужна только структура без данных, то можно воспользоваться ключом —no-data, вот пример

Если нам нужна копия только одной таблицы, то вот пример команды:

Если нам нужна копия триггеров, процедур и событий (встроенного планировщика), то вот пример:

Еще можно сразу создать заархивированный дамп базы. Сделать это можно вот так:

И еще можно указать дату создания архива, вот так:

Ключи для использования mysqldump

Ниже будут приведены наиболее популярные ключи mysqldump:

MySQLdump: скачать, работа с mysqldump, примеры

Разделы:

Установка mysqldump:

Работа с mysqdump:

Что такое mysqldump?

MySQLdump – это серверное приложение, которое позволяет делать резервное копирование (далее дамп) баз данных и сохранять их в отдельном файле. При этом можно осуществлять гибкие настройки дампа: несколько или все базы данных, архивация в gzip, добавление команд lock, drop и многое другое. Также возможнен обратный импорт резервных копий БД. Осуществлять бэкап базы данных можно с помощью PHP, но это неприемлемо для больших проектов, которые имеют большой вес данных.

Эта программа очень полезна при реализации экспорта и импорта данных с БД. Она может быть стандартно установленной на вашем хостинге (точнее mysql сервере). Но для того, чтобы отточить мастерство работы с mysqldump и научится устанавливать, можно поставить ее на denwer. Что мы сейчас и сделаем.

Скачать mysqldump

Вы всегда можете скачать программу mysqldump у нас на сайте, при этом скачивание приложения совершенно бесплатно. Скачайте mysqldump по прямой ссылке ниже.

Как установить mysqldump?

Устанавливать будем на локальный сервер Denwer. Установить приложение легко и просто, для этого следуйте ниже предоставленным инструкциям и скриншотам.

1.Копируем файл mysqldump.exe в папку с денвером:

D:\WebServers\usr\local\mysql5\bin\
При этом у вас может быть немного другое название папки mysql5, например mysql-5.1 или немного видоизмененное. Поэтому, для большей ясности, ниже предоставлен скриншот:

2.Запускаем денвер

Наверняка вы и сами знаете как запустить Denwer.

3.Запускаем консоль:

Пуск->Выполнить->cmd.exe или в ОС Windows 7: Пуск->Поиск->Вводим cmd.exe->Enter, как показано на скриншоте:

4.Тестируем:

С помощью команд в консоле, переходим на виртуальных диск денвера (у меня W:\) и в папку с приложением mysqldump. Для подтверждения выполнения команды жмем Enter.

W: — заходим на виртуальный диск денвера

cdusr\local\mysql5\bin– заходим в папку с приложением

mysqldump -uroot имя_вашей_бд>имя_файла.sql – тестируем, делаем дамп произвольной базы данных в файл, который сохранится в папку bin.

У меня установка выполнена успешно, надеюсь у вас тоже. В папке bin находим файл резервной копии БД. Чтобы научится более широко использовать программу, читайте следующие разделы статьи.

Начало работы: экспорт и импорт БД

Экспорт базы данных

Мы уже установили приложение и научились пользоваться консолем. Тестовый дамп мы уже делали. Теперь сделаем простой дамп базы данных в нужный нам каталог. Для этого я создал ранее используемую БД под именем “test”. Она находится на локальном сервере denwer. Ниже предоставлены пошаговые команды консоля для дампа бд test в нужную папку и нужный файл.

На скриншоте ниже показан дамповый файл в папке тест:

Экспорт выполнен успешно. Теперь попробуем импортировать этот файл обратно на наш сервер.

Импорт базы данных

Для импорта БД, очищаем БД в phpmyadmin, и пользуемся следующей командой в cmd.exe:

Важное замечание: если при экспорте мы использовали mysqldump…, то при импорте нужно начинать команду с mysql. В этом примере заключается базовое использование приложения mysqldump, для создания резервных копий (бэкапа) баз данных. Еще более команд и примеров, вы найдете в разделе mysqldump примеры и статье справочник опций и параметров.

MySQLdump примеры

Ниже предоставлены наиболее используемые примеры использования mysqldump. С помощью которых можно не только делать бэкап, но и добавлять некоторые параметры резервного копирования: сжатие с помощью gzip, добавление даты бэкапа, делать дамп только нескольких таблиц или структуры БД, использовать гибкие настройки. Эти параметры позволяют увеличить скорость выполнения дампа и экономно использовать место дискового пространства.

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

-u или —user=. — имя пользователя

-h или —host=. — удаленный хост (для локального хоста можно опустить этот параметр)

-p или —password — запросить пароль

database — имя экспортируемой базы данных

/path/to/file/dump.sql— путь и файл для дампа

Делаем дамп нескольких баз данных, для этого используем атрибут —databases или сокращенно –B, смотрите на примере ниже:

Если вы желаете создать дамп всех баз данных, необходимо использовать параметр –all-databases или –А в сокращенном виде, смотрим пример:

Создаем структуру базы без данных

Для этого необходимо использовать параметр —no-data как показано на примере ниже:

Создаем дамп только одной или нескольких таблиц БД

Создаем дамп и архивируем его в gzip

Создаем дамп с указанием даты в имени файла

Используем дополнительные атрибуты

Как уже упоминалось раньше, эти атрибуты уменьшают итоговый размер файла и ускоряют процесс резервного копирования. А точнее:

-Q оборачивает имена обратными кавычками

-c делает полную вставку, включая имена колонок

-e делает расширенную вставку.