Web-based tools like phpMyAdmin fail on large dumps: upload limits, PHP timeouts, memory limits. The reliable way to import anything bigger than a few megabytes is the command-line client on the server.

1. Get the file onto the server

rsync -avP backup.sql.gz [email protected]:/home/user/

Compressed transfers are much faster — SQL compresses 5–10×. See rsync.

2. Create the database

sudo mysql -e "CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

Skip this if the dump contains CREATE DATABASE statements (made with --databases or --all-databases).

3. Import

sudo mysql shop < backup.sql
gunzip < backup.sql.gz | sudo mysql shop
zstd -dc backup.sql.zst | sudo mysql shop

Run long imports inside tmux or screen, so a dropped SSH connection doesn’t kill them.

Show progress

sudo apt install -y pv
pv backup.sql.gz | gunzip | sudo mysql shop

pv shows throughput, percentage and an ETA based on the compressed file size.

4. Speed up big imports

A default import commits row batches one by one and checks every constraint. For multi-gigabyte dumps, temporarily relax this for the import session only:

( echo "SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;"
  gunzip < backup.sql.gz
  echo "COMMIT; SET unique_checks=1; SET foreign_key_checks=1;"
) | sudo mysql shop

Server-level settings that help during a one-off import (restore them afterwards):

SET GLOBAL innodb_flush_log_at_trx_commit = 2;   -- fewer fsyncs; default is 1
  • More RAM in the buffer pool also helps — see InnoDB buffer pool.
  • Dumps created with mysqldump --opt (default) already use multi-row inserts and disable keys during load.

For very large databases (tens of GB), logical dumps are slow by nature. Parallel tools like mydumper/myloader import tables in parallel, and physical backups (XtraBackup, mariadb-backup) avoid SQL replay entirely.

5. Common errors

ERROR 2006 (HY000): MySQL server has gone away / Got a packet bigger than 'max_allowed_packet' — a single statement (e.g. a big BLOB or a huge extended insert) exceeds the packet limit. Raise it on the server and client:

SET GLOBAL max_allowed_packet = 1073741824;   -- 1 GB
gunzip < backup.sql.gz | sudo mysql --max_allowed_packet=1G shop

ERROR 1273: Unknown collation: 'utf8mb4_0900_ai_ci' — a MySQL 8 dump imported into MariaDB or MySQL 5.7. Replace the collation:

gunzip < backup.sql.gz | sed 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' | sudo mysql shop

ERROR 1227: Access denied; you need (at least one of) the SUPER privilege(s) — the dump contains DEFINER clauses for users that don’t exist or require privileges. Strip them:

gunzip < backup.sql.gz | sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' | sudo mysql shop

ERROR 1062: Duplicate entry — importing into a database that already has data. Import into an empty database, or drop tables first (dumps usually include DROP TABLE IF EXISTS).

Import stops halfway with no error — check dmesg/journalctl -k for the OOM killer, and disk space with df -h.

To continue past errors (and review them afterwards), add --force — use with care.

6. Verify

sudo mysql shop -e "SHOW TABLES;" | wc -l
sudo mysql shop -e "SELECT COUNT(*) FROM orders;"

Compare row counts with the source. For WordPress, also update URLs if the domain changed — see WordPress search and replace.

Related: mysqldump backup and restore and migrating WordPress to a VPS.

  • MySQL
  • MariaDB
  • Databases