How to Dump and Import Large MySQL or MariaDB Databases: Progress, Compression, Per-Table Dumps and Resuming

Updated: 8 October 2026 · Applies to: MariaDB 11.8 (mariadb-dump) with pv 1.8.5, zstd 1.5.5, tmux 3.4 and screen 4.09; mysqldump options checked against the MySQL 8.4 manual

For a large MySQL or MariaDB database, start a tmux session, dump with --single-transaction --quick into a fast compressor with pv showing progress, and import the same way. If the job is very large or keeps failing, dump each table to its own file so you can resume:

tmux new -s import
mysqldump --single-transaction --quick --routines bigshop | pv | zstd > bigshop.sql.zst
zstd -dc bigshop.sql.zst | pv | mysql bigshop_copy

This article covers what changes when a database is big. For the basics, errors and moving between MySQL and MariaDB, see how to export and import a MySQL or MariaDB database from the command line. The tests used a 118 MB dump of three InnoDB tables (1.3 million rows) on a test server with one CPU; on MariaDB, mysqldump and mysql are also available as mariadb-dump and mariadb.

1. Run long jobs inside tmux or screen

If your SSH connection drops, a dump or import started in a normal session stops halfway. Start a session that keeps running on the server:

tmux new -s import         # start a session and run the job in it
tmux attach -t import      # reconnect later
$ tmux ls
import: 1 windows (created Thu Oct  8 07:20:05 2026)

According to the tmux manual, Ctrl+b then d detaches. With screen: screen -S import, detach with Ctrl+a d, return with screen -r import. Both are packaged on Debian and Ubuntu; AlmaLinux 9 and 10 have tmux in BaseOS but no screen package in their default repositories. More in how to install and use tmux.

2. Dump with --single-transaction and --quick

mariadb-dump --help lists the defaults: quick TRUE, lock-tables TRUE, single-transaction FALSE. So --quick (stream rows instead of holding a whole table in memory) is already on, but --single-transaction is not. Add it: it dumps all InnoDB tables as one consistent snapshot without locking them. The MySQL manual recommends the two together for large tables, with two warnings: MyISAM and MEMORY tables are not consistent this way, and nobody should run ALTER, DROP, RENAME or TRUNCATE TABLE on them during the dump.

3. See progress with pv

Without a size, pv shows the amount, time and speed:

$ mysqldump --single-transaction --quick --routines bigshop | pv | zstd > bigshop.sql.zst
 117MiB 0:00:13 [9.01MiB/s] [             <=>                                  ]

For a percentage, give pv an estimate from the server. It is only a guide: our estimate was 143 MB, the dump 118 MB, so the bar stopped at 82%:

$ mysql -N -e "SELECT ROUND(SUM(data_length+index_length)/1024/1024) FROM information_schema.tables WHERE table_schema='bigshop'"
143
$ mysqldump --single-transaction --quick bigshop | pv -s 143m > bigshop.sql

On import, put pv on the file itself, so the percentage is exact (more in monitor progress with pv):

$ pv bigshop.sql.gz | gunzip | mysql restore1
22.7MiB 0:00:55 [ 415KiB/s] [================================>] 100%

4. Choose the compression

Our 118 MB dump, compressed on one CPU core:

• gzip -1: 25.6 MB in 3.4 s; gzip (default level): 23.8 MB in 10.6 s; pigz: 23.7 MB in 9.7 s
• zstd (default level 3): 18.3 MB in 2.2 s
• zstd -19: 15.0 MB in 330 s; xz: 13.6 MB in 192 s

Use zstd for dumps: smaller than gzip and faster even than gzip -1. Unpack with zstd -dc file.sql.zst. pigz and zstd -T0 use every core, which helps on servers with several CPUs (not on our one-core test). Keep xz and zstd -19 for archives you store for a long time. See zstd on Linux.

5. One file per table, so you can resume

for t in $(mysql -N -e 'SHOW TABLES' bigshop); do
  mysqldump --single-transaction bigshop "$t" | gzip > "bigshop.$t.sql.gz"
done
mysqldump --no-data --no-create-info --skip-triggers --routines bigshop > bigshop.routines.sql

Each file starts with DROP TABLE IF EXISTS and CREATE TABLE and turns off foreign key checks, so you can load the files in any order and load one again after a failure. The catch: each table gets its own snapshot, so if the site keeps writing during the dump, related tables can disagree. Stop writes first, or use a single dump.

This script imports each file once and records what finished:

#!/bin/bash
db=restore3
for f in bigshop.*.sql.gz; do
  if grep -qxF "$f" done.txt 2>/dev/null; then echo "skip   $f"; continue; fi
  echo "import $f"
  gunzip < "$f" | mysql "$db" || { echo "FAILED $f"; exit 1; }
  echo "$f" >> done.txt
done

We stopped the first run in the middle of order_log. Running it again skipped the finished table and redid the half-loaded one:

skip   bigshop.customers.sql.gz
import bigshop.order_log.sql.gz
import bigshop.orders.sql.gz

A single .sql file cannot be resumed; import it again from the start (it drops and recreates each table as it goes).

6. Make the import faster

• Dumps already set UNIQUE_CHECKS=0 and FOREIGN_KEY_CHECKS=0 in their header; no need to add them.
• Committing once at the end helped a little: 600,000 rows took 19.4 s as dumped and 16.5 s with (echo "SET autocommit=0;"; zcat t.sql.gz; echo "COMMIT;") | mysql db.
• Rows bigger than the server's max_allowed_packet (16 MB by default on MariaDB 11.8) stop the import with ERROR 2006 (HY000) at line 40: Server has gone away. A 20 MB row imported after SET GLOBAL max_allowed_packet=1073741824; on the server and mysql --max-allowed-packet=1G on the client. See how to change max_allowed_packet.
• MariaDB 11.5 and later can dump to a folder of data files and load them in parallel. mariadb-dump --dir=/tmp/dumpdir --parallel=2 --single-transaction bigshop took 7.6 s, and mariadb-import --dir=/tmp/dumpdir --parallel=2 restored the database in 23.5 s instead of 56 s. The database server writes the data files itself, so run this on that server, into a folder the mysql user can write to.

For very large databases, the MySQL manual recommends a physical backup that copies the data files (on MariaDB: mariadb-backup), because replaying SQL is slow.

7. Check the copy

$ mysql -e "CHECKSUM TABLE bigshop.orders, restore3.orders"
Table	Checksum
bigshop.orders	2372105764
restore3.orders	2372105764

Equal checksums mean the table data is identical. For routine backups, see how to back up a database using mysqldump. If you would rather have a large migration done for you, Ucartz engineers can help through Hire an expert.