Introducing MySQL 8.0.21 Enhanced Logical Backup and Restore Tool

This article provides a brief introduction to the enhanced logical backup tool bundled with MySQL 8.0.21.

Before presenting the new utility, it reviews the history of MySQL logical backup tools: the original single‑threaded mysqldump , the multi‑threaded mysqlpump introduced in MySQL 5.7, and finally the MySQL Shell UTIL that ships with MySQL 8.0.21 and supports multi‑threaded backup and restore.

The MySQL Shell UTIL offers several features, including default ZSTD compression, 32 MB chunked exports, automatic export of stored procedures, functions, triggers, events, users, and views, four parallel export threads, progress display, UTF‑8 mb4 charset, consistent snapshot, SQL definition export, and TSV data export.

Demo 1 – dump_instance

Python‑style parameters for backing up an entire instance with four parallel threads:

# 备份路径
backup_path='/tmp/ytt_backup_instance';
# 备份选项，定义一致性，不包含哪些数据库，线程数等等。
backup_options = {"consistent":True,"threads":4,"excludeSchemas":["world","ytt_new","score","query_rewrite"]}

Result summary (duration 35 s, 584.89 MB uncompressed, 12.02 MB compressed, compression ratio 48.7, 28 M rows written, average throughput 16.49 MB/s uncompressed, 338.96 KB/s compressed).

Demo 2 – dump_schemas

Backing up a single database ("ytt") with the same options:

# 备份指定的数据库
backup_schema = ["ytt"]
# 备份路径
backup_path='/tmp/ytt_backup_schemas';
# 备份选项
backup_options = {"consistent":True,"threads":4}

The output statistics are similar to dump_instance (duration 32 s, same data sizes and compression).

Demo 3 – load_dump

Restoring the previously created backup:

# 删除原数据库
mysql> drop database ytt;
Query OK, 8 rows affected (0.27 sec)
# 创建指定数据库ytt.
mysql> create database ytt;
Query OK, 1 row affected (0.01 sec)
# 用于恢复的备份集目录
restore_url = '/tmp/ytt_backup_schemas'
# 恢复选项：是否对恢复的表做样例数据收集，是否延迟建立索引，恢复指定的数据库等。
restore_options = {"analyzeTables":"on","deferTableIndexes":"all","includeSchemas":["ytt"]}

The restore process loads 40 chunks (28 M rows, 584.89 MB) for six tables in about 3 minutes 30 seconds, achieving an average throughput of 2.79 MB/s with no warnings.

Conclusion

The MySQL 8.0.21 logical backup and restore utility provides high‑performance, multi‑threaded alternatives to the slower mysqldump and mysqlpump tools, offering built‑in compression, consistent snapshots, and easy integration via simple Python‑style commands.