Как создать дамп базы данных MySQL

MySQL — это наиболее популярная система управления реляционными базами данных выполняющая множество разнородных манипуляций с таблицами: добавление, удаление, поиск, сортировка и вывод по запросу пользователя.

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

• MySQL — это программа, которая оперирует информацией.

• База данных — это информация, записанная на жестком диске.

Исходя из такой архитектуры, MySQL поддерживает функцию выгрузки информации — создание дампа (dump) базы данных.

Такая возможность позволяет выполнять несколько полезных действий:

• Резервное копирование базы данных. Непредвиденные ситуации при использовании облачного (или локального) сервера могут приводить не только к сбоям в работе программ, но и к потере данных. По этой причине важно регулярно создавать дампы базы данных, которые будут храниться на других защищенных носителях.

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

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

В этом руководстве мы рассмотрим основные способы как создания дампа базы данных, так и ее импорта обратно в MySQL.

❯ Подготовка тестовой базы

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

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

Самый простой способ это сделать — создать облачную базу данных в Timeweb Cloud.

Для этого необходимо авторизоваться в панели управления Timeweb Cloud, после чего перейти на страницу «Базы данных».

Далее необходимо нажать либо на кнопку «Создать» (если созданных баз данных еще нет), либо на кнопку «Добавить» (если созданные базы данных уже есть).

После этого откроется страница конфигурирования облачной БД.

Основной параметр здесь — «Тип базы данных». Нужно выбрать «MySQL» с версией 8. Остальные параметры можно оставить как есть, либо настроить по своему усмотрению.

Далее можно нажать на кнопку «Заказать».

После этого откроется страница управления облачной базой данных. Некоторое время сервер MySQL будет запускаться, поэтому потребуется подождать до тех пор, пока статус БД не изменится на «Включена».

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

Для выполнения такого подключения в системе должен быть установлен клиент MySQL.

Во вкладке «Базы данных» находится список созданных БД. По умолчанию имеется только default_db.

Чтобы создать новую базу данных, нужно нажать на кнопку «Добавить», после чего на открывшейся странице написать название БД и кликнуть по кнопке «Создать».

После этого необходимо выполнить подключение к созданной базе данных:

mysql -u ПОЛЬЗОВАТЕЛЬ -p'ПАРОЛЬ' -h 37.220.80.65 -P 3306 -D БАЗА

Например, реальная команда подключения может выглядеть так:

mysql -u gen_user -p'sU}NEyx#<2P~\e' -h 37.220.80.65 -P 3306 -D test_db

Далее необходимо создать простую таблицу, состоящую из трех столбцов:

CREATE TABLE People (     id INT,     name VARCHAR(255) NOT NULL,     bord Date );

И заполнить ее несколькими значениями:

INSERT INTO People VALUES (120, 'Иван', NOW()), (121, 'Александр', NOW()), (122, 'Алексей', NOW());

Таким образом можно заполнить новую БД, чтобы впоследствии создать ее дамп.

Кстати, во вкладке «Пользователи» на странице управления БД расположены кнопки, открывающие интерфейс графических утилит администрирования баз данных MySQL — phpMyAdmin и Adminer.

❯ Способ 1: Терминал консоли

Более классический, но менее интерактивный способ создания дампа базы данных MySQL — использование соответствующей команды в консольном терминале.

Для этого понадобиться подключиться к MySQL по SSH-соединению, после чего ввести команду создания дампа:

mysqldump -u ПОЛЬЗОВАТЕЛЬ -p'ПАРОЛЬ' -h АДРЕС -P ПОРТ БАЗА > ФАЙЛ

Давайте разберем каждый из указанных параметров немного подробнее:

• ПОЛЬЗОВАТЕЛЬ. Имя пользователя, под которым будет выполнять авторизация в MySQL.

• ПАРОЛЬ. Пароль пользователя, под которым будет выполнять авторизация в MySQL.

