Traditional MySQL backup utilities like mysqldump operate using a single-threaded model, processing tables sequentially. This limitation often results in extended backup windows for large datasets. To address this, mydumper was developed as a multi-threaded logical backup tool. It allows for concurrent data extraction from tables and simultaneous writing to distinct files, significantly outperforming the standard utility in terms of speed. However, because mydumper ensures data consistency by locking tables (via FLUSH TABLES WITH READ LOCK), it can block DML operations during the initial phase. Consequently, it is best practice to execute these backups on replica servers to avoid impacting production traffic.

The toolset consists of two main components:

• mydumper: Performs the high-speed parallel export of database data while ensuring a consistent snapshot.
• myloader: Reads the files generated by mydumper and performs a multi-threaded restore to the target database.

Operational Workflow

Understanding the internal logic of mydumper helps in optimizing its usage. The process follows a specific sequence to maintain data integrity:

1. Global Lock Acquisition: The primary thread issues a FLUSH TABLES WITH READ LOCK (FTWRL) to halt all write operations.
2. Binary Log Capture: The current binary log file and position are recorded in a metadata file to support Point-in-Time Recovery (PITR).
3. Snapshot Initialization: Worker threads initiate consistent snapshots using START TRANSACTION WITH CONSISTENT SNAPSHOT.
4. Non-Transactional Dump: Tables using non-transactional storage engines (e.g., MyISAM) are dumped first while the global lock is still active.
5. Lock Release: The global read lock is released immediately after non-transactional tables are processed.
6. Transactional Dump: InnoDB tables are dumped using the established transaction snapshots, allowing concurrent operations on the source server.

Output File Structure

Unlike standard dumps that often produce a single large SQL file, mydumper separates the backup into distinct files for better manageability and parallel restore:

• metadata: Contains the backup start/end timestamps, binary log coordinates, and master log positions if backed up from a replica.
• [database].[table]-schema.sql: Holds the CREATE TABLE statement.
• [database].[table].sql: Contains the actual INSERT statements for data. If chunking is enabled, multiple files (e.g., .00001.sql) are created.
• [database]-schema-create.sql: The CREATE DATABASE statement.
• [database]-schema-post.sql: Contains stored procedures, events, and functions.

Essential Configuration Options

While the tools offer numerous flags, the following parameters are critical for controlling performance and behavior:

MyDumper Parameters

• -B, --database: Specifies the specific database to export.
• -T, --tables-list: Comma-separated list of tables to include in the backup.
• -o, --outputdir: Destination directory for the backup files.
• -t, --threads: Number of worker threads to use (default is 4).
• -c, --compress: Compresses output files to save disk space.
• -m, --no-schemas: Skips schema generation, backing up data only.
• -d, --no-data: Skips data dumping, exporting schemas only.
• --regex: Uses a regular expression to filter databases and tables.
• --events, --routines, --triggers: Include events, stored procedures/functions, and triggers respectively.
• --kill-long-queries: Terminates long-running queries that might block the lock acquisition.

MyLoader Parameters

• -d, --directory: The directory containing the backup files to restore.
• -B, --database: Target database name for the restore.
• -o, --overwrite-tables: Drops existing tables before restoring (use with caution).
• -t, --threads: Number of threads for concurrent data loading.
• -q, --queries-per-transaction: Commits after executing N queries (default 1000).
• -e, --enable-binlog: Enables binary logging during the restore (required for replication chaining).

Practical Implementation Examples

1. Full Database Backup with Compression

This command backs up the entire app_production database using 8 threads and enables compression.

mydumper -u admin_user -p 'SecurePass123' -B app_production -t 8 -c -o /var/backups/mysql_prod/

2. Selective Table Backup

To backup specific tables (users and transactions) without their schemas:

mydumper -u admin_user -p 'SecurePass123' -B app_production -T users,transactions -m -o /var/backups/tables_only/

3. Restoring from Backup

The following command restores the backup into a target database, overwriting existing tables and using 12 threads for faster loading.

myloader -u admin_user -p 'SecurePass123' -B app_production_staging -o -d /var/backups/mysql_prod/ -t 12

4. Excluding Specific Databases using Regex

When backing up all databases, it is common to exclude system schemas. This regex excludes mysql, sys, information_schema, and performance_schema, as well as a temporary test database.

mydumper -u admin_user -p 'SecurePass123' -t 16 \
  --regex '^(?!(mysql\.|sys\.|information_schema\.|performance_schema\.|temp_test_db))' \
  --events --routines --triggers \
  --outputdir /var/backups/all_dbs/

5. Cross-Database Restore

This scenario restores data from a backup of the source database (source_db) into a differently named target database (target_db) on the same server.

myloader -u admin_user -p 'SecurePass123' -s source_db -B target_db -d /var/backups/source_backup/ -o