Australian cloud for business software — built and run in Sydney since 2003

+61 2 8282 5656
support@netleverage.com
Talk to an engineer
Talk to an engineer
NetLeverage
NetLeverage

MySQL Shell dump utilities against mysqldump: consistency, users and restore time

Technical article
Backup & Recovery

Originally published:

About this article: AI-assisted writing, reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

For many years the standard MySQL backup on a small server has been a nightly mysqldump to a file. MySQL's own manual now suggests something else for many cases: the dump utilities in MySQL Shell. They offer parallel dumping, compression and optional checksum verification. These can help a suitable workload, but only a measured rehearsal establishes backup and restore time. They also produce a different kind of backup, with different defaults and restore requirements.

This guide is for Linux and Windows administrators who look after MySQL. It compares the two from the MySQL 8.4 manuals: how each gets a consistent copy, what happens to user accounts, how restore time differs, and how to rehearse a load without touching production. A companion article covers binary log coordinates and point-in-time recovery with mysqldump.

Why it matters

Both tools make logical backups: they turn data back into statements or data files, rather than copying the database files. A backup that runs well every night can still fail you at restore time if accounts are missing, the target server rejects the load, or the load takes far longer than the business can wait (our reading).

What mysqldump is, and what MySQL says about it

The manual describes it plainly: "The mysqldump client utility performs logical backups , producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data."

It also states its limits: "It is not intended as a fast or scalable solution for backing up substantial amounts of data. With large data sizes, even if the backup step takes a reasonable time, restoring the data can be very slow because replaying the SQL statements involves disk I/O for insertion, index creation, and so on."

And it points elsewhere: "Consider using the MySQL Shell dump utilities , which provide parallel dumping with multiple threads, file compression, and progress information display".

For large systems, it says: "For large-scale backup and restore, a physical backup is more appropriate, to copy the data files in their original format so that they can be restored quickly."

How each gets a consistent copy

mysqldump relies on a transaction for InnoDB. The --single-transaction option "sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB , because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications."

Only InnoDB tables receive the consistency guarantee discussed here. MyISAM and MEMORY tables can change during a --single-transaction dump. Concurrent ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE or TRUNCATE TABLE affecting tables being dumped can produce incorrect contents or cause the dump to fail; a transaction does not isolate the dump from these statements. Prevent those changes during the dump under an approved operational plan.

MySQL Shell uses several threads, each with its own transaction, and has to line them up. Its consistent option is on by default: "When true is set, the utility sets a global read lock using the FLUSH TABLES WITH READ LOCK statement (if the user ID used to run the utility has the RELOAD privilege), or a series of table locks using LOCK TABLES statements". Each thread then starts "START TRANSACTION WITH CONSISTENT SNAPSHOT". The manual goes on: "When all threads have started their transactions, the instance is locked for backup (as described in LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE Statements ) and the global read lock is released."

The account privileges matter: "If the user account does not have the BACKUP_ADMIN privilege and LOCK INSTANCE FOR BACKUP cannot be executed, the utilities make an extra consistency check during the dump. If this check fails, an instance dump is stopped".

MySQL Shell also guarantees data consistency only for InnoDB tables. Successful completion does not certify a consistent snapshot of changing non-InnoDB tables. For mixed engines, require a separately validated method or an application quiescence and locking plan that covers every writer; switching to Shell does not remove this requirement.

Lock acquisition and duration depend on the workload; they are not assuredly brief. Waiting for locks and holding a read lock can affect application work. Assess the procedure first on an authorised isolated system, and plan any production run within an approved impact budget.

Parallel threads and chunks

Parallel threads and chunking can improve throughput when the data layout, CPU, storage and network benefit; additional workers can also increase contention. The manual says threads is "The number of parallel threads to use to dump chunks of data from the MySQL instance. Each thread has its own connection to the MySQL instance. The default is 4."

Chunking splits each table: "The default is true , so chunking is enabled by default." It aims to write files "each containing this amount of data before compression is applied", set by bytesPerChunk. Tables without a key are handled differently: "If a table has no primary key or unique index, chunking is done based on the number of rows in the table, the average row length, and the bytesPerChunk value."

Chunking allows parallel loading within a table, which can improve throughput on a suitable target: "If table data was chunked when the dump was created, multiple threads can be used for a table, otherwise each thread loads one table at a time."