• АДРЕС. IP-адрес удаленного сервера MySQL.

• ПОРТ. Порт удаленного сервера MySQL.

• БАЗА. Имя базы данных, дамп которой необходимо создать.

• ФАЙЛ. Имя файла, в котором будет сохранен дамп базы данных на локальной машине.

При этом возможны два варианта создания дампа через консоль:

• Локальный MySQL. Дамп создается из базы данных, которая находится на локальном сервере MySQL. В этом случае параметры АДРЕС и ПОРТ не указываются.

• Удаленный MySQL. Дамп создается из базы данных, которая находится на удаленном сервере MySQL. В этом случае нужно указать АДРЕС и ПОРТ.

В первом случае команда создания дампа БД может выглядеть так:

mysqldump -u admin -p'qwerty123' test_db > just_dump.sql

Во втором случае команда создания дампа БД может выглядеть уже так:

mysqldump -u admin -p'qwerty123' -h 37.220.80.65 -P 3306 test_db > just_dump.sql

При этом в обоих случая в целях безопасности можно опустить явное указание пароля — в этом случае пароль потребуется ввести вручную:

mysqldump -u admin -p test_db > just_dump.sql mysqldump -u admin -p -h 37.220.80.65 -P 3306 test_db > just_dump.sql

Предупреждения и ошибки

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

Давайте разберем каждое из сообщений подробнее.

Сперва MySQL сообщает о небезопасности использования пароля в качестве явного параметра:

mysqldump: [Warning] Using a password on the command line interface can be insecure.

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

Далее идет предупреждение включении глобального идентификатора транзакции (GTID) в итоговый дамп, а также предложение отключить GTID с помощью специального флага --set-gtid-purged=OFF:

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 

Глобальный идентификатор транзакции (GTID) — это уникальный 128-битный идентификатор, связанный с каждой транзакцией, который повышает общую консистентность данных.

Надо сказать, что GTID был введен в MySQL не просто так и его выключение может привести к несогласованности данных (например, из-за дублирования некоторых SQL-инструкций).

Следующее предупреждение аналогично связано с GTID и указывает на не атомарность операции создания данных:

Warning: A dump from a server that has GTIDs enabled will by default include the GTIDs of all transactions, even those that were executed during its extraction and might not be represented in the dumped data. This might result in an inconsistent data dump. In order to ensure a consistent backup of the database, pass --single-transaction or --lock-all-tables or --master-data.

То есть операции изменения БД, выполненные другими программами в момент создания дампа, могут в него не попасть, что нарушит консистентность данных. Чтобы этого не происходило, можно указать флаг либо для создания дампа в одну транзакцию (--single-transaction), либо для блокирования любых других операций над БД в момент создания ее дампа (--lock-all-tables).

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

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

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

Ее можно добавить, выполнив соответствующую команду:

GRANT PROCESS ON *.* TO 'admin'@'localhost'

Однако это не самое лучшее решение с точки зрения безопасности. Вместо предоставления глобальной привилегии лучше использовать флаг --no-tablespaces во время выполнения команды дампа.

Таким образом, со всеми дополнительными флагами команда создания дампа будет выглядеть примерно так:

mysqldump -u admin -p'qwerty123' -h 37.220.80.65 -P 3306 test_db --no-tablespaces --set-gtid-purged=OFF --single-transaction > just_dump.sql

В этом случае останется только одно безобидное предупреждение о явном указании пароля:

mysqldump: [Warning] Using a password on the command line interface can be insecure.

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

ERROR 1044 (42000): Access denied for user 'admin'@'%' to database ' test_db_other'

Это может создавать путаницу, поэтому нужно всегда проверять корректность указываемого названия БД.

Файл с дампом

Если проверить файловую систему после после успешного выполнения команды:

ls

То в файловой системе должен появится соответствующий файл с дампом БД:

just_dump.sql  resize.log  snap

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

cat just_dump.sql

