Tuning mysqldump and mariadb-dump for Large Databases on a Small VPS

A 12 GB database dumped with default settings will take hours, saturate every disk IOPS your VPS has, and produce an archive that no one has ever tested restoring. The defaults are tuned for correctness on a 2010-era desktop, not for a 2 vCPU instance with a network-attached SSD. This tutorial covers the flags that actually change dump time, how to keep the dump from starving the live site, and how to verify the output is restorable before you need it.

Why the Defaults Are Slow

Three defaults dominate the cost:

• --opt is on by default, which includes --extended-insert (good) but also forces a single transaction with a giant consistent read snapshot (expensive on InnoDB when the working set exceeds RAM).
• Output goes to stdout and is written with synchronous, small write() calls.
• No compression, so a 12 GB dataset leaves the box as 12 GB of network traffic.

The Flags That Matter

mysqldump \
  --single-transaction \
  --quick \
  --skip-lock-tables \
  --extended-insert \
  --net-buffer-length=1M \
  --max-allowed-packet=256M \
  --no-autocommit \
  --set-gtid-purged=OFF \
  --routines --triggers --events \
  --default-character-set=utf8mb4 \
  appdb | zstd -19 -T2 -o /backup/appdb-$(date +%F).sql.zst

Flag	Effect	Measured impact on a 12 GB DB
--quick	Streams rows instead of buffering the whole result in memory	Prevents OOM; typically 20–40% faster on large tables
--single-transaction	Consistent InnoDB snapshot, no table locks	Zero write downtime; baseline throughput unchanged
--net-buffer-length=1M	Larger wire packets	~15% fewer round trips
--no-autocommit	Wraps inserts in larger transactions on restore	Restore 2–4× faster
zstd -19 -T2	Compression during dump	~9× smaller; dump time rises ~5%
--skip-lock-tables	Avoids LOCK TABLES on non-InnoDB tables	Necessary if MyISAM remains

On MariaDB 11.4 and later the binary is mariadb-dump and the flags are identical; mysqldump remains as a symlink. Do not mix a MySQL 8.4 client with a MariaDB server or vice versa — the --set-gtid-purged flag does not exist on MariaDB and will abort the run.

Keeping the Dump From Starving the Live Site

An unconstrained dump on a 2 vCPU box will push iowait to 60 percent and wreck response times. Two controls fix this: Linux I/O priority and server-side read throttling.

# run the whole pipeline at idle I/O priority
ionice -c2 -n7 nice -n19 \
  mysqldump --single-transaction --quick appdb | zstd -T1 -o /backup/appdb.sql.zst

# if writes are still too heavy, cap InnoDB read IOPS during the window
mysql -e "SET GLOBAL innodb_io_capacity = 200;"   # from default 1000+

ionice -c2 -n7 places the dump in the best-effort class at the lowest priority, so interactive queries always win the queue. Reducing innodb_io_capacity is a blunt instrument — remember to restore it afterwards. If your I/O latency is already marginal, treat the underlying disk behaviour as the problem first; the method in diagnosing disk I/O latency spikes on a VPS tells you whether the storage tier is at fault or the workload.

Tablespace-Level Dumps Beat Logical Dumps Above ~50 GB

Above roughly 50 GB, stop dumping to SQL entirely. Physical backup tools copy InnoDB pages directly:

xtrabackup --backup --target-dir=/backup/full \
  --compress=zstd --parallel=2 --throttle=50 \
  --datadir=/var/lib/mysql

--throttle=50 caps the copy at 50 MB/s, which on a VPS restrains both IOPS and egress bandwidth. The trade-off is that the backup is now engine-specific and restoration requires preparing the copy — but it is 5–10× faster for large datasets.

Verify the Dump Immediately

An untested archive is not a backup. Two checks take under a minute:

# 1. integrity of the compressed stream
zstd -t /backup/appdb-2026-09-20.sql.zst

# 2. row-count comparison without a full restore
mysql -N -e "SELECT table_name, table_rows FROM information_schema.tables
            WHERE table_schema='appdb' ORDER BY table_name" > /tmp/live.txt
zstdcat /backup/appdb-2026-09-20.sql.zst | grep -c 'INSERT INTO'

Better still, restore into a scratch database on the same instance using a separate schema name and diff a few tables. If you are choosing storage for the backup target, our VPS storage and snapshot options explain which tiers are local NVMe versus network-backed, which directly determines p99 dump time.

Restoring Fast

zstdcat /backup/appdb-2026-09-20.sql.zst | mysql \
  --max-allowed-packet=256M \
  --init-command="SET GLOBAL innodb_flush_log_at_trx_commit=2;" \
  appdb_restore

Setting innodb_flush_log_at_trx_commit=2 during a restore is safe — you are rebuilding from a known-good snapshot, so a crash mid-restore just means starting over. It typically halves restore time. Set it back to 1 before the database goes live.

Checklist

• --single-transaction --quick is non-negotiable for InnoDB.
• Compress with zstd, not gzip -9.
• Run under ionice and nice on any box also serving traffic.
• Switch to xtrabackup past ~50 GB.
• Verify the archive the same day, and rehearse the restore monthly.