Проблемы при импорте больших дампов
Импорт дампов размером 1 ГБ+ через стандартный mysql < dump.sql часто зависает или работает очень медленно. Причины: маленький max_allowed_packet, медленная запись InnoDB, огромное количество транзакций, таймауты.
Оптимизация MySQL перед импортом
mysql -u root -p -e "
SET GLOBAL max_allowed_packet = 1073741824; -- 1GB
SET GLOBAL net_buffer_length = 1048576; -- 1MB
SET GLOBAL innodb_buffer_pool_size = 2147483648; -- 2GB (если есть RAM)
SET GLOBAL innodb_log_buffer_size = 67108864; -- 64MB
SET GLOBAL innodb_flush_log_at_trx_commit = 2; -- быстрее, менее надёжно"Импорт со скоростными оптимизациями
# Стандартный импорт
mysql -u root -p mydb < dump.sql
# Сжатый дамп
gunzip < dump.sql.gz | mysql -u root -p mydb
# С прогресс-баром (pv)
apt install pv
pv dump.sql.gz | gunzip | mysql -u root -p mydbУскорение через отключение проверок
Добавьте в начало дампа (или используйте флаги):
mysql -u root -p mydb << 'EOF'
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;
SOURCE /path/to/dump.sql;
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;
SET AUTOCOMMIT = 1;
EOFmydumper / myloader — параллельный бэкап и импорт
# Установка
sudo apt install mydumper
# Создание параллельного дампа (4 потока)
mydumper -u root -p password -B mydb -t 4 -c -o /backup/mydb_dump/
# Параллельный импорт
myloader -u root -p password -B mydb -t 4 -d /backup/mydb_dump/Импорт частями (для очень больших файлов)
# Разбить дамп на части по 100MB
csplit dump.sql "/^-- Table structure/" {*}
# BigDump — веб-инструмент для импорта через browser
# Скачать: https://www.ozerov.de/bigdump/Мониторинг прогресса импорта
# В другом терминале - смотреть текущую операцию
mysql -u root -p -e "SHOW PROCESSLIST\G" | grep -i import
# Размер таблиц во время импорта
mysql -u root -p -e "
SELECT table_name,
ROUND(data_length/1024/1024, 2) AS 'Data MB',
ROUND(index_length/1024/1024, 2) AS 'Index MB'
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY data_length DESC LIMIT 10;"Типичные ошибки при импорте больших дампов
| Ошибка | Причина | Решение |
|---|---|---|
| ERROR 2006 (HY000): MySQL server has gone away | Строка INSERT в дампе больше, чем max_allowed_packet, соединение обрывается | Увеличить max_allowed_packet до 1G перед импортом, см. команду выше |
| ERROR 1114 (HY000): The table is full | Кончилось место на диске под tmp_table_size//tmp, либо достигнут лимит движка (MyISAM 4 ГБ без явного MAX_ROWS) | Проверить df -h, освободить место, для MyISAM — задать больший MAX_ROWS |
| ERROR 1213 (40001): Deadlock found when trying to get lock | Параллельный импорт нескольких таблиц с внешними ключами конфликтует по блокировкам | Импортировать последовательно или временно отключить проверку внешних ключей, как показано ниже |
Почему параллельный импорт не всегда быстрее
mydumper/myloader ускоряют импорт за счёт параллельных потоков, но выигрыш заметен только если диск и процессор не являются узким местом сами по себе. На сервере с одним медленным HDD параллельные потоки конкурируют за одну и ту же дисковую очередь, и суммарная скорость записи не растёт, а иногда падает из-за случайного доступа вместо последовательного. На NVMe-дисках параллелизация даёт кратный прирост, потому что там нет штрафа за случайный доступ.
Когда оптимизации импорта не помогут
Если дамп содержит одну огромную таблицу с несколькими индексами, самих индексов может создаваться дольше, чем занимает загрузка данных — MySQL перестраивает вторичные индексы после вставки всех строк для InnoDB, и на таблице в десятки миллионов строк это может занять часы независимо от настроек буферов. В этом случае временное удаление вторичных индексов перед импортом и их пересоздание после — единственный способ ускорить процесс, а не тюнинг innodb_buffer_pool_size.
Проверка результата импорта
# Сверить количество строк с ожидаемым (например, из отчёта mydumper)
mysql -u root -p mydb -e "SELECT COUNT(*) FROM orders;"
# Проверить, что все индексы созданы и таблица не осталась в состоянии Crashed
mysql -u root -p -e "SHOW TABLE STATUS FROM mydb;" | grep -i orders
# Проверить план типичного запроса — индексы должны работать как раньше
EXPLAIN SELECT * FROM orders WHERE status = 'pending' LIMIT 10;
# Проверить состояние InnoDB — не должно быть незавершённых транзакций
SHOW ENGINE INNODB STATUS\GСтолбец Comment в SHOW TABLE STATUS покажет ошибку, если таблица повреждена после прерванного импорта — это стоит проверить в первую очередь, если импорт завершился с ошибкой на середине файла. Подробнее про создание бэкапов, которые потом легко и быстро импортировать, — в статье про mysqldump и восстановление, про горячий бэкап больших баз без блокировок — в статье про Percona XtraBackup.