vaspvort
Ночной дозор
Команда форума
Модератор
ПРОВЕРЕННЫЙ ПРОДАВЕЦ
Private Club
Старожил
Migalki Club
Меценат💰️
MySQL — это наиболее популярная система управления реляционными базами данных выполняющая множество разнородных манипуляций с таблицами: добавление, удаление, поиск, сортировка и вывод по запросу пользователя.
Важно понимать, что MySQL, подобно библиотекарю в библиотеке, управляет БД, а не является ею. Поэтому MySQL и база данных отделены друг от друга.
Такая возможность позволяет выполнять несколько полезных действий:
В этом руководстве мы рассмотрим основные способы как создания дампа базы данных, так и ее импорта обратно в MySQL.
В противном случае базу данных можно создать вручную исключительно для тестирования примеров из этого руководства.
Самый простой способ это сделать — создать в Timeweb Cloud.
Для этого необходимо в панели управления 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.
Для этого понадобиться подключиться к MySQL по SSH-соединению, после чего ввести команду создания дампа:
mysqldump -u ПОЛЬЗОВАТЕЛЬ -p'ПАРОЛЬ' -h АДРЕС -P ПОРТ БАЗА > ФАЙЛ
Давайте разберем каждый из указанных параметров немного подробнее:
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.
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
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 -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 -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
Для этого нужно авторизоваться в phpMyAdmin, после чего в левом боковом меню выбрать необходимую базу данных.
Откроется страница со списком существующих таблиц внутри выбранной БД. На ней нужно нажать на кнопку «Экспорт», которая переносит пользователя на отдельную страницу настройки экспорта (дампа) базы данных.
На самом деле на страницу экспорта можно попасть и с главной страницы phpMyAdmin, однако в этом случае некоторые базы данных могут не отображаться в списке доступных для экспорта. Поэтому рекомендуется сперва перейти на страницу конкретной БД, а уже потом нажать на кнопку экспорта.
Стоит также отметить, что phpMyAdmin разрешает делать экспорт только тех БД, в которых есть созданные таблицы. Пустые базы данных не экспортируются.
Страница создания дампа БД в phpMyAdmin
Существует два типа экспорта базы данных через phpMyAdmin:
Страница создания дампа БД в phpMyAdmin с дополнительными настройками
По сути, параметры экспорта повторяют флаги консольной команды создания дампа. Однако в случае с phpMyAdmin конфигурация создания дампа более наглядна.
После настройки всех параметров в самом низу страницы экспорта нужно нажать на кнопку «Экспорт», которая начнет загрузку готового дампа через браузер.
Для начала нужно авторизоваться в Adminer, после чего перейти на страницу экспорта с помощью ссылки «Экспорт» в левом боковом меню.
Страница экспорта базы данных в Adminer
В Adminer нет понятия типа экспорта, поэтому система предлагает настроить сразу все параметры.
В частности можно можно выбрать конкретные таблицы БД, которые будут помещены в дамп.
При этом файл с дампом можно не только сохранить (в определенном формате или GZIP-архиве), но и открыть в новом окне для ручного копирования последовательности SQL-инструкций.
Дамп базы данных, открытый в Adminer через браузер
Другой способ создания дампа — использование визуальных инструментов управления базами данных с помощью графического интерфейса. Утилиты, вроде phpMyAdmin или Adminer, оборачивают взаимодействие с базами данных в наглядный и интерактивный вид, что полезно для людей, плохо знакомых с синтаксисом языка SQL. Таким образом любые манипуляции с БД, в том числе и создание дампа, превращаются в последовательность простых кликов мыши.
Важно понимать, что MySQL, подобно библиотекарю в библиотеке, управляет БД, а не является ею. Поэтому MySQL и база данных отделены друг от друга.
- MySQL — это программа, которая оперирует информацией.
- База данных — это информация, записанная на жестком диске.
Такая возможность позволяет выполнять несколько полезных действий:
- Резервное копирование базы данных. Непредвиденные ситуации при использовании облачного (или локального) сервера могут приводить не только к сбоям в работе программ, но и к потере данных. По этой причине важно регулярно создавать дампы базы данных, которые будут храниться на других защищенных носителях.
- Перенос базы данных с одного сервера на другой. Во время выполнения миграции с одного сервера на другой ручное копирование элементов БД может быть не просто затруднительной, а физически невозможной задачей. Поэтому выполнить быстрый перенос данных можно с помощью дампа.
В этом руководстве мы рассмотрим основные способы как создания дампа базы данных, так и ее импорта обратно в MySQL.
❯ Подготовка тестовой базы
Если у вас уже есть готовая база данных MySQL, на которой можно проверить функцию создания дампа, то этот раздел можно пропустить.В противном случае базу данных можно создать вручную исключительно для тестирования примеров из этого руководства.
Самый простой способ это сделать — создать в Timeweb Cloud.
Для этого необходимо в панели управления 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
Существует два типа экспорта базы данных через phpMyAdmin:
- Быстрый. Дамп создается на основе стандартных настроек экспорта.
- Обычный. Дамп создается на основе ручных настроек экспорта. Например, можно исключать некоторые таблицы, изменять кодировку, корректировать формат и так далее.
Страница создания дампа БД в phpMyAdmin с дополнительными настройками
По сути, параметры экспорта повторяют флаги консольной команды создания дампа. Однако в случае с phpMyAdmin конфигурация создания дампа более наглядна.
После настройки всех параметров в самом низу страницы экспорта нужно нажать на кнопку «Экспорт», которая начнет загрузку готового дампа через браузер.
❯ Способ 3: Интерфейс Adminer
Процесс создание дампа БД в Adminer практически ничем не отличается от phpMyAdmin. Более того, графический интерфейс Adminer выглядит еще проще.Для начала нужно авторизоваться в Adminer, после чего перейти на страницу экспорта с помощью ссылки «Экспорт» в левом боковом меню.
Страница экспорта базы данных в Adminer
В Adminer нет понятия типа экспорта, поэтому система предлагает настроить сразу все параметры.
В частности можно можно выбрать конкретные таблицы БД, которые будут помещены в дамп.
При этом файл с дампом можно не только сохранить (в определенном формате или GZIP-архиве), но и открыть в новом окне для ручного копирования последовательности SQL-инструкций.
Дамп базы данных, открытый в Adminer через браузер
❯ Заключение
Нативный способ создания дампа, который не требует дополнительных инструментов, — консольная команда mysqldump с указанием дополнительных параметров.Другой способ создания дампа — использование визуальных инструментов управления базами данных с помощью графического интерфейса. Утилиты, вроде phpMyAdmin или Adminer, оборачивают взаимодействие с базами данных в наглядный и интерактивный вид, что полезно для людей, плохо знакомых с синтаксисом языка SQL. Таким образом любые манипуляции с БД, в том числе и создание дампа, превращаются в последовательность простых кликов мыши.
