mysqldump is a command-line utility provided by MySQL to create logical backups of databases. Instead of copying raw data files, it exports the database schema and data as SQL statements that can be executed later to recreate the database.
It is commonly used for backups, migrations between servers, version upgrades, and disaster recovery scenarios.
The mysqldump utility connects to a running MySQL server using authentication credentials. It reads metadata and data from system tables and generates SQL commands such as CREATE DATABASE, CREATE TABLE, and INSERT.
Because the output is plain SQL, it can be edited, version-controlled, or partially restored if required.
-- Basic mysqldump syntax
mysqldump -u username -p database_name > backup.sql
This command creates a full backup of one database including both schema and data.
-- Backup a single MySQL database
mysqldump -u root -p mydatabase > mydatabase_backup.sql
Useful for full server backups before upgrades or migrations.
-- Backup all databases from the server
mysqldump -u root -p --all-databases > alldb_backup.sql
This option is helpful when only a subset of tables needs to be backed up.
-- Backup selected tables from a database
mysqldump -u root -p mydatabase users orders > tables_backup.sql
Creates a backup without table data, useful for schema replication.
-- Backup only table structure (no data)
mysqldump -u root -p --no-data mydatabase > structure_only.sql
Exports only table data without CREATE TABLE statements.
-- Backup only table data
mysqldump -u root -p --no-create-info mydatabase > data_only.sql
The resulting SQL file typically contains:
CREATE DATABASE statementsUSE database_nameCREATE TABLE definitionsINSERT INTO statements for dataWhen executed using the MySQL client, these commands recreate the database exactly as it existed at the time of backup.