The mysqldump client is a utility that performs logical backups, producing a set of SQL statements that can be run to reproduce the original schema objects, table data, or both. It dumps one or more MySQL database for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.
# To create a backup (user will be prompted for a password):
mysqldump --user <user> --password <database_name> --result-file=<path/to/file.sql>
# To backup a specific table redirecting the output to a file (user will be prompted for a password):
mysqldump --user <user> --password <database_name> <table_name> > <path/to/file.sql>
# To backup all databases redirecting the output to a file (user will be prompted for a password):
mysqldump --user <user> --password --all-databases > <path/to/file.sql>
# To backup all databases from a remote host, redirecting the output to a file (user will be prompted for a password):
mysqldump --host=<ip_or_hostname> --user <user> --password --all-databases > <path/to/file.sql>
# ---
# To dump a database to a file (Note that your password will appear in your command history!):
mysqldump -uusername -ppassword the-database > db.sql
# To dump a database to a file:
mysqldump -uusername -p the-database > db.sql
# To dump a database to a .tgz file (Note that your password will appear in your command history!):
mysqldump -uusername -ppassword the-database | gzip -9 > db.sql
# To dump a database to a .tgz file:
mysqldump -uusername -p the-database | gzip -9 > db.sql
# To dump all databases to a file (Note that your password will appear in your command history!):
mysqldump -uusername -ppassword --all-databases > all-databases.sql
# To dump all databases to a file:
mysqldump -uusername -p --all-databases > all-databases.sql
# To export the database structure only:
mysqldump --no-data -uusername -p the-database > dump_file
# To export the database data only:
mysqldump --no-create-info -uusername -p the-database > dump_file