mysqldump

Dump database

$ mysqldump -u [user] -p [database] > [backup.sql]

$ mysqldump -u [user] -p [database] [table1] [table2] > [backup.sql]

$ mysqldump -u [user] -p --all-databases > [all.sql]

$ mysqldump -u [user] -p [database] | gzip > [backup.sql.gz]

$ mysqldump -u [user] -p --no-data [database] > [schema.sql]

$ mysqldump -u [user] -p --no-create-info [database] > [data.sql]

$ mysqldump -u [user] -p --routines --triggers [database] > [backup.sql]

$ mysqldump -u [user] -p --single-transaction --master-data [database] > [backup.sql]

mysqldump [-u user] [-p] [--all-databases] [--single-transaction] [options] [database] [tables]

mysqldump creates logical backups of MySQL databases. Output is SQL statements that can recreate the database structure and data when executed.
The --single-transaction option provides consistent backups for InnoDB tables without locking. It starts a transaction and dumps data at that point in time, allowing other operations to continue.
For MyISAM or mixed storage engines, --lock-tables prevents writes during dump. This ensures consistency but blocks writes.
Routines (stored procedures, functions) and events are not included by default. Use --routines and --events explicitly to back up these objects.
The --master-data option records binary log coordinates, essential for setting up replication slaves or point-in-time recovery.
Output is plain SQL text, easily compressed with gzip. For very large databases, consider mysqlpump (parallel) or physical backup tools like Percona XtraBackup.

-u USER, --user USER

MySQL username.

Prompt for or specify password.

Server hostname.

Server port.

Dump all databases.

Dump multiple named databases.

Don't dump table data.

Don't dump CREATE TABLE.

Consistent snapshot for InnoDB.

Include stored procedures/functions.

Include triggers (default on).

Include events.

Add DROP TABLE before CREATE.

Add DROP DATABASE before CREATE.

Include binary log position.

Don't buffer, write directly.

Lock tables during dump.

Not suitable for very large databases - consider physical backups. Memory usage can be high without --quick. Restore time can be long. Character set issues possible. Binary data encoding in SQL. Locking may affect production.

mysqldump has been part of MySQL since early versions, developed at MySQL AB and later Oracle. It remains the standard logical backup tool for MySQL and MariaDB. While physical backup tools are preferred for large databases, mysqldump remains popular for its simplicity and portability.

mysql(1), mysqlpump(1), mariadb-dump(1), pg_dump(1)