A cap is available too. The maxRate option sets "The maximum number of bytes per second per thread for data read throughput during the dump." This is a per-thread read limit, not an application-performance guarantee. The combined configured read allowance grows with the number of active workers. CPU, storage I/O, network use and application latency still need a measured budget; begin on an isolated test system and obtain approval for any production trial (our recommendation).

User accounts: the trap in the defaults

This is the difference most likely to hurt at restore time (our reading).

  • Instance dump includes users by default. "The default is true , so users are included by default."
  • Schema and table dumps do not. "The schema dump utility and table dump utility do not include users, roles, and grants in a dump."
  • Loading skips users by default. For the load utility's loadUsers option, "The default is false , so users are not imported by default." The manual repeats: "Note that users and their roles and grants are excluded from the load by default."

An instance dump can contain account statements while the default load imports none. Even with loadUsers: true, statements for the current loader account are skipped. Existing accounts also clash: "If a user already exists in the target MySQL instance, an error is returned and the user's grants from the dump files are not applied."

Including mysql table data in a mysqldump file does not establish usable account recovery. Define and rehearse a version-appropriate account, role and grant recovery method, preferably using supported account-management statements and protected account configuration. Direct grant-table reload depends on compatible table structures and requires a privilege reload for direct changes to take effect; the manual discourages direct modification. This is a dependency warning, not a production reload procedure. Test new connections, role activation and required application permissions on the isolated target, whichever backup tool you use (our recommendation).

An instance dump is also not a complete physical-server backup. Shell excludes the information_schema, mysql, ndbinfo, performance_schema and sys schemas from instance dumps; account statements are handled separately. Protect server configuration and other external dependencies separately. In MySQL 8.4, mysqldump --all-databases needs explicit --routines and --events options when those objects are required. Record and verify the application objects and accounts the runbook expects.

Checksums and dry runs

Checksum generation and verification are separate choices. Set checksum: true when creating the dump to generate its checksum metadata, and set checksum: true separately for util.loadDump to verify that metadata against the loaded data. The presence of checksum metadata alone does not enable load-side verification. The loader documentation says: "If enabled, the loadDump utility checks the checksum data generated by the dump utility after the corresponding data is loaded." Mismatches and missing tables produce errors.

Both utilities offer dryRun. On dump, it will "Display information about what would be dumped with the specified set of options ... but do not proceed with the dump." On load, it will display "what actions would be performed given the specified options and dump files, including any errors that would be returned based on the dump contents, but do not proceed with the import."

A load with dryRun: true does not verify checksums, even if checksum: true is also set. A real isolated load and verification are necessary. Checksum results, warnings, the expected recovery-point data and application behaviour are separate acceptance evidence.

For a mysqldump recovery plan, verify the restored data and application behaviour against a recorded recovery-point baseline; a completed dump file alone is not that proof.

What a load needs from the target

The load utility has its own requirements:

  • local_infile must be on. The utility "uses the LOAD DATA LOCAL INFILE statement, so the global setting of the local_infile system variable on the target MySQL instance must be ON for the duration of the import."
  • Warnings, not errors. That statement "uses nonrestrictive data interpretation, which turns errors into warnings and continues with the load operation." In our recommendation, review warnings after a load rather than assuming silence means success.
  • Version gaps. By default, if the major versions are non-consecutive, "an error is issued and the import does not proceed".
  • Binary logging. "The default is false , so binary logging is active by default." Loading into a replication source therefore replicates the load (our understanding).

Resuming and restarting a load

Loads can resume. "Progress state for an import is stored in a persistent progress state file, which records steps successfully completed and steps that were interrupted or failed."

Restarting needs care. With resetProgress, "the dump loading utility does not skip objects that were already created". The manual warns: "If you want to use this option, to ensure a correct import, you must first manually remove from the target MySQL instance all previously loaded objects, including schemas, tables, users, views, triggers, routines, and events from that dump." It also warns: "Resuming a dump after changing the data has undefined behavior and can lead to data inconsistency and data loss." Any reset or object removal belongs only in an explicitly authorised, disposable isolated rehearsal with a verified source dump. These untested notes do not authorise deleting production objects.

Index creation can be deferred: deferTableIndexes will "Defer the creation of secondary indexes until after the table data is loaded. This can reduce loading times." The loader's analyzeTables option defaults to off, so it does not explicitly issue ANALYZE TABLE by default. That does not mean statistics never change: InnoDB automatic statistics recalculation is enabled by default and can run asynchronously after sufficient row changes; index creation can also calculate statistics. Assess statistics and representative query performance during the isolated recovery rehearsal.

Read-only checks

