mysqldump Command Builder

mysqldump in depth: consistent backups, streaming, and restore strategy

mysqldump is the canonical logical-backup utility shipped with MySQL and MariaDB. Logical here means it produces a plain SQL script — a sequence of CREATE TABLE, INSERT, and (optionally) routine definitions — that any compatible server can replay. The general shape is mysqldump [options] db_name [tables] > out.sql. It is portable, human-readable, easy to grep, and works across major versions; it is also single-threaded and slow on multi-terabyte datasets, which is why production shops eventually pair it with physical backup tools such as Percona XtraBackup or mydumper.

Connection and selection flags

• -u user — username; -p prompts for the password (never put it inline in shell history).
• -h host, -P port — remote server target.
• --all-databases — every schema on the server, including system tables.
• --databases db1 db2 — multiple specific schemas (includes CREATE DATABASE).
• db_name table1 table2 — single schema, specific tables.
• --ignore-table=db.table — exclude one or more tables (repeatable).

Consistency: the flags that matter in production

A naive mysqldump against a live server can produce an inconsistent snapshot or lock writes for the entire run. The critical flags are:

--single-transaction   InnoDB: dump inside one REPEATABLE READ tx,
                       no table locks, consistent snapshot. Required
                       on any production InnoDB database.
--lock-tables          MyISAM/Aria: lock each DB while it dumps.
--master-data=2        Write the binlog position/GTID as a comment
                       (useful for setting up a replica from the dump).
--set-gtid-purged=ON   Emit SET @@GLOBAL.GTID_PURGED in the output;
                       use OFF when restoring into a new GTID universe.
--flush-logs           Rotate the binlog before dumping.
--quick                Stream rows instead of buffering them in RAM
                       (default in modern versions, harmless to add).

Rule of thumb: on InnoDB use --single-transaction --quick --routines --triggers --events. On MyISAM or mixed-engine databases use --lock-tables (and accept the write pause).

Schema-only, data-only, and format tweaks

• --no-data — structure only (schema migration starter).
• --no-create-info — data only (refresh existing tables).
• --routines — include stored procedures and functions.
• --triggers — include triggers (on by default).
• --events — include scheduled events.
• --add-drop-database, --add-drop-table — emit DROP IF EXISTS before each CREATE.
• --default-character-set=utf8mb4 — avoid mojibake; match the live database.
• --hex-blob — encode binary as hex literals (safer for blobs).
• --compress — compress the wire protocol (helps over WAN).

Worked examples

# Standard production-safe dump, gzip on the fly
mysqldump -u backup -p \
  --single-transaction --quick --routines --triggers --events \
  --default-character-set=utf8mb4 \
  mydb | gzip > mydb-$(date +%F).sql.gz

# Stream straight into S3 without a local file
mysqldump -u backup -p --single-transaction mydb \
  | gzip | aws s3 cp - s3://backups/mydb.sql.gz

# Schema only, ready to seed a staging environment
mysqldump -u root -p --no-data mydb > schema.sql

# Restore: just pipe the SQL back into mysql
gunzip < mydb.sql.gz | mysql -u root -p mydb

# Resume replication from the dumped position
mysqldump --master-data=2 --single-transaction --all-databases > full.sql

Performance, encryption, and cloud variants

mysqldump is single-threaded; for hundreds of gigabytes consider mydumper (parallel logical dump, faster restore via myloader), Percona XtraBackup (physical hot backup, the only sane choice in the multi-terabyte range), or MySQL Enterprise Backup. Always encrypt backups at rest — pipe the output through openssl enc -aes-256-cbc or gpg, or rely on the storage layer (KMS-encrypted S3, LUKS volumes).

Managed services bundle their own equivalents: AWS RDS automated snapshots plus on-demand logical exports, Azure Database for MySQL backups, GCP Cloud SQL export to a bucket. They still ultimately call mysqldump or a binary snapshot under the hood — knowing the flags helps you reason about consistency guarantees.

FAQ

Can I take a hot backup without blocking writes? Yes — on InnoDB, --single-transaction opens a consistent REPEATABLE READ snapshot and lets writes continue. Any DDL during the dump (ALTER TABLE, CREATE TABLE) breaks the consistency guarantee.

How do I dump a single table? mysqldump db_name table_name > t.sql — list tables after the database name. Combine with --where='id>1000' to dump a subset of rows.

Can I do a partial restore from a big dump? Plain SQL is grep-able: sed -n '/^-- Table structure for table .users.$/,/^-- Table structure/p' dump.sql. For routine partial restores, prefer separate per-table dumps from the start.

How long does a restore take? Rule of thumb: a 10 GB plain-SQL dump restores in roughly 30–60 min on SSD, mostly bottlenecked by single-threaded INSERT replay. Speed it up by disabling foreign-key checks (SET FOREIGN_KEY_CHECKS=0; at the top), bumping innodb_buffer_pool_size, and using --extended-insert.

Is the dump portable across versions? Yes, with caveats. mysqldump from MySQL 8 can read 5.7 servers, and a dump from 5.7 generally loads into 8 (watch out for reserved words and authentication plugin changes). Restoring an 8.0 dump into 5.7 may fail on syntax such as WINDOW clauses.