MySQL: экспорт и импорт больших дампов на виртуальном хостинге (split, CLI, phpMyAdmin)

Перенос большой базы данных MySQL на виртуальном хостинге чаще всего упирается в практику: как быстро и безопасно сделать экспорт, как передать файл, как восстановить его без таймаутов, ограничений PHP и ошибок вида «MySQL server has gone away» или «Packet too large». В этой статье собрал проверенные приёмы для админов, девопсов и вебмастеров: mysqldump с правильными флагами, потоковое сжатие gzip, разбиение файла split, импорт через mysql CLI и через phpMyAdmin, а также диагностика типовых ошибок на виртуальном хостинге.

Когда использовать phpMyAdmin, а когда — CLI

Виртуальный хостинг накладывает ограничения: память и время выполнения PHP-скриптов, лимиты загрузки файлов в веб-интерфейсе и отсутствие root-доступа. Поэтому выбор инструмента зависит прежде всего от размера базы и доступности SSH.

• phpMyAdmin: разумно для дампов до 50–200 МБ (вместе со сжатием). Важно заранее узнать лимиты загрузки и таймауты на вашем тарифе. Преимущество — доступность из браузера, недостаток — ограниченные ресурсы и риск таймаутов при импортировании больших таблиц.
• CLI (SSH + mysql/mysqldump): предпочтительно для всего, что крупнее 200–300 МБ, а также при необходимости точного контроля флагов, переменных сессии (max_allowed_packet) и потоковой обработки. Практически всегда надёжнее и быстрее.

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

Подготовка к экспорту: оценка размера и типа таблиц

Перед созданием дампа определите:

• Какие типы таблиц у вас: InnoDB или MyISAM. Для InnoDB можно и нужно использовать --single-transaction для согласованного снимка без долгих блокировок. Для MyISAM требуется блокировка таблиц, и экспорт может влиять на доступность приложения.
• Есть ли процедуры, функции, события, триггеры — их нужно включить отдельными флагами.
• Размер данных и отдельных «тяжёлых» таблиц: большие BLOB/TEXT поля увеличивают требования к max_allowed_packet.

Правильный mysqldump: флаги и пояснения

Базовый шаблон команды для InnoDB на виртуальном хостинге выглядит так:

mysqldump --host=127.0.0.1 --user=dbuser --password --default-character-set=utf8mb4 --single-transaction --routines --events --triggers --hex-blob --skip-lock-tables --set-gtid-purged=OFF --column-statistics=0 --no-tablespaces dbname | gzip -1 > dbname-$(date +%F).sql.gz

Что здесь важно:

• --single-transaction — согласованный снимок без долгих блокировок, подходит для InnoDB. Не используйте при MyISAM.
• --routines --events --triggers — чтобы не потерять логику БД.
• --hex-blob — безопасный экспорт бинарных данных (BLOB) в текстовый SQL.
• --skip-lock-tables — избегаем избыточных блокировок (для InnoDB).
• --set-gtid-purged=OFF — чтобы не пытаться выполнять операции с GTID, которые недоступны на общем хостинге.
• --column-statistics=0 — отключает колонковую статистику, чтобы избежать ошибок совместимости между версиями mysqldump и сервера.
• --no-tablespaces — убирает привязки к табличным пространствам, которые на виртуальном хостинге чаще всего запрещены.
• --default-character-set=utf8mb4 — явно задаём кодировку, чтобы потом не ловить сюрпризы.
• Потоковое сжатие: gzip -1 часто достаточно быстро и сильно уменьшает размер файла. Можно увеличить степень сжатия при необходимости.

Совет по безопасности: не храните пароль прямо в командной строке. Вместо этого используйте файл конфигурации клиента ~/.my.cnf с правами 0600, где задайте [client] и password=..., или экспортируйте переменную окружения для разовой сессии.

Экспорт отдельных таблиц и выборочно по условию

Иногда нужно выгрузить только часть базы — например, без тяжёлой таблицы логов. Тогда перечислите нужные таблицы в конце:

mysqldump --host=127.0.0.1 --user=dbuser --password --default-character-set=utf8mb4 --single-transaction --routines --events --triggers --hex-blob dbname table1 table2 | gzip -1 > dbname-partial-$(date +%F).sql.gz

Для выборки по условию используйте --where (только для одной таблицы за раз):

mysqldump --host=127.0.0.1 --user=dbuser --password dbname events --where='created_at >= "2025-01-01"' | gzip -1 > events-2025.sql.gz

Перенос больших файлов: split и обратная склейка

Когда у вас есть SSH и SFTP — отлично. Но при слабом канале, лимитах размера файла в панели или веб-загрузчике split выручает. Идея проста: делим архив на части фиксированного размера, переносим, затем склеиваем обратно.

Разбиваем архив на части по 100 МБ

split -b 100m -d -a 3 dbname-2025-11-11.sql.gz dbname-2025-11-11.sql.gz.part-

