Импорт и экспорт баз данных MySQL с помощью командной строки

Освоение операций импорта и экспорта баз данных MySQL из командной строки — это обязательный навык для любого администратора баз данных или backend-инженера. Утилита `mysqldump` экспортирует базу данных в переносимый файл `.sql`, содержащий все DDL и DML операторы, необходимые для полного восстановления схемы и данных, тогда как команда клиента `mysql` выполняет обратную операцию — передавая этот файл обратно в работающий экземпляр MySQL.

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

Предварительные требования

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

• MySQL Server (5.7, 8.0 или 8.4) установлен и процесс `mysqld` запущен
• Бинарные файлы `mysqldump` и `mysql` находятся в системном `PATH` (проверьте с помощью `which mysqldump`)
• У вас есть учётная запись MySQL как минимум с привилегиями `SELECT`, `LOCK TABLES`, `SHOW VIEW` и `TRIGGER` для экспорта; `CREATE`, `INSERT`, `ALTER` и `DROP` для импорта
• На целевом устройстве достаточно места на диске — сжатый дамп может увеличиться в 5–10 раз при импорте
• У вас есть доступ к оболочке сервера (локальный терминал, SSH или управляемая среда VPS Хостинга)

Экспорт баз данных с помощью mysqldump

`mysqldump` — это стандартный инструмент логического резервного копирования, поставляемый в комплекте с MySQL. Он сериализует объекты базы данных в читаемый SQL-скрипт. В отличие от инструментов физического резервного копирования, таких как Percona XtraBackup, `mysqldump` не зависит от движка хранения и работает в разных версиях MySQL и даже в форках MariaDB.

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

“`bash

mysqldump -u [username] -p [database_name] > [filename].sql

“`

Описание параметров:

• `-u [username]` — учётная запись MySQL, используемая для подключения
• `-p` — запрашивает интерактивный ввод пароля (никогда не передавайте пароль в строке как `-p[password]` на общих системах; он виден в выводе `ps aux`)
• `[database_name]` — целевая схема для экспорта
• `> [filename].sql` — перенаправляет stdout в выходной файл

Пример:

“`bash

mysqldump -u root -p mydatabase > mydatabase_backup.sql

“`

В результате создаётся файл, содержащий операторы `CREATE TABLE`, `INSERT` и `ALTER TABLE`, достаточные для воссоздания `mydatabase` с нуля.

Важный крайний случай: По умолчанию `mysqldump` устанавливает глобальную блокировку чтения (`FLUSH TABLES WITH READ LOCK`) в начале дампа. На высоконагруженных серверах InnoDB используйте вместо этого `–single-transaction` для создания согласованного снимка без блокировки записи:

“`bash

mysqldump -u root -p –single-transaction mydatabase > mydatabase_backup.sql

“`

`–single-transaction` надёжно работает только с таблицами InnoDB. Если ваша база данных содержит таблицы MyISAM, блокировка неизбежна.

2. Экспорт нескольких баз данных

“`bash

mysqldump -u [username] -p –databases db1 db2 > multiple_databases_backup.sql

“`

Флаг `–databases` указывает `mysqldump` включить в вывод операторы `CREATE DATABASE` и `USE`, делая дамп самодостаточным. Без этого флага данные операторы опускаются, и дамп предполагает, что целевая база данных уже выбрана во время импорта.

Пример:

“`bash

mysqldump -u root -p –databases db1 db2 > multiple_databases_backup.sql

“`

3. Экспорт всех баз данных

“`bash

mysqldump -u root -p –all-databases > all_databases_backup.sql

“`

Это экспортирует все схемы, к которым имеет доступ подключающийся пользователь, включая системные базы данных `mysql`, `information_schema` и `performance_schema`. Избегайте импорта системных баз данных между разными основными версиями MySQL — схема таблицы привилегий существенно изменилась между MySQL 5.7 и 8.0, и импорт старой схемы `mysql` в новый экземпляр 8.0 повредит аутентификацию.

Чтобы исключить системные схемы:

“`bash

mysqldump -u root -p –all-databases

–ignore-table=mysql.user

–ignore-table=mysql.db

> all_user_databases_backup.sql

“`

4. Экспорт только структуры таблиц (без данных)

“`bash

mysqldump -u root -p –no-data mydatabase > structure_only.sql

“`

Это незаменимо для контроля версий схемы, проверки кода или развёртывания пустой тестовой среды. Вывод содержит только DDL-операторы `CREATE TABLE`, `CREATE VIEW`, `CREATE PROCEDURE` и аналогичные — без строк `INSERT`.

5. Экспорт конкретных таблиц

“`bash

mysqldump -u root -p mydatabase table1 table2 > tables_backup.sql

“`

Обратите внимание, что при экспорте конкретных таблиц таким образом `mysqldump` не включает операторы `CREATE DATABASE` или `USE`. Вы должны убедиться, что целевая база данных существует перед импортом.

