mysqldump Backup Command

What Is mysqldump?

mysqldump is a command-line MySQL client that creates a logical dump. Its SQL-format output normally contains statements that recreate schema objects and insert rows. It does not copy raw InnoDB data files.

mysqldump [connection-options] [dump-options] database [tables]
mysqldump [connection-options] [dump-options] --databases db1 db2
mysqldump [connection-options] [dump-options] --all-databases

Use a logical dump when portability, readable SQL, database/table selection or cross-environment migration matters. Do not assume it is automatically a complete disaster-recovery solution: configuration, accounts/grants, encryption keys, binary logs and external application files can require separate protection.

Configure Credentials Safely

Do not place a password after -p on the command line. It can leak through history, process inspection or logs. Use an approved secret manager or create a host-local encrypted login path interactively:

mysql_config_editor set --login-path=backup \
  --host=db.example.internal \
  --user=backup_operator --password

mysql --login-path=backup -e "SELECT VERSION();"
mysqldump --login-path=backup --version

The backup account needs only privileges required by the chosen objects and options. Requirements vary with MySQL version, view definitions, locking mode and whether tablespaces are dumped. Test the exact command using a dedicated least-privileged account; do not routinely use root.

Run the Common mysqldump Commands

# One database; output includes CREATE/USE for school
mysqldump --login-path=backup \
  --databases school --result-file=school.sql

# One table from one database
mysqldump --login-path=backup \
  school students --result-file=students.sql

# Two databases
mysqldump --login-path=backup \
  --databases school library --result-file=two_databases.sql

# All databases plus non-default stored objects
mysqldump --login-path=backup \
  --all-databases --routines --events --triggers \
  --result-file=all_databases.sql

With the first command form, the database name is an argument but the output does not necessarily create/select it. --databases treats following names as databases and writes database-level statements, which changes the restore command. Use --result-file to make the output location explicit.

Create a Consistent InnoDB Dump

mysqldump --login-path=backup \
  --single-transaction --quick \
  --routines --events --triggers \
  --databases school \
  --result-file=school_consistent.sql

--single-transaction issues a transaction isolation sequence that lets transactional tables such as InnoDB be read from a consistent snapshot. --quick retrieves table rows one at a time rather than buffering each entire table in client memory. This combination is a strong default for an InnoDB-focused logical dump.

• Nontransactional tables such as MyISAM can still change during the dump.
• Do not run ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE or TRUNCATE TABLE against dumped tables while it runs.
• Long snapshot reads can retain old row versions and increase storage pressure. Monitor production.
• A consistent snapshot does not replace binary logs for a tighter point-in-time RPO.

If a mixed-engine dump needs a single lock-based boundary, plan the controlled write pause and required privileges explicitly. Measure impact on a staging copy before production.

Select Schema, Data and Stored Objects

# Definitions only: tables and other selected definitions, no rows
mysqldump --login-path=backup \
  --no-data --databases school --result-file=school_schema.sql

# Rows only: no CREATE TABLE statements
mysqldump --login-path=backup \
  --no-create-info school --result-file=school_data.sql

# Include recovery-relevant stored objects explicitly
mysqldump --login-path=backup \
  --routines --events --triggers \
  --databases school --result-file=school_objects.sql

Definition-only and data-only files are useful for review or phased migration, but restore order and foreign keys must be planned. Do not edit a dump casually; it is executable SQL and small changes can affect object ownership or data.

Dump Selected Tables or Rows

# Selected tables
mysqldump --login-path=backup school \
  students courses --result-file=academic_tables.sql

# Exclude one table from a database dump
mysqldump --login-path=backup \
  --ignore-table=school.audit_archive \
  school --result-file=school_without_archive.sql

# Export rows that match a server-side WHERE condition
mysqldump --login-path=backup school orders \
  --where="order_date >= '2026-08-01'" \
  --result-file=recent_orders.sql

--where is useful for controlled extraction, not an automatic relational subset. Related parent/child rows, constraints and application invariants can be missing. Quote it for the operating shell and test the predicate with SELECT first.

For compressed storage on Unix-like systems, stream deliberately and preserve pipeline failure status:

set -o pipefail
mysqldump --login-path=backup --single-transaction \
  --quick --databases school | gzip -c > school.sql.gz
statuses=("${PIPESTATUS[@]}")
test "${statuses[0]}" -eq 0 && test "${statuses[1]}" -eq 0

Use organization-approved encryption and checksum tools after creation. A compressed file that opens is still not a verified restore.

Restore and Verify the SQL Dump

Restore into an isolated compatible server first. A file created with --databases or --all-databases contains database selection statements:

mysql --login-path=restore < school_consistent.sql

# Interactive mysql client alternative
SOURCE /approved/backup/school_consistent.sql;

If the dump was made with mysqldump school students and lacks database selection, create/select the intended database deliberately:

mysql --login-path=restore -e \
  "CREATE DATABASE school_restore CHARACTER SET utf8mb4;"
mysql --login-path=restore school_restore < students.sql

1. Check dump and restore exit codes; capture stderr.
2. Verify file byte size and checksum against the manifest.
3. Review warnings, SQL modes, character set and server-version compatibility.
4. Compare table/object inventory, row counts and critical business aggregates.
5. Verify views, triggers, routines and events; keep restored events disabled until approved.
6. Run application smoke tests with external side effects blocked.
7. Record achieved restore duration and recovery point.

Handle Scale, Replication and GTID Deliberately

Benchmark both export and import on representative data. Logical restore may spend significant time recreating indexes and constraints. For very large databases or strict RTOs, compare MySQL Shell dump/loading utilities and supported physical backup tools rather than assuming one SQL file will scale.

• Schedule around DDL and monitor disk, I/O, replica lag and snapshot history.
• Use separate destination storage so a full filesystem cannot crash the database.
• Split by database/table only when the recovery runbook preserves dependencies.
• Keep binary logs for point-in-time recovery after the full dump.

On GTID-enabled environments, --set-gtid-purged=AUTO|ON|OFF|COMMENTED affects whether GTID state is written into the dump. The correct choice depends on whether the target is a new standalone server, migration target or replication topology. Do not copy an option from the internet: follow the DBA-approved topology runbook and validate on a clone.

Continue with the complete backup and restore plan, protect the dump account through least privilege, and review restored scheduled events before enabling traffic.

Official References

• MySQL 8.4: mysqldump
• MySQL 8.4: Reloading SQL-Format Backups
• MySQL 8.4: Dumping Definitions and Data Separately

Command forms, object options, transaction limits and reload behavior were checked against the official MySQL manual. Confirm options with mysqldump --help for the deployed client version and test against a compatible isolated server.