Материал подготовлен командой Simple-Server для администраторов VPS и выделенных серверов. Команды и пути проверяйте на тестовой машине перед production.
Кратко о задаче
Если у вас уже есть готовая база данных MySQL, на которой можно проверить функцию создания дампа, то этот раздел можно пропустить.
В противном случае базу данных можно создать вручную исключительно для тестирования примеров из этого руководства.
Самый простой способ это сделать — создать облачную базу данных в Simple-Server.
Для этого необходимо авторизоваться в панели управления Simple-Server, после чего перейти на страницу «Базы данных».
Далее необходимо нажать либо на кнопку «Создать» (если созданных баз данных еще нет), либо на кнопку «Добавить» (если созданные базы данных уже есть).
После этого откроется страница конфигурирования облачной БД.
Основной параметр здесь — «Тип базы данных». Нужно выбрать «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.
АДРЕС. IP-адрес удаленного сервера MySQL.
ПОРТ. Порт удаленного сервера MySQL.
БАЗА. Имя базы данных, дамп которой необходимо создать.
ФАЙЛ. Имя файла, в котором будет сохранен дамп базы данных на локальной машине.
При этом возможны два варианта создания дампа через консоль:
-
Локальный MySQL. Дамп создается из базы данных, которая находится на локальном сервере MySQL. В этом случае параметры
АДРЕСиПОРТне указываются. -
Удаленный 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'Это может создавать путаницу, поэтому нужно всегда проверять корректность указываемого названия БД.
Если проверить файловую систему после после успешного выполнения команды:
То в файловой системе должен появится соответствующий файл с дампом БД:
just_dump.sql resize.log snapНесмотря на то, что этот файл можно открыть в любом текстовом редакторе, его размер может оказаться достаточно большим. Например, если исходная база данных содержала большое количество информации:
В самом начале идет информация о созданном дампе, после чего следуют 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Способ 2: Интерфейс phpMyAdmin
Если вы используете phpMyAdmin, то создать дамп базы данных можно через графический интерфейс без ручного выполнения команд — за вас это сделает сам phpMyAdmin.
Для этого нужно авторизоваться в phpMyAdmin, после чего в левом боковом меню выбрать необходимую базу данных.
Откроется страница со списком существующих таблиц внутри выбранной БД. На ней нужно нажать на кнопку «Экспорт», которая переносит пользователя на отдельную страницу настройки экспорта (дампа) базы данных.
На самом деле на страницу экспорта можно попасть и с главной страницы phpMyAdmin, однако в этом случае некоторые базы данных могут не отображаться в списке доступных для экспорта. Поэтому рекомендуется сперва перейти на страницу конкретной БД, а уже потом нажать на кнопку экспорта.
Стоит также отметить, что phpMyAdmin разрешает делать экспорт только тех БД, в которых есть созданные таблицы. Пустые базы данных не экспортируются.
Существует два типа экспорта базы данных через phpMyAdmin:
- Быстрый. Дамп создается на основе стандартных настроек экспорта.
Быстрый. Дамп создается на основе стандартных настроек экспорта.
- Обычный. Дамп создается на основе ручных настроек экспорта. Например, можно исключать некоторые таблицы, изменять кодировку, корректировать формат и так далее.
Обычный. Дамп создается на основе ручных настроек экспорта. Например, можно исключать некоторые таблицы, изменять кодировку, корректировать формат и так далее.
Также можно указать формат файла с дампом. Это может быть не только SQL, но и PDF, JSON, CSV, YAML, а также множество других.
По сути, параметры экспорта повторяют флаги консольной команды создания дампа. Однако в случае с phpMyAdmin конфигурация создания дампа более наглядна.
После настройки всех параметров в самом низу страницы экспорта нужно нажать на кнопку «Экспорт», которая начнет загрузку готового дампа через браузер.**
**
Способ 3: Интерфейс Adminer
Процесс создание дампа БД в Adminer практически ничем не отличается от phpMyAdmin. Более того, графический интерфейс Adminer выглядит еще проще.
Для начала нужно авторизоваться в Adminer, после чего перейти на страницу экспорта с помощью ссылки «Экспорт» в левом боковом меню.
В Adminer нет понятия типа экспорта, поэтому система предлагает настроить сразу все параметры.
В частности можно можно выбрать конкретные таблицы БД, которые будут помещены в дамп.
При этом файл с дампом можно не только сохранить (в определенном формате или GZIP-архиве), но и открыть в новом окне для ручного копирования последовательности SQL-инструкций.
Разверните MySQL в облаке за минуту
Нативный способ создания дампа, который не требует дополнительных инструментов, — консольная команда mysqldump с указанием дополнительных параметров.
Другой способ создания дампа — использование визуальных инструментов управления базами данных с помощью графического интерфейса. Утилиты, вроде phpMyAdmin или Adminer, оборачивают взаимодействие с базами данных в наглядный и интерактивный вид, что полезно для людей, плохо знакомых с синтаксисом языка SQL. Таким образом любые манипуляции с БД, в том числе и создание дампа, превращаются в последовательность простых кликов мыши.
Нужен сервер для практики? Арендуйте VPS/VDS в России — root-доступ, NVMe, DDoS-защита и поддержка 24/7.