6. Экспорт с хранимыми процедурами, триггерами и событиями

По умолчанию `mysqldump` включает триггеры, но не включает хранимые процедуры, функции и запланированные события. Для полного резервного копирования приложения:

“`bash

mysqldump -u root -p

–routines

–triggers

–events

–single-transaction

mydatabase > full_backup.sql

“`

Забыть `–routines` — одна из наиболее распространённых причин неудачного восстановления приложений: схема и данные присутствуют, но бизнес-логика отсутствует.

7. Сжатый экспорт

Передайте вывод напрямую через `gzip` для уменьшения размера файла на 60–80%:

“`bash

mysqldump -u root -p mydatabase | gzip > mydatabase_backup.sql.gz

“`

Для максимального сжатия больших баз данных (за счёт времени CPU):

“`bash

mysqldump -u root -p mydatabase | gzip -9 > mydatabase_backup.sql.gz

“`

Импорт баз данных с помощью клиента mysql

Клиент командной строки `mysql` принимает SQL-файл через перенаправление stdin и последовательно выполняет каждый оператор на целевом сервере.

1. Импорт в существующую базу данных

“`bash

mysql -u [username] -p [database_name] < [filename].sql

“`

Пример:

“`bash

mysql -u root -p mydatabase < mydatabase_backup.sql

“`

Важно: Если файл `.sql` был экспортирован с `–databases` или `–all-databases`, он уже содержит директивы `CREATE DATABASE` и `USE`. В этом случае не указывайте имя базы данных в командной строке — это создаёт конфликт:

“`bash

mysql -u root -p < all_databases_backup.sql

“`

2. Импорт в новую базу данных

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

Шаг 1 — Создайте базу данных:

“`bash

mysql -u root -p -e "CREATE DATABASE newdatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

“`

Шаг 2 — Импортируйте дамп:

“`bash

mysql -u root -p newdatabase < mydatabase_backup.sql

“`

Всегда явно указывайте кодировку и сортировку при создании базы данных. Использование настроек сервера по умолчанию — частый источник несоответствий кодировок, особенно при миграции между серверами с разными конфигурациями `character_set_server`.

3. Импорт сжатого дампа

“`bash

gunzip < mydatabase_backup.sql.gz | mysql -u root -p mydatabase

“`

Или с использованием `zcat` (эквивалент в большинстве дистрибутивов Linux):

“`bash

zcat mydatabase_backup.sql.gz | mysql -u root -p mydatabase

“`

4. Импорт с мониторингом прогресса

Для больших дампов стандартный клиент `mysql` не даёт никакой обратной связи. Используйте `pv` (pipe viewer) для отображения индикатора прогресса в реальном времени:

“`bash

pv mydatabase_backup.sql | mysql -u root -p mydatabase

“`

Установите `pv` с помощью `apt install pv` или `yum install pv`. На Выделенном Сервере, обрабатывающем многогигабайтные производственные базы данных, эта видимость критически важна с операционной точки зрения.

mysqldump в сравнении с альтернативными методами резервного копирования

Для сред, где несколько производственных баз данных работают на управляемом VPS с cPanel, `mysqldump` остаётся наиболее универсально поддерживаемым вариантом благодаря своей совместимости и простоте.

Расширенная настройка и крайние случаи

Правильная работа с кодировками

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

“`bash

mysqldump -u root -p

–default-character-set=utf8mb4

mydatabase > mydatabase_backup.sql

“`

“`bash

mysql -u root -p

–default-character-set=utf8mb4

mydatabase < mydatabase_backup.sql

“`

Примечание: `utf8` в MySQL — это 3-байтовое подмножество, которое не может хранить 4-байтовые символы Unicode (эмодзи, некоторые иероглифы CJK). Всегда используйте `utf8mb4` для новых баз данных.

Ускорение крупных импортов

По умолчанию MySQL выполняет полный коммит после каждого оператора `INSERT` в дампе. Для больших наборов данных это катастрофически медленно. Добавьте следующее в начало вашей сессии импорта:

“`bash

mysql -u root -p mydatabase <<EOF

SET foreign_key_checks = 0;

SET unique_checks = 0;

SET autocommit = 0;

SOURCE /path/to/mydatabase_backup.sql;

COMMIT;

SET foreign_key_checks = 1;

SET unique_checks = 1;

EOF

“`

Либо экспортируйте с `–extended-insert` (включено по умолчанию) и `–disable-keys` для пакетной вставки и отложенного перестроения индексов после загрузки данных.

Автоматизация резервного копирования с помощью Cron

Запись автоматического резервного копирования производственного уровня в `/etc/cron.d/mysql-backup`:

“`bash

0 2 * * * root mysqldump -u backup_user -p'StrongPass'

–single-transaction –routines –triggers –events

mydatabase | gzip > /backups/mydatabase_$(date +%F).sql.gz

“`