• -b 100m — размер части.
• -d — числовые суффиксы.
• -a 3 — длина суффикса (000, 001, 002 ...).

Склеиваем на целевой стороне

После переноса всех частей (сохраните порядок, лучше использовать маску part-*):

cat dbname-2025-11-11.sql.gz.part-* > dbname-2025-11-11.sql.gz

Почему делим именно архив, а не «сырой» SQL? Разделённый «сырой» SQL может разорвать оператор посередине. А вот разделённый .gz безопасно склеивается байт-в-байт без потери целостности, и только затем распаковывается для импорта.

Импорт через CLI: быстро и контролируемо

Оптимальный способ для больших дампов — потоковый импорт с распаковкой «на лету» и настройкой сессионных переменных. Базовый шаблон:

zcat dbname-2025-11-11.sql.gz | mysql --host=127.0.0.1 --user=dbuser --password --default-character-set=utf8mb4 --max_allowed_packet=1G dbname

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

( echo "SET SESSION max_allowed_packet=1073741824;"; echo "SET FOREIGN_KEY_CHECKS=0;"; zcat dbname-2025-11-11.sql.gz ) | mysql --host=127.0.0.1 --user=dbuser --password --default-character-set=utf8mb4 dbname

После импорта включите проверки (если они остались выключены):

mysql --host=127.0.0.1 --user=dbuser --password -e "SET FOREIGN_KEY_CHECKS=1;" dbname

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

Проверка после восстановления

• Сравните количество строк в ключевых таблицах до/после (если есть доступ к обеим БД).
• Просмотрите предупреждения: SHOW WARNINGS в рамках тестовых импортов или отлавливайте ошибки при импорте в отдельный файл.
• Проверяйте кодировку: SHOW VARIABLES LIKE 'character_set_%' и collation_%.

Импорт через phpMyAdmin: ограничения и техники

Если SSH недоступен и остаётся только phpMyAdmin, действуем аккуратно.

• Загружайте сжатый .sql.gz — так вы экономите трафик и снижаете риск таймаутов при загрузке.
• Следите за лимитом размера файла и временем выполнения. Если дамп крупный, разбейте его на несколько логически завершённых частей: по таблицам или по диапазонам.
• Избегайте слишком тяжёлых запросов при импорте. Если в дампе есть очень большие INSERT, может помочь пересоздание дампа с меньшим размером батча: по умолчанию --extended-insert уже объединяет строки, но при проблемах экспортируйте по одному INSERT на строку (--skip-extended-insert).

Виртуальный хостинг для сайтов

Универсальное решение для создания и размещения сайтов любой сложности в Интернете от 95₽ / мес

Подробнее

Разбиение «сырого» SQL по таблицам

Если у вас есть dump.sql (не архив), можно разрезать файл на куски по заголовкам таблиц. Пример с csplit:

csplit -sz dump.sql '/^-- Table structure for table /' '{*}'

Файлы получатся xx00, xx01, ... Первая часть может содержать преамбулу с SET-командами и созданием базы — её импортируйте первой.

Альтернативно — через awk, генерируя файлы на основе встреченных DROP TABLE или CREATE TABLE:

awk '/^-- Table structure for table/ { if (f) close(f); table=$NF; gsub("`", "", table); f=sprintf("table-%s.sql", table); print >> f; next } { if (f) print >> f }' dump.sql

Типичные ошибки и быстрые решения

1) MySQL server has gone away

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

• При CLI-импорте используйте потоковую распаковку и избегайте огромных отдельных INSERT. Если дамп создан с длинными множественными INSERT, пересоздайте его с --skip-extended-insert или разбейте таблицу на части.
• Попробуйте увеличить --net-read-timeout, --net-write-timeout и --connect-timeout на стороне клиента.

2) Packet too large (Got a packet bigger than 'max_allowed_packet')

• На импорт: задайте клиенту --max_allowed_packet=1G и добавьте в поток SET SESSION max_allowed_packet=.... Если серверное ограничение жёсткое и сессия не даёт поднять лимит, уменьшайте размер INSERT при экспорте.
• На экспорт: у mysqldump тоже есть ключ --max_allowed_packet — пригодится, если встречаются сверхдлинные поля.

3) Access denied; you need (at least one of) the SUPER privilege(s) for this operation

• Уберите из дампа конструкции с DEFINER и операции, требующие SUPER. Для процедур и триггеров: перед экспортом примените --set-gtid-purged=OFF, --no-tablespaces. При необходимости замените DEFINER=`user`@`host` на DEFINER=CURRENT_USER:

sed -E 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' dump.sql > dump-sanitized.sql

4) Unknown collation 'utf8mb4_0900_ai_ci'

Разница версий MySQL/MariaDB. Экспортируйте со старшей версии в более старую осторожнее: используйте --default-character-set=utf8mb4, отключайте новые колляции или заменяйте их в дампе на совместимые (например, utf8mb4_unicode_ci), если целевой сервер их не поддерживает.