В самом начале идет информация о созданном дампе, после чего следуют SQL-инструкции:

-- MySQL dump 10.13  Distrib 8.0.40, for Linux (x86_64) -- -- Host: 37.220.80.65    Database: default_db -- ------------------------------------------------------ -- Server version       8.0.22-13  /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;  ...  /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;  -- Dump completed on 2024-11-19  5:33:16

Кстати, выходной файл можно сохранять не только в текущую директорию, но и в любую другую:

mysqldump -u admin -p'qwerty123' -h 37.220.80.65 -P 3306 test_db --no-tablespaces --set-gtid-purged=OFF --single-transaction > /tmp/just_dump.sql

В этом случае файл с дампом БД just_dump.sql будет создан в директории /tmp.

Дамп нескольких БД

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

mysqldump -u admin -p'qwerty123' -h 37.220.80.65 -P 3306 --all-databases --no-tablespaces --set-gtid-purged=OFF --single-transaction > just_dump.sql

Такая команда ничем не отличается от предыдущей за исключением того, что вместо имени конкретной БД указывается флаг --all-databases.

Либо можно поочередно перечислить базы данных, которые нужно поместить в дамп:

mysqldump -u admin -p'qwerty123' -h 37.220.80.65 -P 3306 db_first db_second db_third --no-tablespaces --set-gtid-purged=OFF --single-transaction > just_dump.sql

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

Также можно создать дамп только структуры БД в виде таблиц без каких-либо данных, получив своего рода шаблон базы данных. Для этого указывается дополнительные флаг --no-data:

mysqldump -u admin -p'qwerty123' -h 37.220.80.65 -P 3306 test_db --no-data --no-tablespaces --set-gtid-purged=OFF --single-transaction > just_dump.sql

Конкретные таблицы

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

mysqldump -u admin -p'qwerty123' -h 37.220.80.65 -P 3306 test_db table1 table2 --no-data --no-tablespaces --set-gtid-purged=OFF --single-transaction > just_dump.sql

С другой стороны, можно создать дамп БД со всеми таблицами, кроме указанной, с помощью параметра --ignore-table:

mysqldump -u admin -p'qwerty123' -h 37.220.80.65 -P 3306 test_db --ignore-table=test_db.logs --no-data --no-tablespaces --set-gtid-purged=OFF --single-transaction > just_dump.sql

При этом перед названием таблицы всегда указывается имя базы данных, разделенное символом точки.

Чтобы исключить несколько таблиц, их нужно последовательно перечислить с указанием опции --ignore-table:

mysqldump -u admin -p'qwerty123' -h 37.220.80.65 -P 3306 test_db --ignore-table=test_db.table1 --ignore-table=test_db.table2 --no-data --no-tablespaces --set-gtid-purged=OFF --single-transaction > just_dump.sql

Кодировка

В некоторых случаях необходимо в явном виде указать кодировку создаваемого дампа:

mysqldump -u admin -p'qwerty123' -h 37.220.80.65 -P 3306 test_db --no-tablespaces --set-gtid-purged=OFF --single-transaction --default-character-set=utf8 > just_dump.sql

Зачастую в качестве основной кодировки используется UTF-8.

Архивирование

Иногда полезно созданный дамп сразу упаковать в архив. Это можно сделать, передав поток вывода mysqldump во входной поток gzip, после чего сохранить архив на диск:

mysqldump -u admin -p'qwerty123' -h 37.220.80.65 -P 3306 test_db --no-tablespaces --set-gtid-purged=OFF --single-transaction | gzip > just_dump.sql.gz

Теперь если проверить текущую директорию командой ls, то можно увидеть архивированный дамп:

just_dump.sql  just_dump.sql.gz  resize.log  snap

Загрузка дампа

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

Для загрузки дампа БД в MySQL предусмотрена соответствующая команда:

mysql -u admin -p'qwerty123' -h 37.220.80.65 -P 3306 test_db < just_dump.sql