These checks only read; they change nothing. In a MySQL client session (any shell), inventory tables outside the InnoDB-only consistency guarantees of both methods discussed here:

SELECT table_schema, table_name, engine
FROM information_schema.TABLES
WHERE engine <> 'InnoDB'
  AND table_schema NOT IN ('mysql','sys','information_schema','performance_schema');

Use SHOW GRANTS as an inventory aid for the connected account, and inspect the target's local_infile setting. Run each check on the intended source or target connection. A plain SHOW GRANTS result is not complete proof of effective rights: inspect assigned and active roles and role-derived privileges as applicable, using the documented USING form where needed. Rehearse with the intended backup and application accounts:

SHOW GRANTS FOR CURRENT_USER();
SHOW GLOBAL VARIABLES LIKE 'local_infile';

In MySQL Shell's JavaScript mode, a dry run shows what a dump would contain without writing data (the path is an example):

util.dumpInstance('/srv/dumps/dryrun-test', {dryRun: true})

These checks show the design. They do not prove a restore works; a test does (our recommendation).

Safe restore rehearsal

These are untested planning steps for an authorised isolated rehearsal, to be adapted by a qualified MySQL administrator. Use synthetic data and test accounts.

  1. Define a consistent test recovery point and record expected data, required objects, accounts, role membership and application operations. Use InnoDB for the snapshot methods discussed here, or a separately validated mixed-engine procedure. Prevent conflicting DDL.
  2. Create and protect a completed dump with checksum: true for the Shell route. Verify completion, errors, freshness, checksum metadata and inclusion of the exact files in the protected backup before selecting it for recovery. Retain the last good backup if the new dump fails.
  3. Build an isolated compatible target with no network path to production and no replication links. Restore the exact protected dump, account-recovery records and required configuration to this test environment.
  4. Inspect the proposed load with dryRun: true; this is not checksum verification. Then perform the real util.loadDump with checksum: true, checksum metadata present, and loadUsers set explicitly for the tested account plan. Account for skipped current-loader statements and existing-user errors.
  5. Verify checksum outcomes and warnings, compare restored data and objects with the baseline for that same recovery point, then open fresh application connections and test role-derived permissions and required operations. Investigate each failure separately.
  6. Measure the complete recovery through index creation, any statistics work and application checks. Compare the observed result with the recovery objective before choosing the tool and settings.

Diagnosing what you find

  • Missing accounts or grants after load: check loadUsers, whether account statements are present, existing-user errors and the current-loader-account exception. Confirm role activation and permissions through new test connections.
  • The load exceeds the recovery budget: measure data loading, index creation, statistics work and application checks separately. Investigate chunking, worker count, target resources and contention before attributing the cause.
  • The dump reports a consistency error: inspect the exact failed check and concurrent DDL or other relevant activity. Missing BACKUP_ADMIN triggers an additional check; it is not itself proof that consistency failed. Do not assume every non-InnoDB change is detected or that a completed dump makes those tables consistent.
  • Data looks wrong but no error appeared: warnings were not reviewed; character sets differ; or SQL modes differ on the target.

Decision example (illustrative)

A team is considering Shell because its logical recovery may exceed the business outage budget. It first inventories storage engines, required accounts and application objects. In a proposed isolated rehearsal, an absent application account would lead it to inspect the dump's account statements, loadUsers, existing-user errors and the loader-account exception, rather than assume the data load failed.

The decision depends on a completed rehearsal: verify checksums, fresh application connections and the correct recovery-point data, then measure all recovery stages. Compare the result with an appropriate physical-recovery option under the same acceptance conditions. Select a method only once the measured recovery meets the requirement. This is a decision scenario, not a performed test or a benchmark result.

Risks and limits

  • The corrected claims use official Oracle-hosted 8.4 manuals retrieved during independent review and reread for this correction. Displayed revision dates were unavailable; check the manual for the exact installed version.
  • Option names and defaults change between MySQL Shell releases.
  • Nothing here was tested in a lab.
  • The cited scalability warning concerns mysqldump. Compare logical and physical recovery methods for the actual system; no speed or recovery-time result is established here.

Sources

Official Oracle-hosted MySQL 8.4 manuals, retrieved on 14 November 2026 during independent review and reread for this correction. Displayed revision dates were unavailable. The original dev.mysql.com URLs returned HTTP 403; these public official equivalents supplied the evidence.

Related future topics

  • MySQL binary log coordinates and point-in-time recovery
  • Physical MySQL backups and restore time
  • Restoring MySQL users and grants

This article was originally published on .