mysqldump

Create a backup (user will be prompted for a password)

$ mysqldump --user [user] --password [database_name] --result-file=[path/to/file.sql]

$ mysqldump --user [user] --password [database_name] [table_name] > [path/to/file.sql]

$ mysqldump --user [user] --password --all-databases > [path/to/file.sql]

$ mysqldump --host=[ip_or_hostname] --user [user] --password --all-databases > [path/to/file.sql]

mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name1 [db_name2 ...]
mysqldump [options] --all-databases

--add-drop-database
    Prepend DROP DATABASE to each CREATE DATABASE statement.

--add-drop-table
    Include DROP TABLE before each CREATE TABLE (default).

--add-drop-trigger
    Include DROP TRIGGER before each CREATE TRIGGER.

--all-databases
    Dump all databases (ignores db_name args).

--all-tablespaces
    Dump all tablespaces.

--allow-keywords
    Allow keywords as table/column names without quoting.

--apply-slave-statements
    Include slave statements for replication.

--bind-address
    Bind to specified network interface.

--character-sets-dir
    Directory of character sets.

--comments
    Include comments (default).

--compact
    Compact extended INSERT format.

--compatible
    Make output compatible with specified mode (e.g., mysql323).

--complete-insert
    Use complete INSERT statements with column names.

--compress
    Compress data (client-server).

--create-options
    Include CREATE TABLE options (default).

--databases
    Dump listed databases (use with db names).

--debug
    Debug logging.

--debug-check
    Print debug info at exit.

--debug-info
    Include debug info and memory usage.

--default-auth
    Default authentication plugin.

--default-character-set
    Set default charset.

--defaults-extra-file
    Read options from file.

--defaults-file
    Read options from file (override others).

--defaults-group-suffix
    Group suffix for options.

--delayed-insert
    Use DELAYED INSERT (deprecated).

--disable-keys
    Disable keys before INSERT, enable after.

--dump-date
    Include dump date (default).

--dump-slave
    Dump slave replication info.

--enable-cleartext-plugin
    Enable cleartext plugin.

--events
    Include events (default).

--extended-insert
    Use multi-row INSERT (default).

--fields-enclosed-by
    Fields enclosed by string (CSV mode).

--fields-escaped-by
    Fields escaped by char (CSV).

--fields-terminated-by
    Fields terminated by string (CSV).

--fields-optionally-enclosed-by
    Optionally enclose fields (CSV).

--flush-logs
    Flush logs before dump.

--force
    Continue even on errors.

--gtid
    Dump GTID sets.

--hex-blob
    Dump binary columns in hex.

--host
    Connect to host.

--ignore-table
    Ignore specified table.

--include-master-host-port
    Include master host/port in dump.

--insert-ignore
    Use INSERT IGNORE.

--lock-all-tables
    Lock all tables across databases.

--lock-tables
    Lock each table (default).

--log-error
    Log errors to file.

--login-path
    Read login path from mysql_config_editor.

--master-data
    Dump CHANGE MASTER statement.

--max_allowed_packet
    Max packet size.

--net_buffer_length
    Network buffer length.

--no-autocommit
    Enclose in AUTOCOMMIT.

--no-backslash-escapes
    Use ANSI quotes.

--no-data
    Dump no data (structure only).

--no-set-names
    Don't SET NAMES.

--opt
    Optimize: extended-insert, quick, etc. (default).

--order-by-primary
    Order by primary key.

--password
    Connect password.

--pipe
    Use named pipe.

--port
    TCP/IP port.

--protocol
    Connection protocol.

--quick
    One row per INSERT.

--quote-names
    Quote identifiers (default).

--replace
    Use REPLACE instead of INSERT.

--routines
    Dump stored procedures/functions.

--single-transaction
    Consistent snapshot for InnoDB.

--skip-add-drop-table
    Omit DROP TABLE.

--skip-add-locks
    Omit LOCK TABLES.

--skip-comments
    Omit comments.

--skip-dump-date
    Omit dump date.

--skip-opt
    Skip --opt optimizations.

--skip-quick
    Don't use quick INSERT.

--skip-set-charset
    Omit SET CHARSET.

--skip-triggers
    Don't dump triggers.

--socket
    Unix socket file.

--ssl-ca
    SSL CA file.

--ssl-cert
    SSL certificate file.

--ssl-cipher
    SSL cipher list.

--ssl-key
    SSL key file.

--ssl-mode
    SSL mode.

--ssl-verify-server-cert
    Verify server cert.

--tab
    Dump CREATE TABLE and tab-separated data.

--tables
    Override db_name as tables.

--triggers
    Dump triggers (default).

--tz-utc
    Set TIME_ZONE='+00:00'.

--user
    MySQL username.

--verbose
    Verbose output.

--version
    Output version info.

--where
    Dump rows matching condition.

-h, --host=...
    Short form for --host.

-p, --password[=...]
    Short form for password.

-P, --port=...
    Short form for port.

-u, --user=...
    Short form for user.

mysqldump is a command-line client program included with MySQL distributions for creating logical backups of MySQL/MariaDB databases. It generates a set of SQL statements that can recreate the original database structure, data, and objects like views, stored procedures, functions, triggers, and events from a dump file.

Primarily used for backups, database migrations, replication setup, and testing. It supports dumping the entire server (--all-databases), specific databases, tables, or routines. For consistency, use --single-transaction with InnoDB (read-consistent snapshot without locking) or --lock-tables (default) for MyISAM. Output can be compressed via pipes to gzip.

Limitations include no support for physical backups (use Percona XtraBackup for that). Large databases may take time and require sufficient disk space. It handles character sets, delimiters, and quotes properly. Restoring requires mysql client or source command. Ideal for development/staging copies but pair with binary logs for point-in-time recovery in production.

Avoid --password on command line (visible in process list); use config files or -p prompt. Large dumps can lock tables long (use --single-transaction for InnoDB). No built-in compression (pipe to gzip). Partial dumps miss dependencies (views/routines). Binary data may need --hex-blob. Not for high-traffic production without replication setup.

Single DB: mysqldump -u root -p dbname > backup.sql
Full server: mysqldump -u root -p --all-databases | gzip > full.sql.gz
Consistent InnoDB: mysqldump -u root -p --single-transaction --routines --triggers dbname > backup.sql
Structure only: mysqldump -u root -p --no-data dbname > schema.sql

mysql -u root -p dbname < backup.sql
Or: mysql -u root -p < full.sql

0: success
1: usage error or DB access denied
2+: dump failed (warnings/errors)

Developed by MySQL AB as part of MySQL 3.23 (2001). Evolved with MySQL 5.0+ for routines/triggers, 5.1 for events/partitioning, 5.6/5.7 for GTID/SSL, 8.0 for roles/set-charset changes. Maintained in MariaDB as mariadb-dump since 5.2 (2009). Widely used for backups pre-XtraBackup era.

mysql(1), mysqld(8), mariadb-dump(1), percona-xtrabackup(1)