Используйте выделенного пользователя MySQL с минимально необходимыми привилегиями вместо `root`. Создайте его с помощью:

“`sql

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPass';

GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, EVENT ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

“`

Защита учётных данных с помощью .my.cnf

Передача паролей в командной строке раскрывает их в истории оболочки и списках процессов. Храните учётные данные в `~/.my.cnf`:

“`ini

[client]

user=root

password=YourSecurePassword

“`

Немедленно установите права доступа:

“`bash

chmod 600 ~/.my.cnf

“`

После этого все команды `mysqldump` и `mysql` автоматически используют учётные данные без флагов `-u` и `-p`.

Экспорт удалённой базы данных

Для создания дампа базы данных с удалённого сервера MySQL:

“`bash

mysqldump -h remote.server.com -P 3306 -u remoteuser -p remotedatabase > remote_backup.sql

“`

Убедитесь, что удалённый экземпляр MySQL разрешает подключения с вашего IP-адреса и что порт 3306 открыт в брандмауэре. Для зашифрованных передач используйте туннель через SSH:

“`bash

ssh -L 3307:127.0.0.1:3306 user@remote.server.com -N &

mysqldump -h 127.0.0.1 -P 3307 -u remoteuser -p remotedatabase > remote_backup.sql

“`

Практическая матрица принятия решений

Ключевые технические выводы

• Всегда используйте `–single-transaction` для баз данных InnoDB, чтобы избежать блокировки записей приложения во время экспорта
• Всегда явно указывайте `utf8mb4` — никогда не полагайтесь на настройки кодировки сервера по умолчанию
• Включайте `–routines`, `–triggers` и `–events` в каждое полное резервное копирование приложения, иначе вы рискуете потерять бизнес-логику
• Никогда не импортируйте таблицы системных схем (`mysql.*`) между разными основными версиями MySQL
• Храните учётные данные в `~/.my.cnf` с правами `chmod 600` — никогда не передавайте пароли в качестве встроенных аргументов
• Для баз данных объёмом более 10 ГБ рассмотрите использование `mysqlpump` или утилит параллельного дампа MySQL Shell, так как `mysqldump` станет узким местом
• Проверяйте каждую резервную копию, выполняя тестовое восстановление в изолированной среде, прежде чем полагаться на неё для аварийного восстановления
• При размещении баз данных нескольких клиентов изолируйте среды с помощью отдельных Панелей управления VPS, чтобы предотвратить межарендный доступ во время операций восстановления
• Дополните стратегию резервного копирования баз данных действующим SSL-сертификатом на любом веб-уровне приложения, подключающемся к MySQL, чтобы предотвратить перехват учётных данных при передаче

Часто задаваемые вопросы

В чём разница между mysqldump и mysqlpump?

`mysqldump` является однопоточным и создаёт один SQL-файл — надёжный и универсально совместимый. `mysqlpump`, представленный в MySQL 5.7.8, поддерживает параллельный экспорт нескольких баз данных и таблиц одновременно, значительно сокращая время создания дампа на многоядерных серверах. Однако `mysqlpump` имеет известные проблемы с согласованным резервным копированием баз данных со смешанными движками и менее подходит для миграций между версиями.

Можно ли импортировать дамп MySQL 5.7 в MySQL 8.0?

Да, с оговорками. Пользовательские данные и схемы приложений импортируются без проблем. Однако никогда не импортируйте системную базу данных `mysql` напрямую — плагин аутентификации изменился с `mysql_native_password` на `caching_sha2_password` в версии 8.0, и импорт старых таблиц привилегий нарушит аутентификацию. Воссоздайте пользователей вручную с помощью операторов `CREATE USER` и `GRANT`.

Почему импорт завершается с ошибкой «ERROR 1005: Can’t create table» из-за ограничений внешних ключей?

Это происходит, когда таблицы импортируются в порядке, нарушающем зависимости внешних ключей. Решение — добавить `SET foreign_key_checks = 0;` в начало сессии импорта и `SET foreign_key_checks = 1;` после завершения. Либо экспортируйте с `–single-transaction`, что сохраняет ссылочную целостность в самом файле дампа.

Как экспортировать только данные без операторов CREATE TABLE?

Используйте флаг `–no-create-info`: `mysqldump -u root -p –no-create-info mydatabase > data_only.sql`. Это полезно, когда нужно перезагрузить данные в существующую схему без изменения её структуры.

Каков наиболее безопасный способ передачи дампа MySQL между двумя удалёнными серверами?

Передавайте дамп напрямую через SSH без записи промежуточного файла на диск: `mysqldump -u root -p sourcedb | ssh user@destination.server "mysql -u root -p targetdb"`. Это одновременно быстрее и безопаснее, чем копирование открытого файла `.sql`, особенно при работе в среде Общего Веб-хостинга, где квоты на диск являются ограничением.