Performance Comparison of MySQL Shell Logical Backup/Restore with mysqldump, mysqlpump, and mydumper

MySQL Shell 8.0.21 introduces a new logical backup and restore method that offers faster backup/restore efficiency, supports real‑time ZSTD compression, chunked parallel export, parallel LOAD DATA import, and can store backups to OCI object storage.

The article explains three utility functions: util.dumpInstance() for full‑instance backup, util.dumpSchemas() for backing up specific schemas, and util.loadDump() for restoring dumps.

A comparative test was conducted under zero load with unchanged MySQL configuration, using a mixed set of large and small tables. Various tools—MySQL Shell, mysqldump (gzip, zstd, no compression), mysqlpump (with/without gzip), and mydumper (with different compression and chunk settings)—were measured for backup time, restore time, and backup size.

The result table shows that util.dumpSchemas with default ZSTD compression and 32 MiB chunks achieved the fastest backup (15 s) and restore (158 s) among schema‑level methods, while mysqldump with gzip required 169 s backup and 255 s restore, and mysqlpump with gzip took 185 s backup and 121 s restore. Mydumper’s performance varied with compression and chunking options.

Conclusions: MySQL Shell’s new backup/restore is the overall fastest due to ZSTD real‑time compression and parallelism; compression improves backup speed but can slow restore; mysqlpump provides fast parallel backup but single‑threaded restore; mydumper’s speed is limited by compression overhead.

Sample command lines and output logs for each tool are provided below.

MySQL Shell backup example:

mysqlsh> util.dumpSchemas(["test"],"test1")
Acquiring global read lock
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Preparing data dump for table `test`.`customer1`
... (output truncated) ...
Average compressed throughput: 48.97 MB/s

MySQL Shell restore example:

util.loadDump("test1")
Loading DDL and Data from 'instance' using 4 threads.
Target is MySQL 8.0.21. Dump was produced from MySQL 8.0.21
Checking for pre-existing objects...
Executing DDL script for schema `test`
Executing DDL script for table `test`.`sbtest1`
... (output truncated) ...
168 chunks (10.46M rows, 2.06 GB) for 7 tables in 1 min 26 sec (avg throughput 23.97 MB/s)

mysqldump with gzip:

/usr/bin/time mysqldump -umsandbox -pmsandbox -h127.0.0.1 -P8021 test | gzip > db.sql.gz
mysqldump: [Warning] Using a password on the command line interface can be insecure.
169.40 real 24.65 user 1.34 sys

mysqldump restore:

/usr/bin/time gzip -d < db.sql.gz | ./use test
257.11 real 9.74 user 0.55 sys

mysqlpump with gzip:

/usr/bin/time mysqlpump --default-parallelism=4 -umsandbox -pmsandbox -h127.0.0.1  -P8021 test | gzip > db2.sql.gz
Dump progress: 6/7 tables, 10421749/10406264 rows
185.50 real 31.18 user 6.34 sys

mysqlpump restore:

/usr/bin/time gzip -d < db2.sql.gz | ./use test
121.17 real 9.66 user 0.76 sys

mydumper backup (gzip, 4 threads):

/usr/bin/time mydumper -u msandbox -p msandbox -h 127.0.0.1 -P 8021 -B test -t 4 -v 3 -c -o dumper
... (log output) ...
164.54 real 167.58 user 2.28 sys

myloader restore:

/usr/bin/time myloader -u msandbox -p msandbox -h 127.0.0.1 -P 8021 -B test -t 4 -v 3 -d dumper/
... (log output) ...
187.10 real 14.24 user 1.97 sys