5) Ошибки внешних ключей при импорте

Импортируйте в порядке зависимостей или временно отключайте проверки:

SET FOREIGN_KEY_CHECKS=0;
-- импорт
SET FOREIGN_KEY_CHECKS=1;

Практические сценарии

Сценарий 1: интернет-магазин, дамп ~2 ГБ, есть SSH

1. На исходной стороне: mysqldump с --single-transaction и потоковым gzip.
2. split на части по 200–300 МБ, перенос через SFTP.
3. Склейка на целевой стороне и потоковый импорт с zcat | mysql, предварительно установив SET SESSION max_allowed_packet и SET FOREIGN_KEY_CHECKS=0.
4. Проверка количеств строк, включение FOREIGN_KEY_CHECKS обратно.

Сценарий 2: блог, дамп ~80 МБ, только phpMyAdmin

1. Экспорт с mysqldump в .sql.gz.
2. Проверка лимита загрузки в phpMyAdmin; при необходимости уменьшить размер архива уровнем сжатия gzip -9 или разрезать «сырой» SQL по таблицам.
3. Импорт пролога (SET/CREATE DATABASE), затем таблиц по очереди, начиная со справочников и далее контентные таблицы.

Оптимизация скорости

• Сжатие: на слабых CPU используйте gzip -1 для быстрого компромисса; на мощных — можно -6...-9 для экономии места.
• Для больших дампов избегайте --skip-extended-insert без необходимости: он разгоняет размер дампа и замедляет импорт. Но если бьёт по max_allowed_packet, включайте.
• Если таблиц очень много и импорт идёт медленно из-за индексов, вариант — временно отключить ключи для MyISAM (ALTER TABLE ... DISABLE KEYS), но для InnoDB обычно лучше не трогать.

Автоматизация резервного копирования на виртуальном хостинге

Даже на общем хостинге часто доступен cron. Пример простого ротируемого бэкапа (оставляем 7 последних архивов):

BACKUP_DIR=~/db-backups
mkdir -p "$BACKUP_DIR"
mysqldump --host=127.0.0.1 --user=dbuser --password --default-character-set=utf8mb4 --single-transaction --routines --events --triggers --hex-blob --set-gtid-purged=OFF --column-statistics=0 --no-tablespaces dbname | gzip -1 > "$BACKUP_DIR/dbname-$(date +%F).sql.gz"
find "$BACKUP_DIR" -type f -name 'dbname-*.sql.gz' -mtime +7 -delete

Добавьте контрольную сумму, если переносите архивы на стороннее хранилище:

sha256sum "$BACKUP_DIR/dbname-$(date +%F).sql.gz" > "$BACKUP_DIR/dbname-$(date +%F).sql.gz.sha256"

Если нужен offsite, посмотрите наше сравнение подходов к бэкапам в объектное хранилище: бэкапы в S3 с restic и borg. Для восстановления «до точки» изучите PITR по binlog/GTID.

Мини-чек-лист перед миграцией

• Понимаю ли я тип таблиц? Для InnoDB — --single-transaction; для MyISAM — готовимся к блокировкам.
• Включил ли я процедуры, события, триггеры? --routines --events --triggers.
• Есть ли потенциально «толстые» строки? Настроил max_allowed_packet.
• Делаю ли я потоковое сжатие (gzip) и при необходимости разбиение (split)?
• Смогу ли импортировать через CLI? Если нет — готов ли разрезать SQL по таблицам для phpMyAdmin.
• Учёл ли версии серверов, колляции и запрет GTID/табличных пространств?

FAQ: короткие ответы

Можно ли импортировать части по очереди без склейки? Для сжатых частей безопаснее сначала склеить cat ... > dump.sql.gz, затем распаковать и импортировать. Для «сырого» SQL последовательный импорт частей возможен, но следите за целостностью операторов и порядком зависимостей.

Что быстрее: phpMyAdmin или CLI? Почти всегда CLI. Он устойчив к таймаутам, может работать потоково и детальнее управляет параметрами.

Нужно ли останавливать сайт на время дампа? Для InnoDB с --single-transaction обычно нет. Для MyISAM — желательно, чтобы избежать неконсистентности.

Как понять, какой нужен размер max_allowed_packet? Если в дампе встречаются длинные INSERT с BLOB, ставьте 256M–1G на время импорта. Или экспортируйте с более мелкими INSERT.

Итоги

Главный секрет успешного переноса MySQL на виртуальном хостинге — потоковый подход и правильные флаги. Делайте консистентный mysql dump с --single-transaction, сжимайте его gzip, при необходимости делите split, переносите и восстанавливайте через mysql CLI, корректно настроив max_allowed_packet. Если остался только phpMyAdmin, разбивайте «сырой» SQL на логические части по таблицам и проверяйте лимиты. Такой процесс стабильно проходит даже на ограниченных тарифах виртуального хостинга и экономит нервы при миграциях и аварийном восстановлении.