Иногда в MySQL по умолчанию стоит ограничение на размер загружаемого дампа — при слишком большом размере он не будет загружен.

Изменить максимальный размер загружаемого дампа можно с помощью соответствующего флага:

mysql -u admin -p'qwerty123' -h 37.220.80.65 -P 3306 test_db --max_allowed_packet=64M < just_dump.sql

В данном случае максимальный размер дампа, возможного для загрузки, составляет 64 Мб.

mysql -u gen_user -p'Mahe878787' -h 37.220.80.65 -P 3306 default_db < just_dump.sql  mysqldump -u gen_user -p'Mahe878787' -h 37.220.80.65 -P 3306 --all-databases --no-tablespaces --set-gtid-purged=OFF > just_dump.sql  mysqldump -u gen_user -p'Mahe878787' -h 37.220.80.65 -P 3306 --all-databases --no-tablespaces --set-gtid-purged=OFF > just_dump.sql

❯ Способ 2: Интерфейс phpMyAdmin

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

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

Откроется страница со списком существующих таблиц внутри выбранной БД. На ней нужно нажать на кнопку «Экспорт», которая переносит пользователя на отдельную страницу настройки экспорта (дампа) базы данных.

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

Стоит также отметить, что phpMyAdmin разрешает делать экспорт только тех БД, в которых есть созданные таблицы. Пустые базы данных не экспортируются.

Существует два типа экспорта базы данных через phpMyAdmin:

• Быстрый. Дамп создается на основе стандартных настроек экспорта.

• Обычный. Дамп создается на основе ручных настроек экспорта. Например, можно исключать некоторые таблицы, изменять кодировку, корректировать формат и так далее.

Также можно указать формат файла с дампом. Это может быть не только SQL, но и PDF, JSON, CSV, YAML, а также множество других.

По сути, параметры экспорта повторяют флаги консольной команды создания дампа. Однако в случае с phpMyAdmin конфигурация создания дампа более наглядна.

После настройки всех параметров в самом низу страницы экспорта нужно нажать на кнопку «Экспорт», которая начнет загрузку готового дампа через браузер.

❯ Способ 3: Интерфейс Adminer

Процесс создание дампа БД в Adminer практически ничем не отличается от phpMyAdmin. Более того, графический интерфейс Adminer выглядит еще проще.

Для начала нужно авторизоваться в Adminer, после чего перейти на страницу экспорта с помощью ссылки «Экспорт» в левом боковом меню.

В Adminer нет понятия типа экспорта, поэтому система предлагает настроить сразу все параметры.

В частности можно можно выбрать конкретные таблицы БД, которые будут помещены в дамп.

При этом файл с дампом можно не только сохранить (в определенном формате или GZIP-архиве), но и открыть в новом окне для ручного копирования последовательности SQL-инструкций.

❯ Заключение

Нативный способ создания дампа, который не требует дополнительных инструментов, — консольная команда mysqldump с указанием дополнительных параметров.

Другой способ создания дампа — использование визуальных инструментов управления базами данных с помощью графического интерфейса. Утилиты, вроде phpMyAdmin или Adminer, оборачивают взаимодействие с базами данных в наглядный и интерактивный вид, что полезно для людей, плохо знакомых с синтаксисом языка SQL. Таким образом любые манипуляции с БД, в том числе и создание дампа, превращаются в последовательность простых кликов мыши.

---

Новости, обзоры продуктов и конкурсы от команды Timeweb.Cloud — в нашем Telegram-канале ↩

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

• ➤ Чиним блокировку AVG и AVAST антивирусов – этот продукт не поддерживается в вашем текущем местоположении;

• ➤ Размышления о декларативной конфигурации;

• ➤ Создаем свою ORM на python — гайд;

• ➤ Вернер Бухгольц. Байт на Stretсh;

• ➤ Ментальная модель потоков в Node.js.