mysqldump(1)
mysqldump creates logical backups of MySQL databases by generating SQL statements to recreate tables and data.
Synopsis
mysqldump [OPTIONS] [--databases DB1 [DB2 ...] | --all-databases | [DB_NAME [TABLE_NAME ...]]]Description
mysqldump is a client utility that produces a logical backup of a MySQL database. It generates SQL statements that can recreate the database structure and populate it with data. The output can be piped to another MySQL server to restore the database, or saved to a file for archival purposes.
By default, mysqldump locks tables during the dump (unless using --single-transaction for InnoDB), and outputs to stdout. It can dump entire databases, specific tables, or all databases on a server.
Common options
| Flag | What it does |
|---|---|
--all-databases | dump all databases; same as --databases with all database names |
--databases | treat all arguments as database names; dump named databases and their tables |
--single-transaction | use BEGIN/COMMIT for consistent snapshot; safe for InnoDB without locking |
-u, --user=USER | MySQL user for authentication |
-p, --password[=PASSWORD] | prompt for password or use provided password (no space if inline) |
-h, --host=HOST | MySQL server host; defaults to localhost |
--no-data | dump only table structures, omit data rows |
--no-create-info | do not generate CREATE TABLE statements, only INSERT data |
-l, --lock-tables | lock all tables before dumping; default for MyISAM |
--quick | retrieve rows one at a time instead of buffering; useful for large tables |
--compress | compress all information sent between client and server |
--routines | include stored procedures and functions in the dump |
Examples
dump the entire 'mydb' database to a file; prompts for password
mysqldump -u root -p mydb > mydb_backup.sqldump two databases with consistent snapshot for InnoDB without table locks
mysqldump -u root -p --single-transaction --databases db1 db2 > dbs_backup.sqldump a single table and pipe directly to remote MySQL server for restore
mysqldump -u root -p mydb mytable | mysql -u root -p -h remote.host mydbbackup all databases with quick retrieval mode for better performance
mysqldump -u root -p --all-databases --quick > full_backup.sqldump only the table structure without any data rows
mysqldump -u root -p mydb --no-data > schema_only.sqldump database including stored procedures and trigger definitions
mysqldump -u root -p mydb --routines --triggers > with_objects.sqlcreate a compressed backup using server compression and gzip for InnoDB database
mysqldump -u backup -p --single-transaction --compress mydb | gzip > backup.sql.gz